Constants

Few things in life are constant. Examples of constants include the number of hours in a day, the number of days in a week, the speed of light, a dozen, pi, and several others.

To declare a constant in Excel VBA we use the CONST statement. The CONST statement takes the form:
Const constantname [As Datatype] = Value

constantname is a valid name you choose that should be representative of the constant your’re naming. Valid names begin with a letter, contain no spaces or punctuation, and must be unique, meaning not a reserved keyword.

[As Datatype] Anytime you see arguments in [ ] symbols, that indicates they are optional parameters. If you don’t declare a datatype, the default data type is called variant, which stores any value, like numbers, dates, text, currency, etc.

= Value  This is a required portion of the CONST declaration, you need to assign a value to the constant when you create it, and if the value is text, it should be enclosed in ” ” marks.

From the Microsoft Excel Help system, here is a table that summarizes the most commonly used datatypes:

Data type	Storage size	Range
Byte	1 byte	0 to 255
Boolean	2 bytes	True or False
Integer	2 bytes	-32,768 to 32,767
Long (long integer)	4 bytes	-2,147,483,648 to 2,147,483,647
Single (single-precision floating-point)	4 bytes	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values
Double (double-precision floating-point)	8 bytes	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency (scaled integer)	8 bytes	-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Decimal	14 bytes	+/-79,228,162,514,264,337,593,543,950,335 with no decimal point; +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest non-zero number is +/-0.0000000000000000000000000001
Date	8 bytes	January 1, 100 to December 31, 9999
Object	4 bytes	Any Object reference
String (variable-length)	10 bytes + string length	0 to approximately 2 billion
String (fixed-length)	Length of string	1 to approximately 65,400
Variant (with numbers)	16 bytes	Any numeric value up to the range of a Double
Variant (with characters)	22 bytes + string length	Same range as for variable-length String
User-defined (using Type)	Number required by elements	The range of each element is the same as the range of its data type.

The important takeaway from the data types tables is that different variable data types require different amounts of random access memory (RAM). With today’s current computer systems that typically have two or more gigabytes of ram (a gigabyte is 1,000 megabytes) it’s unlikely you’ll run out of ram unless you create over 100,000 variables with a variant data type.

Variables

A more apt quote is by Heraclitus (a Greek Philosopher) who said: The only thing constant is change. Change is constant and variables are used to capture change. In the context of Excel, the names of workbooks change, the number of workbooks you have open is variable, the number of sheets, pivot tables and charts is usually variable, the name of sheets is variable, the values in cells are variable, and on, and on.

To declare a variable in Excel VBA we use the DIM statement. The DIM statement takes the form:
Dim variablename [As Datatype]

variablename is a valid name you choose that should be representative of the object your’re naming. Valid names begin with a letter, contain no spaces or punctuation, and must be unique, meaning not a reserved keyword.

Additionally, though not required, best practice is to use Hungarian notation to prefix the variable name with its data type. Hungarian notation is a 1-3 letter prefix to help clarify what type of data the variable will store. For example:

Dim varValueChanges as Variant

Dim sngSingleValue as Single

Dim strName as String

After you create a variable with the Dim statement the variable has no value. To store information in the variable you need to assign the value to the variable using the assignment operator, which is the = (equals) symbol.

Imagine you want to store the value in cell A2 in a variable named varValue. To assign the value in cell A2 in a variable you could type:

varValue = Range(“A2″).value

or

varValue = Cells(2,2).value

Placing the variable to the left of the assignment operator is called a get statement, you get a value to store in the variable. If you put the variable on the right of the assignment operator this is termed a set statement, you set the value equal to the variable. For example,

Range(“A2″).value=varValue

would assign the value stored in the varValue variable to cell A2 of the active worksheet.

Array Variables

A variable typically stores a single value. What if you need to loop through hundreds of cells and store all the values? It would be inefficient to create hundreds of variables. An array variable is capable of storing a nearly unlimited number of rows, columns, and sheets of data (subject to computer RAM).

Array variables require you define the number of dimensions (think of rows as the first dimension, columns as the second dimension, sheets as the third dimension, workbooks as the fourth dimension, applications as a fifth dimension, etc). Most variable are only two dimensions. The limit is 60 dimensions

To declare an array variable in Excel VBA when we know the size of the dimensions in advance we use the DIM statement. The DIM statement for an array variable takes the form:
Dim variablename(dimension1, dimension2, dimension 3) [As Datatype]

(dimension#) is a whole number greater than 0; If the variable has more than 1 dimension, separate the dimensions with commas.

Often times you don’t know in advance the variable count of rows and columns of data you’ll need to store. A common approach to creating an array variable of variable size is to REDIM the array variable.

Imagine i create an array variable called varValues by typing

Dim varValues() as variant

Note that i did not include the dimensions in the array variable declaration.

Now i create two variables, lngRowCount and lngColCount and store the last row and column of data in those variables. To locate the last row of data, refer to our previous blog post Learn VBA to Navigate

Use the REDIM statement to change the size of the array by typing:

Redim varValues(lngRowCount, lngColCount)

A few notes about array variables. When you use the redim command to change the size of the array, you have the option of preserving existing array variables if you include the optional keyword PRESERVE after REDIM. Also, array variables start counting from zero by default. Programmatically, it is preferable to start counting from 1. You can change this by placing OPTION BASE 1 in the global declarations at the top of  a module before or after OPTION EXPLICIT

Global Declarations

When working with variables, best practice is to include the following statements in the global declarations section of your code. The global declarations are statements that appear at the top of a vba module before any procedures or functions.

Option Explicit

The option explicit statement requires that you explicitly use Dim before you work with a variable. If you don’t include option explicit you can create variables on the fly, without first dimensioning them. The benefit of using the option explicit declaration is that when you debug compile your code the vba compiler will catch typographical mistakes in the spelling of variable names

Option Base 1

By default, array variables start counting from zero, not one. The option base 1 statement changes the default setting of the array variable index from zero to one.