Excel VBA Indentation and Auto Indent

What is Excel VBA Indentation?

Indentation represents some spaces at the beginning of a new row of sentence. Indentation is not a word reserved for VBA, it is a word that also appears in Microsoft Word, Excel spreadsheet and even Powerpoint. Indentation is also used for all kinds of computer programming.

In VBA coding, indentation is not mandatory, which means it does not cause compile error if you miss it, but it is definitely a good practice for you or for others to follow your code easily. There is no black and white rule for what code needs to be indented, but there are generally acceptable rules.

Why do you need Excel VBA Indentation?

I will show an example below for you to understand more easily. The below code has no indentation.

Public Sub test()
Dim total As Integer
x = 124
If Len(x) = 3 Then
For i = 1 To 3
total = total + Mid(x, i, 1)
Next i
If total = 7 Then
MsgBox ("String length is 3 and total is 7")
Else
MsgBox ("String length is 3")
End If
Else
MsgBox ("String length is not 3")
End If
End Sub

Now with indentation

Public Sub test()
    Dim total As Integer
    x = 124
    If Len(x) = 3 Then
        For i = 1 To 3
            total = total + Mid(x, i, 1)
        Next i
        If total = 7 Then
            MsgBox ("String length is 3 and total is 7")
        Else
            MsgBox ("String length is 3")
        End If
    Else
        MsgBox ("String length is not 3")
    End If
End Sub

The first code is difficult to read because it looks like a mess, all codes combine together.

The second code is easier because codes are in different layers.

Rules of Excel VBA Indentation

There are some generally acceptable rules for indentation

1) For each set of code that has an starting and ending keyword such as IF…End If, indent the lines of code between. Use “Tab” key for indentation, Shift+Tab to undo the Tab effect.

2) Do not need to indent code that has starting keyword but no ending keyword in separate line, such as Dim xxx As Integer

2) Double line spacing is optional, just use it if it makes you easier to read

3) Do not worry about spacing within each line of code, it will be adjusted automatically

Example 1

Indent the codes between Sub and End Sub

Public Sub test()
    Dim total As Integer
End Sub

Example 2

Indent the codes between If and End If

Public Sub test()
    Dim total as Integer
    x = 124
    If Len(x) = 3 Then
        MsgBox("String length is 3")
    End If
End Sub

Example 3

Indent the codes between For /Next and nested IF / Else / End IF

Just ignore what the code means, focus on the indentation.

Public Sub test()
    Dim total As Integer
    x = 124
    If Len(x) = 3 Then
        For i = 1 To 3
            total = total + Mid(x, i, 1)
        Next i
        If total = 7 Then
            MsgBox ("String length is 3 and total is 7")
        Else
            MsgBox ("String length is 3")
        End If
    Else
        MsgBox ("String length is not 3")
    End If
End Sub

VBE Option – Auto Indent

VBE (Visual Basic Editor) has an option called Auto Indent, but it does not automatically indent all lines of code. Auto Indent functions when you press Enter key to start a new line, if the code before you press Enter has indentation, the new line will begin at the position of the previous indentation. If you don’t turn on the Auto Indent function, the next line will start on the left.

In VBE, navigate to Tools > Options >Editor Tab

Check the box Auto Indent to enable the option, you can also specify Tab width, the default value is 4, you can leave it as is.

auto_indent

Outbound References

http://mrbool.com/importance-of-code-indentation/29079

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *