Fixing Macros

Note: You may find it easier to follow if you download TitleBlock with the sample code.

The quickest way to get started with Macros is to record one using the Macro recorder. It comes included in Microsoft Word and Microsoft Excel, though not in Microsoft PowerPoint. We did a simple example of one for Word in our first tutorial, Beginning with VBA: Recording a Simple Macro in Word. And that is a simple enough process. Once you get used to the idea you will start to see more opportunities to use macros to make your workflow more efficient. But if you are at all like me (which I will assume for the purpose of these tutorials), you make mistakes. My typing is not perfect, and my fat fingers often hit keys other than the one I intended to hit. So how do we deal with that?

Technically, you could just ignore it as long as the macro works. Remember that when you “play back” the macro, it will run exactly as you recorded it. So if you made a mistake, then backspaced to get rid of it before resuming, your macro will do that every time you run it. And you may be fine with that, but I like to clean up my work when possible and make it presentable. That means fixing it. And as any experienced coder will tell you, one of the best ways to learn a coding language (which is what VBA is) is to read code and make changes. So I will now look at a few simple examples to show how this works.

First, a simple macro you might want to use in Excel. This will create a title block on a spreadsheet and format it appropriately:

Sub TitleBlock()
 '
 ' TitleBlock Macro
 ' Example of a simple formatting macro in Excel
 '

'
  Range("A1:E1").Select
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
  Selection.Merge
  With Selection.Font
    .Name = "Arial"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .Color = -13224394
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
  End With
  With Selection.Font
    .Name = "Arial"
    .Size = 14
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .Color = -13224394
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
  End With
  Selection.Font.Bold = True
  With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.599993896298105
    .PatternTintAndShade = 0
  End With
 End Sub

So, what is going on this code? It looks more complicated than it is. It starts with the notation Sub (for Sub Procedure) followed by the name of the macro, and I called it TitleBlock when I started the Macro recorder.  Remember that the macro as a whole is one sub-procedure here. Under that is the description I gave it: Example of a simple formatting macro in Excel. Following that is the first action recorded by the macro: I clicked on cell A1, held down the Shift key, and then clicked on cell E1. That is what gave me Range(“A1:E1”).Select. Following that is a list of properties for these cells I selected, but since I did not do anything to change these, you are seeing the defaults. This is in a section that begins With Selection and ends with End With. 

Then there is a command to merge the cells that were selected: Selection.Merge.  This happened when I clicked on the Merge & Center button. This turned my selection into one large merged cell instead of 5 cells. With this selected, I then went to the font button to select Arial as my font. The font family is one of 11 properties for font that I could have selected, and all of the other properties are default. This section again ends  with End With.  And that leads to a second section on font, because I then clicked on a Font Size to make it 14.  This change occurred in an identical block of Font properties. This happens because changing the Font family and changing the size were two separate actions, and each one is handled separately by the code. But there is no reason you could not combine them into one Font block that does both of these changes. Just go to the first With Selection.Font section and change the property .Size = 10 to read .Size = 14. then you can delete the second With Selection.Font section.

Following this is the command Selection.Font.Bold = True. This is what I get for clicking the Bold button. then the last With Selection.Interior section is where I clicked on the background color selector for my merged Title Block, and selected a light blue background color. This again closes with End With, and then finally the macro ends with End Sub.

Testing and VBA Editor Modes

We just looked at a sample macro and made a small fix to make the code smaller by combining two sections. This is a good way to get started in learning the code, but to get to the next level we need to talk about testing code. Suppose you have code that you have written that is not working for some reason. You would probably want to be able to zero in on the place where the code stopped working and get some indication of what is wrong. And that is quite possible. but a word of warning first: Never test a macro, or run an untested macro, in a file that has data you care about! That is an excellent way to introduce Mr. Foot to Mr. Bullet, i.e., to shoot yourself in the foot. All you really need to do is make a copy of the file first, like ImportantFile.Test.xlsm, and use that to create, test, and debug your macro. When it is working to your satisfaction, Export the macro from your test file and Import the macro to your working file. To Export, just right-click on the module in the Project Explorer and choose Export File from the context menu and save it on your hard drive. To Import the module, right-click the Project in the Project Explorer and select Import File.

The VBA editor is always in one of three modes:

1. Design Mode – This is what we have already been looking at, and it is how you write code or design a user form. this is where we do most of the work, in other words.
2. Execution Mode – This is entered by pressing F5, and lets you step through your code and execute it one line at a time by pressing F8. As long as everything is working you can keep pressing F8 to get to the next line.
3. Break Mode – If you have a bad line of code that cannot be executed, you halt on that line. Or you can set a Breakpoint to stop there.

To see how this works using our example macro, go to the line that reads Selection.Merge. This is perfectly valid, but if we change one letter, such as changing Merge to Marge, we will get an error because there is no function in Excel called Marge. (Evidently no one on the Excel team is a Simpsons fan.) If you then Save the macro, any attempt to execute it by pressing F5 will immediately highlight that line, and open an error window:

 

 

 

 

 

 

If you haven’t yet saved the macro you will be able to step through it by pressing F5, then F8 line-by-line, but when you get to this line you will halt, it will be highlighted, and the same error window will appear.

The error message says Excel does not support this property or method, and that makes sense since we know there is no such method as “Marge”.

Stepping through code line-by-line is great for small macros, but if you get a longer macro with many lines of code that can get tedious, so you might want to put in a Breakpoint. This tells the VBA editor to stop there when executing the code, and this way you can rapidly execute right up to that point and then stop for line-by-line execution where you suspect the error is. To set a Breakpoint, right-click on a line of executable code (i.e. not a comment), and in the context menu go to Toggle –> Breakpoint to set it. You will see this:

 

 

 

 

To remove it after your testing, just repeat the procedure since this is a toggle. Now, put your cursor at the top of the Macro, and press F5. The macro will execute very rapidly right up to this line, then stop and highlight the line. It doesn’t mean anything is wrong with the line, it just means you told the program to stop there. Now you can press F8 to step through the code line-by-line.