Microsoft Office Tutorials and References
In Depth Information
You can use a string variable to store a date, of course, but then you can't perform date calculations using the
variable. Using the Date data type is a better way to work with dates.
A variable defined as a Date uses 8 bytes of storage and can hold dates ranging from January 1, 0100, to
December 31, 9999. That's a span of nearly 10,000 years — more than enough for even the most aggressive fin-
ancial forecast! The Date data type is also useful for storing time-related data. In VBA, you specify dates and
times by enclosing them between two number signs (#).
The range of dates that VBA can handle is much larger than Excel's own date range,
which begins with January 1, 1900. Therefore, be careful that you don't attempt to use a
date in a worksheet that lies outside of Excel's acceptable date range.
Here are some examples of declaring variables and constants as Date data types:
Dim Today As Date
Dim StartTime As Date
Const FirstDay As Date = #1/15/2013#
Const Noon = #12:00:00#
Date variables display dates according to your system's short date format, and times
appear according to your system's time format (either 12 or 24 hours). You can modify
these system settings by using the Regional and Language Options dialog box in the
Windows Control Panel. See Chapter 6 for more information on working with dates and
Using Assignment Statements
An assignment statement is a VBA instruction that evaluates an expression and assigns the result to a variable
or an object. An expression is a combination of keywords, operators, variables, and constants that yields a
string, number, or object. An expression can perform a calculation, manipulate characters, or test data.
If you know how to create formulas in Excel, you'll have no trouble creating expressions in VBA. With a work-
sheet formula, Excel displays the result in a cell. Similarly, you can assign a VBA expression to a variable or
use it as a property value.
VBA uses the equal sign (=) as its assignment operator. Note the following examples of assignment statements.
(The expressions are to the right of the equal sign.)