What Does Dim Mean When Coding VBA Macros?

By Chris Newman •  Updated: 02/19/14 •  5 min read
What Does Dim Mean When Coding VBA Macros?

What Is Dim And Why Do People Use It?

Dim is short for the word Dimension and it allows you to declare variable names and their type.  Dim is often found at the beginning of macro codes and has the following format:

Dim [Insert Variable Name] as [Insert Variable Type]

The variable name can be anything you want as long as it is one word and does not match the name of any VBA function, class, or accessor (so you couldn’t name your variable Worksheet or Sub).  The other part of a dimension statement is spelling out the Variable Type.  This restricts what kind of data can be stored in the variable.  I will not go into great detail about all the different variable types but I will list some of the ones I most often use:

  • Range – Stores an Excel range
  • Long – Has the ability to store any number between (2,147,483,647) to 2,147,483,647
  • String – Stores text (must surround text with quotation marks)
  • Worksheet – Stores a Worksheet
  • Boolean – Stores either a True (-1) or False (0) value

Note, that if you are declaring an Array variable you can also set the parameters of the array by writing the following:

Dim myArray(1 To 10) As Variant

Do I Have To Use Dim?

In short, you do not have to declare your variables while coding in VBA.  Any undeclared variables default to a Variant variable type, which means that VBA will cycle through all the variable types and pick one that allows your code to run without error.  I DO NOT RECOMMEND DOING THIS AS A BEST PRACTICE!

Can I Force Myself To Dimension All My Variables?

I’m so glad you asked!  If you type in the phrase Option Explicit at the very top of your module (before your first subroutine or function) you will be forced to dimension every single variable you create.  If you try to run your code without declaring a variable, the Visual Basic Editor will highlight the variable and give you the following error message:

Dim Compile Error Dialog Box

Automatically Add Option Explicit

It is possible to have the Visual Basic Editor automatically write Option Explicit at the top of every module.  This can be done by navigating to the Tools menu inside the VBE and selecting Options.

What Does Dim Mean in VBA Excel Macros

If you check Require Variable Declaration, the editor will automatically add the phrase Option Explicit to every module that is created.  This will help save you time and prevent you from forgetting to add this short but very powerful phrase!

Option Explicit Require Variable Declaration

I'd like to give a special thanks to Bob Phillips who recommended adding this tip to the post via the comment section. Thank you Bob!

Why Use Dim?

There are a bunch of reasons why you should use Dim and declare your variables.  Here are the ones I can think of (please leave a comment if you can think of any other reasons and I will add them to the list):

  1. It’s easier for Others to Understand Your Code.  If you have a bunch of variables scattered throughout you’re subroutines, it helps other Excel Gurus who are looking at your code to comprehend if you are using a variable to read in a number or store a range location.
  2. Keep Track of Your Variables and Stay Organized.  If you make it a common practice to have a declaration section in your code, you essentially are creating a vocabulary list that you can reference while you are coding.  This will allow you to easily keep track of all your variable names and what values they should have.
  3. Helps Prevent Spelling Errors. I make it a common practice to have at least one uppercase letter in all of my variables that are longer than three characters.  I then will type the body of my code in all lowercase letters.  The Visual Basic Editor will automatically capitalize any word that is stored in its own vocabulary list along with any variable that you have dimensioned.  So if I notice that a declared variable that I created did not capitalize after I typed its name, I know that I must have misspelled it.
  4. Stops the Wrong Type of Data from Getting Stored.   If a variable is set to only store numbers and you accidentally add a line of code that sets that variable equal to a text string, your code will error out.  This will serve as a flag that you either messed up your logic or you need to create a separate variable to store that text string.
  5. Standard Coding Best Practice.  If you haven’t gathered already, declaring variables is a computer coding best practice!  So just get in the habit of doing it.  In the long run, it will save you a lot of heartaches, just trust me on this one.

Hopefully, this provides you with the basic concepts of why you see all those Dims floating around on the internet.  It took me quite some time before I realized the importance of declaring my variables.  However, after I made an effort to really understand the concept, it really helped me write my VBA code in less time and with fewer errors.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.