VBA Etiquette: Dim all variables at the top of Sub or Dim as you Go?

[Bill Bisco]

When I was taught VBA, my instructor stated that we should always Dim all our variables at the top of the code. However, I see several examples of people "Dimming" variables as they go?

What is this board's opinion of the best practice of where and when to Dim Variables?

Thanks,

Bill

 

[Worf]

I prefer to dim at the top, and do not forget to use Option Explicit to force dimming.

 

[Joe4]

I think it is really a matter of opinion, but I like declaring them all at the top myself. Then it is really easy to see/locate all the different variable names you are using in one place.

For example, let's say that the code is rather lengthy, and you encounter a variable, and you want to see how it is declared. If it is all at the top, you know where to look. If not, you might have to do a Find/Search for it. Not hard, but it can be annoying. And we programmers usually like to be lazy as possible!


Edit: Worf's suggestion about using Option Explicit is an excellent one! Doing that forces you to declare all variables and can help avoid errors caused by typos!

 

[Peter_SSs]

Another vote for "all at the top"

 

[starl]

Vote for at the top of the Sub they relate to (vs declaring global variables...)
Definitely have Option Explicit.. saves many a headache. I also like MZ-Tools to run a cleanup (i'm a neatfreak)

 

[UniMord]

For the most part, I prefer to Dim at the top.

Exceptions:
1) When the variables will be needed only if a condition is met, I like to Dim those variables at the top of the section where the condition is met. Why declare variables that will not be needed?
2) If it's a simple counter for a loop, or some similar throwaway variable that won't be used elsewhere, I like to Dim on-site.

 

[RoryA]

1) When the variables will be needed only if a condition is met, I like to Dim those variables at the top of the section where the condition is met. Why declare variables that will not be needed?

They're still declared and allocated, so I'm not sure I see the benefit of this.

I'd also suggest that using a mix and match approach is probably the one option that doesn't make sense to me. YMMV.

 

[starl]

Rory - memory is allocated for variables when the code is compiled - correct?
But if you have late binding, I guess the initial variable (as object) is allocated at the beginning, but once Excel figures out exactly what the object's variable type is (ie. Document), which is done at run time (right?), does that change the memory allocated??

 

[RoryA]

I'm not really an expert on this, but this is my understanding of it:

Rory - memory is allocated for variables when the code is compiled - correct?

VBA isn't strictly a compiled language, but yes the compiler knows from the declarations how much memory to allocate when the code is loaded.

But if you have late binding, I guess the initial variable (as object) is allocated at the beginning, but once Excel figures out exactly what the object's variable type is (ie. Document), which is done at run time (right?), does that change the memory allocated??

All object variables are just pointers, so the actual memory allocation for the variable itself is the same regardless. (strictly speaking, it's a pointer to a particular interface rather than an actual object per se, since an object can implement more than one interface. In fact almost all COM objects implement interfaces inherited from IDispatch and IUnknown so that they can be both late and early bound.)

If you early bind, the compiler can preload the various vTables of exposed properties/methods etc. which presumably has some memory overhead.

If you late bind, the compiler can't do that, which is why it's slower. (It has to call the IDispatch interface's GetIDsOfNames function to get the dispid (memory location) of a function name, and then the Invoke method to actually call the function.)

The upshot is that I'm not sure there's much, if any, difference in terms of memory allocation, since a pointer is a pointer, regardless of what it points to, and the actual object in question will occupy the same amount of memory when it's created. The only real difference at run-time is the speed of execution.

 

[wesborland1234]

I agree with declare everything at the top in one section. Then initialize everything in the next section. Then get into the logic of your code.

This makes stuff easy to find, and read. And it comes with the added bonus of the fact that if you find yourself scrolling back and forth over a million lines to check your variable names, your code is too long, and it forces you to break it up into smaller functions and subroutines.