Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
Working with strings
Like Excel, VBA can manipulate both numbers and text (strings). There are two types of strings
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 requires 1 byte of storage, plus a small amount of storage for the
header of each string. When you declare a variable with a Dim statement as data type String ,
you can specify the length if you know it (that is, a fixed-length string), or you can let VBA
handle it dynamically (a variable-length string).
In the following example, the MyString variable is declared to be a string with a maximum
length of 50 characters. YourString is also declared as a string; but it’s a variable-length string,
so its length is unfixed.
Dim MyString As String * 50
Dim YourString As String
Working with dates
You can use a string variable to store a date, but if you do, it’s not a real date (meaning you can’t
perform date calculations with it). 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 hash marks ( # ).
The range of dates that VBA can handle is much larger than Excel’s own date range,
which begins with January 1, 1900, and extends through December 31, 1999. Therefore,
be careful that you don’t attempt to use a date in a worksheet that is outside of Excel’s
acceptable date range.
In Chapter 10, I describe some relatively simple VBA functions that enable you to create
formulas that work with pre-1900 dates in a worksheet.