In this tutorial we will learn about VBA Variables. We will start with understanding the VBA Dim statement needed to declare a VBA Variable. Next we will dive into the Data Types available in Excel VBA. We will continue with diving into more advanced topics.

Declare and Define VBA Variable

First let’s start with a simple example of using a variable:

Dim myVar As Integer 
myVar = 10         

So what is a variable? Is it basically a symbolic name for a storage location, for storing data or pointers to data. Without variables you would be able to process any data. When using variables there are 2 steps you need to complete:

1. Declare the variable – declare the symbolic variable name (and sometimes data type)
2. Define the variable – set a value to the variable

Dim myVar as Long: myVar = 10

Let’s distinguish the two steps:

Dim myVar As Integer 'Explicit Declaration using VBA Dim statement
myVar = 10 'Definition

VBA usually (w/o the Option Explicit macro) allows you to simply define a new variable with any explicit declaration. This means that this is equally valid:

myVar = 10 'Implicit Declaration & Definition

When declaring variables remember precede them with the Dim statement.

Declaring multiple variables
You can also use the Dim statement to declare multiple variables in one line using the VBA Dim statement.

Dim myVar1 As Integer, myVar2 as String, someDate as Date

Data Types

Below the list of data types available in VBA. For a more detailed list see here (MSDN).

Option Explicit

I personally prefer explicit variable declaration as you will learn this will help reduce any errors resulting from mistyping variables names. Let me give you an example:

myVar = 10 'myVar = 10
res = myVa 'res = 0

As you can see above, I have defined a variable named myVar with value 10. In the second line I defined a new variable res with the value of myVa – mistyping the name of my myVar variable. Now as myVa is a new variable without a defined value, VBA will assign a default null value to this new variable equal to 0.

To prevent such errors use the Option Explicit statement in the first line of you Module as show below:

Option Explicit
Sub Run()
   myVar = 10 'ERROR! myVar not defined!
End Sub

Option Explicit
Sub Run()
   Dim myVar As Integer
   myVar = 10 'OK!
End Sub

As you can see adding the Option Explicit statement will generate and error when you use/define a variable that was not previously declared.

Constants

In VBA you can also declare constants – values that can be defined only once and cannot be modified (will throw an exception). This is an example of a constant declaration:

   Const myVar As Integer = 10
   Dim x as Integer
   x = myVar + 1 'OK!
   myVar = 11 'ERROR! Constants cannot be redefined

Private and Public VBA variables

Private VBA Variable

A variable that is not accessible only within the scope in which it was declared. Below are some examples of Private variables. In VBA additionally Prviate variables can be declared in ALL scopes except for Subs and Functions (use the Dim statement instead – which is the equivalent).

Private var as String
Sub SomeSub()
  'var is Private within this module
  var = "Hello there!"
  'These variables are also Private in scope of the Sub
  Dim subVar as String
  subVar = "Hello!"
End Sub

Sub TestSub()  
   SomeSub 

   'Will show a message "Hello There!"
   MsgBox var
  'Will show an empty MsgBox - as subVar is not available in this scope
   MsgBox subVar 
End Sub

Public VBA Variable

A variable that is accessible within ALL scopes in contrast to Private variables. Below are some examples of Public variables. In VBA additionally Public variables can be declared in ALL scopes except for Subs and Functions.

'---Module 1---
Public var as String
Sub SomeSub()
  'var is Public within this module
  var = "Hello there!"
End Sub

'---Module 2---
Sub TestSub()  
   SomeSub 

   'Will show a message "Hello There!"
   MsgBox var
End Sub

Global VBA Variables

Global variables are basically equivalent to Public variables. The difference is that Global variables can only be used in modules, whereas Public can be used in all contexts/scopes e.g. modules, classes, forms etc.
Global variables are most likely kept for backwards compatibility (older versions of Excel) so I recommend you stick to using only Public variables.