VBA Variables – declaring using VBA Dim and VBA Data Types

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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.

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

Let’s distinguish the two steps:

Dim myVar As Integer 'ExplicitDeclaration 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).

Data Type Bytes Value Range
Boolean Depends on implementing platform True or False
Byte 1 byte 0 through 255 (unsigned)
Date 8 bytes 0:00:00 (midnight) on January 1, 0001 through 11:59:59 PM on December 31, 9999
Double (double-precision floating-point) 8 bytes -1.79769313486231570E+308 through -4.94065645841246544E-324 † for negative values;4.94065645841246544E-324 through 1.79769313486231570E+308 † for positive values
Integer 4 bytes -2,147,483,648 through 2,147,483,647 (signed)
Long (long integer) 8 bytes -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 (9.2…E+18 †) (signed)
Object 4 bytes on 32-bit platform; 8 bytes on 64-bit platform Any type can be stored in a variable of type Object
Single (single-precision floating-point) 4 bytes -3.4028235E+38 through -1.401298E-45 † for negative values;1.401298E-45 through 3.4028235E+38 † for positive values
String (variable-length) Depends on implementing platform 0 to approximately 2 billion Unicode characters
Type variable A custom data type containing one or more elements (variables). Read more here. Can’t contain Subs or Functions like the Class data type, however, is useful in Binary Writing/Reading files.
User-Defined(structure) Depends on implementing platform Each member of the structure has a range determined by its data type and independent of the ranges of the other members

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 variables

Private
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
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
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.