“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.
- 1 A Quick Guide to using the VBA Dim Statement
- 2 Useful Links
- 4 What is the VBA Dim Statement
- 6 Format of the VBA Dim Statement
- 7 How to Use Dim with Multiple Variables
- 9 Where Should I Put the Dim Statement?
- 11 Using Dim in Loops
- 12 Can I use Dim to Assign a Value?
- 14 Is Dim Actually Required?
- 16 Using Dim with Basic Variables
- 17 Using Dim with Variants
- 18 Using Dim with Objects
- 19 Using Dim with Arrays
- 20 Troubleshooting Dim Errors
- 21 Local versus Module versus Global Variables
- 22 Conclusion
- 23 What’s Next?
A Quick Guide to using the VBA Dim Statement
|Basic variable||Dim [variable name] As [Type]||Dim count As Long
Dim amount As Currency
Dim name As String
Dim visible As Boolean
|Fixed String||Dim [variable name] As String * [size]||Dim s As String * 4
Dim t As String * 10
|Variant||Dim [variable name] As Variant|
Dim [variable name]
|Dim var As Variant
|Object using Dim and New||Dim [variable name] As New [object type]||Dim coll As New Collection
Dim coll As New Class1
|Object using Dim, Set and New||Dim [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 array||Dim [variable name]([first] To [last] ) As [Type]||Dim arr(1 To 6) As Long|
|Dynamic array||Dim [variable name]() As [Type]|
ReDim [variable name]([first] To [last])
|Dim arr() As Long
ReDim arr(1 To 6)
|Dim [variable name] As New [item]||Dim dict As New Dictionary
(Early Binding using Set)*
|Dim [variable name] As [item]|
Set [variable name] = New [item]
|Dim dict As Dictionary
Set dict = New Dictonary
|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.
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.
- Basic variable
The following is a brief description of each type:
- Basic variable – this variable type holds one value. These are the types such as Long, String, Date, Double, Currency.
- 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.
- Object – This is a variable that can have multiple methods(i.e. subs/functions) and multiple properties(i.e. values). There are 3 kinds:
- Excel objects such as the Workbook, Worksheet and Range objects.
- User objects created using Class Modules.
- External libraries such as the Dictionary.
- Object – This is a variable that can have multiple methods(i.e. subs/functions) and multiple properties(i.e. values). There are 3 kinds:
- 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
' https://excelmacromastery.com/ 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 Long, 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:
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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
- All variables are variants (see the Variant section for issues with this).
- 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.
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.
Let’s look at some of the errors that may go undetected if we don’t use Dim.
In the code below we use the Total variable without using a Dim statement
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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.
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.
' https://excelmacromastery.com/ 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
|Boolean||2 bytes||True or False||This variable can be either True or False.|
|Long||4 bytes||-2,147,483,648 to 2,147,483,647||Long is short for Long Integer. Use this instead of the Integer* type.|
|Currency||8 bytes||-1.79769313486231E308 to-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values||Similar to Double but has only 4 decimal places|
|Double||8 bytes||-922,337,203,685,477.5808 to 922,337,203,685,477.5807|
|Date||8 bytes||January 1, 100 to December 31, 9999|
|String||varies||0 to approximately 2 billion||Holds 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
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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:
- Runtime Errors – VBA will not notice incorrect type errors(i.e. Data Mismatch).
- Compile Errors – VBA cannot detect compile errors.
- Intellisense is not available.
- Size – A variant is set to 16 bytes which is the largest variable type
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:
' https://excelmacromastery.com/ Sub MarksVariant() Dim mark 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.
' https://excelmacromastery.com/ 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
Using the compiler to check for errors is very efficient. It will check all of your code for problems before you run it. You use the compiler by selecting Debug->Compile VBAProject from the menu.
In the following code, there is an error. The Square function expects a long integer but we are passing a string(i.e. the name variable):
' https://excelmacromastery.com/ Sub CompileError() Dim name As String Debug.Print Square(name) End Sub Function Square(value As Long) As Long Square = value * value End Function
If we use Debug->Compile on this code, VBA will show us an error:
This is good news as we can fix this error right away. However, if we declare the value parameter as a variant:
Function Square(value As Variant) As Long Square = value * value End Function
then Debug.Compile will not treat this as an error. The error is still there but it is undetected.
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
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.
The size of a variant is 16 bytes. If the variable is going to be a long then it would only take up 4 bytes. You can see that this is not very efficient.
However, unlike the 1990’s where this would be an issue, we now have computers with lots of memory and it is unlikely you will notice an inefficiency unless you are using a huge amount of variables.
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:
- Excel objects
- Class Module objects
- 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 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
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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
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.
' https://excelmacromastery.com/ 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
Here is an example of a real-world version based on the data below:
' Class Module - clsStudent Public Name As String Public Subject As String ' Standard Module ' https://excelmacromastery.com/ 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 thing we can do with VBA is to access 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.
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:
- Early Binding
- Late Binding
Let’s look at these in turn.
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”:
Once we have the reference added we can use the Dictionary like a class module object
' https://excelmacromastery.com/ 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 means that we create the object at runtime.
We declare the variable as an “Object” type. Then we use CreateObject to create the object.
' https://excelmacromastery.com/ 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:
- Static – the array size is set in the Dim statement and it cannot change.
- 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 Strings - 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
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.
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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.
|Array already dimensioned||Compile||Using Redim on an array that is static|
|Expected: identifier||Syntax||Using a reserved word as the variable name|
|Expected: New of type name||Syntax||The type is missing from the Dim statement|
|Object variable or With block variable not set||Runtime||New was not used to create the object(see Creating an Object)|
|Object variable or With block variable not set||Runtime||Set was not used to assign an object variable.|
|User-defined type not defined||Compile||The 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 block||Compile||Variable name is missing from the Dim statement|
|Variable not defined||Compile||The variable is used before the Dim line.|
Local versus Module 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 in the procedure where it is used.
The following are the different types of variables found in VBA:
- Local variables are variables that are available to the procedure only. Local variables are declared using the Dim keyword.
- Module variables are variables that are only available in the current module only. Module variables are declared using the Private keyword.
- Global variables are variables that are available to the entire project. Global variables are declared using the Public keyword.
In the code below we have declared count as a global variable:
' Global ' https://excelmacromastery.com/ Public 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:
' https://excelmacromastery.com/ ' Global Public 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 trouble as it is difficult to track which count is being used.
In general global variables should be avoided. 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.
The one use of a global variable is retaining a value between code runs. This can be useful for certain applications where you want to ‘remember’ a value after the code has stopped running.
In the code below we have a sub called Update. Each time we run the Update sub(using Run->Run Sub from the menu or F5) it will add 5 to the amount variable and print the result to the Immediate Window(Ctrl + G to view this window):
Public amount As Long Sub Update() amount = amount + 5 Debug.Print "Update: " & amount End Sub
The results are:
First run: amount = 5
Second run: amount = 10
Third run: amount = 15
and so on.
If we want to clear all global variable values then we can use the End keyword. Note that the End keyword will stop the code and reset all variables so you should only use it as the last line in your code.
Here is an example of using the End keyword:
Sub ResetAllGlobals() End ' Resets all variables and ends the current code run End Sub
Dim versus Private versus Public
We can declare variables using Public, Private and Dim. In some cases, they can be used interchangeably.
However, the following is the convention we use for each of the declaration keywords:
- Dim – used to declare local variables i.e. available only in the current procedure.
- Private – used to declare module variables and procedures. These are available within the current module only.
- Public – used to declare global variables and procedures. These are available throughout the project.
- Global(obsolete) – an older and obsolete version of Public. Can only be used in standard modules. It only exists for backward compatibility.
Note the Public and Private keywords are also used within Class Modules.
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.
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.)