VBA Code Formatting | Basic VBA Code for Beginner | VBA Code Help | Simple VBA Code 2024

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 take care that, when you return to work six months later, you do not have to rack your brains trying to figure out what this code should do. An even more unpleasant situation is when someone else will continue your work on the code and will not be able to understand how it works.

This article focuses on comments, code indentation, and line breaks – the elements that make your code neat and understandable.

Comments in VBA

The most important thing to write neat and understandable code is to leave comments more often. Comments are lines in code that act as notes and help you figure out what actions a particular part of the code is doing.
Comments do not participate in the process of program execution and do not affect the result of the macro. Each line that begins with an apostrophe (‘) will be considered a comment in VBA. The VBA editor in Excel will highlight such a line in green font color so that at first glance it is clear that this is a comment that will not be executed.

The following demonstrates how a simple Sub procedure works using comments:

‘Sub procedure to view the range of cells A1-A100 of the active
‘sheet and search for a cell containing the string passed to the procedure

Sub Find_String (sFindText As String)
Dim i As Integer ‘variable of type Integer for’ For ‘loop
Dim iRowNumber As Integer ‘variable of type Integer to store the result
iRowNumber = 0
‘look sequentially through cells A1-A100 until the value’ sFindText ‘is found
For i = 1 To 100
If Cells (i, 1).Value = sFindText Then
‘found a match with the passed string
‘save the current line number and exit the loop
iRowNumber = i
Exit For
End If
Next i

‘a message in the pop-up window informs the user,
‘whether a line was found, and if found – 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

Do not be discouraged if some part of the code shown above was not understood – we will take a closer look at this topic later in the tutorial. The purpose of this example is to demonstrate how comments are used to explain each block of code.
Programmers are often too lazy to add detailed comments to their code, but believe me, the effort spent will pay off in abundance! A few minutes spent writing a clear comment can save you hours in the future.

Indenting VBA Code

Another trick to make your code more readable is to indent correctly. In the example above, you can see that the code inside the main Sub procedure is indented and then the indentation is increased for each nested code block. These increased indents help you understand where each individual block of code starts and ends.

Line breaks in VBA

Another way to make your code more readable and easier to work with is to hyphenate and break one long line of code into several short ones. In VBA, to break a line, you insert “_” (space + underscore) characters 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 long lines of code can be made much clearer and easier to read by using line breaks.

Look at this If statement :

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 If statement 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 If statement is split into four lines, then its constituent blocks with conditions are seen much more clearly. This example illustrates how neat layout can make your code more readable and result in fewer errors and confusion.