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.

  1. Variables in Excel Visual Basic macros
  2. What is a Variable? VBA macro variables.
  3. Declaring Variables in Visual Basic for Applications
  4. Using Variables (Assigning and Retrieving Values)
  5. Forcing Declaration of Variables (Option Explicit)
  6. Useful Things to Know about Variables (this blog)
  7. The Scope of Variables
  8. Constants in Excel Visual Basic for Applications

This blog is part of our Excel macros online tutorial series.  Alternatively, we run training courses in the UK in Excel and also in Visual Basic for Applications (and are always looking for partners in the US or other English-language countries!).

Posted by Andy Brown on 05 September 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Useful Things to Know about Variables

A couple of miscellaneous things which will make your coding life much more efficient!

Moving to and from Variables

SHIFT + F2 and also SHIFT + CTRL + F2are useful keys for moving to and from a variable definition.  Here's how they work:

Going to definition of variable

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:

Highlighted variable

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.

 What Determines Variable Case?

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:

Lower case variable

There is no point typing in herorating in the correct case ...

 
Variable in corrected 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:

Changed case After changing case
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.

This blog has 0 threads Add post