Variables

Variables


When you are creating a macro project it is the best practice to use Option Explicit as the beginning line of code to force the use of variables which saves memory during the macro’s run.  The VarType function defines how the data in a Variant is treated. All variables become Variant data types if not explicitly declared as some other data type.


 VariableDescription Size 
Byte It is used to hold positive integer numbers ranging from 0 to 255. Single 8-bit (1-byte) numbers
Boolean This has only two possible values, True (any non-0 number) or False (0) 16-bit (2-byte) numbers
Integer (%) A data type that holds integer variables (whole numbers) in the range -32,768 to 32,767. The Integer data type is also used to represent enumerated values. 2-byte whole numbers
Long (&) An integer ranging in value from -2,147,483,648 to 2,147,483,647. 4-byte integer
Currency (@) A data type with a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Use this data type for calculations involving money and for fixed-point calculations where accuracy is particularly important. 64 bit (8 Bytes)
Single (!) A data type that stores single-precision floating-point variables, ranging in value from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. 32-bit (2-byte) floating-point numbers
Double (#) A data type that holds double-precision floating-point numbers in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. 64-bit numbers
Date A data type used to store dates and times as a real number. The value to the left of the decimal represents a date, and the value to the right of the decimal represents a time. 64-bit (8-byte) numbers
String ($) A data type consisting of a sequence of contiguous characters that represent the characters themselves rather than their numeric values. A String can include letters, numbers, spaces, and punctuation. Fixed Length:
0 to appx. 63k
Dynamic Length:
0 to 2 billion characters
Object A data type that represents any Object reference. Object variables are stored as addresses that refer to objects. 32-bit (4-byte) addresses
Variant A special data type that can contain numeric, string, or date data as well as the special values Empty and Null. The Variant data type has a numeric storage size of 16 bytes and can contain data up to the range of a Decimal, or a character storage size of 22 bytes (plus string length), and can store any character text. Number: 16 bytes
String: 22 bytes

Variable Scope


ScopeHow the variable with this scope is declared
Single Procedure Include Dim or Static statement within the procedure
Single Module Include Dim or Private statement before the first procedure in a module.
All Modules Include Public statement before the first procedure in a module.

Local Variables

A local variable is declared within a procedure and can only be used in the procedure in which it was declared. When the procedure ends, the variable no longer exists and Excel frees up its memory.

Some Examples of Variable Use

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Option Explicit
Sub MySub() ‘Example of local variables
Dim x As Integer
Dim First As Long
Dim InterestRate As Single
Dim TodaysDate As Date
Dim UserName As String
Dim MyValue
‘[Add procedure’s code here]
End Sub
[/sourcecode]
[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Option Explicit
Sub MySub() ‘Another Example of local variables
Dim x As Integer, y As Integer, z As Integer
Dim First As Long, Last As Double
‘[Add procedure’s code here]
End Sub
[/sourcecode]

Module-Wide Variables

To have module-wide variables just add the variables before the modules first procedures in a module. Outside of any procedures or functions.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Dim CurrentValue As Integer
‘ Both procedure 1 and 2 have access to “CurrentValue”

Sub Procedure1()
‘code goes here
End Sub

Sub Procedure2()
‘code goes here
End Sub
[/sourcecode]

Public Variables

To make a variable available to all the procedures in all VBA modules in a project, declare the variable at the module level (before the first procedure declaration) by using the Public keyword rather than the Dim.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Public CurrentRate As Long
[/sourcecode]

Constants

A named value or string that never changes (constant).

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Const NumQuarters As Integer = 4
Const Rate = .0725, Period = 12
Const ModName As String = “Budget Application”
Public Const AppName As String = “Budget Application”
[/sourcecode]

To make a constant available to all modules in the workbook, use the Public keyword and declare the constant before the first procedure in a module.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Public Const InterestRate As Double = 0.0725
[/sourcecode]

To fix a string variable to make it a constant length

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]

‘ A 50 character string variable
Dim MyString As String * 50
‘ This is an unlimited string (65,535 characters max)
Dim MyString2 As String [/sourcecode]