Microsoft Office Tutorials and References
In Depth Information
Using Comments in Your Code
The vbInformation constant has a value of 64 , but it’s not important that you know that. If
you use the Excel macro recorder to record your actions, you’ll find many other constants in the
Like Excel, VBA can manipulate both numbers and text (strings). VBA supports two types of
h Fixed-length strings are declared with a specified number of characters. The maximum
length is 65,535 characters.
h Variable-length strings theoretically can hold up to 2 billion characters.
Each character in a string takes 1 byte of storage. When you declare a string variable with a Dim
statement, you can specify the maximum length if you know it (that is, a fixed-length string), or
you can let VBA handle it dynamically (a variable-length string). In some cases, working with
fixed-length strings may be slightly more efficient in terms of memory usage.
In the following example, the MyString variable is declared to be a string with a fixed length of
50 characters. YourString is also declared as a string but with an unspecified length.
Dim MyString As String * 50
Dim YourString As String
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 financial 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/2010#
Const Noon = #12:00:00#