This may be old news to most of you, but I only found this out yesterday and I'd never seen it done anywhere before so here it is.
As we all know, declaring a variable without using the As Type qualifier results in a variable of type "Variant". This is generally frowned upon as variants take up a lot more space in memory (espcially if all you need is a number) and will do all sorts of crazy type conversion when accessing those variables to perform operations on them.
I've always stuck to this mantra and make sure all my variables are strictly defined where I can. So for some totally fictitious sub i may do something like this:
Quite a bit of typing required just to do one variable declaration. I could shorten the above by comma separating the declarations, but that's only saving me a few "Dim"s. Imagine if i wanted a few numbers to handle array indexing in a loop:
Not overly onerous but it would be nice if I could save my fingers from all that every once in a while (I know there's defType but I came a cropper with that when copy/pasting some code to another module so I generally avoid that).
(Nb. If you are wondering why i use longs: "VBA converts all integer values to type Long, even if they're declared as type Integer. So there's no longer a performance advantage to using Integer variables; in fact, Long variables may be slightly faster because VBA does not have to convert them." - msdn)
Now, on to the important part. Yesterday I found this little nugget:
Turns out, if you look at the pages for various data types you find out what these are. Here's the entry for long. The important bit on that page is this "The type-declaration character for Long is the ampersand (&)" Which basically tells you that you can use:
And you get three variables, all of type Long. Brilliant! My fingers are saved
There are a few more of them out there, but I haven't managed to find a list which lets me know all of them. But for the time being I've found them for each of the variable types in that fictional sub asTypeVar. Here they are:
- Sub quickVar()
- Dim lng&, intgr%, [email protected], str$, dbl#, sng!, lnglng^, vr
- End Sub
That results in exactly the same variable types as the first example but is a quite a bit shorter.
Will I use this a lot? Probably not because it's a bit too ambiguous for my liking and requires others to know what the hell I'm on about. But I think every once in a while when I've got quite a lot of variables of the same type I just might.
Anyway, thought it would be nice to share, and at least they're all in one place!
If anybody knows any more please feel free to reply with any new ones. Be great if we could have a single list of all the type-declaration characters as a reference.
Hope this helps someone out there