Never struggle with these VBA errors again

In this article we’ll look at three common error messages in VBA: why they appear, and how to fix them.

Which VBA developer isn’t familiar with the battles fought on the VBA front line? It can be a real rough and tumble to bend VBA to your will, especially for beginning developers. VBA errors are especially annoying, since they are presented in full-intrusive pop up messages—you have to click them away to continue writing code. Worse still, you cannot save a workbook when there are errors in VBA.

#1 Object variable or With block variable not set

Arguably one of the worst VBA error messages; it is as undescriptive as it is annoying. In VBA, you have to declare a variable type before assigning it a value. For example, if you want to store a number you might declare a variable as Integer or Double:

Dim PI As Double
PI = 3.14

Similarly, you might declare a String for some text:

Dim message As String
message = "Hello World"

It’s important to use the word Set when assigning values to variables of object types, such as a Range or a Worksheet. If you try to add a worksheet and assign it to a variable ws:

Dim ws As Worksheet
ws = Worksheets.Add

…then you’ll get the dreaded message:

VBA errors // Run-time error '91 : Object variable or With block variable not set // PerfectXL Academy

To fix it, simply make use of the word Set:

Dim ws As Worksheet
Set ws = Worksheets.Add

Note that the message is a poor indicator of where in your code a Set keyword was missing. Regularly run your code to know where a mistake was made.

#2 Subscript out of range

This one seems obvious. Let’s say you have a list of three items:

Dim MyFirst(1 To 3) As String
MyFirst(1) = "A"
MyFirst(2) = "B"
MyFirst(3) = "C"

If you then try to pick the fourth one (e.g. MsgBox MyFirst(4)), it rightfully complains that the subscript (in this case “4”) is out of range (1 to 3):

VBA errors // Run-time error '9 : Subscript out of range // PerfectXL Academy

Yet there are plenty of cases where the cause of this message is not so clear. You get this error not only when an index is out of range of a list, but whenever a run-time resolved object doesn’t have a specified property. Too cryptic? Let’s look at an example. We want to programmatically bring a worksheet named “Results” to the foreground:

Worksheets("Results").Activate

That works—if and only if the worksheet Results actually exists. If you had made a typo, and either in VBA or in the actual name of the worksheet spelled it as Result, you’d get the same “Subscript out of range error”. The trick is not just to be careful about spelling, but to also verify when variables are involved.

#3 Handling Run-time errors

You can’t always prevent every possible error, so let’s finish with the proper way to catch VBA errors and report them appropriately.

Within any Sub or Function, you can put the line On Error GoTo MyErrorHandler at the beginning of the method to redirect errors to a special error handler:

Sub MyMethod()
On Error GoTo MyErrorHandler
' do your thing
Exit Sub
MyErrorHandler:
MsgBox "Something went wrong"
End Sub

Note that without Exit Sub, the code under “MyErrorHandler” will always be executed.

In the example above, handling the error simply involved telling the user the procedure didn’t go to plan. In many cases, some form of “reset” is also important. For example, in VBA it is common to disable ScreenUpdating and set Calculation to manual at the start of the script, only to reset them at the end of the script. If you do this naively:

Sub MyMethod()
Application.Calculation = xlManual
Application.ScreenUpdating = False
' do your thing
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

…then if your script fails at the “do your thing” stage, it will never reset the Calculation and ScreenUpdating properties. To make sure they are always reset, include them in the error handler as well:

Sub MyMethod()
On Error GoTo Finally
Application.Calculation = xlManual
Application.ScreenUpdating = False
' do your thing
Finally:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Note that “Finally” will always be called since there is no Exit Sub statement, which is exactly what we want. Want to show a MsgBox in case of an error only? Simply add another GoTo statement:

Sub MyMethod()
On Error GoTo CatchError
Application.Calculation = xlManual
Application.ScreenUpdating = False
' do your thing
GoTo Finally
CatchError:
MsgBox "Something went wrong"
Finally:
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

Don’t care about errors and just want to continue executing code when VBA encounters an error? You can instruct VBA to do so explicitly:

On Error Resume Next

This is especially common when, while looping over a list, it’s okay to skip those items that result in an error. Careful: this line will also mask any errors that weren’t supposed to be skipped.