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

You make your life easier if you get into the habit of making your variable names as descriptive as possible. VBA does, however, have a few rules regarding variable names.

 You can use alphabetic characters, numbers, and some punctuation characters, but the first character must be alphabetic.

 VBA doesn't distinguish between case. To make variable names more readable, programmers often use mixed case (for example, InterestRate rather than interestrate).

 You can't use spaces or periods. To make variable names more readable, programmers often use the underscore character (Interest_Rate).

 You can't embed special type declaration characters (#, $, %, &, or !) in a variable name.

 Variable names can be as long as 254 charactersbut using such long variable names isn't recommended.

The following list contains some examples of assignment expressions that use various types of variables. The variable names are to the left of the equal sign. Each statement assigns the value to the right of the equal sign to the variable on the left.

x = 1 InterestRate = 0.075 LoanPayoffAmount = 243089.87 DataEntered = False x = x + 1 MyNum = YourNum * 1.25 UserName = "Bob Johnson" DateStarted = #12/14/2012#

VBA has many reserved words, which are words that you can't use for variable or procedure names. If you attempt to use one of these words, you get an error message. For example, although the reserved word Next might make a very descriptive variable name, the following instruction generates a syntax error:

Next = 132

Unfortunately, syntax error messages aren't always descriptive. If the Auto Syntax Check option is turned on, you get the error Compile error: Expected: variable. If Auto Syntax Check is turned off, attempting to execute this statement results in Compile error: Syntax error. It would be more helpful if the error message were something like Reserved word used as a variable. So, if an instruction produces a strange error message, check the VBA Help system to ensure that your variable name doesn't have a special use in VBA.

Defining data types

VBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data. Some programming languages, however, are strictly typed, which means that the programmer must explicitly define the data type for every variable used.

Data type refers to how data is stored in memoryas integers, real numbers, strings, and so on. Although VBA can take care of data typing automatically, it does so at a cost: slower execution and less efficient use of memory. As a result, letting VBA handle data typing may present problems when you're running large or complex applications. Another advantage of explicitly declaring your variables as a particular data type is that VBA can perform some additional error checking at the compile stage. These errors might otherwise be difficult to locate.

Table 3.1 lists VBA's assortment of built-in data types. (Note that you can also define custom data types, which are covered later in this chapter in the section User-Defined Data Types.)

TABLE 3.1VBA Built-in Data Types

NOTE

NOTE

The Decimal data type is unusual because you can't declare it. In fact, it is a subtype of a variant. You need to use the VBA CDec function to convert a variant to the Decimal data type.

Generally, it's best to use the data type that uses the smallest number of bytes yet still can handle all the data that will be assigned to it. When VBA works with data, execution speed is partially a function of the number of bytes that VBA has at its disposal. In other words, the fewer the bytes used by the data, the faster that VBA can access and manipulate the data.

For worksheet calculation, Excel uses the Double data type, so that's a good choice for processing numbers in VBA when you don't want to lose any precision. For integer calculations, you can use the Integer type (which is limited to values less than or equal to 32,767). Otherwise, use the Long data type. In fact, using the Long data type even for values less than 32,767 is recommended because this data type may be a bit faster than using the Integer type. When dealing with Excel worksheet row numbers, you want to use the Long data type because the number of rows in a worksheet exceeds the maximum value for the Integer data type.

Declaring variables

If you don't declare the data type for a variable that you use in a VBA routine, VBA uses the default data type, Variant. Data stored as a Variant acts like a chameleon: it changes type, depending on what you do with it.

The following procedure demonstrates how a variable can assume different data types:

Sub VariantDemo() MyVar = True MyVar = MyVar * 100 MyVar = MyVar / 4 MyVar = "Answer: " & MyVar MsgBox MyVar End Sub

In the VariantDemo procedure, MyVar starts as a Boolean. The multiplication operation converts it to an Integer. The division operation converts it to a Double. Finally, it's concatenated with text to make it a String. The MsgBox statement displays the final string: Answer: -25.

To demonstrate further the potential problems in dealing with Variant data types, try executing this procedure:

Sub VariantDemo2() MyVar = "123" MyVar = MyVar + MyVar MyVar = "Answer: " & MyVar MsgBox MyVar End Sub

The message box displays Answer: 123123. This is probably not what you wanted. When dealing with variants that contain text strings, the + operator will join (concatenate) the strings together rather than perform addition.

Determining a data type

You can use the VBA TypeName function to determine the data type of a variable. Here's a modified version of the VariantDemo procedure. This version displays the data type of MyVar at each step.

Sub VariantDemo3() MyVar = True MsgBox TypeName(MyVar) MyVar = MyVar * 100 MsgBox TypeName(MyVar) MyVar = MyVar / 4 MsgBox TypeName(MyVar) MyVar = "Answer: " & MyVar MsgBox TypeName(MyVar) MsgBox MyVar End Sub

Thanks to VBA, the data type conversion of undeclared variables is automatic. This process may seem like an easy way out, but remember that you sacrifice speed and memoryand you run the risk of errors that you may not even know about.

Declaring each variable in a procedure before you use it is an excellent habit. Declaring a variable tells VBA its name and data type. Declaring variables provides two main benefits.

 Your programs run faster and use memory more efficiently. The default data type, Variant, causes VBA to perform time-consuming checks repeatedly and reserve more memory than necessary. If VBA knows the data type, it doesn't have to investigate, and it can reserve just enough memory to store the data.

 You avoid problems involving misspelled variable names. This benefit assumes that you use Option Explicit to force yourself to declare all variables (see the next section). Say that you use an undeclared variable named CurrentRate. At some point in your routine, however, you insert the statement CurentRate = .075. This misspelled variable name, which is difficult to spot, will likely cause your routine to give incorrect results.

Forcing yourself to declare all variables

To force yourself to declare all the variables that you use, include the following as the first instruction in your VBA module:

Option Explicit

When this statement is present, VBA won't even execute a procedure if it contains an undeclared variable name. VBA issues the error message shown in Figure 3.1, and you must declare the variable before you can proceed.

FIGURE 3.1 VBA's way of telling you that your procedure contains an undeclared variable

TIP

To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, enable the Require Variable Declaration option on the Editor tab of the VBE Options dialog box (choose Tools Options). It is generally considered a best practice to enable this option. Be aware, however, that this option will not affect existing modules; the option affects only those modules created after it is enabled.

Scoping variables

A variable's scope determines in which modules and procedures you can use the variable. Table 3.2 lists the three ways in which a variable can be scoped.

TABLE 3.2Variable Scope

We discuss each scope further in the following sections.