memory management - Why should I dimension my variables in VBA really?

Question

They used to say it was about memory management. However, having a look at things I find that failure to dimension a variable will default it to a variant data type which allocates 22 bytes of memory.

Let's consider just a single megabyte of memory. 1,000,000 bytes. This means I would require 45,454 variables just to chew through a single MB of memory. Most modern systems have several GB of memory. So the question is should I really care about carefully setting the right dim for all of my variables? Or is it a waste of time by modern standards?

Answer 1

Memory usage is only pleasant a side effect. The real reason I'd recommend using Option Explicit is that it allows the compiler to protect you from coding mistakes that compile and run, but don't do what you intend.

Example 1: It keeps typos from turning into unexpected disasters.

Dim variableNamedFoobar As String
variableNamedFoobar = "Something"

If varableNamedFoobar <> "Something" Then
    Debug.Print "Did you catch that? The compiler would..."
End If

If you aren't explicitly declaring variables, the compiler happily gives you a 22 byte empty string to compare against.

Example 2: It also protects against leaking scope. Consider a huge project with multiple levels of variable scope. Hope you remember what your globals are:

Private x As Integer

Private Sub First()
    'I remembered here that x is a global.
    x = 2
    Debug.Print x
    Second
    Debug.Print x
End Sub

Private Sub Second()
    'I forgot here.
    For x = 1 To 5
    Next x
End Sub

Adding Dim x as Integer for use as the loop counter ensures that it is scoped to Second().

Example 3: It allows the compiler to detect type mismatches.

Set foo = New Collection
foo.Add "Bar"
Debug.Print TypeName(foo)

'... 100 lines of code later...

foo = 6
Debug.Print TypeName(foo)

If you had Dim foo As Collection in there somewhere, you get a compile time error letting you know that you already had a foo and you shouldn't be assigning 6 to it.

There are lots of other examples of where you can shoot yourself in the foot (or higher) with implicit variable declarations. While these examples are easy to spot in the isolation of the code blocks above, any one of them can cause subtle and extremely difficult to debug errors in a large code base. Do yourself (and everyone that might need to maintain your code later) a favor and type the extra line of code.