Excel 2019 Power Programming with VBA - Michael Alexander 19 стр.

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

TIP

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 Mod operator (used in modulo arithmetic). The Mod operator 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.3Operator Precedence

CAUTION

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

=-5^2

In VBA, x equals 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, x is 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.4VBA Logical Operators

The following instruction uses the Not operator to toggle the gridline display in the active window. The DisplayGridlines property takes a value of either True or False. Therefore, using the Not operator changes False to True and True to False.

ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines

The following expression performs a logical And operation. The MsgBox statement displays True only when Sheet1 is the active sheet and the active cell is in Row 1. If either or both of these conditions aren't true, the MsgBox statement displays False.

MsgBox ActiveSheet.Name = "Sheet1" And ActiveCell.Row = 1

The following expression performs a logical Or operation. The MsgBox statement displays True when either Sheet1 or Sheet2 is the active sheet.

MsgBox ActiveSheet.Name = "Sheet1" Or ActiveSheet.Name = "Sheet2"

Arrays

An array is a group of elements of the same type that have a common name. You refer to a specific element in the array by using the array name and an index number. For example, you can define an array of 12 string variables so that each variable corresponds to the name of a month. If you name the array MonthNames, you can refer to the first element of the array as MonthNames(0), the second element as MonthNames(1), and so on, up to MonthNames(11).

MsgBox ActiveSheet.Name = "Sheet1" Or ActiveSheet.Name = "Sheet2"

Arrays

An array is a group of elements of the same type that have a common name. You refer to a specific element in the array by using the array name and an index number. For example, you can define an array of 12 string variables so that each variable corresponds to the name of a month. If you name the array MonthNames, you can refer to the first element of the array as MonthNames(0), the second element as MonthNames(1), and so on, up to MonthNames(11).

Declaring arrays

You declare an array with a Dim or Public statement, just as you declare a regular variable. You can also specify the number of elements in the array. You do so by specifying the first index number, the keyword To, and the last index numberall inside parentheses. For example, here's how to declare an array comprising exactly 100 integers:

Dim MyArray(1 To 100) As Integer

TIP

When you declare an array, you need to specify only the upper index, in which case VBA assumes that 0 is the lower index. Therefore, the two statements that follow have the same effect:

Dim MyArray(0 To 100) As Integer Dim MyArray(100) As Integer

In both cases, the array consists of 101 elements.

By default, VBA assumes zero-based arrays. If you would like VBA to assume that 1 is the lower index for all arrays that declare only the upper index, include the following statement before any procedures in your module:

Option Base 1

Declaring multidimensional arrays

The array examples in the preceding section are one-dimensional arrays. VBA arrays can have up to 60 dimensions, although you'll rarely need more than three dimensions (a 3D array). The following statement declares a 100-integer array with two dimensions (2D):

Dim MyArray(1 To 10, 1 To 10) As Integer

You can think of the preceding array as occupying a 10 × 10 matrix. To refer to a specific element in a 2D array, you need to specify two index numbers. For example, here's how you can assign a value to an element in the preceding array:

MyArray(3, 4) = 125

The following is a declaration for a 3D array that contains 1,000 elements (visualize this array as a cube):

Dim MyArray(1 To 10, 1 To 10, 1 To 10) As Integer

Reference an item in the array by supplying three index numbers.

MyArray(4, 8, 2) = 0

Declaring dynamic arrays

A dynamic array doesn't have a preset number of elements. You declare a dynamic array with a blank set of parentheses.

Dim MyArray() As Integer

Before you can use a dynamic array in your code, however, you must use the ReDim statement to tell VBA how many elements are in the array. You can use a variable to assign the number of elements in an array. Often the value of the variable isn't known until the procedure is executing. For example, if the variable x contains a number, you can define the array's size by using this statement:

ReDim MyArray (1 To x)

You can use the ReDim statement any number of times, changing the array's size as often as you need. When you change an array's dimensions, the existing values are destroyed. If you want to preserve the existing values, use ReDim Preserve. Here's an example:

ReDim Preserve MyArray (1 To y)

Arrays crop up later in this chapter when we discuss looping (see the section Looping blocks of instructions).

Object Variables

An object variable is one that represents an entire object, such as a range or a worksheet. Object variables are important for two reasons.

 They can simplify your code significantly.

 They can make your code execute more quickly.

Object variables, like normal variables, are declared with the Dim or Private or Public statement. For example, the following statement declares InputArea as a Range object variable:

Dim InputArea As Range

Use the Set keyword to assign an object to the variable. Here's an example:

Set InputArea = Range("C16:E16")

To see how object variables simplify your code, examine the following procedure, which doesn't use an object variable:

Sub NoObjVar() Worksheets("Sheet1").Range("A1").Value = 124 Worksheets("Sheet1").Range("A1").Font.Bold = True Worksheets("Sheet1").Range("A1").Font.Italic = True Worksheets("Sheet1").Range("A1").Font.Size = 14 Worksheets("Sheet1").Range("A1").Font.Name = "Cambria" End Sub

This routine enters a value into cell A1 of Sheet1 on the active workbook, applies some formatting, and changes the fonts and size. That's a lot of typing. To reduce wear and tear on your fingers (and make your code more efficient), you can condense the routine with an object variable.

Sub ObjVar() Dim MyCell As Range Set MyCell = Worksheets("Sheet1").Range("A1") MyCell.Value = 124 MyCell.Font.Bold = True MyCell.Font.Italic = True MyCell.Font.Size = 14 MyCell.Font.Name = "Cambria" End Sub

After the variable MyCell is declared as a Range object, the Set statement assigns an object to it. Subsequent statements can then use the simpler MyCell reference in place of the lengthy Worksheets("Sheet1").Range("A1") reference.

TIP

After an object is assigned to a variable, VBA can access it more quickly than it can a normal, lengthy reference that has to be resolved. So, when speed is critical, use object variables. One way to think about code efficiency is in terms of dot processing. Every time VBA encounters a dot, as in Sheets(1).Range("A1"), it takes time to resolve the reference. Using an object variable reduces the number of dots to be processed. The fewer the dots, the faster the processing time. Another way to improve the speed of your code is by using the With-End With construct, which also reduces the number of dots to be processed. We discuss this construct later in this chapter.

The true value of object variables will become apparent when we discuss looping later in this chapter.

User-Defined Data Types

VBA lets you create custom, or user-defined, data types. A user-defined data type can ease your work with some types of data. For example, if your application deals with customer information, you may want to create a user-defined data type named CustomerInfo.