When starting to practice writing VBA code, it is very important to develop good coding habits from the very beginning so that later on the written code is easy to read and understand how it works.
In the process of writing code, a programmer can have a very clear idea of what kind of code he is writing and how this code should work. But you also need to make sure that when you return to work six months later, you don’t have to rack your brains trying to figure out what this code should do. An even more unpleasant situation is when someone else continues to work on your code and cannot understand how it works.
This article focuses on comments, code indentation, and line breaks, elements that make code neat and understandable.
Comments in VBA
The most important thing for writing clean and understandable code is to leave comments more often. Comments are lines in the code that act as notes and help you understand what actions a particular part of the code performs.
Comments do not participate in the process of program execution and do not affect the result of the macro. Each line that starts with an apostrophe (‘) will be considered a comment in VBA. The VBA editor in Excel will highlight such a line with a green font color so that at a glance it is clear that this is a comment that will not be executed.
The following shows how a simple procedure is explained using comments. Sub:
' Sub procedure for viewing the range of cells A1-A100 of the active ' sheet and searching for a cell containing the string passed to the procedure Sub Find_String(sFindText As String) Dim i As Integer storing the result iRowNumber = 0 ' sequentially look at cells A1-A100 until the value 'sFindText' is found and exit the loop iRowNumber = i Exit For End If Next i ' a pop-up message tells the user if a row was ' found, and if found, tells the row number If iRowNumber = 1 Then MsgBox "Row " & sFindText & " not found" Else MsgBox "String " & sFindText & " found in cell A" & iRowNumber End If End Sub
Don’t be discouraged if you don’t understand some part of the code shown above – we’ll cover this topic in more detail later in the tutorial. The purpose of this example is to demonstrate how comments explain each block of code.
Often programmers are too lazy to add detailed comments to their code, but believe me, the effort expended will justify itself in abundance! A few minutes spent writing an understandable comment can save you hours in the future.
Indentation in VBA code
Another technique that makes written code more readable is to properly indent. In the example above, you can see that the code inside the main procedure is indented. Sub and then the indentation increases for each nested block of code. Such increased indentation helps to understand where each individual block of code begins and ends.
Line breaks in VBA
Another way to make code more readable and easier to work with is to wrap and break one long line of code into several short ones. In VBA, to break a line, you need to insert the characters “_” (space + underscore) just before the line break. This tells the VBA compiler that the current line of code continues on the next line.
The following example demonstrates how line breaks can be used to make long lines of code much clearer and easier to read.
Look at this operator If:
If (index = 1 And sColor1 = "red") Or (index = 2 And sColor1 = "blue") Or (index = 3 And sColor1 = "green") Or (index = 4 And sColor1 = "brown") Then
Using line breaks, the same operator If can be written like this:
If (index = 1 And sColor1 = "red") Or _ (index = 2 And sColor1 = "blue") Or _ (index = 3 And sColor1 = "green") Or _ (index = 4 And sColor1 = "brown" ) Then
If the considered operator If is divided into four lines, then its constituent blocks with conditions are visible much more clearly. This example illustrates how neat typing can make code more readable and result in fewer errors and confusion.