Contents
When you started learning VBA coding, it is important to develop the right habits from the very beginning to better understand and use these codes.
Even if you understand what you are doing when you write code, you still need to program in such a way that you will not have problems when you return to this code after a long period of time. Or when someone else is working on your code. In this tutorial, we’ll go over what comments, indents, and line breaks are.
Comments
One of the most important habits when writing code is creating comments. Comments are notes in your code that act as reminders and clarifications for you or someone who will be working on the code.
Comments do not affect how the code works. VBA accepts any characters that are separated by an apostrophe “`” as a comment. In addition, the program automatically highlights comments in green. How it looks in code:
‘The Sub procedure should search cells A1-A100 on the current worksheet and find the value you are looking for’
Sub Find_String(sFindText As String)
Dim i As Integer ‘the number that is specified in the For loop’
Dim iRowNumber As Integer ‘Value to store result’
iRowNumber = 0
‘Loop through cells A1-A100 until ‘sFindText’ will not be found’
For i = 1 To 100
If Cells(i, 1).Value = sFindText Then
‘A match was found’
‘Keep the current line number and break the For loop’
iRowNumber = i
Exit For
End If
Next i
‘Display a message to tell the user if the value you were looking for was found’
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 don’t understand some of the values from the above example – everything will be explained later. The example simply shows the use of comments.
Using comments may seem like a lot of work, but it actually makes it much easier to work with the code later.
Indentation
This is another way to make code easier to read and understand. For example, in the example above, you can see the indentation in individual blocks of text – this helps to better understand the code and understand which command refers to what.
Line break
Line breaks are used to make long codes easier to read and understand. When working with VBA, to add a break, you need to put “_” after the space. This will tell VBA to wrap the following characters on a new line.
Below we will present an example that will depict the break of a long code into several short lines:
If (index = 1 And sColor1 = “red”) Or (index = 2 And sColor1 = “blue”) Or (index = 3 And sColor1 = “green”) Then
Now let’s add breaks:
If(index=1 And sColor1=”red”) Or_
(index=2 And sColor1=”blue”) Or_
(index = 3 And sColor1 = “green”) Then
When the “If” condition is broken down into a few short lines of code, things become much easier to read. The example shows how you can present your code in such a way that you or someone else can easily check it and find errors.