Excel VBA Programming For Dummies. Dick KusleikaЧитать онлайн книгу.
and all the procedures that deal with printing in a module called Printing. Organizing your code in this manner makes it easier for you and others to understand and, if necessary, modify the code.
Getting VBA code into a module
An empty VBA module is like the fake food you see in the windows of some restaurants; it looks good, but it doesn’t really do much for you. Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:
Enter the code directly.
Use the Excel macro recorder to record your actions and convert those actions to VBA code (see Chapter 6).
Copy the code from one module and paste it into another.
Entering code directly
Sometimes, the best route is the most direct one. Entering code directly involves … well, typing the code using your keyboard. Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and drag-and-drop.
Throughout this book, you’ll see the terms Sub procedure, routine, program, procedure, and macro. These terms can be a bit confusing. Programming folks usually use the word procedure to describe an automated task. Technically, a procedure can be a Sub procedure or a Function procedure — both of which are sometimes called routines — or even programs. All these terms can be used interchangeably. As detailed in later chapters, however, an important distinction exists between Sub and Function procedures. For now, don’t worry about the programming terminology. Just try to understand the concepts.
Use the Tab key to indent some of the lines to make your code easier to read. Indenting isn’t necessary, but it’s a good habit to acquire. As you study the code in this book, you’ll understand why indenting code lines is helpful.
Here’s an example of a single statement split into three lines:
Selection.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlTopToBottom
This statement would perform exactly the same way if it were entered in a single line (with no line-continuation characters). Notice that the second and third lines of this statement are indented. Indenting is optional, but it helps you see that these lines are not separate statements.
1 Create a new workbook in Excel.
2 Press Alt+F11 to activate the VBE.
3 Click the new workbook’s name in the Project Explorer.
4 Choose Insert ⇒ Module to insert a VBA module into the project.
5 Type the following code in the module: Sub GuessName() Msg = "Is your name " & Application.UserName & "?" Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then MsgBox "Oh, never mind." If Ans = vbYes Then MsgBox "I must be psychic!"End Sub
6 Position the cursor anywhere within the text you typed and press F5 to execute the procedure.F5 is a shortcut for Run ⇒ Run Sub/UserForm. If you entered the code correctly, Excel executes the procedure, and you can respond to the simple dialog box shown in Figure 3-4. The text in the dialog box that appears on your screen will differ, of course, from the text shown in the figure.
FIGURE 3-4: The GuessName procedure displays this dialog box.
When you enter the code listed in Step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable.
COMPILE ERROR?
There’s a chance that the GuessName macro won’t work. When you try to run it, Excel may complain and pop up an error message: Compile Error: Variable Not Defined. Don’t worry; there’s an easy fix for that.
If you receive that error, look at the top of your module, and you’ll see this text: Option Explicit. Just delete that line, and the macro should work. That line, when present at the top of a module, means that you must “declare” all your variables. (See Chapter 7 for more about variables.) If that line was added, it means that your VBE is set up to add the line automatically. For now, don’t worry about it. Delete the line, and forget about the rude interruption.
If you followed the previous steps, you just wrote a VBA Sub procedure, also known as a macro. When you press F5, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. (Don’t let this newfound power go to your head.) You can execute this macro any number of times — although it tends to lose its appeal after a few dozen times.
For the record, the following concepts were used to create this simple macro, all of which are covered later in this book:
Defining a Sub procedure (the first line)
Assigning values to variables (Msg and Ans)
Concatenating (joining) a string (using the & operator)
Using a built-in VBA function (MsgBox)
Using built-in VBA constants (vbYesNo, vbNo, and vbYes)
Using an If-Then construct (twice)
Ending a Sub procedure (the last line)
Not bad for a beginner, eh?
Using the macro recorder
Another way you can get code into a VBA module is by recording your actions, using the Excel macro recorder. If you worked through the hands-on exercise in Chapter 2, you already have some experience with this technique.