When learning to produce VBA code, it is important, from the very start, that you adopt good code presentation habits, so that it is easy to read the code and understand how it works.
While you may, at the time you are writing the code, have a clear idea of what it does and how it works, you need to consider what the code will look like when you return to it in 6 months' time. Or even worse, what if someone else needs to work on your code and has to figure out how it works?
This page discusses comments, code indentation and line breaks all of which will assist in making your code clearer and easier to interpret.
The single most important practice for writing clear, decipherable code is to add frequent comments.
Comments are lines in your code which act as notes to yourself or others, to explain what the code means or what it is doing.
Comments are not executed during the running of the program, so have no impact on the result your macro. VBA considers any text that follows an apostraphe (') to be a comment and the Excel VBA editor highlights this text in green, so you can see, at a glance, that it is a comment and will not be executed.
See the example below, which shows comments used to clarify the details of a simple Sub procedure:
|
' Sub procedure to search cells A1-A100 of the current active
' sheet, and find the cell containing the supplied string
Sub Find_String(sFindText As String)
Dim i As Integer ' Integer used in 'For' loop
Dim iRowNumber As Integer ' Integer to store result
iRowNumber = 0
' Loop through cells A1-A100 until 'sFindText' is found
For i = 1 To 100
If Cells(i, 1).Value = sFindText Then
Next i
' A match has been found to the supplied string
End If
' Store the current row number and exit the 'For' Loop iRowNumber = i Exit For
' Pop up a message box to let the user know if the text
' string has been found, and if so, which row it appears on
If iRowNumber = 0 Then
MsgBox "String " & sFindText & " not found"
Else
MsgBox "String " & sFindText & " found in cell A" & iRowNumber
End If
|
Don't worry if you don't understand some of the code in the example above - this will be explained later in this tutorial. The example has been included simply to show how comments are used to explain each section of the code.
It is easy to get lazy about adding comments to your code, but it really is worth making the effort. The minutes invested in ensuring your code is well documented could save you hours of frustration in the long term.
Another way of assisting in making your code readable is by adding indentations to it. You can see how, in the example above, the code has been indented within the main Sub procedure and then indented further inside individual blocks of code. These indented sections enable you to easily see where each block of code starts and ends.
Your code can also be made more readable by inserting line breaks in the middle of long lines of code. In VBA, if you want to split a line up, you need to add a space followed by an underscore ( _) just before the line break. This tells the VBA compiler that the current line of code continues on the following line.
The following example shows how simple line breaks can be used to make long lines of code much easier to read and understand.
Consider the following 'If' statement:
By adding line breaks the same 'If' statement can be presented as follows:
|
If (index = 1 And sColor1 = "red") Or _ (index = 2 And sColor1 = "blue") Or _ (index = 3 And sColor1 = "green") Then |
When the 'If' statement is broken up over three lines, you can see the different conditions within the 'If' statement much more clearly. This example illustrates how presenting your code in a clear way can help you to produce readable code, which will help you to avoid introducing bugs or errors.