Before using a variable in a formula, you must declare it. A variable can hold values of a given type. The allowed types are the seven simple types (Number, Currency, String, Boolean, Date, Time and DateTime), the six range types (Number Range, Currency Range, String Range, Date Range, Time Range and DateTime Range) and variables that hold arrays of the previously mentioned types. This gives a total of 26 different types that a variable can have.
When you declare a variable, you also specify its name. A variable cannot have the same name as any function, operator or other keyword that is valid for Basic syntax. For example, your variable cannot be named Sin, Mod or If because Sin is a built in function, Mod is a built in operator and If is a built in keyword. When typing formulas in the formula editor, the names of the built-in functions, operators, and other keywords are highlighted in a different color. This makes it easy to check if the variable name conflicts.
Once a variable is declared, it can be used in the formula. For example, you might want to assign it an initial value:
Dim x As Number 'Declare x to be a Number variable x = 10 'Assign the value of 10 to x
You can declare more than one variable per statement by separating their declaration by commas:
Dim x As Number, y as String, z as DateTime Range x = 10 : y = "hello" z = #Jan 1, 1999# To #Jan 31, 1999#
In general, the type of a variable does not need to be explicitly given when declaring it. In such cases, the variable's type is determined by the first assignment that is made to it. This is similar to the special variable formula. This is different from Visual Basic in which a variable whose type is not given at declaration automatically has the Variant type. However, in practice, it means that you can write formulas in a similar style to what you would do if using a Variant in Visual Basic.
Dim p 'The type of p is not known yet p = "bye" 'The type of p is now set to be String Dim q 'The type of q is not known yet q = Array ("hello", p) 'q is a String Array 'Error- p is a String variable and cannot hold a Number p = 25 Dim r 'r is a Number variable, and holds the value 5 r = (10 + 5) / 3 'The types of a and c are not known yet Dim a, b As Boolean, c b = False 'The type of a is now set to Boolean 'and its value is False a = b 'The type of c is now set to Number and its value is 17 c = 2 + 3 * 5
Examples of declaring and initializing range variables
Dim gradeA, quarter 'The type of gradeA is set to Number Range gradeA = 90 To 100 'The type of quarter is set to Date Range quarter = CDate (1999, 10, 1) To CDate (1999, 12, 31)