Declaring and Using Variables in VBA

Among the very first language keywords one comes across when learning VBA, is the Dim keyword; declaring and using variables is easily the first step one takes on their journey away from the macro recorder.

About Scopes

Before we can really understand what variables do and what they’re useful for, we need to have a minimal grasp of the concept of scoping. When you record a macro, the executable instructions for you inside a procedure scope that’s delimited with Sub and End Sub tokens (tokens are the grammatical elements of the language, not necessarily single keywords), with the identifier name of the macro after the Sub keyword:

Sub DoSomething()
    ' executable code goes here
End Sub

Exactly none of the above code is executable, but compiling it creates an entry point that the VBA runtime can invoke and execute, because the procedure is implicitly public and as such, can be accessed from outside the “Module1” module it exists in (with or without Option Private Module). In other words the above code could tell us explicitly what the scope of the DoSomething procedure is, using the Public keyword before the Sub token:

Public Sub DoSomething()
    ' executable code goes here
End Sub

If we used Private instead, then Excel (or whatever the host application is) could not “see” it, so you would no longer find DoSomething in the list of available macros, and other modules in the same VBA project couldn’t “see” or invoke it either; a private procedure is only callable from other procedures in the same module.

Standard modules are themselves public, so you can refer to them from any other module in your project, and invoke their public members using the member access operator, the dot:

Public Sub DoStuff()
   Module1.DoSomething
End Sub

Because public members of public modules become part of a global namespace, the public members can be referred to without an explicit qualifier:

Public Sub DoStuff()
    DoSomething
End Sub

While convenient to type, it also somewhat obscures exactly what code is being invoked: without an IDE and a “navigate to definition” command, it would be pretty hard to know where that other procedure is located.

The global namespace contains not only the public identifiers from your VBA project, but also all the public identifiers from every referenced library, and they don’t need to be qualified either so that’s how you can invoke the VBA.Interaction.MsgBox function without qualifying with the library or module it’s defined in. If you write your own MsgBox function, every unqualified MsgBox call in that project is now invoking that new custom function, because VBA always prioritizes the host VBA project’s own type library over the referenced ones (every VBA project references the VBA standard library and the type library that defines the COM extension and automation model for the host application).

But that’s all going outward from a module: within a module, there are two levels of scoping: module level members can be accessed from anywhere in the module, and procedure level declarations can be accessed from anywhere inside that procedure.

Module-level declarations use Public and Private modifiers, and procedure-level ones use the Dim keyword. Dim is legal at module level too, but because Private and Public are only legal at module level (you can’t use them for procedure scope / “local” declarations), Rubberduck encourages you to use Dim for locals only.

For example a variable declared in a conditional block is allocated regardless of the state when the condition gets evaluated, and a variable declared inside a loop body is the same variable outside that loop, and for every iteration of that loop as well.

Non-Executable Statements

Procedures don’t only contain executable instructions: Dim statements, like statements with Private and Public modifiers, are declarative and do not do anything. You cannot place a debugger breakpoint (F9) on such statements, either. This is important to keep in mind: the smallest scope in VBA is the procedure scope, and it includes the parameters and all the local declarations of that procedure – regardless of where in the procedure body they’re declared at, so the reason to declare variables as you need them has more to do with reducing mental load and making it easier to extract a method by moving a chunk of code into another procedure scope. Declaring all locals at the top of a procedure often results in unused variables dangling, because of the constant up-and-down, back-and-forth scrolling that inevitably happens when a procedure eventually grows.

Const statements (to declare constant values) are also legal in local/procedure scope, and they’re identically non-executable; the same applies to Static declarations (variables that retain their value between invocations).

ReDim statements however are executable, even though they also count as a compile-time declaration – but they don’t count as a duplicate declaration, so the presence of ReDim doesn’t really justify skipping an initial Dim declaration.

Explicitness as an Option

Not only access modifiers can be implicit in VBA; the language lets you define a Variant variable on the fly, without a prior explicit declaration. If this behavior is practical for getting the job done and will indeed work perfectly fine, it’s also unnecessarily putting you at risk of typos that will only become a problem at run-time, if you’re lucky close enough to the source of the problem to hunt down and debug. By specifying Option Explicit at the top of every module, the compiler will treat implicit declarations as compile-time errors, telling you about the problem before it even becomes one.

Option Explicit has its limits though, and won’t protect you from typos in late-bound member calls, where invoking a member that doesn’t exist on a given object throws error 438 at run-time.

When to Declare a Variable

There are many reasons to declare a variable, but if you’re cleaning up macro recorder code the first thing you’ll want to do is to remove the dependency on Selection and qualify Range and Cells member calls with a proper Worksheet object.

For example before might look like this:

Sub Macro1
    Range("A10") = 42
    Sheet2.Activate
    Range("B10") = 42
End Sub

And after might look like this:

Public Sub Macro1()
    Dim Sheet As Worksheet
    Set Sheet = ActiveSheet

    Sheet.Range("A10") = 42
    Sheet2.Activate
    Sheet.Range("B10") = 42
End Sub

The two procedures do exactly the same thing, but only one of them is doing it reliably. If the Sheet2 worksheet is already active, then there’s no difference and both versions produce identical output. Otherwise, one of them writes to whatever the ActiveSheet is, activates Sheet2, and then writes to that sheet.

There’s a notion of state in the first snippet that adds to the number of things you need to track and think about in order to understand what’s going on. Using variables, exactly what sheet is active at any point during execution has no impact whatsoever on the second snippet, beyond the initial assignment.

It’s that (global) state that’s behind erratic behavior such as code working differently when you leave it alone than when you step through – especially when loops start getting involved. Managing that global state makes everything harder than necessary.

Keep your state close, and your ducky closer, they say.

Set: With or Without?

Not being explicit can make the code read ambiguously, especially when you consider that objects in VBA can have default members. In the above snippets, the value 42 reads like it’s assigned to… the object that’s returned by the Range property getter of the Worksheet class. And that’s weird, because normally you would assign to a property of an object, not the object itself. VBA understands what it needs to do here, because the Range class says “I have a default member!” and that default member is implemented in such a way that giving it the value 42 does exactly the same as if the Range.Value member was being invoked explicitly. Because that behavior is an implementation detail, it means the only way to know is to read its documentation.

The Set keyword modifies an assignment instruction and says “we’re assigning an object reference”, so VBA doesn’t try to check if there’s a default member on the left-hand side of the assignment operator, and the compiler expects an object reference on the right-hand side, …and then only throws at run-time when that isn’t the case – but because this information is all statically available at compile-time, Rubberduck can warn about such suspicious assignments.

So to assign a variable that holds a reference to a Range object, we must use the Set keyword. To assign a variable that holds the value of a Range object, we must not use the Set keyword. Declaring an explicit data type for every variable (meaning not only declaring things, but also typing them) helps prevent very preventable bugs and subtle issues that can be hard to debug.

As SomethingExplicit

Whether Public or Private, whether local or global, most variables are better off with a specific data type using an As clause:

  • Dim IsSomething
  • Dim SomeNumber As Long
  • Dim SomeAmount As Currency
  • Dim SomeValue As Double
  • Dim SomeDateTime As Date
  • Dim SomeText As String
  • Dim SomeSheet As Worksheets
  • Dim SomeCell As Range

Using an explicit data/class/interface type, especially with objects, helps keep things early-bound, meaning both the compiler and static code analysis tools (like Rubberduck) can better tell what’s going on before the code actually gets to run.

We can often chain member calls; the Worksheets collection’s indexer necessarily yields a Worksheet object, no?

Public Sub Macro1()
    ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = 42
End Sub

If you manually type this instruction, you’ll notice something awkward that should be unexpected when you type the dot operator after Worksheets(“Sheet1”), because the property returns an Object interface… which tells VBA it has members that can be invoked, but leaves no compile-time clue about any of them. That’s why the Range member call is late-bound and only resolved at run-time, and because the compiler has no idea what the members are until the code is running, it cannot populate the completion list with the members of Worksheet, and will merrily compile and attempt to invoke a Range member.

By breaking the chain and declaring variables, we restore compile-time validations:

Public Sub Macro1()
    Dim Sheet As Worksheet
    Set Sheet = ActiveWorkbook.Worksheets("Sheet2")
    Sheet.Range("A1").Value = 42
End Sub

When NOT to Declare Variables

Variables are so nice, sometimes we declare them even when we don’t need them. There are many valid reasons to use a variable, including abstracting the result of an expression behind its value. Assuming every variable is assigned and referenced somewhere, there are still certain variables that are always redundant!

Objects are sneaky little things… not only can they have a default member that gets implicitly invoked, they can also have a default instance that lives in the global scope and is always named after the class it’s an instance of.

Declaring a local variable to hold a copy of a reference to an object that’s already globally accessible, is always redundant! Document modules (in Excel that’s ThisWorkbook and the Worksheet modules) and UserForms always have such a default instance:

Public Sub Macro1()
    Dim WB As Workbook
    Set WB = ThisWorkbook 'redundant and obscures intent!
    Dim Sheet As Worksheet
    Set Sheet = Sheet1 'redundant, just use Sheet1 directly!
End Sub

Sprinkle Generously

Variables are a simple but powerful tool in your arsenal. Using them enhances the abstraction level of your code, practices your brain to stop and think about naming things, can help prevent binding errors and remove implicit late-binding / keep your code entirely visible to the compiler and Rubberduck. Used wisely, variables can make a huge difference between messy and redundant macro-recorder code and squeaky-clean, professionally-written VBA code.

Modern VBA Best Practices: Default Members

Today I learned that VB.NET does in fact support Default properties. For years I was under the impression that dismissing the Set keyword meant default members couldn’t possibly exist in .NET, and I was wrong: dismissing the Set keyword meant that parameterless default members couldn’t exist in .NET, but VB.NET can still implicitly invoke a Public Property Get Item(index) default member, just like its VB6 ancestor.

Rewind to their inception, and default members/properties have all the looks of a language feature that’s considered a nice convenient way to type code faster (in 20/20 hindsight, that was at the cost of readability). That’s why and how Debug.Print Application can compile, run, and output Microsoft Excel in the debug pane; it’s why and how an ADODB.Connection object and its ConnectionString properties can be impossible to tell apart… as a convenience; how a Range “is” its value(s), a TextBox “is” its text, or an OptionButton “is” True or False.

These are the modern-day considerations for VB.NET default properties (emphasis mine, .NET-specifics removed):

Default properties can result in a small reduction in source code-characters, but they can make your code more difficult to read. If the calling code is not familiar with your class […], when it makes a reference to the class […] name it cannot be certain whether that reference accesses the class […] itself, or a default property. This can lead to compiler errors or subtle run-time logic errors. […]
Because of these disadvantages, you should consider not defining default properties. For code readability, you should also consider always referring to all properties explicitly, even default properties.

I cannot think of a single valid reason for any of these considerations to not be applicable to modern VBA, or even VB6 code. VB.NET removed the need for a disambiguating Set keyword by making a parameterless default member throw a compiler error. For contrast consider this code, and imagine the Set keyword doesn’t exist:

Dim things(9)
things(0) = New Thing

If the Thing class defines a parameterless default member, then who can tell what’s at index 0 of the things array? A Thing object reference? A SomethingElse object reference? The String representation of a Thing instance? 42?

Default members are hopefully not side-effecting magic invisible stardust code that is by definition invoked implicitly, by code that says one thing and does another, and requires looking up the documentation or the object browser definition of a type to remember what member we’re actually invoking – and even then, it can be obscured; the Excel type library is a prime example, with a hidden _Default property being the (drumroll) default property of the Range class, for example. Lastly, an implicit default member call is not 100% equivalent to an explicit one, and that tiny little difference can go as far as instantly crashing Excel.

Sounds terrible. Why would Rubberduck have a @DefaultMember annotation then?

With Rubberduck’s annotation and inspection/quick-fix system, you can easily define default members for your class modules; simply decorate the procedure with a '@DefaultMember annotation, synchronize member attributes, and done.

It’s not because you can, that you should. If you’re like me and someone gave you a knife, you’d probably at least try not to cut yourself. If you’re writing a custom collection class and you want it to be usable with the classic things(i) syntax rather than an explicit things.Item(i) member call, Rubberduck’s job is to help you do exactly that without needing to remove/export the code file, tweak it manually in Notepad++, then re-import it back into the project – that’s why the @DefaultMember annotation exists: because for the rare cases where you do want a default member, your ducky doesn’t let you down.

Currently, Rubberduck won’t complain if you make a parameterless procedure a default member. There’s an inspection idea that’s up-for-grabs to flag them though, if you’re looking for a fun contribution to an open-source project!

Clean VBA Code pt.2: Avoiding implicit code

Clean code adheres to a number of principles. Does adhering to these principles make good code? Maybe, maybe not. But it definitely helps. One thing I find myself repeating quite a lot in my more recent Stack Overflow answers, is that code should “say what it does, and do what it says” – to me this means writing explicit code. Not just having Option Explicit specified, but avoiding the pitfalls of various “shortcuts” VBA lets us use to… cheat ourselves.

Avoid implicit member calls, write code that says what it does, and does what it says. Instead of:

Cells(i, 2) = 42

Prefer explicit qualifiers, and explicit member calls:

ActiveSheet.Cells(i, 2).Value = 42

In Excel, avoid working with ActiveSheet when you mean to work with Sheet1. Use the Worksheets collection instead of the Sheets collection when you mean to retrieve a worksheet in a workbook; sheets can contain charts and other non-worksheet sheet types.

Dim targetBook As Workbook
Set targetBook = Application.Workbooks.Open(path)

Dim targetSheet As Worksheet
Set targetSheet = targetBook.Worksheets("Sheet1")

Debug.Print targetSheet.Range("A1").Value

If the sheet we need exists in ThisWorkbook at compile-time, then we don’t need a variable for it – it already exists:

Debug.Print Sheet1.Range("A1").Value

Every sheet in your Excel VBA project has a code name that you can set to any valid VBA module identifier name (up to 31 characters), and that identifier is now accessible from anywhere in your VBA project. To change the name, modify the (Name) property in the properties toolwindow (F4).


About the Bang! operator…

Avoid the Bang! operator. How many of the people using it know that the identifier to the right of the operator is a string literal that isn’t compile-time validated? It looks like early-bound code, but it isn’t. The Bang! operator is an implicit default member call against a default member that takes a string parameter. So this:

rs.Fields!Field1 = 42

Is really this:

rs.Fields.Item("Field1").Value = 42

Now, this doesn’t mean we have to go crazy and dogmatic here – default properties are idiomatic, and not necessarily toxic… when used carefully. The Item member of a collection class is, by convention, the default member of the class:

rs.Fields("Field1").Value = 42

Note that Fields is plural, which strongly signals that ("Field1") is an indexed property accessor (it is)… and we could even infer that it returns a Field object reference. There’s an implicit default member call happening, yes, but it’s pure syntax sugar here: even if we don’t know that Fields is a class with a default Item property, we can tell that syntactically, we’re invoking something, getting an object reference back and assigning its Value property with a value.

Contrast with rs.Fields!Field1 = 42, which reads like… witchcraft, come to think of it.

As an Excel programmer I’m biased though: Access programmers probably see the Bang! operator differently. After all, it’s everywhere, in every tutorial – why would it suddenly be wrong?

Pros:

  • Faster to type (?).
  • Encourages using standard PascalCase field names and collection keys. Kinda.

Cons:

  • Confusing syntax for an unfamiliar reader; makes a string look like a member access. That one’s arguably on the reader/maintainer to read up, yes. Still.
  • No compile-time validation: what follows the ! operator is a string… And any member calls that might follow are always late-bound: Option Explicit will not save you from a typo.
  • If any explicit member call follows the string, it is inherently late-bound and not compile-time validated either; the editor will offer no intellisense for it.
  • Requires otherwise rather uncommon [square bracket] tokens around the name when the name contains spaces.

You have to put the Bang! operator in context: 25 years ago, using fully spelled-out variable names was seen as wasteful and borderline ludicrous. Code was written to be executed, not read: the faster you could type, the better. Oh, how things have changed!

Here’s a screenshot from an old, deleted Stack Overflow question about the Bang! operator in… VB.NET:

BangOperator

The Bang! operator is a relic of the past. There’s no reason to use it in modern code, be it in VBA, VB6… or VB.NET.