Michael Alexander - Excel 2019 Power Programming with VBA

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 Dimstatement 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 MyStringvariable is declared to be a string with a maximum length of 50 characters. YourStringis also declared as a string; but it's a variable-length string, so its length is not fixed.

Dim MyString As String * 50 Dim YourString As String

You can use a string variable to store a date, but if you do, it's not a real date (meaning that you can't perform date calculations with it). Using the Datedata 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 Datedata 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, 9999. Therefore, be careful that you don't attempt to use a date in a worksheet that is outside Excel's acceptable date range.

In Chapter 5, “Creating Function Procedures,” we describe some relatively simple VBA functions that enable you to create formulas that work with pre-1900 dates in a worksheet.

It is commonly known that Excel has a date bug: it incorrectly assumes that the year 1900 is a leap year. Even though there was no February 29, 1900, Excel accepts the following formula and displays the result as the 29th day of February 1900:

=Date(1900,2,29)

VBA doesn't have this date bug. The VBA equivalent of Excel's DATEfunction is DateSerial. The following expression (correctly) returns March 1, 1900:

DateSerial(1900,2,29)

Therefore, Excel's date serial number system doesn't correspond exactly to the VBA date serial number system. These two systems return different values for dates between January 1, 1900, and February 28, 1900.

Here are some examples of declaring variables and constants as Datedata types:

Dim Today As Date Dim StartTime As Date Const FirstDay As Date = #1/1/2019# Const Noon = #12:00:00#

Dates are always defined using month/day/year format, even if your system is set to display dates in a different format (for example, day/month/year).

If you use a message box to display a date, it's displayed according to your system's short date format. Similarly, a time is displayed according to your system's time format (either 12- or 24-hour). You can modify these system settings by using the Regional Settings option in the Windows Control Panel.

An assignment statement is a VBA instruction that evaluates an expression and assigns the result to a variable or an object. Excel's Help system defines expression as “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.”

Much of the work done in VBA involves developing (and debugging) expressions. If you know how to create formulas in Excel, you'll have no trouble creating expressions in VBA. With a worksheet formula, Excel displays the result in a cell. The result of a VBA expression, on the other hand, can be assigned to a variable or used as a property value.

VBA uses the equal sign ( =) as its assignment operator. The following are examples of assignment statements (the expressions are to the right of the equal sign):

x = 1 x = x + 1 x = (y * 2) / (z * 2) FileOpen = True FileOpen = Not FileOpen Range("TheYear").Value = 2010

Expressions can be complex. You may want to use the line continuation sequence (space followed by an underscore) to make lengthy expressions easier to read.

Often, expressions use functions. These functions can be built-in VBA functions, Excel's worksheet functions, or custom functions that you develop in VBA. We discuss built-in VBA functions later in this chapter (see the upcoming section “Built-in Functions”).

Operators play a major role in VBA. Familiar operators describe mathematical operations, including addition ( +), multiplication ( *), division ( /), subtraction ( –), exponentiation ( ^), and string concatenation (&). Less familiar operators are the backslash ( \) operator (used in integer division) and the Modoperator (used in modulo arithmetic). The Modoperator returns the remainder of one number divided by another. For example, the following expression returns 2:

17 Mod 3

VBA also supports the same comparison operators used in Excel formulas: equal to (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

With one exception, the order of precedence for operators in VBA is exactly the same as in Excel (see Table 3.3). And, of course, you can use parentheses to change the default order of precedence.

TABLE 3.3 Operator Precedence

The negation operator (a minus sign) is handled differently in VBA. In Excel, the following formula returns 25:

=-5^2

In VBA, xequals –25 after this statement is executed.

x = -5 ^ 2

VBA performs the exponentiation operation first and then applies the negation operator. The following statement returns 25:

x = (-5) ^ 2

In the statement that follows, xis assigned the value 10 because the multiplication operator has a higher precedence than the addition operator:

x = 4 + 3 * 2

To avoid ambiguity, you may prefer to write the statement as follows:

x = 4 + (3 * 2)

In addition, VBA provides a full set of logical operators, shown in Table 3.4. For complete details on these operators (including examples), use the VBA Help system.

TABLE 3.4 VBA Logical Operators

The following instruction uses the Notoperator to toggle the gridline display in the active window. The DisplayGridlinesproperty takes a value of either Trueor False. Therefore, using the Notoperator changes Falseto Trueand Trueto False.