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:
- Declare the variable – declare the symbolic variable name (and sometimes data type)
- 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
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|
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.
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.