Page 86 - Microsoft Office Excel 2003 Programming Inside Out
P. 86
Microsoft Office Excel 2003 Programming Inside Out
Variable-length strings can hold approximately 2 billion (2 ^ 31) characters, subject to avail-
able memory, and fixed-length strings can hold about 65,000 characters (2 ^ 16).
Both types of strings are declared similarly using the Dim statement just as was done with the
numeric types. Fixed-length strings add extra code to specify the length of the string. The
first line in the following code fragment specifies a variable-length string, and the second line
specifies a fixed-length string of 25 characters:
Dim MyString as String
Dim MyFixedString as String * 25
Strings that have been declared but have not yet had a value assigned to them are known as
empty strings. To assign a value to a string variable, it needs to be enclosed within double quo
tation marks. The following lines are examples of assigning values to strings:
MyString = "Hello world."
MyFixedString = "This is a fixed string."
MyEmptyString = ""
Note Fixed strings must be exactly 25 characters in length; therefore, the Visual Basic
Editor will either add spaces or truncate the string so it is the proper length.
Handling Dates and Times
Dates and times are stored within the Date data type. The range of dates that can be stored is
from January 1, 100, to December 31, 9999, with all times from 0:00:00 to 23:59:59. Although
a Date variable can hold both a date and time, if you need only one element, only that element
needs to be assigned to the variable; the other will be omitted from the variable.
You can assign values to a date variable by enclosing a recognizable literal string within num
Chapter 4
ber signs (#). VBA can recognize dates and times in just about any format, but you should
always be as explicit as possible to ensure the correct value is being used. Here are some
examples of dates and times VBA can recognize.
MyDate = #15 July 1999#
StartDate = #April 8, 2001#
MyTime = #8:47 PM#
StartingDateTime = #05/07/1992 15:56#
The Visual Basic Editor might change the dates and times that you type to reflect your com
puter’s date and time settings. For example, #15 July 1999# might become #7/15/1999#.
Note When entering dates, VBA will recognize two-digit years and uses 2029 as the cut-
off year. All two-digit years from 00 to 29 are recognized as being a part of the 2000s. The
remaining years, 30 to 99, are considered part of the 1900s. It is best to always include
the full four-digit year to remove any ambiguities.
60
Part 2: Visual Basic for Applications
