What Does Dim Mean When Coding VBA Macros?

What Does Dim Mean in VBA Excel 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 of 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, no you do not have to declare your variables while coding in VBA.  Any undeclared variables are defaulted to a Variant variable type which basically means that VBA will cycle through all of 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 for recommending to add 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. 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 it's 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 heartache, just trust me on this one.

Hopefully this provides you with the basic concepts of why you see all those Dims floating around out 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.

Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on Facebook, Twitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!