VBA - Best Practices To Follow While Coding

Hello!!

I hope you remember our posts on VBA so far. In earlier posts, we have recorded macros & analysed the code behind them. Today, we are going to see few good practises that saves your time and make the code efficient. Let’s dig into them.

Indentation

Did you notice the code saved by Excel when it records a macro? For example, notice our MyFirstMacro code. You can see how various lines of code under With qualifier are indented.

With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

End With

You, while writing code should ideally follow this. Excel wouldn't complain if you don’t, but your code looks cool and tidy. If you have multiple With qualifiers or If statements, keeping track of where a qualifier is starting and where it is ending will become difficult without indentation. Look at the code snippet below.

Sub Test()

    If ActiveSheet.Name = "Sheet1" Then

        If ActiveCell.Value < 10 Then

            MsgBox ("You are in Sheet1 and cell value is less than 10")

        Else

            MsgBox ("You are in Sheet1 and cell value is not less than 10")

        End If

    Else

        MsgBox ("Select Sheet1")

    End If

End Sub

 And see this.

Sub Test1()
If ActiveSheet.Name = "Sheet1" Then
If ActiveCell.Value < 10 Then
MsgBox ("You are in Sheet1 and cell value is less than 10")
Else
MsgBox ("You are in Sheet1 and cell value is not less than 10")
End If
Else
MsgBox ("Select Sheet1")
End If
End Sub

Which one is looking cool? Pressing Tab key will add indent to your code, repeated press will add more indent. You can use Shift+Tab to outdent (to remove indent).

If you select Auto Indent option, VBA auto indents the next line as per indent of previous line. You can turn this option on and off by clicking on Tools > Options > Editor tab > check or clear Auto Indent. You will also have option to enter what width your indent should have.

Think of All Possible Errors

Prevention is better than cure, right? Think of the errors that can occur while your code is used. These errors can be because of version compatibility, incorrect usage of syntax etc. If you see the possibilities, you can categorize those under 3 types.

Syntax Errors

Syntax error is where your code itself having errors. For example, you misspelled object’s name, one example could be, Application object is entered as Applicatoin (i and o have changed places). There is plethora of other scenarios where you err while coding. VBA highlights syntax errors with red colour and often with an alert message.

You can enable or disable auto syntax check by clicking on Tools > Options > Editor tab > check or clear Auto Syntax Check.

Runtime Errors

These are the errors that can occur while executing your macro. You will have to think of all the possible run time errors while coding. For example, if you are writing a code that enters some value in a cell, you will have to anticipate a situation where the sheet is protected and you will have to write code to have a workaround. Another example is, if your code is using a newly introduced object which was not available in earlier version of Excel, executing your code in other machines which uses older version will throw errors.

For run time errors, Excel displays an alert and stops executing the macro unless you provide a workaround in your code.

Logical Errors

These errors occur if you fail to draft your code logically. Logical errors may not stop macro execution all the times but will give incorrect results. For example; if you were to enter some value in currently selected cell of Sheet1 but you used ActiveCell object to do this, your macro will enter the values in currently selected cell of currently active sheet which might not be Sheet1.

Test and Retest Your Code

As a good developer always do, test and retest your macro every time you do a change to your code. Your testing should include all possible scenarios you can think of. Use VBAs debugger tool extensively for this purpose. There is a whole menu dedicated to debugging in VBA (look at Debug menu). We will see about each option available in this menu but in one of future posts.

Use Recorder

We saw about recording a macro in one of our previous posts. Use this feature and you will for sure get a way around the code. Though recorded code is not perfect, at least you get an idea on what object can be used.

Use Comments

While analysing our recorded code in our previous post, we saw that comments can be entered to clearly mention what the code is trying to do for someone looking at the code that could be even for writer of the code. Looking at a code I wrote 6 months ago makes no sense to me in terms of logic I used; comments in this situation will be a saviour. Go ahead and use comments extensively to brief about your code.

Keep it Simple Silly

If you are trying to achieve a mammoth task or various tasks, try to break your code into multiple modules, each module aimed at achieving something. Calling a macro within other macro is very easy. Your code looks less clumsy this way and it will be easier to find any errors.

Provide Error Trapping

Run time error causes the macro to stop executing. If you want your macro to run without interruption, you will code your macro to tell what to do if an error occurred. This is called errortrapping. You can do this by using statements like On Error Resume Next or On Error Goto. So, make provision for error trapping in your code.

Use Option Explicit

You can use variables in VBA to capture some values or facilitate looping (we will discuss all these as we proceed deeper). Though VBA facilitates variables usage without declaring them first, it is a good choice to declare all the variables explicitly. This not only makes your code run faster and memory saving but also saves for risk of misspelling.

Use Help

Last but not the least in anyway, use built-in help in any case you are in need of help. VBA help is documented in a fantastic way; you will probably get all that you need from here. Else, there is a fair chance that one of the online documents already has a solution for your problem.