In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.

I think I have heard/read that Bill Jellen (Mr Excel) doesn’t dim his variables. You have to be a very good coder AND typist to get away with not dimming your variables. I am happy to admit that I am not a great typist or a great coder. There are many good reasons to dim your variables. I will list them below, but first.

What does declaring (or dimming) your variables do?

The Dim statement is a through back to the early programming days and it stands for Dimension. You are basically telling Excel to put some memory aside to use for a variable, you can also specify what type of variable. Some examples below

Dim strName as String
 
Dim lngRow as Long
 
Dim wsReport as Worksheet
 
Dim c

Different variable types take up different amounts of memory. This is less of an issue these days with the amount of RAM on all machines.

The last example is typically used in For Each loops (example below) and it is a Variant variable type because you haven’t specified the type. If you omit the type  it defaults to Variant.

For Each c In Sheet1.Range("myRange")

If you use the Option Explicit command at the top of your Module then Excel will also check you have dimmed ALL your variables. It won’t run the code until all variables are dimmed. I use that all the time. See this post about automating Option Explicit.

Two great reasons to always declare your variables using the Dim statement.

1. When you have declared an object variable eg a worksheet, you get all the options pop up automatically as soon as you press the . this alone is a great reason to dim at least object variables. See image below.
   
2. Once you have dimmed any variable you can press Ctrl + Space to autocomplete after typing the first few letters – this saves typing. Let’s say you have dimmed many worksheet variables all prefixed with ws. By typing ws and the Ctrl + Space you can see all those variables listed and chose the one you want. See below

I always dim my variables and I always use Option Explicit.

I use at least one uppercase character in most of my variable names (variables used in loops typically are lowercase). Once you type the name Excel will convert the name to match the case of the one you dimmed. If you type the variable in lowercase and it is still in all lowercase you have made a typing mistake and you can correct it immediately. Point 2 above also allows you to reduce typing and select the exact name anyway.

If you use Option Explicit you can test the variable names in your code before running the code by using the Compile VBAProject under the Debug menu – see below.

This will let you know of any variables that are not defined and you can correct them before trying to run the code.