VBA Dim – A Complete Guide

VBA Dim

“One man’s constant is another man’s variable.” – Alan Perlis

 
 

This post provides a complete guide to using the VBA Dim statement.

The first section provides a quick guide to using the Dim statement including examples and the format of the Dim statement.

The rest of the post provides the most complete guide you will find on the VBA Dim Statement.

If you are interested in declaring parameters then you can read about them here.

 
 

A Quick Guide to using the VBA Dim Statement

DescriptionFormatExample
Basic variableDim [variable name] As [Type]Dim count As Long
Dim amount As Currency
Dim name As String
Dim visible As Boolean
Fixed StringDim [variable name] As String * [size]


Dim s As String * 4
Dim t As String * 10
VariantDim [variable name] As Variant
Dim [variable name]
Dim var As Variant
Dim var
Object using Dim and NewDim [variable name] As New [object type]Dim coll As New Collection
Dim coll As New Class1
Object using Dim, Set and NewDim [variable name] As [object type]
Set [variable name] = New [object type]
Dim coll As Collection
Set coll = New Collection

Dim coll As Class1
Set coll = New Class1
Static arrayDim [variable name]([first] To [last] ) As [Type]
Dim arr(1 To 6) As Long
Dynamic arrayDim [variable name]() As [Type]
ReDim [variable name]([first] To [last])
Dim arr() As Long
ReDim arr(1 To 6)
External Library
(Early Binding)*
Dim [variable name] As New [item]Dim dict As New Dictionary
External Library
(Early Binding using Set)*
Dim [variable name] As [item]
Set [variable name] = New [item]
Dim dict As Dictionary
Set dict = New Dictonary
External Library
(Late Binding)
Dim [variable name] As Object
Set [variable name] = CreateObject("[library]")

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

 
*Note: Early binding requires that you add the reference file using Tools->References from the menu. See here for how to add the Dictonary reference.

 

Useful Links

Declaring parameters in a sub or function
Using Objects in VBA
VBA Arrays
VBA Collection
VBA Dictionary
VBA Workbook
VBA Worksheet
 
 

What is the VBA Dim Statement

The Dim keyword is short for Dimension. It is used to declare variables in VBA.

Declare means we are telling VBA about a variable we will use later.

There are four types of Dim statements. They are all pretty similar in terms of syntax.

They are

  1. Basic variable
  2. Variant
  3. Object
  4. Array

 
 
The following is a brief description of each type

  1. Basic variable – this variable type holds one value. These are the types such as Long, String, Date, Double, Currency.
  2.  

  3. Variant – VBA decides at runtime which type will be used. You should avoid variants where possible but in certain cases it is a requirement to use them.
  4.  

  5. Object – This is a variable that can have multiple methods(i.e. subs/functions) and multiple properties(i.e. values). There are 3 kinds:
     

    1. Excel objects such as the Workbook, Worksheet and Range objects.
    2. User objects created using Class Modules.
    3. External libraries such as the Dictionary.
  6.  

  7. Array – this is a group of variables or objects.

 
 

In the next section, we will look at the format of the VBA Dim statement with some examples of each.

In later sections we will look at each type in more detail.
 
 

Format of the VBA Dim Statement

 
The format of the Dim statement is shown below

' 1. BASIC VARIABLE
' Declaring a basic variable
Dim [variable name] As [type]

' Declaring a fixed string
Dim [variable name] As String * [size]

' 2. VARIANT
Dim [variable name] As Variant
Dim [variable name]

' 3. OBJECT
' Declaring an object
Dim [variable name] As [type]

' Declaring and creating an object
Dim [variable name] As New [type]

' Declaring an object using late binding
Dim [variable name] As Object

' 4. ARRAY
' Declaring a static array
Dim [variable name](first To last) As [type]

' Declaring a dynamic array
Dim [variable name]() As [type]

 
 
Below are examples of using the different formats

Sub Examples()

    ' 1. BASIC VARIABLE
    ' Declaring a basic variable
    Dim name As String
    Dim count As Long
    Dim amount As Currency
    Dim eventdate As Date
    
    ' Declaring a fixed string
    Dim userid As String * 8
    
    ' 2. VARIANT
    Dim var As Variant
    Dim var
    
    ' 3. OBJECT
    ' Declaring an object
    Dim sh As Worksheet
    Dim wk As Workbook
    Dim rg As Range
    
    ' Declaring and creating an object
    Dim coll1 As New Collection
    Dim o1 As New Class1
    
    ' Declaring an object - create object below using Set
    Dim coll2 As Collection
    Dim o2 As Class1
    
    Set coll2 = New Collection
    Set o2 = New Class1
    
    ' Declaring and assigning using late binding
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 4. ARRAY
    ' Declaring a static array
    Dim arrScores(1 To 5) As Long
    Dim arrCountries(0 To 9) As String
    
    ' Declaring a dynamic array - set size below using ReDim
    Dim arrMarks() As Long
    Dim arrNames() As String
    
    ReDim arrMarks(1 To 10) As Long
    ReDim arrNames(1 To 10) As String

End Sub

 
 
We will examine these different types of Dim statements in the later sections.

 
 

How to Use Dim with Multiple Variables

We can declare multiple variables in a single Dim statement

Dim name As String, age As Long, count As Long

 
 

If we leave out the type then VBA automatically sets the type to be a Variant. We will see more about Variant later.

' Amount is a variant
Dim amount As Variant

' Amount is a variant
Dim amount

' Address is a variant - name is a string
Dim name As String, address

' name is a variant, address is a string
Dim name, address As String

 
 
When you declare multiple variables you should specify the type of each one individually

Dim wk As Workbook, marks As Count, name As String

 
 
You can place as many variables as you like in one Dim statement but it is good to keep it to 3 or 4 for readability.
 
 

Where Should I Put the Dim Statement?

The Dim statement can be placed anywhere in a procedure. However, it must come before any line where the variable is used.

If the variable is used before the Dim statement then you will get a “variable not defined” error


 
 

When it comes to the positioning your Dim statements you can do it in two main ways. You can place all your Dim statements at the top of the procedure

Sub DimTop()

    ' Placing all the Dim statements at the top
    Dim count As Long, name As String, i As Long
    Dim wk As Workbook, sh As Worksheet, rg As Range
    
    Set wk = Workbooks.Open("C:\Docs\data.xlsx")
    Set sh = wk.Worksheets(1)
    Set rg = sh.Range("A1:A10")
    
    For i = 1 To rg.Rows.count
        count = rg.Value
        Debug.Print count
    Next i
  
End Sub

 
 
OR you can declare the variables immediately before you use them:

Sub DimAsUsed()

    Dim wk As Workbook
    Set wk = Workbooks.Open("C:\Docs\data.xlsx")
    
    Dim sh As Worksheet
    Set sh = wk.Worksheets(1)
    
    Dim rg As Range
    Set rg = sh.Range("A1:A10")
    
    Dim i As Long, count As Long, name As String
    For i = 1 To rg.Rows.count
        count = rg.Value
        name = rg.Offset(0, 1).Value
        Debug.Print name, count
    Next i
  
End Sub

 
 

I personally prefer the latter as it makes the code neater and it is easier to read, update and spot errors.
 
 

Using Dim in Loops

Placing a Dim statement in a Loop has no effect on the variable.

When VBA starts a Sub (or Function), the first thing it does is to create all the variables that have been declared in the Dim statements.

The following 2 pieces of code are almost the same. In the first, the variable Count is declared before the loop. In the second it is declared within the loop.

Sub CountOutsideLoop()

    Dim count As Long

    Dim i As Long
    For i = 1 To 3
        count = count + 1
    Next i
    
    ' count value will be 3
    Debug.Print count

End Sub

 
 

Sub CountInsideLoop()

    Dim i As Long
    For i = 1 To 3
        Dim count As Long
        count = count + 1
    Next i
    
    ' count value will be 3
    Debug.Print count

End Sub

 
 

The code will behave exactly the same because VBA will create the variables when it enters the sub.

 
 

Can I use Dim to Assign a Value?

In languages like C++, C# and Java, we can declare and assign variables on the same line

' C++
int i = 6
String name = "John"

 
 
We cannot do this in VBA. We can use the colon operator to place the declare and assign lines on the same line.

Dim count As Long: count = 6

 
 

We are not declaring and assigning in the same VBA line. What we are doing is placing these two lines(below) on one line in the editor. As far as VBA is concerned they are two separate lines as here:

Dim count As Long
count = 6

 
 
Here we put 3 lines of code on one editor line using the colon:

count = 1: count = 2: Set wk = ThisWorkbook

 
 

There is really no advantage or disadvantage to assigning and declaring on one editor line. It comes down to a personal preference.
 
 

Is Dim Actually Required?

The answer is that it is not required. VBA does not require you to use the Dim Statement.

However, not using the Dim statement is a poor practice and can lead to lots of problems.

You can use a variable without first using the Dim statement. In this case the variable will automatically be a variant type.

This can lead to problems such as

  1. All variables are variants (see the Variant section for issues with this).
  2. Some variable errors will go undetected.

 
 

Because of these problems it is good practice to make using Dim mandatory in our code. We do this by using the Option Explicit statement.

 
 

Option Explicit

We can make Dim mandatory in a module by typing “Option Explicit” at the top of a module.

We can make this happen automatically in each new module by selecting Tools->Options from the menu and checking the box beside “Require Variable Declaration”. Then when you insert a new module, “Option Explicit” will be automatically added to the top.

VBA Require Variable Declaration
 
 

Let’s look at some of the errors that may go undetected if we don’t use Dim.

 
 

Variable Errors

In the code below we use the Total variable without using a Dim statement

Sub NoDim()

    Total = 6
    
    Total = Total + 1
    
    Debug.Print Total

End Sub

 
 
If we accidentally spell Total incorrectly then VBA will consider it a new variable.

In the code below we have misspelt the variable Total as Totall.

Sub NoDimError()

    Total = 6
    
    ' The first Total is misspelt
    Totall = Total + 1
    
    ' This will print 6 instead of 7
    Debug.Print Total

End Sub

 
 
VBA will not detect any error in the code and an incorrect value will be printed.

 
 
Let’s add Option Explicit and try the above code again

Option Explicit 

Sub NoDimError()

    Total = 6
    
    ' The first Total is misspelt
    Totall = Total + 1
    
    ' This will print 6 instead of 7
    Debug.Print Total

End Sub

 
 
Now when we run the code we will get the “Variable not defined” error. To stop this error appearing we must use Dim for each variable we want to use.

When we add the Dim statement for Total and run the code we will now get an error telling us that the misspelt Totall was not defined.

variable not defined 2
 
 
This is really useful as it helps us find an error that would have otherwise gone undetected.

Keyword Misspelt Error

Here is a second example which is more subtle.

When the following code runs it should change the font in cell A1 to blue.

However, when the code runs nothing happens.

Sub SetColor()

    Sheet1.Range("A1").Font.Color = rgblue

End Sub

 
 

The error here is that rgblue should be rgbBlue. If you add Option Explicit to the module, the error “variable not defined” will appear. This makes solving the problem much easier.

These two examples are very simple. If you have a lot of code then errors like this can be a nightmare to track down.
 
 

Using Dim with Basic Variables

VBA has the same basic variable types that are used in the Excel Spreadsheet.

You can see a list of all the VBA variable types here.

However, most of the time you will use the following ones

TypeStorageRangeDescription
Boolean2 bytesTrue or FalseThis variable can be either True or False.
Long4 bytes-2,147,483,648 to 2,147,483,647Long is short for Long Integer. Use this instead of the Integer* type.
Currency8 bytes -1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive valuesSimilar to Double but has only 4 decimal places
Double8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date8 bytesJanuary 1, 100 to December 31, 9999
Stringvaries0 to approximately 2 billionHolds text.

 
 
*Originally we would use the Long type instead of Integer because the Integer was 16-bit and so the range was -32,768 to 32,767 which is quite small for a lot of the uses of integer.

However on a 32 bit(or higher) system the Integer is automatically converted to a Long. As Windows has been 32 bit since Windows 95\NT there is no point in using an Integer.

In a nutshell, always use Long for an integer type in VBA.

 
 

Fixed String Type

There is one unusual basic variable type in VBA that you may not be familiar with.

This is the fixed string type. When we create a normal string in VBA we can add text and VBA will automatically resize the string for us

Sub StringType()

    Dim s As String
    
    ' s is "John Smith"
    s = "John Smith"
    
    ' s is "Tom"
    s = "Tom"

End Sub

 
 
A fixed string is never resized. This string will always be the same size no matter what you assign to it

Here are some examples

Sub FixedString()
    
    Dim s As String * 4
    
    ' s is "John"
    s = "John Smith"
    
    ' s = "Tom "
    s = "Tom"

End Sub

 
 

Using Dim with Variants

When we declare a variable to be a variant, VBA will decide at runtime which variable type it should be.

We declare variants as follows

' Both are variants
Dim count
Dim count As Variant

 
 

This sounds like a great idea in theory. No more worrying about the variable type


Sub UsingVariants()
    
    Dim count As Variant
        
    count = 7
    
    count = "John"
    
    count = #12/1/2018#

End Sub

 
 

However, using variants is poor practice and this is why:

  1. VBA will not notice incorrect type errors(i.e. Data Mismatch).
  2. You cannot access the Intellisense.
  3. VBA guesses the best type and this may not be what you want.

 
 

Type Errors

Errors are your friend!

They may be annoying and frustrating when they happen but they are alerting you to future problems which may not be so easy to find.

The Type Mismatch error alerts you when incorrect data is used.

For example. Imagine we have a sheet of student marks. If someone accidentally(or deliberately) replaces a mark with text then the data is invalid.

If we use a variant to store marks then no error will occur

Sub MarksVariant()
    
    Dim marks As Variant
    
    Dim i As Long
    For i = 1 To 10
        
        ' Read the mark
        mark = Sheet1.Range("A" & i).Value
        
    Next

End Sub

 
 

This is not good because there is an error with your data and you are not aware of it.

If you make the variable Long then VBA will alert you with a “Type Mismatch” error if the values are text.

Sub MarksLong()
    
    Dim mark As Long
    
    Dim i As Long
    For i = 1 To 10
        
        ' Read the mark
        mark = Sheet1.Range("A" & i).Value
        
    Next

End Sub

 
 

Accessing the Intellisense

The Intellisense is an amazing feature of VBA. It gives you the available options based on the type you have created.

Imagine you declare a worksheet variable using Dim

Dim wk As Workbook

When you use the variable wk with a decimal point, VBA will automatically display the available options for the variable.

You can see the Intellisense in the screenshot below

VBA Intellisense
 
 

If you use Variant as a type then the Intellisense will not be available

Dim wk As Variant

 
 
This is because VBA will not know the variable type until runtime.

 
 

Using Dim with Objects

If you don’t know what Objects are then you can read my article about VBA Objects here.

There are 3 types of objects:

  1. Excel objects
  2. Class Module objects
  3. External library objects

 
Note: The VBA Collection object is used in a similar way to how we use Class Module object. We use new to create it.

Let’s look at each of these in turn.
 
 

Excel objects

Excel objects such as the Workbook, Worksheet, Range, etc. do not use New because they are automatically created by Excel. See When New is not required.

When a workbook is created or opened then Excel automatically creates the associated object.

For example, in the code below we open a workbook. VBA will create the object and the Open function will return a workbook which we can store in a variable

Sub OpenWorkbook()
    
    Dim wk As Workbook
    Set wk = Workbooks.Open("C:\Docs\data.xlsx")

End Sub

 
 
If we create a new worksheet, a similar thing happens. VBA will automatically create it and provide use access to the object.

Sub AddSheet()
    
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets.Add

End Sub

 
 
We don’t need to use the New keyword for these Excel objects.

We just assign the variable to the function that either creates a new object or that gives us access to an existing one.

Here are some examples of assigning the Workbook, Worksheet and range variables

Sub DimWorkbook()
    
    Dim wk As Workbook
    
    ' assign wk to a new workbook
    Set wk = Workbooks.Add
    
    ' assign wk to the first workbook opened
    Set wk = Workbooks(1)
    
    ' assign wk to The workbook Data.xlsx
    Set wk = Workbooks("Data.xlsx")
    
    ' assign wk to the active workbook
    Set wk = ActiveWorkbook
    
End Sub

 
 

Sub DimWorksheet()
    
    Dim sh As Worksheet
    
    ' Assign sh to a new worksheet
    Set sh = ThisWorkbook.Worksheets.Add
    
    ' Assign sh to the leftmost worksheet
    Set sh = ThisWorkbook.Worksheets(1)
    
    ' Assign sh to a worksheet called Customers
    Set sh = ThisWorkbook.Worksheets("Customers")
    
    ' Assign sh to the active worksheet
    Set sh = ActiveSheet

End Sub

 
 

Sub DimRange()

    ' Get the customer worksheet
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Customers")
    
    ' Declare the range variable
    Dim rg As Range
    
    ' Assign rg to range A1
    Set rg = sh.Range("A1")
    
    ' Assign rg to range B4 to F10
    Set rg = sh.Range("B4:F10")
    
    ' Assign rg to range E1
    Set rg = sh.Cells(1, 5)
    
End Sub

 
 
If you want to know more about these objects you can check out these articles: VBA Workbook, VBA Worksheet and VBA Ranges and Cells.

 
 

Using Dim with Class Module Objects

In VBA we use Class Modules to create our own custom objects. You can read all about Class Modules here.

If we are creating an object then we need to use the New keyword.

We can do this in the Dim statement or in the Set statement.

The following code creates an object using the New keyword in the Dim statement:

' Declare and create
Dim o As New class1
Dim coll As New Collection

 
 

Using New in a Dim statement means that exactly one object will be created each time our code runs.

Using Set gives us more flexibility. We can create many objects from one variable. We can also create an object based on a condition.

This following code shows how we create a Class Module object using Set.

(To create a Class Module, go to the project window, right-click on the appropiate workbook and select “Insert Class Module”. See Creating a Simple Class Module for more details.)

' Declare only
Dim o As Class1

' Create using Set
Set o = New Class1

 
 

Let’s look at an example of using Set. In the code below we want to read through a range of data. We only create an object if the value is greater than 50.

We use Set to create the Class1 object. This is because the number of objects we need depends on the number of values over 50.

Sub UsingSet()
    
    ' Declare a Class1 object variable
    Dim o As Class1
    
    ' Read a range
    Dim i As Long
    For i = 1 To 10
        If Sheet1.Range("A" & i).Value > 50 Then

            ' Create object if condition met
            Set o = New Class1
            
        End If
    Next i

End Sub

 
 
I’ve kept this example simple for clarity. In a real world version of this code we would fill the Class Module object with data and add it to a data structure like a Collection or
Dictionary.

Here is an example of a real world version based on the data below:
 
 
dim sample data
 
 

' Class Module - clsStudent
Public Name As String
Public Subject As String

' Standard Module
Sub ReadMarks()

    ' Create a collection to store the objects
    Dim coll As New Collection
    
    ' Current Region gets the adjacent data
    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
    
    Dim i As Long, oStudent As clsStudent
    For i = 2 To rg.Rows.Count
        
        ' Check value
        If rg.Cells(i, 1).Value > 50 Then
            ' Create the new object
            Set oStudent = New clsStudent
            
            ' Read data to the student object
            oStudent.Name = rg.Cells(i, 2).Value
            oStudent.Subject = rg.Cells(i, 3).Value
            
            ' add the object to the collection
            coll.Add oStudent
            
        End If
        
    Next i
    
    ' Print the data to the Immediate Window to test it
    Dim oData As clsStudent
    For Each oData In coll
        Debug.Print oData.Name & " studies " & oData.Subject
    Next oData

End Sub

 
 
To learn more about Set you can check out here.

 
 

Objects from an External Library

A really useful part VBA is the way we have access to external libraries. This opens up a whole new world to what we can do.

Examples are the Access, Outlook and Word libraries that allow us to communicate with these applications.

We can use libraries for different types of data structures such as the Dictionary, the Arraylist, Stack and Queue.

There are libraries for scraping a website (Microsoft HTML Object Library), using Regular Expressions (Microsoft VBScript Regular Expressions) and many other tasks.

We can create these objects in two ways:

  1. Early Binding
  2. Late Binding

 
Let’s look at these in turn.
 

Early Binding

Early binding means that we add a reference file. Once this file is added we can treat the object like a class module object.

We add a reference using Tools->Reference and then we check the appropriate file in the list.

For example, to use the Dictionary we place a check on “Microsoft Scripting Runtime”

vba references dialog
 
 

Once we have the reference added we can use the Dictionary like a class module object

Sub EarlyBinding()

    ' Use Dim only
    Dim dict1 As New Dictionary
    
    ' Use Dim and Set
    Dim dict2 As Dictionary
    Set dict2 = New Dictionary

End Sub

 
 
The advantage of early binding is that we have access to the Intellisense. The disadvantage is that it may cause conflict issues on other computers.

The best thing to do is to use early binding when writing the code and then use late binding if distributing your code to other users.
 
 

Late Binding

Late binding means that we create the object at runtime.

We declare the variable as an “Object” type. Then we use CreateObject to create the object.

Sub LateBinding()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
End Sub

 
 

Using Dim with Arrays

There are two types of arrays in VBA. They are:

  1. Static – the array size is set in the Dim statement and it cannot change.
  2. Dynamic – the array size is not set in the Dim statement. It is set later using the ReDim statement.
' STATIC ARRAY

' Stores 7 longs - 0 to 6
Dim arrLong(0 To 6) As Long

' Stores 7 longs - 0 to 6
Dim arrLong(6) As String

 
 

A dynamic array gives us much more flexibility. We can set the size while the code is running.

We declare a dynamic array using the Dim statement and we set the size later using ReDim.

' DYNAMIC ARRAY

' Declare the variable
Dim arrLong() As Long

' Set the size
ReDim arrLong(0 To 6) As Long

 
 

Using ReDim

The big difference between Dim and ReDim is that we can use a variable in the ReDim statement. In the Dim statement, the size must be a constant value.

Sub UserSet()

    ' Declare the variable
    Dim arrLong() As Long
    
    ' Ask the user for the size
    Dim size As Long
    size = InputBox("Please enter the size of the array.", Default:=1)
    
    ' Set the size based on the user input
    ReDim arrLong(0 To size) As Long

End Sub

 
 

We can actually use the Redim Statement without having first used the Dim statement.

In the first example you can see that we use Dim:

Sub UsingDimReDim()

    ' Using Dim
    Dim arr() As String

    ReDim arr(1 To 5) As String
    
    arr(1) = "Apple"
    arr(5) = "Orange"
    
End Sub

 
 

In the second example we don’t use Dim:

Sub UsingReDimOnly()

    ' Using  ReDim only
    ReDim arr(1 To 5) As String
    
    arr(1) = "Apple"
    arr(5) = "Orange"
    
End Sub

 
 
The advantage is that you don’t need the Dim statement. The disadvantage is that it may confuse someone reading your code. Either way it doesn’t make much difference.

You can use the Preserve keyword with ReDim to keep existing data while you resize an array. You can read more about this here here.

You can find everything you need to know about arrays in VBA here.

 
 

Troubleshooting Dim Errors

The table below shows the errors that you may encounter when using Dim. See VBA Errors for an explanation of the different error types.

ErrorTypeCause
Array already dimensionedCompileUsing Redim on an array that is static
Expected: identifierSyntaxUsing a reserved word as the variable name
Expected: New of type nameSyntaxThe type is missing from the Dim statement
Object variable or With block variable not setRuntimeNew was not used to create the object(see Creating an Object)
Object variable or With block variable not setRuntimeSet was not used to assign an object variable.
User-defined type not definedCompileThe type is not recognised. Can happen if a reference file is not added under Tools->Reference or the Class Module name is spelled wrong.
Statement invalid outside Type blockCompileVariable name is missing from the Dim statement
Variable not definedCompileThe variable is used before the Dim line.

 
 

Local Versus Global Variables

When we use Dim in a procedure (i.e. a Sub or Function), it is considered to be local. This means it is only available with this procedure.

Global variables are declared outside of procedures. Depending on the type, they can be accessed by all procedures in the same module or by all the procedures in all modules in the current workbook.

In the code below we have declared count as a global variable:

' Global
Dim count As Long

Sub UseCount1()

    count = 6
    
End Sub


Sub UseCount2()

    count = 4
    
End Sub

 
 
What happens if we have a global variable and a local variable with the same name?

It doesn’t actually cause an error. VBA gives the local declaration precedence

' Global
Dim count As Long

Sub UseCount()
    ' Local
    Dim count As Long
    
    ' Refers to the local count
    count = 6
    
End Sub

 
 
Having a situation like this can only lead to a world of trouble as it is difficult to track which count is being used.

In general global variables should be avoided where possible. They make the code very difficult to read because their values can be changed anywhere in the code. This makes errors difficult to spot and resolve.

It is important to know and understand global variables as you as you may come across them in existing code.

 
 

Dim Versus Private

There is a keyword in VBA called Private.

If we use a Private keyword with a variable or a sub/function then this item is only available within the current module.

Using Dim and Private for a variable has the same result

' Available throughout this module
Private priCount As Long
Dim dimCount As Long

Sub UseCount()

    ' Only Available in this sub
    Private priName As String
    Dim dimName As String
    
End Sub

 
 
In VBA, the convention is to use Private for global variables and Dim for locals

' Available throughout this module
Private priCount As Long

Sub UseCount()
    ' Local Only
    Dim dimName As String
    
End Sub

 
 

There are 2 other declaration types in VBA called Public and Global.

The following is a summary of all 4 types:

  1. Dim – used to declare local variables i.e. in procedures.
  2. Private – used to declare global variables and procedures. These variables are available to the current module only.
  3. Public – used to declare global variables and procedures. These variables are available in all modules.
  4. Global – an older and obsolete version of Public. Can only be used in standard modules. It only exists for backward compatibility.

 
 

Conclusion

This concludes the article on the VBA Dim Statement. If you have any questions or thoughts then please let me know in the comments below.

 
 

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

 
 

Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the website, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook

 
 


 
 

26 comments

  1. Hi Paul,
    in the description 3.III it appears there is a sentence within a sentence…III. External librDeclaring a static arrayaries such as the Dictionary. Can you clarify this section please?
    Thanks for sharing your expertise!

  2. Hi Paul
    Appears to be a proofing error here… Please clarify
    For example. Imagine we have a sheet of student marks. If someone accidentally There are 3 types of objects(or deliberately) replaces a mark with text then the data is invalid.

  3. Hello It is not discussed what happens that we use class or collection. Can we go by some other methods instead of creating class or objects?
    I think the concept of class or collection is difficult for beginners.
    I saw some links for complete VBA Class module, but the fact is that I have limited time and beginner can absorb some amount of knowledge only. They can’t learn all the material in one shot. Anyway, I am trying to catch up with these.

    This code gave me errors:
    Sub UsingSet()

    ‘ Declare a Class1 object variable
    Dim o As Class1

    ‘ Read a range
    Dim i As Long
    For i = 1 To 10
    If Sheet1.Range(“A” & i).Value > 50 Then

    ‘ Create object if condition met
    Set o = New Class1

    End If
    Next i

    End Sub
    Al
    I am learning VBA and I believe in a life time learning. I can spend time on this since I am home, later on, in the future that won’t be possible, I don’t know how to catch up with these materials. Is there any advise how to break each lesson for myself?

    ———————–
    Yesterday I received some materials on worksheet and workbook material and the line of code
    codeNameFrom…. was giving error and it was saying undefined.
    ===============

    1. Hi,

      If you are a beginner then I wouldn’t worry about Collections and Class Modules for the moment.

      The most important thing to concentrate on is ranges/cells, the workbook and the worksheet.

      I have created a tutorial aimed at complete VBA beginner here.

      What error message are you getting in the code?

  4. Hi Paul, this is an excellent “Reference Source” – I have relearnt a lot that I had taken for granted in VBA coding. Thanks a lot.
    John

  5. Paul,

    Thank’s for your useful clarifications.
    I have notice a copy/paste error in the comments of the first example in “Fixed String Type” section (i.e.: Dim s As String); shouldn’t it be:
    ‘ s is “John Smith” instead of ‘ s is “John”
    ‘ s is “Tom” instead of ‘ s is “Tom ”
    Alike David L Bedford, I also appreciated the Early vs Late Binding use recommendation.
    As well as the “Fixed String Type” I didn’t have the opportunity to use yet.

    Best (from France).

  6. Great article!
    Still supported but old is the idea of using a character suffix instead of spelling out the type
    Instead of:
    dim i as integer
    You can say
    dim i%
    You do not need the “As Integer”
    How I usually code when I need to dimension many of the same type of variable is I use the type spelled out only on the last variable and the character suffix for the others, something like this:
    dim i%, j%, k as Integer
    VBA will make the variables all integers in the above example
    I never use it for just one variable alone because saying for example “As Integer” is clearer. That is why the last variable, I will always spell out.
    There are only a few possible suffixes:
    Currency: @
    Double: #
    Integer: %
    Long: &
    Single: !
    String: $

    1. Thanks for that information Dan, very interesting.

      I actually remember using $ for strings when I first started using Basic on a home computer many moons ago.

      Paul

  7. Awesome post, thanks Paul!

    Regarding early binding, I was wondering what are your thoughts on adding the reference programmatically.
    Eg. For the Microsoft Scripting Runtime Library:
    wb.VBProject.References.AddFromGuid “{420B2830-E718-11CF-893D-00A0C9054228}”, 1, 0

    1. Hi Martin,

      It depends on what your are trying to achieve. I did this kind of COM programming many years ago through C++.

      Regards
      Paul

  8. Hi Paul
    A point to note, a Variant object acts as a Double when used numerically.

    Good simple explanation of early vs. late bindings 🙂
    The reason it’s best practice to use Late bindings is that it makes code transportable across different versions of Excel. A module written using Office 2016 that accesses Word or Access objects may not work on a PC with Office 2007 if early binding is used as the incorrect references may be set. Late binding avoids this as Excel always uses the latest available objects for the version of Office on the PC on which the VBA is running.

    Way beyond the interest of most people, but I saw you’ve used C in the past; what is a LongPtr data type for? I was hoping it was a true pointer, which is useful for VBA functions which for example parse cell formulae.
    I initially thought it was the Excel version of a Pointer variable which doesn’t have a value in itself, but the excel documentation suggests it’s a version of Long or LongLong. (I haven’t used C pointers, my pointer knowledge is from pascal, way before C was around)

    1. Hi Johnny,

      Late binding uses COM(component object modeling) technology to interrogate the files at runtime. This makes it more flexible and allows it to select the latest version of a function. I did some work with COM in the 1990s – something I hope to never repeat:-)

      LongPtr is used to make the code portable of 32/64 bit systems. It is becomes a long integer in a 32-bit system and longlong integer in a 64-bit system.

      Paul

    1. Hi Lauren,

      I’m not clear what you mean by “used over several worksheets”.

      You can use dim to declare multiple worksheet variables if that’s what you’re asking.

      Paul

  9. Hello Paul,
    Paul, thank you for your awesome work. My question with regard to “dimming” variables: Is it possible to let a variable declaration pick up a name from e.g. the contents of a cell, such as: Dim Range(“A1″).Text as variant”, where A1 contains a String? I am looking desperately for a way to name variables automatically based on the Headers of Columns in my worksheet. A Hint of yours would be greatly appreciated. Thank you in advance. Regards, hgl

    1. What you are talking about is Refection. VBA doesn’t support this but if you use google you can see some ways that are used to get around it.

  10. Hi Paul,

    thank you for this great tutorials and concepts. I hope you expand the topics similar with this VBA and Excel tp VBA and Access-SQL Database concepts.

    Thank you!
    Kind regards,

    Aaron =)

Leave a Reply

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