A variable is a storage area that you name and create.
Naming Variables
You should make your variable names as descriptive as possible as it makes code easier to read. There are some rules, however that you should be aware of when naming variables
– The first character should be a letter
– VBA does not distinguish between upper and lower case
– Spaces are not allowed, but underscores _ are.
– You cannot use certain characters = #,$,%,& or ! in a variable name
– You cannot use the following reserved words as variable names:
And, As, Boolean, ByRef, Byte, ByVal, Call, Case, CBool, CByte, CDate, CDbl, CInt, CLng, Const, CSng, CStr, Date, Dim, Do, Double, Each, Else, ElseIf, End, EndIf, Error, FALSE, For, Function, Get, GoTo, If, Integer, Let, Lib, Long, Loop, Me, Mid, Mod, New, Next, Not, Nothing, Option, Or, Private, Public, ReDim, REM, Resume, Select, Set, Single, Static, Step, String, Sub, Then, To, TRUE, Until, vbCrLf, vbTab, With, While, Xor
– You are limited to 254 characters
Example Code Using Variables
You can declare a variable using a Dim statements.
Sub understandingvariables()
Dim CellDestination As Range
Dim MyName As String, Message As String
Dim MyAge As Byte
Dim DOB As Date
Set CellDestination = Range("A1")
MyName = InputBox("Hi, what's your name?")
DOB = InputBox("Now, what day where you born?")
MyAge = Int((Date - DOB + 1) / 365.25)
Message = "Hi, " & MyName & ", you are " & MyAge & " years old."
MsgBox Message
CellDestination = Message
End Sub
Once you have declared a variable you can use the VBE’s autocomplete feature to enter variable names in your code. Just type the first 1 or 2 characters of the varaiable name and use CTRL SPACE to autocomplete.
Declaring a Variable’s Data Type
A variable can contain a wide variety of data types as shown in the table below. Data types reserve different amounts of memory, so you should aim to assign the data type that reserves the least amount.
| Data Type | Size | Range of Values |
|---|---|---|
| Byte | 1 byte | 0 to 255 |
| Boolean | 2 bytes | True or False |
| Integer | 2 bytes | -32,768 to +32,767 |
| Long | 4 bytes | -2,147,483,648 to +2,147,483,647 |
| Single | 4 bytes | -3.4e38 to +3.4e38 |
| Double | 8 bytes | -1.8e308 to +1.8e308 |
| Currency | 8 bytes | -922,337,203,685,477.5808 to +922,337,203,685,477.5807 |
| Date | 8 bytes | 1st January 100 to 31st December 9999 |
| Object | 4 bytes | Any Object Reference |
| String | varies | Fixed - Up to 65,500 characters |
| Variable - Up to approx. 2 billion characters | ||
| Variant | varies | Number - same as Double Type |
| String - same as String Type |
Although you don’t have to declare a variable’s data type it is more efficient to do so. Without a data type declaration, VBA defaults to the Variant data type which can hold any type of data and can slow your code down aswell as reserve more memory than is always necessary.
Creating Date Variables
The range of dates that VBA can handle is much wider than in Excel itself. VBA can handle dates from 01/01/100 to 31/12/9999.
To assign a date to a variable you must write the date in US format mm/dd/yyyy and enclosed between two hash marks.
StartedJob = #8/4/2014#
The date will however appear in your own regional format when displayed in a cell or message box.
Dim StartedJob As Date Dim LengthofService As Single StartedJob = #8/4/2014# LengthofService = Round((Date - StartedJob + 1) / 365.25, 1) MsgBox "You started working here on the " & StartedJob & vbNewLine & _ "and your length of service is " & LengthofService & " years."
The code above produces the message shown below.
Creating String Variables
There a two types of string variables : fixed length and variable length. You can specify the maximum number of characters that a string variable can hold as a ‘fixed length’ as long as that number does not exceed 65,526, otherwise use a ‘variable length’ which can up to 2 billion characters.
Specify a fixed length as shown below
Dim StudentName As String * 25
The following code…
Dim x As String * 2 x = "AAA" MsgBox x
…produces this dialog box.
Option Explicit
When you use Option Explicit within a module you force yourself to declare your variables. Option Explicit is placed as the first instruction in a module above your procedures. You can have Option Explicit inserted automatically whenever you create a module: in the Visual Basic Editor click Tools | Options and check the Require Variable Declaration option in the Editor tab of the Options dialog box.
Another benefit of using Option Explicit is that it will notify you if you have mispelled a variable somewhere in your procedure with a complie error.
Scoping Variables
The scope of a variable determines which modules and procedures can use it.
Local Variables
Local variables are declared within a procedure, so are only available within that procedure. When the procedure has finished, Excel frees up the memory that stored the variable’s value.
Option Explicit Sub myprocedure() Dim rg As Range Code goes here End Sub
Module-Wide Variables
To make a variable available to all procedures or functions in a module, declare it outside them at the top of the module.
Option Explicit Dim rg as Range Sub firstprocedure() Code goes here End Sub Sub secondprocedure() Code goes here End Sub
Public Variable
To make a variable available to all modules in a VBA project, declare the variable at the module level (in any module within the project) but with the keyword Public rather than Dim.
Option Explicit Public rg as Range
Static Variables
Static Variables retain their value when the procedure ends, unlike normal variables that lose theirs. The variable keeps the previous value each time you call the procedure. Static variables are declared at the procedure level; they cannot have a module-wide or public scope.
Each time you run this procedure x will equal 1
Sub Counter1() Dim x As Integer x = x + 1 MsgBox "x will always be " & x End Sub
With a Static variable the variable retains it’s value when the procedure ends. Therefore each time this procedure runs x will increase by 1.
Sub Counter2() Static x As Integer x = x + 1 MsgBox "You have run the procedure " & x & " times." End Sub
Life of Variables
To delete all variable values from memory use the Reset button in the Visual Basic Editor.
Or
Click End in a runtime error dialog box.
Procedure level variables are removed from memory at End Sub, unless a Static variable is used inwhich case the value is retained when the procedure is run again.
Module and Public variables retain their values between procedure runs.
Example of the Lifetime of Module Level Variable
In the code shown below the variable a has been declared at the module level. When I run proc1 it increments a by 1. When I run the proc2 it retains the value assigned to a in proc1 and multiplies it by 10. If I was to run proc1 a second time it retains the new value assigned to a by proc2 and so on.
Option Explicit Dim a As Integer Sub proc1() a = a + 1 MsgBox a End Sub Sub proc2() a = a * 10 MsgBox a End Sub
Assigning Values to Variables
You can assign a value to a variable using an assignment statement.
Some Assignment Statement Examples
x = 1
Markup = 8%
Target = 500000
MyName = InputBox("Hi, what's your name?")
DOB = InputBox("Now, what day where you born?")
MyAge = Int((Date - DOB + 1) / 365.25)
Message = "Hi, " & MyName & ", you are " & MyAge & " years old."
Using Set to Assign an Object to a Variable
If you want to assign an object to a variable, such as a range of cells use the Set keyword.
Set CellDestination = Range("A1")
Constants
A constant refers to a value that will not change, as opposed to a variable that can change. You might create a constant for a VAT rate or markup value or a financial year end date.
Declare a constant as shown below.
Const VAT As Single = 0.2
Example Code Using a Constant
Const VAT As Single = 0.2
Dim UnitPrice As Currency
Dim PriceIncVAT As Currency
UnitPrice = InputBox("Enter the unit price please")
PriceIncVAT = UnitPrice * (1 + VAT)
MsgBox "Price inclusive of VAT will be " & Format(PriceIncVAT, "£#,##0.00")
The scope of a constant works in the same way as the scope of a variable.