When learning the VBA macro language from the very beginning, it is necessary to form the right coding habits to make it easy for both you and others to understand.
Suppose you have an idea for an interesting program and an understanding of how to put it into practice. But in order to keep this idea, you need to immediately consider whether it will be easy to understand the code in six months. After all, for such a long period, you can easily forget the algorithm of the written program. This is how the human brain works. This is necessary to be more cognitively flexible. But this mechanism in this case will interfere.
And if another person reads this code, he will have to explain for a long time how everything works. And this is if the author of the code himself understands it after such a long period of time. This takes a lot of time and effort.
Today we will look at such important concepts as comments, indents and line breaks, which will greatly increase the ease of code perception.
Comments
The easiest way to increase the readability of the code is to add comments that explain the meaning of one or another item of the algorithm. They are not executed by the interpreter, they do not affect the result of the program, but they are necessary for the programmer or the team to understand the meaning of the code.
To add a comment, you must specify an apostrophe (‘). In the editor, such a line is immediately underlined in green so that it can be easily and quickly distinguished from the main code. Let’s look at an example that shows how comments work.
‘Routine to find cells A1-A100 for the selected active sheet
‘or find the cell that contains the specified string
Sub Find_String(sFindText As String)
Dim i As Integer ‘Integer applied in ‘For’ loop
Dim iRowNumber As Integer ‘An integer representing the result’
iRowNumber = 0
‘Loop through cells A1-A100 until the text ‘sFindText’ is found
For i = 1 To 100
If Cells(i, 1).Value = sFindText Then
‘Finding the same content
‘Save the current line number and terminate the loop
iRowNumber = i
Exit For
End If
Next i
‘Window to inform who is running the macro
‘ that the cell was found. It also reports the line number.
If iRowNumber = 0 Then
MsgBox «String » & sFindText & » not found»
else
MsgBox «String » & sFindText & » found in cell A» & iRowNumber
End If
End Sub
Don’t worry if you can’t understand this code, as there are some topics you may not know yet. This example is provided just to demonstrate how comments work in real code.
Of course, sometimes you are too lazy to add comments, because in the moment you can understand everything. But it’s important to make an effort. A few minutes invested in improving the readability of the code will save a lot of time later.
Code indentation
Another option to increase the readability of the code is to add indents. You can see in the example above how they help to highlight individual blocks. In the future, you can add others, and it is immediately clear where its boundaries are.
Line breaks
This is another way to increase the readability of the code. To split one line into two, you must add an underscore before the break. This tells the interpreter that this line continues on the next line.
Here is an example that shows how easy it is to use breaks to simplify long lines in order to quickly understand what is happening. For example, consider this line.
If (index = 1 And sColor1 = «red») Or (index = 2 And sColor1 = «blue») Or (index = 3 And sColor1 = «green») Then
If you add line breaks, the appearance of the same code snippet will be 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 breaks into three lines, you can see all the conditions separately, and this allows you to quickly understand which ones are set for a particular conditional statement. This example shows how working on the appearance of the code can make it readable and avoid ridiculous bugs or errors that have to be fixed long and painfully.