In VBA What Does DIM Mean?

What does Dim mean in the VBA language? This is a question that I get asked a lot in by students in my courses. Dim in the VBA language is short for Dimension and it is used to declare variables. The Dim statement is put at the start of a VBA module after the Sub statement (short for Subroutine). It is a way to refer to the declared term rather than the same object over and over again. For example a range might need to be referred to 10 times within a macro. If you declare the variable up front you only have to use the declared variable rather than the range reference

A listing of Dim statements helps users read your code. An example of some commonly used Dim statements are as follows:

Declare a String Variable

Dim str as String

The above declares the term ‘str’ as a String variable, a string is text. See below for the output for the txt string.

txt = “Cowboy”

After this line instead of typing Cowboy - txt is typed - in the VBA language the two are now the same thing and every time instead of typing Cowboy you would type txt.

Declare a Range Variable

Dim rng as Range

The above declares the term ‘rng’ as a Range variable, a Range is a cell or grouping of cells. See below for the output for the rng variable.

Set rng = [A1:A10]

Because a range is part of the object hierarchy the Set statement needs to be added before the rng. The above statement means that rng is equal to [A1:A10] and now instead of typing [A1:A10] you simply type rng.

Declare a sheet Variable

Dim sh as Worksheet

The above declares the term ‘sh’ as a Sheet variable. A Sheet is is a worksheet and is mostly used to denote a worksheet inside the active workbook.

Set sh = Sheet1

The above sets the term ‘sh’ to equal the worksheet code name Sheet1. Don’t confuse the worksheet code name with the worksheet name.

Declare an Integer Variable

Dim i as Integer

An integer is a whole number and i is a very common variable for integers.

i = 1

The above is one way to reference an integer.

A list of possible variables is in the table below.

Understanding the upper and lower bounds of each data type helps to decide which variable to use in which situation but this comes with practice and time.

The Importance of Variable Declaration

The use of variables helps to generate a list of variables in a single place where both you and others can refer.  In time the list of dim statements in your language will narrow so you use the same shortened dim statements over and over again. For example using (rng) to denote a Range, using (i) for the declaration of an Integer. The Dim statements are a library that help keep track of your VBA coding in an ordered and logical way.

Dim statements with the help of Option Explicit allow you to trap spelling mistakes which have a habit of creeping in.  They are instantly flagged when you run a procedure as Option Explicit acts as your personal goal keeper.  Nothing that is spelt incorrectly will get past Option Explicit.  Also no new variables are allowed without being declared with the Dim statement.

Dim statements allow you to stay in control of what is being declared.  If the variables are not declared then the Visual Basic Editor decides what data type the variable will be in order for the code not to fail. 

Declaring your variables is considered best practice.  It is an ordered catalogue of the short cuts you will use in code with the added advantage of your code running faster with correct use of Dim statements.

Do You Need to Declare Your Variables?

In short no - but The Dim statement should always be used in conjunction with the Option Explicit statement. The Option Explicit term forces the declaration of all variables and I heartily recommend getting into the habit of using it. You can force all workbooks to have Option Explicit at the top of each sheet and regular module by going through the following process.

On the Tools menu choose Options.

Under Editor - Require Variable Declaration needs to be ticked.

The VBA language is reasonably easy to learn for people using Excel a lot but the terms first need to be understood. The Dim statement is a great place to start, after you have pushed your recorded macros to the limit. Enjoy the journey and the best of luck.