Testing Macros

by | Mar 16, 2021 | Macros, Programming, Spreadsheets, Step-by-step, Word Processing

The final step, before sharing a macro with others, is testing. Why? Well, would you buy a car without giving it a test drive? Of course not. Similarly, you need to know that your macro is going to work before you put it into production. This blog post covers how to test a macro. It is the fourth article in a series about creating macros in Microsoft Excel and/or Microsoft Word.  The previous three articles in the series are What is a macro?,  Plan your macro or else…, and How to record a macro.

The testing process includes the following steps:

Step 1 – Undo any actions made by the recorder

The first step in testing a macro is to undo any actions that were performed during the recording process. For example, if you recorded an Excel macro that inserted the column headings shown below, then before testing it you should remove those column headings. Otherwise, you would have no idea if the macro actually inserted the column headings.

Similarly, if you have a macro in Microsoft Word that insert a table of contents, prior to testing it, you should remove the table of contents that was created when you recorded the macro.

Another example: In Excel, if the macro changed column widths, then you should set the column widths back to what they were prior to recording the macro. The following image shows my sheet prior to running the macro.

Bottom line: any changes the macro made should be undone so your test can see if the macro will do all you expect it to do.

Step 2 – Place the cursor

Before running the macro, put your cursor in a location that is different from where it was when you recorded the macro. For example, when I recorded the Insert_Column_Heading Excel macro my cursor started out in cell A1. Then later, I tested it by running it with the cursor starting out in cell C4. As you can see from the image below, the macro has a bug. If I had run the macro with the cursor in A1, I would not have noticed the bug.

Similarly, if you recorded your Table of Contents Word macro with the cursor at the top of the document, you might test it by having the cursor in the middle of the document. This will allow you to see if the macro puts the Table of Contents in the correct location.

Step 3 – Run the macro

From the ribbon’s developer tab click the Macros button, see #1 in the image below. This will display a list of macros. You can also display a list of the macros by pressing Alt F8 on the keyboard.

If the macro you created is not listed, change the Macros in drop-drown menu to the location of the macro, see #2 in the image below. For example, if you recorded the macro into This Workbook, then it will not show if the drop-down menu is set to the Personal Macro Workbook.

Next, select the macro you wish to run and then click Run, see #s 3 and 4 in the image below.

Step 4 – Inspect the results

When the macro is finished, inspect the results. Did the macro do everything it was supposed to do? If not, you will need to fix it. Also check, did the macro do anything it was not supposed to do? If so, you will also need to fix that.

Step 5 – Inspect and edit the code

If you need to fix the macro you can either try to rerecord it (not recommended), or you can edit the code. To edit the code, repeat the steps you did for running the macro, but this time click Edit instead of Run.

The macro’s code will be displayed in the Visual Basic for Applications editor. From here you can read the code, and insert, delete and/or modify statements as needed.

Although understanding VBA code is beyond the scope of this blog post, here are a few things to note:

  • Every macro must begin with the keyword Sub followed by the name of the macro.
  • Every macro must end with the phrase End Sub.
  • Any statement that begins with an apostrophe is a comment.  The computer will ignore these lines, they are for us humans to read.  In the VBA editor the comments are easily identified by their green font. Generously adding comments to your code can be very helpful when you later need to modify the macro.
  • In Excel, the text .FormulaR1C1 = can be removed if the text after the equal sign is not an Excel formula.
  • In Excel, if one statement ends with .Select and the next statement begins with ActiveCell or with Selection, then the two lines can be combined into one statement by removing the .Select and the ActiveCell or Selection. This usually make the code much more direct and easier to understand.

The screen shot below shows the Insert_Column_Headings macro after it has been edited.

If you would like to learn more about the VBA programming language, consider signing up for the TechMentors Excel VBA Macro class.