Practical recommendations for VBA developers

As a programming language, Visual Basic for Applications has a lot of bad design choices. It was born in the early 1990s as a way to script Microsoft Office, and still keeps problems of that era.

Variables do not have to be declared before use, and a single typo can quietly create a brand-new variable instead of raising an error. Almost everything defaults to the loosely typed Variant, so type mistakes surface at runtime rather than at compile time. Error handling relies on On Error and GoTo labels instead of structured exceptions, which makes it easy to hide failures instead of dealing with them. The language passes arguments by reference by default, so a procedure can mutate its caller's data without any obvious sign. On top of that, the tooling is dated: no package manager, no real dependency management, and an editor that has barely changed in decades.

None of this means VBA is useless. It is still the fastest way to automate Office, and millions of critical spreadsheets and business processes depend on it even today. The problem is that the language gives you very little protection against your own mistakes, which means the discipline that other languages enforce for you must instead come from you.

As a developer with more then 6 years of VBA experience, I decided to create a collection of practical recommendations that may help you to write VBA code that stays readable, predictable, and maintainable despite the language's fundamental flaws.

Force yourself to declare every variable

VBA has an important directive: Option Explicit. If you put this line at the top of a module, the VBA compiler will force every variable to be declared. Otherwise, a variable name with a typo is treated as a new variable, which leads to unexpected errors.

For example, this code compiles but fails at runtime. Because Shet is never declared, VBA creates it as an empty Variant, and calling .Range on it raises Object required (error 424):

Public Sub WriteHelloWorld(ByVal Sheet As Worksheet)
    Shet.Range("A1").Value = "Hello, world"  ' Shet is a typo for Sheet
End Sub

With Option Explicit, the same typo is caught before the code even runs, with a Variable not defined compile error:

Option Explicit

Public Sub WriteHelloWorld(ByVal Sheet As Worksheet)
    Shet.Range("A1").Value = "Hello, world"
End Sub

You can enable automatic Option Explicit row creation at the start of every module with Tools → Options → Require Variable Declaration.

Declare a type for everything

Always declare types explicitly where possible. This allows the VBA compiler to reason about your code and perform type checking. A type is also an important marker while developing: in many cases it conveys the programmer's intent even more than the variable name does.

Of course, sometimes you cannot declare a type explicitly. For example, the control variable of a For Each loop must be a Variant (or an object type), so you cannot declare it as Long:

Dim num As Variant

For Each num In numbers
    ' ...
Next num

In this case I usually add a comment that documents the real type:

Dim num As Variant ' Long

Furthermore, I always do this for collection types. VBA has no generics, so the element type of a Collection or Dictionary is invisible; a comment restores that information for the next reader:

' Instead of
Dim dict As Dictionary

' ... I write
Dim dict As Dictionary ' Dictionary<String, Long>

Choose correct integral types

Integer is a bad choice for your numeric type is 99% of cases, because it is a 16-bit integer. This means that it overflows at 32767.

Always use Long: it is the natural 32-bit integer type and is as fast as Integer. On modern systems Integer is stored as 16 bits but offers no performance benefit, so there is rarely a reason to prefer it.

Explicit `ByRef` and `ByVal`

When declaring functions' or subs' parameters, explicitly mark it with ByVal or ByRef keyword. Prefer ByVal, because it's an additionali safety measure for your code: callee won't be able to silently mutate the caller's variable.

Naming of modules

Never leave default module, class, and sheet names like Module1, Class1, or Sheet1. VBA allows you to change these names, and for modules and sheets I prefer to prefix each name with its type, for example: ModuleDatabase, SheetPayments.

You change the object name in the Properties window (the (Name) property), not by renaming the tab.

Please note that every worksheet has two names:

The tab name, which the user sees and can change, and which Worksheets("...") looks up by text.
The VBA object name (also called the code name), which you use directly in code — SheetPayments in our case. It does not break when a user renames the tab, which is exactly why it is worth setting deliberately.

Classes are the exception: I don't prefix them. A class models an entity and is instantiated as a variable, so a simple noun reads better than a prefixed name. Person is clearer than ClassPerson, especially at the call site:

Dim customer As Person

Avoid implicitness

VBA has a lot of implicit functionality. For example, when you write:

amount = Range("A1")

The compiler treats it as the following call:

amount = ActiveSheet.Range("A1").Value

Two things are filled in for you here: .Value (the default property of a Range) and the ActiveSheet qualifier. During execution the active sheet can be changed unintentionally, and you may start reading data from the wrong worksheet.

So I suggest always qualifying these calls explicitly:

Pass a worksheet when you use members like Range or Cells.
Pass a workbook when you use members like Worksheets("...") or Sheets(1).

amount = wsPayments.Range("A1").Value
Set ws = wbData.Worksheets("Payments")

VBA allows you to directly pass correct objects, so avoiding functionality that emulates user actions like .Select / .Activate / Selection is also a good idea.

Avoid early binding of external libraries

Microsoft Office has very limited repository of external libraries, and I don't recomment to use early bindings to types of these libraries.

For example I don't recomment to do this:

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

This code is not bad by itself, but Microsoft Office very often breaks dependencies that you manually add.

Unfortunately, in order to make your code more stable, you should use late binding:

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

This solution is not perfect either, because you sacrifice IntelliSense and compile-time type checking.

But you can also write a thin wrapper class around the late-bound object, which restores IntelliSense at every call site while keeping late binding internally.

Better collections classes

The built-in Collection class, used to store a variable-length list of elements, is very limited: it lacks functionality, so you have to reinvent the wheel every time for trivial operations like searching, filtering, or sorting.

I have developed a VBA-List repository: a class built to be compatible with Collection, but adds the higher-level operations the standard Collection is missing.

Scripting.Dictionary is also problematic: it doesn't work on MacOS and requires late binding, as discussed in previous section. So you may use 100% compatible class from VBA-Dictionary repository.

Avoid `As New`

Don't use As New in a variable declaration. Variables declared this way are auto-instancing: VBA silently re-creates the object the moment you touch the variable again after it has been set to Nothing. This leads to non-obvious behavior that is hard to catch:

Dim a As New List

Dim b As List
Set b = New List

Set a = Nothing
Set b = Nothing

Debug.Print (a Is Nothing) ' False - reading `a` re-created it
Debug.Print (b Is Nothing) ' True

Because evaluating a Is Nothing touches a, VBA instantiates a fresh List before the comparison runs, so you can never observe a as Nothing. Always declare the type and create the instance explicitly with Set ... = New ..., as shown with b.

Correct error handling

Unlike modern language, the only way of error handling in VBA is goto statement (On Error GoTo <label>), or just ignoring it (On Error Resume Next).

The pattern below keeps error handling explicit and narrowly scoped. Before each risky statement you put On Error GoTo <label>, after each risky statement - On Error GoTo 0. Every unsafe operation gets its own dedicated handler label, so you always know precisely which statement failed and can react accordingly:

Sub DoSomething()
    ' safe operation

    On Error GoTo unsafeOperation1Handler
    ' unsafe operation 1
    On Error GoTo 0

    ' safe operation 

    On Error GoTo unsafeOperation2Handler
    ' unsafe operation 2
    On Error GoTo 0

    'safe operation

    Exit Sub

unsafeOperation1Handler:


    Exit Sub

unsafeOperation2Handler:


    Exit Sub
End Sub

Two details make this schema work:

On Error GoTo 0 after each block turns error trapping back off, so the "safe" code between blocks runs without an active handler. Without it, one handler would silently stay in effect and swallow later errors.
The Exit Sub before the first label is essential. Handler labels are just ordinary lines of code; without an early Exit Sub, execution would fall through into unsafeOperation1Handler even when no error occurred. Each handler also ends with Exit Sub so one handler never falls through into the next.

You should also avoid On Error Resume Next: use it only if you do not care about result of operation. But even if you do not care about result of operation, always put On Error Goto 0 after it:

On Error Resume Next
' operation that is allowed to fail
On Error GoTo 0

'safe operation

Don't be afraid of the macro recorder

The macro recorder is your friend. It works as living documentation for every piece of Excel functionality: you record a macro while performing an action by hand, and VBA generates the code that reproduces it. When you don't know which object or method does something, the recorder shows you.

But the code it generates is horrible — always refactor it. The recorder writes everything in terms of .Select, .Activate, and Selection, against the active sheet — exactly the implicit, click-dependent style this article warns against. Treat its output as a hint about which members to call, then rewrite it with explicit object references:

' Recorded
Range("A1").Select
Selection.Value = 10

' Refactored
wsPayments.Range("A1").Value = 10

Unit testing

With vba-test library you may write unit tests for your functionality developed in VBA.

Build components from controls with `WithEvents`

Let us imagine you want three buttons on a form, and all of them behave the same way. The first intuition is to give each button its own state variable and its own event handler:

' BAD: a separate handler and a separate counter for every button
Private timesAlpha As Long
Private timesBeta As Long
Private timesGamma As Long

Private Sub btnAlpha_Click()
    timesAlpha = timesAlpha + 1
    lblAlpha.Caption = btnAlpha.Caption & " clicked " & timesAlpha & " time(s)"
End Sub

Private Sub btnBeta_Click()
    timesBeta = timesBeta + 1
    lblBeta.Caption = btnBeta.Caption & " clicked " & timesBeta & " time(s)"
End Sub

Private Sub btnGamma_Click()
    timesGamma = timesGamma + 1
    lblGamma.Caption = btnGamma.Caption & " clicked " & timesGamma & " time(s)"
End Sub

Of course, we can move the repeating logic into a separate procedure:

' BETTER, but still not good: one shared procedure, but the handlers stay
Private timesAlpha As Long
Private timesBeta As Long
Private timesGamma As Long

Private Sub btnAlpha_Click()
    UpdateButtonClickCount btnAlpha, lblAlpha, timesAlpha
End Sub

Private Sub btnBeta_Click()
    UpdateButtonClickCount btnBeta, lblBeta, timesBeta
End Sub

Private Sub btnGamma_Click()
    UpdateButtonClickCount btnGamma, lblGamma, timesGamma
End Sub

Private Sub UpdateButtonClickCount(ByVal btn As MSForms.CommandButton, _
                                   ByVal lbl As MSForms.Label, _
                                   ByRef counter As Long)
    counter = counter + 1
    lbl.Caption = btn.Caption & " clicked " & counter & " time(s)"
End Sub

This is better, but it still does not scale. You write a _Click handler for every button by hand, and as the form grows it is easy to wire the wrong label to the wrong button.

WithEvents is the way out. A class module can declare an object variable with this keyword, and the events of that object then show up inside the class exactly as if the control were sitting on the form. That lets you assemble one small component out of several plain controls and reuse it for every group.

The whole behavior then lives in a single class:

' Class ButtonLabelLink
Option Explicit

Private WithEvents mButton As MSForms.CommandButton
Private mLabel As MSForms.Label
Private mCount As Long

Public Sub Bind(ByVal Button As MSForms.CommandButton, _
                ByVal Display As MSForms.Label)
    Set mButton = Button
    Set mLabel = Display
End Sub

Private Sub mButton_Click()
    mCount = mCount + 1
    mLabel.Caption = mButton.Caption & " clicked " & mCount & " time(s)"
End Sub

Because mButton is declared WithEvents, it appears in the left-hand dropdown at the top of the class module and its events appear in the right-hand one, just like a control in a form module. Bind is the usual replacement for a constructor, since VBA classes cannot take arguments on creation.

The form then only has to create one instance per pair:

Option Explicit

Private mLinks As Collection ' Collection<ButtonLabelLink>

Private Sub UserForm_Initialize()
    Set mLinks = New Collection

    AddLink Me.btnAlpha, Me.lblAlpha
    AddLink Me.btnBeta, Me.lblBeta
    AddLink Me.btnGamma, Me.lblGamma
End Sub

Private Sub AddLink(ByVal Button As MSForms.CommandButton, _
                    ByVal Display As MSForms.Label)
    Dim Link As ButtonLabelLink
    Set Link = New ButtonLabelLink
    Link.Bind Button, Display
    mLinks.Add Link
End Sub

One detail here is critical: mLinks must be a module-level variable of the form. If you declare it inside UserForm_Initialize, the instances are destroyed when the procedure ends, and the buttons stop responding without any error at all. The events live exactly as long as the object that holds the WithEvents reference.

Two more limitations are worth knowing. WithEvents needs a real type, so As Object does not compile — this is a rare case where late binding is not an option. And it cannot be combined with As New, which is a good thing anyway.

Custom Ribbon

Instead of a plain menu of buttons, MS Office allows you to design custom ribbons whose buttons can trigger your VBA code. It is the professional way to give users an entry point to your macros.

The ribbon is defined by a customUI XML part stored inside the workbook file. Excel has no built-in editor for it, so it is usually edited with a third-party tool such as the Office RibbonX Editor. Each button's onAction attribute names the VBA callback that runs when it is clicked.

Conclusion

Unfortunately, VBA macros have become a synonym for "unmaintainable, messy code" — because of the language design, the many developers who never learned to write it well, the lack of tooling, and tons of legacy code.

To write good VBA code, you have to follow strict hygiene rules. This set of rules I created the hard way — by stepping on every rake in the yard, so you don't have to. Follow them, and VBA becomes a surprisingly productive tool instead of a mess waiting to happen.