A variable is a placeholder, which stores data, i.e. a storage area in memory. It can be recalled, reassigned or fixed throughout a procedure, function or during the lifetime of a module being executed.

Structure (syntax)

Variable Name = Value

Variable Name = Object.Property

For example:

Result = Activecell.Value

Where Result is the variable name which is assigned the value in the Activecell.

Declaring a Variable

Declaring a variable allows you to state the names of the variables you are going to use and also identify what type of data the variable is going to contain.

For example, if Result = 10, then the variable Result could be declared as being an Integer.

Explicit Declaration

Explicit Declaration is when you declare your variables to be a specific data type.

Variables can be declared as one of the following data types:

1. Boolean

2. Byte

3. Integer

4. Long

5. Currency

6. Single

7. Double

8. Date

9. String (for variable-length strings)

10. String * length (for fixed-length strings)

11. Object

12. Variant

Note that if you do not specify a data type, the Variant data type is assigned by default.

The Declaration Statement is written as follows:

Dim Result As Integer

Dim MyName As String

Dim Sales As Currency

Dim Data

Example:

The following example declares the MySheet variable to be a String.

Sub VariableTest()
    Dim MySheet As String
    MySheet = ActiveSheet.Name
    ActiveCell.Value = MySheet
End Sub

The position of the declaration statement is important as it determines whether the variable is available for use throughout the module or just within the current procedure (see Understanding Scope & Visibility later in this section).

Another Example:

The following example declares the MyData variable to be an Integer.

Sub VariableTest()
    Dim MyData As Integer
    MyData = ActiveWorkbook.Name
    ActiveCell.Value = MyData
End Sub

However, when you run this macro, an error will occur because
MyData = ActiveWorkbook.Name is invalid since ActiveWorkbook.Name is not an Integer but a String.

When you click on the Debug button, it will highlight incorrect code.

But if you declare MyData as String (i.e. text) then MyData = ActiveWorkbook.Name will become valid.

Dim MyData As String

Benefits of using Explicit Declaration

If you do not specify a data type, the Variant data type is assigned by default. Variant variables require more memory resources than most other variables. Therefore, your application will be more efficient if you declare the variables explicitly and with a specific data type.

Explicitly declaring all variables also reduces the incidence of naming-conflict errors and spelling mistakes.

Following the conventions set by Microsoft (see Rules and Conventions), variables too have a standard that in most cases is recommended.

A variable declared explicitly, should have a prefix in lowercase preceding the variable meaningful name. For example, a variable to store the vat amount of type Double may be shown as dblVatAmount where ‘dbl’ is the prefix for a Double data type and the two word variable (initial capped) referring to the purpose of the variable.

The table below shows what prefixes could be used for each common data type:

Data type	Prefix	Example
Boolean	bln	blnContinue
Byte	byt	bytWeekdayValue
Collection object	col	colWidgets
Currency	cur	curCost
Date (Time)	dtm	dtmOrderDate
Double	dbl	dblRevenue
Error	err	errInvoiceNo
Integer	int	intQuantity
Long	lng	lngDistance
Object	obj	objWordDoc
Single	sng	sngVatRate
String	str	strContact
Variant	vnt	vntColumnData or varColumnData or ColumnData (no prefix).

By default, all variables not explicitly defined are Variant (the largest memory allocation reserved).

The exception to the above (developers can decide whether to follow the above or not) is when defining Sub and Function procedures with arguments. As part of the signature of such a procedure, it can be clearer to the end user to see meaningful named arguments rather than the conventions stated in the above table.

For example, which is clearer for the end user to understand?

Function GrossTotal(Net As Double, Qty As Long, Optional
VatRate As Single) As Double

..........

End Function

Function GrossTotal(dblNet As Double, lngQty As Long, Optional
sngVatRate As Single) As Double

..........

End Function

When calling this function within Excel, users will see the arguments via the Insert Function paste command:

Constants

Constants are values that do not change. They prevent you from having to repeatedly type in large pieces of text.

The following example declares the constant MYFULLNAME to equal "Ben Beitler". Therefore, wherever MYFULLNAME has been used, the value that will be returned will be "Ben Beitler".

Sub ConstantTest()
    Const MYFULLNAME As String = "Ben Beitler"
    ActiveCell.Value = MYFULLNAME
    ActiveCell.EntireColumn.AutoFit
End Sub

(Note: When using a constant, the convention is normally in uppercase).

Implicit Declaration

As previously mentioned, if you do not declare your variables and constants, they are assigned the Variant data type, which takes up more resources and spelling mistakes are not checked.

A Variant Variable/Constant can contain any type of data.

Data = 10

Data = "Fred"

Data = #01/01/2010#

When you run the following macro, the value in the active cell will be 10.

Sub ImplicitTest()
data = 10
ActiveCell.Value = data
End Sub

When you run the following macro, the value in the active cell will be Fred.

Sub ImplicitTest()
data = "Fred"
ActiveCell.Value = data
End Sub

This can lead to errors and memory abuse though VBA is relaxed in using variables this way - it's just not good practice!

Option Explicit (Declaration)

If you type Option Explicit at the top of the module sheet, you must declare all your variables and constants.

If you don’t declare your variables/constants you will get the following message.

If you wish to ensure that Option Explicit is always entered at the top of the module:

1. Go into the Visual Basic Editor.

2. Click on the Tools menu, select Options...

3. Click on the Editor tab and select "Require Variable Declaration".

You now must always use the Dim keyword to declare any variable.

Understanding Scope & Visibility

Variables and procedures can be visible or invisible depending on the following keywords used:

Private
Public
Static
Friend (Class Modules only – not covered in this guide)

Depending where users use the above keywords, visibility can vary too within a module, class module or user-form.

In a standard module when using the keyword Dim to declare a variable.If the variable is outside a procedure and in the main area of the module, this variable is automatically visible to all procedures in that module. The lifetime of this variable is governed by the calling procedure(s) and can be carried forward into the next procedure within the same module.

If a variable declared with the Dim keyword is within a procedure, it is visible for that procedure only and the lifetime of the variable expires when the procedure ends.

The Dim keyword can be used in either the module or procedure level and are both deemed as private to the module or procedure.

Instead of using the Dim keyword, it is better practice to use the Private keyword when declaring variables at the module level. Users must continue to use the Dim keyword within a procedure.

Using Public to declare a variable at the module level is potentially unsafe as it is exposed beyond this module to all other modules and user-forms. It may also provide confusion if the two variables with the same name exist across two modules. When a variable is declared Public , users should take caution and try and be explicit in the use of the variable.

For example:

Module A

Option Explicit

Public intMonth As Integer

code continues..........

Module B

Option Explicit

Private intMonth As Integer

Sub ScopeVariables()
intMonth = 10 'This is ModuleB’s variable
ModuleA.intMonth = 10 'This is ModuleA’s variable (explicit)
End Sub

code continues..........

Two variables with the same name and data type were declared in both module A and B. A procedure in module B calls the local variable and then explicitly calls the public variable declared in module A. Users must therefore use the notation of the name of the module followed by the period separator ( . ) and its variable.

Public and Private keywords can also be applied to a procedure. By default, in a standard module, all procedures are Public unless explicitly defined as Private.

It is good practice to apply either Public or Private for each procedure as later releases of Visual Basic may insist on this convention.

If a procedure is Private, it can only be used within the module it resides. This is particularly designed for internal procedures being called and then discarded as part of a branching routine (nested procedures).

If users mark a procedure as Private, it cannot be seen in the macros dialog box in Excel.

Static Variables

Using the Static keyword allows users to declare variables that retain the value from the previous calling procedure.

Example using Dim:

'Standard variable (non-static).
Sub TotalValue()
    Dim intValue As Integer
    intValue = 10
    Msgbox intValue
End Sub

Example using Static:

'Standard variable (non-static).
Sub TotalValue()
    Static intValue As Integer
    intValue = 10
    Msgbox intValue
End Sub

Running the first example will simply display the value 10 and the variable’s value will be lost when the procedure ends.

Running the second example will display the value 10 but it will retain the variable and its value in memory so that when running it once more, the value displayed now equals 20 and so on until the file is either closed or the reset command is executed.

Static can only be used in a procedure and is therefore private.

Do not confuse Static with Const (constant).

Use the Const keyword to fix a value for lifecycle of the module or procedure. Users will not be able to modify the value at run time as with conventional variables.

Example:

Public

'Vat Rate is currently fixed at 17.5%
Public Const VATRATE As Single = 0.175

or Private

'Vat Rate is currently fixed at 17.5%
Const VATRATE As Single = 0.175

Using the constant

Sub GrossTotal()
    Dim dblNet As Double
    dblNet = 100
    MsgBox Round(dblNet * (1 + VATRATE), 2)
End Sub

(Note: Round is a VBA function)

It is acceptable to use uppercase convention for constants.

Const keyword can be public or private (private by default) declared at the module and private only at the procedure level.

User forms, which allow users to design custom form interfaces, also have scope issues using Private and Public

By default, any control’s event that is drawn onto a form will be private as the form should be the only object able to call the procedure.

Other event driven procedures, which can be found in a worksheet or a workbook, will also be private by default.

ByVal versus ByRef

Passing arguments is a procedure can be either by value (ByVal) or by reference (ByRef). Both keywords precede the argument name and data type and if omitted is ByRef by default.

When passing an argument by value, the value is passed into a calling procedure and then lost when the procedure ends or reverts back to the original value as it returns to the original procedure.

ByVal example:

Sub CustomSub(ByVal AddTo As Integer)
    AddTo = AddTo + 5
    MsgBox AddTo
End Sub

Sub TestVariable()
    Dim x As Integer
    x = 5
    Call CustomSub(x)
    MsgBox x
End Sub

The procedure TestVariable starts by seting x = 5. It’s CustomSub procedure is called passing the variable’s value of x and incremented by 5. The first message box seen is via the CustomSub procedure (shows the value 10). The second message box is via the TestVariable procedure which follows as it returns the focus (shows the value 5). Therefore the ByVal AddTo variable stored is lost as it is passed back into the original call procedure (TestVariable) resulting in x being equal to 5 again.

ByRef example:

Sub CustomSub(ByRef AddTo As Integer)
    AddTo = AddTo + 5
    MsgBox AddTo
End Sub

Sub TestVariable()
    Dim x As Integer
    x = 5
    Call CustomSub(x)
    MsgBox x
End Sub

The procedure TestVariable starts by seting x = 5. It’s CustomSub procedure is called passing the variable’s value of x and incremented by 5. The first message box seen is via the CustomSub procedure (shows the value 10). The second message box is via the TestVariable procedure which follows as it returns the focus (shows the value 10 again). Therefore the ByRef AddTo variable stored is not lost as it is passed back into the original call procedure (TestVariable) resulting in x now being equal to 10.

Next Topic: Object variables

Want to teach yourself Access? Free online guide at About Access Databases

Tip: Remember to enable Option Explicit (it's good practice).

VBA Keywords: ActiveCell, ActiveSheet, ActiveWorkbook, MsgBox, Dim, Private, Public, ByVal, ByRef, Round.