Microsoft Office Tutorials and References
In Depth Information
Assigning Values to Variables
A variable is useless if you aren’t able to assign a value to it. Assigning a value to a variable is
done using an equal sign (=), which is also known as the assignment operator . The name of
the variable is given first, followed by the assignment operator, and then an expression of the
value that you wish to assign to the variable. The value expression could be a literal value, or
it could be something more complicated, such as a mathematical equation.
MyNumber = 23
YourNumber = 16 + 85
MyString = "This is a literal string."
TwoStrings = "This is the first part." + "This is the second part."
MyDate = #05/07/1992#
You can also assign the value from one variable to another variable, or you can reference the
same variable you are assigning a value to. For example, if you are keeping a running total of
hours worked, you can update the total hours worked by adding the number of hours worked
during the current day.
TotalLaps = LapCounter
TotalHoursWorked = TotalHoursWorked + HoursWorkedToday
Working with Arrays
An array is a group of values that are of the same data type. Although an array contains mul
tiple values, VBA treats the array as one variable, which offers some flexibility when working
with arrays. You can work with the entire array at once or just one element in the array at a
time. To work with an individual element of an array, you specify the element’s index number ,
which represents the element’s position within the array. If you visualize an array as a list, the
index number is the element’s position in the list.
Arrays have two boundaries, a lower and an upper. The default lower boundary for arrays is
0, which can lead to a bit of confusion at times. With a starting index number of 0, the first
element in the list would be at position 0. This means you are always working with an index
number that is actually one less than the actual position in the array.
If you prefer to start counting from 1, you can change the lower boundary of an array using
the Option Base statement. The Option Base statement must be placed in the declarations sec
tion of a module before any arrays are defined and before any procedures. The Option Base
statement can take only an argument of 0 or 1, as shown in this example:
Option Base 1