Question

discuss the purpose of using macros in an Excel worksheet. Include the following:

1) An example of an instance where a user would find the creation of a macro useful.

2) An example of an instance where a user would not find the creation of a macro useful.

Answer 1

A macro can be defined as the recording of a series of tasks. It’s the simplest form of automation – show a software program the steps you follow to get something done, and the software will follow along. When used right, macros can save you hours by automating simple, repetitive tasks.

1)
COMPARING STRINGS

It is not uncommon to compare strings in a macro. For instance, you may need to compare what a user typed with some pre-determined value. If you do this directly, you must take into consideration that the user may not have typed his (or her) string in the same way as you expected. Particularly vexing is the fact that the user may have mixed upper and lower case in their response.

The quickest and easiest way around this is to use either the UCase() or LCase() function on their input before you do the comparison. For instance, let's assume you prompt the user for the word "yes" to verify they want an action done. The following code will check the input, regardless of how the user typed it.

If LCase(sUserIn) = "yes" then bDoIt = True
The trick is make sure your test string is either all upper or all lower case, and then convert the user's input to that same case.

2)
Keep your macros small and specific to the tasks at hand. The bigger the macro, the slower it runs, especially if it’s required to perform many functions or calculate a lot of formulas in a large spreadsheet. Also, if you combine all the tasks into one long macro and it fails, it takes forever to locate the point of failure. If you run each macro separately, you can quickly review the results and verify accuracy.