Lecture №6. Essential VBA Language Elements
In This Chapter
1. Knowing when, why, and how to use comments in your code
2. Using variables and constants
Вecause VBA is a real, live programming language, it uses many elements common to all programming languages. In this chapter, I introduce you to several of these elements: comments, variables, constants, data types, arrays, and a few other goodies. If you’ve programmed with other languages, some of this material will be familiar. If you’re a programming newbie, it’s time to roll up your sleeves and get busy.
Using Comments in Your VBA Code
A comment is the simplest type of VBA statement. Because VBA ignores these statements, they can consist of anything you want. You can insert a comment to remind yourself why you did something or to clarify some particularly elegant code you wrote.
Use comments liberally and extensively to describe what the code does (which isn’t always obvious by reading the code itself). Often, code that makes perfect sense today mystifies you tomorrow. Been there. Done that.
You begin a comment with an apostrophe (‘). VBA ignores any text that follows an apostrophe in a line of code. You can use a complete line for your comment or insert your comment at the end of a line of code. The following example shows a VBA procedure with four comments:
The “apostrophe indicates a comment” rule has one exception: VBA doesn’t interpret an apostrophe inside a set of quotation marks as a comment indicator. For example, the following statement doesn’t contain a comment, even though it has an apostrophe:
Msg = “Can’t continue”
When you’re writing code, you may want to test a procedure by excluding a particular statement or group of statements. You could delete the statements and then retype them later. But that’s a waste of time. A better solution is to simply turn those statements into comments by inserting apostrophes. VBA ignores statements beginning with apostrophes when executing a routine. To reactivate those “commented” statements, just remove the apostrophes.
Here’s a quick way to convert a block of statements to comments. In the VBE, choose View➪Toolbars➪Edit to display the Edit toolbar. To convert a block of statements to comments, select the statements and click the Comment Block button. To remove the apostrophes, select the statements and click the Uncomment Block button.
|
|
|
Everyone develops his or her own style of commenting. To be useful, how- ever, comments should convey information that’s not immediately obvious from reading the code.
Using Variables, Constants, and Data Types
VBA’s main purpose is to manipulate data. VBA stores the data in your com- puter’s memory, and the data may or may not end up on disk. Some data, such as worksheet ranges, resides in objects. Other data is stored in variables that you create.
Understanding variables
A variable is simply a named storage location in your computer’s memory that’s used by a program. You have lots of flexibility in naming your variables, so make the variable names as descriptive as possible. You assign a value to a variable by using the equal sign operator. (More about this later in the “Using Assignment Statements” section.)
To make variable names more readable, programmers often use mixed case (for example, InterestRate) or the underscore character (interest_rate).
VBA has many reserved words that you can’t use for variable names or pro- cedure names. These include words such as Sub, Dim, With, End, Next, and For. If you attempt to use one of these words as a variable, you may get a compile error (which means your code won’t run). So, if an assignment state- ment produces an error message, double-check and make sure that the variable name isn’t a reserved word.
VBA does allow you to create variables with names that match names in Excel’s object model, such as Workbook and Range. But, obviously, using names like that just increases the possibility of getting confused. Here’s a perfectly valid (but very confusing) macro that declares Range as a variable name and works with a cell named Range, on a worksheet named Range.
 |
So resist the urge to use a variable named Workbook or Range and use some- thing like MyWorkbook or MyRange instead.
What are VBA’s data types?
When I talk about data type, I’m referring to the manner in which a program stores data in memory — for example, as integers, real numbers, or strings. Although VBA can take care of these details automatically, it does so at a
|
|
|
cost. (There’s no free lunch.) Letting VBA handle your data typing results in slower execution and inefficient memory use. For small applications, this
usually doesn’t present much of a problem. But for large or complex applica- tions, which may be slow or need to conserve every last byte of memory, you need to be on familiar terms with data types.
VBA automatically handles all the data details, which makes life easier for programmers. Not all programming languages provide this luxury. For exam- ple, some languages are strictly typed, which means the programmer must explicitly define the data type for every variable used.
VBA does not require that you declare the variables that you use, but it’s definitely a good practice. You’ll see why later in this chapter.
VBA has a variety of built-in data types. Table 7-1 lists the most common types of data that VBA can handle.
Declaring and scoping variables
If you read the previous sections, you now know a bit about variables and data types. In this section, you discover how to declare a variable as a certain data type.
If you don’t declare the data type for a variable you use in a VBA routine, VBA uses the default data type: Variant. Data stored as a variant acts like a chame- leon; it changes type depending on what you do with it. For example, if a vari- able is a Variant data type and contains a text string that looks like a number (such as “143”), you can use this variable for string manipulations as well as numeric calculations. VBA automatically handles the conversion. Letting VBA handle data types may seem like an easy way out — but remember that you sacrifice speed and memory.
Before you use variables in a procedure, it’s an excellent practice to declare your variables — that is, tell VBA each variable’s data type. Declaring your variables makes your macro run faster and use memory more efficiently. The default data type, Variant, causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows a variable’s data type, it doesn’t have to investigate and can reserve just enough memory to store the data.
|
|
|
To force yourself to declare all the variables you use, include these two words as the first statement in your VBA module:
 |
When this statement is present, you won’t be able to run your code if it con- tains any undeclared variables.
You need to use Option Explicit only once: at the beginning of your module, prior to the declaration of any procedures in the module. Keep in mind that the Option Explicit statement applies only to the module in which it resides. If you have more than one VBA module in a project, you need an Option Explicit statement for each module.
Literature
1. Джон Уокенбах, Excel VBA Programming For Dummies 5 августа 1999 г.
2. Джон Мюллер, VBA for dummies 2003 г.
3. Ричард Мансфилд, Mastering VBA for Microsoft Office 2007 2008 г.
4. Билл Джелен, Трейси Сирстад, Excel 2013 VBA and Macros 2013 г
5. Ричард Мансфилд, Mastering VBA for Office 2010 2010 г
6. Ричард Шеперд, Excel 2007 VBA Macro Programming 2009 г
7. Майк Мак-Грат, EXCEL VBA IN EASY STEPS. 2017 г.
Дата добавления: 2019-03-09; просмотров: 219; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!