Declaring Variables, Using Dim Statement, Variable Names & Data Types in Excel VBA
--------------------------------------------------
Contents:
--------------------------------------------------
A variable is a named storage location used to store temporary values or information for use in execution of the code. In your vba program, a variable stores data and its content is used or changed later while executing the code. By declaring a variable for use in your code, you tell the Visual Basic compiler the variable's data type (type of value it represents viz. integer, decimal, text, boolean, etc.) and other information such as its scope/level (what code can access it - variables can be Procedure Level, Module Level or can have a Public scope). When you declare a variable in your code using a Dim statement, you are setting aside sufficient memory for the variable (viz. 2 bytes for a Boolean or Integer variable, 4 bytes for a Long variable, and so on), and that the information being stored in the variable has an allowable range (of True or False for a Boolean variable, a whole number between -32,768 to 32,767 for an Integer variable, a whole number between -2,147,483,648 to 2,147,483,647 for a variable subtype of Long, and so on). You will receive a run-time error if trying to assign a string value to a variable declared as Integer.
Variables must be explicitly declared using the Dim, Private, Public, ReDim, or Static statements. When you declare variables by using a Dim statement (Dim is short for dimension): for declaring a variable to hold an Integer value, use "Dim rowNumber As Integer"; for declaring a variable to hold text values, use "Dim strEmployeeName As String"; and so on. Though the Dim statement can be used anywhere in the code, it should precede the first use of the variable, and all Dim statements are usually used and clubbed at the beginning of each Sub or Function procedure.
While declaring a variable if you do not specify its data type, or if you do not declare a variable at all it will default to Variant data type that can hold any type of data (string, date, time, Boolean, or numeric values). This makes Excel reserve more memory than is required (at least 16 bytes). Declaring a variable is telling the computer to reserve space in memory for later use. Therefore your VBA code will run more efficiently. Not declaring a variable could also result in mistyping a variable name and not knowing it viz. you might type rowNumbre instead of rowNumber. VBA will asume it to be a new variable and initialize it to zero or "", and this could lead to bugs in the code which might be difficult to locate. However, not declaring a variable or declaring a variable as a Variant data type can in itself be an advantage at times, in that in this case the variable can contain any type of string, numeric, date, time or Boolean values & can automatically convert the values that it contains.
When you run a macro, all variables are initialized to a default value. The initial default value: for a numeric variable is zero; for a variable length string it is a zero-length or empty string (""); a fixed length string is initialized with the ASCII code 0, or Chr(0); an object variable defaults to Nothing; a Variant variable is initialized to Empty. In numeric context, an Empty variable denotes a zero while in a string context an Empty variable is a zero-length string ("") . A zero-length string ("") is also referred to as a null string. However, it is advised to explicitly specify an initial value for a variable instead of relying on its default initial value.
In VBA, to assign a value to a variable of Numeric or String type - type the variable name, followed by the equal sign (=), followed by the String or Numeric value. To assign an object reference, ie. to assign an object to a variable in your vba code, you need to use the Set keyword. We declare a variable (myRange) of Range data type, and then assign the Range object to this variable using the Set keyword, so that every time you want to refer to the specific Range, you can do so by using the variable. Dim myRange As Range; Set myRange = ActiveSheet.Range("A1:C3"); myRange.Value = "Hello"; myRange.Value = 10. This will enter the text "Hello" or the value 10 in cells A1 to C3 in the active worksheet.
Using Option Explicit will require that all variables are necessarily declared using the Dim statement. You can do this by putting a line mentioning 'Option Explicit' at the top of your code (above all subroutines) or on top of a module. When Option explicit statement is inserted at the top of a module, it instructs Visual Basic to require explicit declaration of all variables in the module. If you omit declaring the variable or else mistype the variable as 'rowNumbre' instead of 'rowNumber', the compiler will give an error ("Variable not defined" - refer Image 1b) and the code will resume only after the error is rectified. Option Explicit can be added manually by typing these words at the top in the Code Module. Or else Option Explicit can be added automatically in the Code Modules by going to VBE Editor -> Tools -> Options -> Editor and selecting "Require Variable Declaration" (Refer Image 1a) - this will add Option Explicit to every new code module thereafter, but not to any pre-existing code module.
Dim a single variable:
Dim firstRowNumber As Integer
Dim multiple variables:
Incorrect method:
Dim firstRowNumber, secondRowNumber, thirdRowNumber As Integer
It will only Dim thirdRowNumber as Integer and the other 2 variables firstRowNumber & secondRowNumber as Variant (default).
Correct methods:
Dim firstRowNumber As Integer, secondRowNumber As Integer, thirdRowNumber As Integer
or
Dim totalRows As Integer, studentName As String, validDate As Date
or
Dim totalRows As Integer
Dim studentName As String
Dim validDate As Date
Dim statements can be declared at 3 basic levels:
1) Procedure Level. These are the most widely used variables. A variable declared at procedure level is a local variable; it is local to the procedure that declares it. The variable is destroyed when the Procedure finishes, on use of the End statement in the procedure (viz. End Sub). If the Procedure containg the local variable calls another procedure, the variable value is retained while the other Procedure is running but the variable is not available to that other procedure. You can use the Dim, Private or Static statement within a subroutine or function (viz. between the Sub and End Sub statements) to declare a local variable. In case of these local variables, the same variable name can be used in different subroutines without any conflict. Refer Image 2.
2) Module Level: These variables are created with a 'Dim' or 'Private' statement in the general declarations section of a Form or code module. Using Dim or Private statements means creating a private module level variable. Such variable is visible only from within the module it is created in and is not accessible from outside. The variable is placed at the top of a Module and is available to all Procedures within that module but not to procedures in other modules. The variable is destroyed when the module it is declared in is edited or when Workbook is closed. A general practice is to use Private at the Module level, and Dim at the Procedure (Sub/Function) level. Refer Image 3.
3) Project Level: These are Public Variables (Public scope) and are created with a 'Public' statement in the general declarations section of a Form or code module. The variable is placed at the top of a Standard Module using the 'Public' Keyword, viz. Public rowNumber As Integer. This variable is available to all Procedures in all Modules. The variable is destroyed when the Workbook is closed. Refer Images 4a & 4b.
Initialize variables
Remember, variables must be explicitly declared using the Dim, Private, Public, ReDim, or Static statements. When you run a macro, all variables are initialized to a default value. The initial default value: for a numeric variable is zero; for a variable length string it is a zero-length or empty string (""); a fixed length string is initialized with the ASCII code 0, or Chr(0); an object variable defaults to Nothing; a Variant variable is initialized to Empty. In numeric context, an Empty variable denotes a zero while in a string context an Empty variable is a zero-length string ("") . For a static variable or for a module-level variable, you can reset & initialize their values by clicking the Reset button on the Standard toolbar, or by clicking Reset on the Run menu in VBE. On starting to run a procedure, all local variables are initialized to their default values automatically. However, it is advised to explicitly specify an initial value for a variable instead of relying on its default initial value.
Static Statement:
Only Procedure-level variables can be declared Static. Variables declared with the Static statement retain their values while the code is running and thereafter, until your project is reset or the document is closed. This means the variable is not destroyed and retains its value between multiple calls to the procedure. Normally, a local variable in a procedure ceases to exist as soon as the procedure terminates. A static variable remains in existence and retains its most recent value. The next time your code calls the procedure, the variable is not reinitialized, and it still holds the latest value you assigned to it. You can use Static only on local variables. Example:
Sub Counter1()
Dim counter As Integer
counter = counter + 1
MsgBox counter
End Sub
Sub Counter2()
Static counter As Integer
counter = counter + 1
MsgBox counter
End Sub
Each time you run the procedure Counter1, it will display the value 1. Each time you run the procedure Counter 2, the displayed value will be incremented by 1 ie. it will display 1, then 2, then 3, and so on.
For live codes of declaring variables, click to download excel file.
ReDim Statement to Resize Dynamic Array:
VBA Arrays: An array is a set or collection of variables (referred as elements) having the same data type and name. Note that if the data type is Variant, each element may consist of a different type of data viz. String, Number, etc. If you work with a list of items of similar data type, say 20 numbers, then instead of declaring each item as a separate variable, you can declare them as only one array of variables. VBA allows to refer them with the same name and treats them as a single variable. The individual items in an array are referred to as elements and their respective index values would be 0, 1, 2, and so on.
Fixed-size and Dynamic Arrays: Two type of arrays can be created in VBA, Fixed-size and Dynamic Arrays. An array which has a fixed number of elements is a fixed-size array and is used when you know in the beginning of writing the code, the precise number of elements you need in the array. Most times you will need to create dynamic arrays because you will not know the exact size of the array required in the code in the beginning and flexibilty to change the number of array elements will be required to run the code. Besides being a convenient VBA tool, dynamic arrays help in conserving memory to only what is actually required. Arrays can be of any data type, which will mean that each element of the array will be of that same data type (viz. String, Long, ...).
ReDim Statement: Declare a dynamic variable with empty parentheses ie. leaving the index dimensions blank. You can thereafter size or resize the dynamic array that has already been declared, by using the ReDim statement. To resize an array, it is necessary to provide the upper bound, while the lower bound is optional. If you do not mention the lower bound, it is determined by the Option Base setting for the module, which by default is 0. You can specify Option Base 1 in the Declarations section of the module and then index will start from 1. This will mean that respective index values of an array with 3 elements will be 1, 2 and 3. Not enterring Option Base 1 will mean index values of 0, 1 and 2.
For example,
Declare the myArray array as a dynamic array: Dim myArray() As String
To set the array's size and resize the array to 3 elements: ReDim myArray(3) As String
If instead of a fixed-size array, you want to be flexible to changes that may occur to the number of records in your database: following (Image 5a & Image 5b) shows a procedure that sets the upper bound of the myArray array equal to the current recordset count.
-----------------------------------------------------------------------------------------------------------------------
Option Base 1
Sub demoArray()
Dim myArray() As String
ReDim myArray(3) As String
myArray(1) = "Mon"
myArray(2) = "Tue"
myArray(3) = "Wed"
MsgBox myArray(1) & ", " & myArray(2) & ", " & myArray(3)
End Sub
------------------------------------------------------------------------------------------------------------------------
Option Base 1
Sub demoArray()
Dim myArray() As String
ReDim myArray(3) As String
myArray(1) = "Mon"
myArray(2) = "Tue"
myArray(3) = "Wed"
ReDim myArray(4) As String
myArray(4) = "Thurs"
MsgBox myArray(1) & ", " & myArray(2) & ", " & myArray(3) & ", " & myArray(4)
End Sub
------------------------------------------------------------------------------------------------------------------------
When an array is resized using the ReDim statement, its values might get lost, as you notice in Image 5b. To ensure that the array values are not lost, use the 'Preserve' Keyword with the ReDim statement, which will enable to retain the existing data in the array. For example, first the ReDim statement used to size the myArray array was: "ReDim myArray(3) As String" and then 3 elements were populated. To resize the array to allow 4 variables without losing the existing data, you should use the statement "ReDim Preserve myArray(4) As String". Refer Image 5c.
Option Base 1
Sub demoArray()
Dim myArray() As String
ReDim myArray(3) As String
myArray(1) = "Mon"
myArray(2) = "Tue"
myArray(3) = "Wed"
ReDim Preserve myArray(4) As String
myArray(4) = "Thurs"
MsgBox myArray(1) & ", " & myArray(2) & ", " & myArray(3) & ", " & myArray(4)
End Sub
Declaring a variable tells VBA the name and datatype of the variable to be used. Variable name and datatype are mentioned after the Dim word viz. Dim rowNumber As Integer. There are some rules which are to be followed when naming your variables:
The name of a variable must begin with a letter, and not a number or underscore.
A name can consist of letters, numbers or underscores but cannot have a period (.) or punctuation characters or special characters (such as ! @ # $ % ^ & * ( ) + - = [ ] { } ; ' : " , . / < > ? \ | ` ~).
Variable name should consist of a string of continuous characters, with no intervening space.
A variable name can have a maximum of 255 characters.
Variable names cannot use keywords / reserved words such as If, And, Or, Loop, Do, Len, Close, Date, ElseIf, Else, Select, ... that VBA uses as part of its programming language.
Valid Names:
TotalRows
total_rows
Total_number_of_Rows
InValid Names:
Total.Rows
5Rows
Total&Rows
Total Rows
Naming Conventions for Variables:
It will help to have the variable name convey its use and data type. You can use a three-letter prefix to describe its data type viz. strStudentName. You can additionally use a one letter prefix (preceding the data type prefix) to describe its scope.
Sample three-letter prefix for different data types: bln for Boolean, byt for Byte, cur for Currency, dtm for Date/Time, dbl for Double, int for Integer, lng for Long, obj for Object, sng for Single, str for String, var for Variant, …..
Sample Prefixes for Scope: no prefix for a local / procedure-level variable (viz. intRowNumber), m for a module-level variable (viz. mlngRunningTotal), g for global / project-level variable (viz. gintRowsTotal), p for passed parameter, when a variable is passed as an argument to a Function or Sub procedure (viz. pstrStudentName) and s for Static (viz. sintCounter).
Prefix for Array Name: Further, the letter a be used to indicate an array name viz. ablnOption, mablnOption, gablnOption, ….
It might be a good idea to start a variable name with a lowercase letter, so as not to confuse with inbuilt VBA names which start with a capitalization. Multiple words can be distinguised by say using underscore or capitalizing the first character of the word viz. rowNumber or total_sales.
Use of single letters as variable names is generally not advisable.
The case (uppercase or lowercase) of variables is preserved by VBA throughout the procedure. If you mention the variable name as rowNumber, and later in the procedure enter the name as rownumber, VBA will automatically convert it to rowNumber. Hence, to change case, you will need to change the variable name case when it is first mentioned in the procedure (which is usually in the Dim statement).
Though the Dim satatement can be used anywhere in the code, it should precede the first use of the variable, and all Dim statements are usually used and clubbed at the beginning of each Sub or Function procedure.
A Type Declaration Character is a character appended to a variable name indicating the variable's data type. While declaring a variable using the Dim statement, for certain data types only (see below), you can use "type-declaration character" instead of the "As" clause. However this is not commonly used.
| Data Type | Type-Declaration Character/Suffixes |
| Integer | % |
| Long | & |
| Single | ! |
| Double | # |
| String | $ |
| Currency | @ |
Example using Type-Declaration Character: Dim studentName$ instead of Dim studentName As String
Numeric Data Types (which consist of numbers and can be computed mathematically using standard artihmetic operators) - 7 types:
Byte, Integer and Long data types for calculations involving round figures (ie. integers).
Single and Double data types (also called floating-point data types) for high precision (allow extremely small or large numbers) calculations.
Currency data type for monetary calculations and accuracy.
Decimal data type for calculations which involve a large number of digits and where accuracy is important.
Non-Numeric Data Types - 5 types:
String data type for text values.
Date data type for dates.
Boolean data types which take only one of two values: True or False.
Object data type for any Object reference.
Variant data type to hold any type of data. It is the default VBA data type - if you do not declare a variable.
Each data type has been elaborated below.
| Data Types | Storage Size | Range or Types of Values they Store | ||
| Byte | 1-byte | Holds small positive integer values from 0 to 255. It is the smallest VBA numeric data type, used less frequently. | ||
| Integer | 2-bytes | Holds integer values, ie. whole numbers in the range -32,768 to 32,767. For larger whole numbers, or a greater range, use data type Long. | ||
| Long | 4-bytes | Holds long integer values and stores only whole numbers. Is also an Integer data type but the range is much larger: from –2,147,483,648 to 2,147,483,647. | ||
| Single | 4-bytes | Used to store numericals that have a decimal component. Holds 32-bit (4-byte) single-precision floating-point numbers ranging in value from -3.402823E+38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E+38 for positive values. Single data type take half the memory space than Double type (4-bytes versus 8-bytes), but Double data type have a greater range (ie. can accommodate much larger or smaller numbers) and a greater accuracy (15 digits of precision versus the 7 digits of Single-precision). See Data Type Double for more details. | ||
| Double | 8-bytes | Used to store numericals that have a decimal component. Holds 64-bit (8-byte) double-precision floating-point numbers in the range -1.79769313486232E+308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E+308 for positive values. Double-precision floating-point numbers as compared to Single-precision numbers (ie. Double Data Type vs Single Data Type): They take twice the memory space (8-bytes versus 4-bytes), but have a greater range (ie. can accommodate much larger or smaller numbers) and a greater accuracy (15 digits of precision versus the 7 digits of Single-precision). The precision (allowing extremely small or large numbers) with which values are stored is a very important difference between Double and Single. Data Type Single being limited to 7 digits, the value 12345678 stored as Single will get rounded to 12345680 and similarly 1.2345678 will get rounded to 1.234568, whereas data type Double permits 15 digits of accuracy. | ||
| Note: The E stands for exponent. To avoid writing extremely long numbers, use scientific notation (SN), which is supported by Visual Basic. Single or Double data types usually use SN ie. a numeric value containing the letter E followed by a number. To convert a SN number say 5.0E+3 to the actual number, move the decimal position 3 positions to the right OR multiply the number to the left of E by 10 raised to the power of 3 viz. the actual number is 5000. If the exponent is negative, like in 5.25E-3, move the decimal position 3 positions to the left OR multiply the number to the left of E by 10 raised to the power of -3 viz. the actual number is -0.00525. |
||||
| Currency | 8-bytes | Numeric data type used to store monetary values in the range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. It accomodates up to 15 digits to the left of the decimal point, and maintains precision upto 4 decimal places (ie. 4 digits to the right of the decimal point). Currency is a scaled integer data type and provides a high level of accuracy. It is also referred to as fixed-point data type, as opposed to the floating-point numbers like Single and Double data types which can have rounding errors. The scaled integer types represent decimal values as integers by multiplying them by a factor of 10. Currency data type is suitable for use in monetary calculations and calculations where accuracy is important. | ||
| Decimal | 12-bytes | Stores values (if non-decimal) from –79,228,162,514,264,337,593,543,950,335 to 79,228,162,514,264,337,593,543,950,335. Maintains precision up to 28 decimal places with values from –7.9228162514264337593543950335 to 7.9228162514264337593543950335. The Decimal data type is a Variant subtype, and not really a separate data type. To use it, first the variable is to be declared as Variant and then convert it by using the CDec function. Decimal is a scaled integer data type and provides a high level of accuracy. It is also referred to as fixed-point data type, as opposed to the floating-point numbers like Single and Double data types which can have rounding errors. The scaled integer types represent decimal values as integers by multiplying them by a factor of 10. The Decimal data type is suitable for calculations, such as financial, which involve a large number of digits and where accuracy is important without rounding errors. | ||
| String | - | Stores values as text. A string data type can hold letters, numbers, spaces or special symbols (example, punctuation marks), but treats them as text. It is of 2 types: variable-length or fixed-length. By default, all String variables are of variable-length. | ||
| String (variable-length) | 10 bytes + string length | Holds upto approx 2 billion characters. To declare a variable-length string: Dim strProjectStatus As String Assign value to the variable: strProjectStatus = "First & Second Process_Stage" Quotation marks are used to state that the value is a string. |
||
| String (fixed-length) | Length of String | Can hold from 1 to 65,535 characters. To declare a fixed-length string: Dim strFirstNumbers As String * 7. The string variable strFirstNumbers in this case can hold a maximum of 7 characters. At runtime, the string cannot be resized and only the first 7 characters will be stored in the variable. For example: strFirstNumbers = "123456789", then the strFirstNumbers variable will only store "1234567" . |
||
| Date | 8-bytes | Holds either the date or the time, or both. Values can range from January 1, 100 to December 31, 9999. After declaring the variable, you can assign a value to it. A date value must be entered between two # signs, viz. DateOfBirth = #2/15/1961# TimeOfBirth = #10:05 AM# MarriageDate = #2/19/94 17:30# Note: Date equates to a numeric value which is the count of number of days elapsed from a certain referenced date. Dates are stored as 64-bit (8-byte) Double Precision floating-point numbers. The integer part (values to the left of decimal) is the number of days elapsed from December 30, 1899 (which equates to zero). Dates prior to December 30, 1899 are stored as negative values. For example, January 1, 1900 is stored as 2; March 15, 2001 is stored as 36,965. The fractional part (values to the right of decimal) holds time information, and represents the time as a fraction of a whole day. For example, 12.00AM (midnight) is stored as 0; 6:00AM is stored as 0.25; 12.00PM (noon) is stored as 0.5; 6:00PM is stored as 0.75; 6:00:30PM is stored as 0.750347222. |
||
| Boolean | 2-bytes | Stores Boolean values which take only one of two values: True or False. You need to assign a TRUE or FALSE value to the Boolean variable, else it will assume the default value of FALSE. A Boolean variable also corresponds to numeric values, wherein the FALSE value equates to 0 while any other numeric value, positive or negative, equates to TRUE. | ||
| Object | 4-bytes | Any Object reference. Object Variables, like other variables, are declared with the Dim or Public statement: Dim ws As Worksheet. An object variable has to be assigned with the Set keyword viz. Set ws = Worksheets("Sheet1"). | ||
| Variant | - | Variant type variables can hold any type of data except a fixed length string. Is the most flexible data type as it stores both numeric (matches the Double data type) and non-numeric values. Should be used only when you are not sure the data type you are going to use, because it can slow down code execution while guessing what data type it should be for the assigned value. You may also pay a heavy price on debugging. You could accidentally assign a text string instead of a numerical value to the variable. It is the default VBA data type - if you forget or do not declare a variable, it will automatically become a Variant type. Using the Dim statement to declare a variant: Dim varValue, which is the same as: Dim varValue As Variant. | ||
| Variant (numeric) | 16 bytes | Range: Any value as large as Double. | ||
| Variant (text) | 22-bytes + string length | Range: Same as variable-length string. |
