Seeking knowledge on an unknown VBA concept

What you're talking about is very close to the concept of late binding versus early binding.

In your particular example the 2nd example won't be any faster than the first - when you declare a variable VB is just allocating the memory space it's going to use. Unless you were talking about thousands of variables it's not likely to make any noticeable difference at all, and certainly won't make up for the time you'll waste hunting through your code trying to find your variable declarations.

A better example would be this code:

Please Login or Register  to view this content.

This is late-bound code and will work fine, but VBA doesn't know exactly what I want my two variables to hold up until the point where I assign them. This means that it has to reserve a big old chunk of memory and convert the variables to the correct type at the point of assignment.

Also, if I make a mistake and instead of:

Please Login or Register  to view this content.

I accidentally code:

Please Login or Register  to view this content.

Then the compiler won't throw an error, it will just assume that I want Example1 to be a string, and treat it as such. It might not error until long after this assignation, when I actually try to treat Example1 as a range object instead of a string.

So, this code could be re-written to early-bind my variables, thus:

Please Login or Register  to view this content.

Now VBA knows from the outset that Example1 is a range and Example2 is a worksheet, and sets them up as such from the start. It will throw an error if I try to treat either of them as something other than their assigned type.

A Google for late binding versus early binding will give you a lot more information than I can.

Does that help?

I'd read your question slightly differently to Andrew, so here's my two pennyworth in addition to Andrew's eloquent description of late binding.

Convention is to declare your variables at the top of the routine since VBA variables are scoped for the routine they run in. This actually makes little sense until you compare it to other languages, for example:

VB.NET

PHP Code: 

Dim Test As Boolean

Test = true

If Test then
    Dim t as long
    t = 200
    Messagebox.show(t)
End If

't is now out of scope, so it is not declared 


VBA

Please Login or Register  to view this content.

So whereas VB.Net has block level scope - t is only available in the If block, VBA does not, t is available throughout the routine.

In VBA, it's therefore really a matter of preference. Keeping the variable declaration near the code that uses it makes sense from a refactoring viewpoint - it makes it easier to transfer the whole bit to another sub routine. Just bear in mind it can't be used before it's declared - so usually the preference is to declare at the top so it's usable throughout.

In addition to Andrew's point, the only time declaring at the top may slow your code down is when you auto instance in your declaration, so:

Please Login or Register  to view this content.

As opposed to:

Please Login or Register  to view this content.

The reason for that is the class is created earlier - it's generally not good practice anyway since you can't check whether the class is nothing (which is often useful)

@Joseph, I agree, which is why I said created earlier rather at declaration the act of testing if the object is nothing creates it. Thanks for the clarification though - should have been clearer

OK, some general points first... There's a very definite limit to how large your subs can be. If I recall correctly each sub is limited to 64Kb of plain text, but this is rarely a problem if your code is well structured. I did help out somebody with a procedure that was too large recently, but it's really not usually an issue.

This page shows how much memory is reserved by Excel for each variable type when it's declared. As you can see it's a pretty trivial amount, certainly on modern machines with many Gigabytes of usable memory.

So, to answer your specific questions:

1. Not that I know of, sorry. In manufacturing there's a principle called Just-In-Time, where you avoid holding stock by ordering components such that they will arrive at the factory exactly when they're needed for assembly, so I suppose you could call this JIT coding

2. The difference between declaring variables early and late is likely to be non-existent. Given that you're talking a couple of bytes for each type you'd run out of space for the module before you used a significant amount of memory. The savings would be somewhere on par with those gained by only using single character variable names. That being the case I'd go with always declaring variables at the start of the code, because it makes the definitions easy to find.

3. According to the link up there you can have very long strings; approximately 2 billion characters. According to my rough 'n' ready maths that's about 2Gb of memory space! If you were dealing with strings of that size then there probably would be an advantage to storing them in memory for as little time as possible. However, if you're trying to store strings of that size and manipulate them in memory you've got to think that there are bigger problems with your code than where variables are declared/assigned. For 'normal' sized strings the actual memory usage (1Kb or less) would be trivial compared to the amount of available memory, so any savings would be negligible.

Which, I suppose, brings me to a final point - VBA isn't a fast language. It's a very convenient and relatively easy way to access the power of Excel, but if you're interested in shaving every possible millisecond off your run time then it's probably not the language to be working in.

What Andrew says

It's also worth noting that if you know your strings in advance it's probably worth using constants, not variables and declared with module scope if you are going to be using them in multiple places. Setting your strings to vbnullstring is also pointless, they will go out of scope at the end of the sub and be released anyway. If you're really keen on ultra-optimisation, then have a look here http://www.aivosto.com/vbtips/stringopt.html (but string optimization is really only an issue with extremely long strings or 10,000s of iterations; but if that matters to you, then you should probably be looking at another platform - as Andrew says VBA is sloooow.

Thanks for the feedback

Macros can be optimised a lot, but I think you're barking up the wrong tree with this one these are pretty much all the tips you'd need http://www.cpearson.com/excel/optimize.htm. Anything like the link I posted is just ringing out any last slack in the application and is unlikely to have any noticeable difference for most people. Far better to ditch the selects and turn off screen updating

re the setting to vbnullstrings, you don't really need to set anything to null/nothing when you're done with it since the variable will be released automatically when there are no more references to it (though there are some exceptions to this, they are not common for many users).

vbNullStrings of course have a practical use, when you want to set a string to null; this doesn't however require doing at the end of a procedure.

Consider:

Please Login or Register  to view this content.

Has the same effect as:

Please Login or Register  to view this content.

The vbnullstring has no practical use. When the sub completes, sMyString will no longer be in scope so the memory will be released.

Another Example:

Please Login or Register  to view this content.

Here, since sMyString is a module/class level declaration it will not go out of scope until all the code has finished running. As other subs may reference the sMyString it can be useful to set it to null.