Excel 2019 Power Programming with VBA. Michael Alexander
Читать онлайн книгу.
you control the error color (use the Editor Format tab) and whether the error message is displayed (use the Auto Syntax Check option in the Editor tab).
Comments
A comment is descriptive text embedded in your code and ignored by VBA. It's a good idea to use comments liberally to describe what you're doing because an instruction's purpose isn't always obvious.
You can use a complete line for your comment, or you can insert a comment after an instruction on the same line. A comment is indicated by an apostrophe. VBA ignores any text that follows an apostrophe—except when the apostrophe is contained within quotation marks—up until the end of the line. For example, the following statement doesn't contain a comment, even though it has an apostrophe:
Msg = "Can't continue"
The following example shows a VBA procedure with three comments:
Sub CommentDemo() ' This procedure does nothing of value x = 0 'x represents nothingness ' Display the result MsgBox x End Sub
Although the apostrophe is the preferred comment indicator, you can also use the Rem keyword to mark a line as a comment. Here's an example:
Rem -- The next statement prompts the user for a filename
The Rem keyword (short for Remark) is essentially a holdover from older versions of BASIC, and it is included in VBA for the sake of compatibility. Unlike the apostrophe, Rem can be written only at the beginning of a line, not on the same line as another instruction.
The following are a few general tips on making the best use of comments:
Use comments to describe briefly the purpose of each procedure that you write.
Use comments to describe changes you make to a procedure.
Use comments to indicate you're using functions or constructs in an unusual or a nonstandard manner.
Use comments to describe the purpose of variables so that you and other people can decipher otherwise cryptic names.
Use comments to describe workarounds that you develop to overcome Excel bugs or limitations.
Write comments while you code rather than afterward.
When you've completed all coding, take some time to go back and tidy up your comments, removing any comments that are no longer needed and expanding on comments that may be incomplete or a bit too cryptic.
TIP
In some cases, you may want to test a procedure without including a particular instruction or group of instructions. Instead of deleting the instruction, convert it to a comment by inserting an apostrophe at the beginning. VBA then ignores the instruction when the routine is executed. To convert the comment back to an instruction, just delete the apostrophe.
The Visual Basic Editor offers an Edit toolbar containing tools to assist you in editing your code. In particular, there are two handy buttons that enable you to comment and uncomment entire blocks of code at once.
Note that the Edit toolbar isn't displayed by default. To display this toolbar, choose View ⇨ Toolbars ⇨ Edit.
You can select several lines of code at once and then click the Comment Block button on the Edit toolbar to convert the selected lines to comments. The Uncomment Block button converts a group of comments back into uncommented code.
Variables, Data Types, and Constants
VBA's main purpose is to manipulate data. Some data resides in objects, such as worksheet ranges. Other data is stored in variables that you create.
You can think of a variable as a named storage location in your computer's memory. Variables can accommodate a wide variety of data types—from simple Boolean values (True or False) to large, double-precision values (see the following section). You assign a value to a variable by using the equal sign operator (more about this process in the upcoming section “Assignment Statements”).
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 characters—but 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 memory—as 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.1 VBA Built-in Data Types
| Data Type | Bytes Used |
|