How to Write Presentable VBA Code and Macros - VBA Tutorial

Good code presentation is an important part of writing VBA (or any other programming language for that matter), and will make your code easier to read, understand, and debug.

Contents:

1. Commenting

3. Code Indentations

3. Line Breaks

How to Write Presentable Code

How and Where to Include Comments in VBA

Commenting code well is one of most important steps to writing presentable code. Your comments should explain what the code is doing and why. This makes it easier for others (or even your future self) to understand the code and why it was written in a certain way.

In VBA, you can add a comment by starting a line with an apostrophe (') character. Anything on that line after the apostrophe will be treated as a comment and ignored by VBA when the code is executed. For example:

' This is a comment

When adding comments, it is important to keep them concise and to the point. Avoid adding long, detailed explanations of your code, as this can make the comments themselves difficult to read. Instead, focus on providing the key information that will help someone understand the code without having to read through it in detail.

It is also a good idea to use comments to break up your code into logical sections or blocks. This can help to make your code more organized and easier to read. For example:

' Calculate the result
result = 10 + 5

' Display the result 
MsgBox "The result is: " & result

In this example, the comments are used to divide the code into two sections: one for calculating the result, and another for displaying it. This makes it clear what each part of the code is doing and why.

How to Best Use Code Indentations in VBA

Indenting your code helps to visually organize it and make it easier to read. In VBA, you can indent your code by using the Tab key.

In addition to using the Tab key, you can also use the spacebar to add spaces for indentation. However, using the Tab key is generally considered to be a better practice because it ensures that your indentation is consistent across different editors and environments.

In general, lines of code that are part of a block should be indented. This includes lines of code inside loops, conditional statements, and subroutines or functions. Indenting these lines of code helps to visually organize the code and make it easier to read.

Here the green arrows represent where a tab is present:

Here is an example of properly indented code inside a loop:

Sub Example()
    For i = 1 To 10
    ' This line is indented because it is inside the loop
        Debug.Print i
    Next i
End Sub

On the other hand, lines of code that are not part of a block (e.g. standalone statements) should not be indented. This helps to clearly differentiate between individual statements and blocks of code.

Here is an example of code with proper indentation and line breaks:

Sub Example()

    ' This line is not indented because it is a standalone statement
    Debug.Print "Hello World"
    
    For i = 1 To 10
        ' This line is indented because it is inside the loop
        Debug.Print i
    Next i
    
End Sub

In summary, lines of code that are part of a block (such as loops and conditional statements) should be indented, while standalone statements should not be indented. This helps to improve the readability and organization of your code.

Where to put Line Breaks in VBA

In general, line breaks should be used to separate different sections of your code, as well as to break up long lines of code into smaller, more manageable chunks.

For example, you can use line breaks to separate the different parts of your code, such as subroutines or functions, loops, and conditional statements. This can help to make your code more organized and easier to read. For example:

Sub Example()

    For i = 1 To 10
        ' This is the loop
        
        If i = 5 Then
            ' This is the conditional statement
        End If
    Next i

    'This is the final part of the code
End Sub

In this code, line breaks are used to separate the different sections of the code. This makes it clear what each part of the code is doing and why, and makes it easier to read and understand.

In addition to separating different sections of your code, you can also use line breaks to break up long lines of code into smaller chunks. This can be useful when you have a long statement that doesn't fit on a single line, or when you want to make your code more readable by breaking it up into smaller pieces. For example:

Sub Example()
    
    result = "This is a long string that doesn't fit on a single line, " _
             & "so it is continued on the next line using the underscore."
             
End Sub

In this code, the long statement is broken up into multiple lines using the underscore character (_). This makes the code easier to read and understand, and makes it more manageable to work with.