Excel VBA and Variables

Using Option Explicit

Variables can speed up your code and make maintenance a lot easier. You should always declare or Dim (technical term) your variables, here’s why.

Declaring means informing Excel you are going to use a variable. Once you have declared a variable by using the Dim statement, then you can use the variable in your code.

Dim strName as String

The above line of code declares the variable strName and defines it as a String (text) type. Dimming a variable means declaring it, usually at the top of your code.

Specifying the type of variable saves memory.

I recommend you change one of VBA’s options when you use variables. In the VBA window click the Tools menu item and select Options.

VBA options

Make sure Require Variable Declaration is ticked and click OK.

Variables Declaration

This will automatically insert the statement

Option Explicit

at the top of all your new code windows. This statement instructs Excel to require every variable to have a Dim statement.

If you use a variable without a Dim statement, you will see the following window displayed.

Variable error

By declaring your variables you can avoid typing errors inadvertently creating extra, invalid variables. If you don’t use Option Explicit then debugging your code can be a nightmare.

Once a variable is declared you can type the name quickly by using the Ctrl + Spacebar shortcut. This is an intellisense feature.

VBA short cut

Type the first few letters of the variable then press Ctrl + Space, this will either fill in the variable name, or give you a list of options starting with those letters – see image below. Press the Tab key to accept the Blue entry.

Auto completeThis shortcut works for built-in keywords like Application. Type appl and press Ctrl + Spacebar to enter Application.

By the way, Dim is short for dimension, the term used in the early days of programming.


Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.