564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Variables and Constants in Excel Visual Basic
Part six of an eight-part series of blogs
This blog explains the nuts and bolts of Excel VBA variables, including how to declare them, how to set their scope and all sorts of other tips and tricks. The blog also covers constants in Excel macros.
A couple of miscellaneous things which will make your coding life much more efficient!
SHIFT + F2 and also SHIFT + CTRL + F2are useful keys for moving to and from a variable definition. Here's how they work:
Right-click anywhere on a variable and choose the option shown to go to its declaration (or just click on the variable and press SHIFT + F2).
When you go to a variable's definition, Excel VBA highlights the variable in question:
Excel takes you to the definition of the variable and highlights it.
If you press SHIFT + CTRL + F2 it acts like an undo button for positioning, and takes you back to your previous position in the code, then the one before that, then the one before that, and so on.
The case of a variable (whether it appears in capitals, lower case or some combination of the two) is entirely determined by the DIM statement declaring it:
There is no point typing in herorating in the correct case ...
... because Excel VBA will correct it for you when you move off the line.
This means that any changes you make to case in a DIM statement propagate through your entire procedure's code:
|Changes you make to a variable here ...||... will apply wherever the variable is used in your procedure|
The net effect of the above is that you should type variables in in lower case - it's quicker, and VBA will correct the case for you when you press ENTER.
|Parts of this blog|
25 Aytoun Street