CODEWITHSUNDEEP
vbamemory-management
Johnson Jason
Johnson Jason

Reputation: 721

Why should I dimension my variables in VBA really?

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?

Upvotes: 1

Views: 946

Answers (2)

Comintern
Comintern

Reputation: 22195

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.

Upvotes: 7

hypetech
hypetech

Reputation: 166

The most helpful reason I've found is it's just nice to be able to see what are variables and what are controls right away (that's more in Access than Excel), and also to just know what type of variable it's really supposed to be. This is mostly useful for other people looking through your code, as I've inherited some projects where the variables were not defined at all, and projects where they were very well defined, and the latter definitely makes things much easier. In regards to memory though, I wouldn't say it doesn't matter but the difference is negligible so it's personal choice.

Upvotes: 1

Related Questions