Commenting Out Lines:
In coding a recurring problem is that the code made sense to you when you wrote it. But will it make sense to anyone else? Or will it even make sense to you once a year or two has gone by? Chances are it won’t if the code is even a little complicated. This is a case of making good documentation, and there is saying about that which goes: You should document as if the person who will have to maintain your code is a homicidal maniac who has your home address. That is why you want to insert comments into your code that explain what each section is doing. Since the comments are in the code itself, they cannot get separated, as might happen with a separate document. Comments are lines within the code that will be ignored when the code is executed. And in VBA there are a couple of ways to do this. First, you can begin a line with an apostrophe, which will mark the line as non-executable. Or, if you prefer to be a little clearer, begin the line with REM (for Remark), which will also mark the line as a comment.This is also useful if you want to try leaving out some lines when you are executing the code, such as when you are debugging a script. Just comment out the line while you execute, and that line will be skipped. Later, you can remove the apostrophe or the REM at the beginning to uncomment the line and make it executable again.
A breakpoint is a place in the script where the VBA will stop executing the code. In a longer script, you may have sections that you know are executing as designed, and it is time-consuming to step through those sections. By putting in a breakpoint just before a problem area, you can execute everything up to that point by pressing F5. Then it will stop at the breakpoint, and you can step through line by line from that point by pressing F8.You can toggle breakpoints on or off by selecting a line of executable code (not a comment), right-clicking, and selecting Toggle->Breakpoint. When you have set a breakpoint that line of code will be shaded red. Note that breakpoints are not permanent parts of your code! They exist only during the session you are editing the code. This makes sense when you consider that once you have finished editing you don’t really want your script to stop midway through executing.
Use the Immediate Window:
Recall that when you open the VBA Editor there is a window available called the Immediate Window. This a window where you can enter lines of code and have them executed as you enter them. Clearly this can be very useful in checking to see how a statement will execute, and if it does not execute here you can start looking for the syntax errors or whatever else might have caused the problem.
Avoid long scripts:
You may be tempted to to write a big script that will solve several problems all at once, but this should be resisted. A better practice is to modularize your code by writing short scripts that solve one problem. This has several benefits. First, finding and fixing problems in short scripts is a lot easier than doing that for long monolithic scripts. Second, your modules become a library you can call upon in future scripts if you need similar functionality for another problem. Third, by creating simple scripts to solve individual problems, you can move these solutions into production in an iterative manner that is more like Agile development and less like traditional waterfall methodology that makes users wait for a giant software solution before they see any benefit. And of course, like Agile you get the added benefit of feedback on your script that can help you to better meet your users needs. And it can result in slimmer code since modules can be called as needed instead of duplicated each time.
Use Else as Alert:
Some of the most useful coding tools in are the If…Then and Select statements. But you can get into trouble if you haven’t included the Else option. You may think that the set of possible values is already completely determined, but experience shows that people have been wrong about that. Adding the Else option accounts for any value you have not anticipated, and can be paired with an error statement that alerts you to a problem. This can save you time when you need to debug.
This little gem causes the value of a variable to be printed in the Immediate Window as your code is running. This is done by inserting a line of code that reads Debug.print xxxxx, where xxxxx is the variable name. This could be inserted in the middle of a For…Next loop, for instance, where it comes after the For statement and before the Next statement. One nice feature of this is that you get immediate results without breaking the execution of your code. You can execute the script and watch what gets printed in the Immediate Window. One thing to keep in mind is that this is a permanent part of the code, so when you have finished the debugging you should remove all of your Debug.print statements.
Anything to Declare?:
This is the canonical question asked by Customs agents when you re-enter from a trip to another country, but it is also useful in coding. As you write your code, you will be using variables to store and manipulate data values. VBA will allow you to do this in an ad hoc manner by creating Implicit Variables. These are created as you code when you assign a name to some data and use that name in a procedure. But this can cause problems. In particular, because VBA knows nothing about the variable it will always give it a variable type of Variant, and it will use more resources dealing with it. To avoid this, it is a best practice to declare your variables explicitly. This lets you select the variable type, scope, lifetime, etc. You can enforce this good practice by configuring VBA to require explicit declarations. To do this, in the VBA Editor go to Tools –>Options, and place a checkmark in the box for Require Variable Declaration.
From now on, VBA will not let you create variables implicitly, but will instead make you declare your variables properly.
Indent your code:
Consider the macro we created as an example in the previous tutorial:
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
This shows how indenting can make your code easier to read and to work with. There are several sections the begin with a With Selection statement, end with an End With statement, and all of the statements in between them are indented to show that they are part of a group. And this let us see quickly that we had two sections of With Selection.Font that had identical property statements so we could easily combine them into one section. In this case, we recorded the macro, so the code turned out like this because the VBA recorder did it automatically, but we can employ this practice in code we write. To indent to the right, select the lines you want to indent and press the TAB key. And if you change your mind later, just select the statements you want to move back to the left and press Shift+TAB.