Excel VBA Programming For Dummies. Dick Kusleika

Чтение книги онлайн.

СКАЧАТЬ the formula cell, and press Ctrl+C to copy that cell to the Clipboard.

      11 Choose Home ⇒ Clipboard ⇒ Paste ⇒ Values (V).This command converts the formula to its value.

      12 Select both the cell with your name and the one with the date and time.

      13 Use the controls in the Home ⇒ Font group to change the formatting to Bold and make the font size 16 point.

      14 Choose Developer ⇒ Code ⇒ Stop Recording.The macro recorder is turned off.

      Congratulations! You just recorded your first Excel VBA macro. You might want to phone your mother and tell her the good news.

Running the Macro

      Once you’ve finished writing your macro, it’s time to see if it actually works. The following are some of the ways to run a macro:

       Press the shortcut key. If you created a shortcut key sequence for your macro, you can run it by press those keys. For example, in the previous section you assigned Ctrl+Shift+N in the Macro Options dialog. If you hold down Ctrl and Shift, and press N, your macro will insert your name and the date and format them.

       Choose Developer ⇒ Code ⇒ Macros. This shows the Macro dialog where you can select your macro and click Run.

       Run it from the VBE. Open the VBE, place the cursor anywhere within your macro, and choose Run ⇒ Run Sub/UserForm from the menu (or press F5).

       Assign the macro to the Quick Access toolbar. Right-click on the Quick Access toolbar and choose Customize Quick Access Toolbar to show the Excel Options dialog. Choose Macros from the Choose commands from the drop-down dialog box and add your macro. Click OK to close the dialog. Now you can click the tool on the Quick Access toolbar to run the macro.

      

For your macro to run, the workbook that contains it must be open. If it’s closed, the macro won’t show up in the dialog box or in the VBE. The workbook doesn’t have to be active, however. You can activate a workbook and run code that’s contained in a different workbook. You can also store your macro in the Personal Macro Workbook (see Chapter 6) or in an add-in (see Chapter 21). Those are both types of files that are open in the background.

The NameAndTime macro you recorded in the previous section does not warn you if there is already data in the cells you are about to write data to. It’s not what you would call production-quality code.

      Whatever way you choose to run your macro, run it and make sure it does what you expect it to. If it doesn’t, read on to see how to view and edit it.

Viewing a Macro in the Visual Basic Editor

      After you record and run a macro, you may be curious to see what the macro looks like. You might even wonder where it’s stored.

      Excel stores macros in the workbook you indicate when creating the macro. So for the example used in this chapter, the macro is stored in This Workbook. To view macros, however, you need to activate the Visual Basic Editor (VBE, for short).

      Follow these steps to see the macro used as an example throughout this chapter:

      1 Choose Developer ⇒ Code ⇒ Visual Basic (or press Alt+F11).The Visual Basic Editor program window appears, as shown in Figure 2-4. This window is highly customizable, so your VBE window may look a bit different. The VBE window contains several other windows, which can be intimidating. Don’t fret; you’ll get used to it.

      2 In the VBE window, locate the Project Explorer.The Project Explorer contains a list of all workbooks and add-ins that are currently open. Each project is arranged as a tree and can be expanded (to show more information) or contracted (to show less information). The VBE uses quite a few different windows, any of which can be open or closed. If a window isn’t immediately visible in the VBE, you can choose an option from the View menu to display the window. For instance, if the Project Explorer is not visible, you can choose View ⇒ Project Explorer (or press Ctrl+R) to display it. You can display any other VBE window in a similar manner. Chapter 3 covers the components of the VBE.

      3 If necessary, expand the project that corresponds to the workbook in which you recorded the macro by clicking the plus sign next to it.If you haven’t saved the workbook, the project is probably called VBAProject (Book1).FIGURE 2-4: The VBE displays the VBA code in Module1 of Book1.

      4 Click the plus sign (+) to the left of the folder named Modules.The tree expands to show Module1, which is the only module in the project.

      5 Double-click Module1.The VBA code in that module is displayed in a Code pane (refer to Figure 2-4). Your screen may not look exactly the same as Figure 2-4. The code that’s recorded depends on the specific actions you made while recording the macro.

      At this point, the macro probably might look a bit mysterious. Don’t worry. Travel a few chapters down the road, and all will be crystal clear.

      The NameAndTime macro consists of several statements. Excel executes the statements one by one, from top to bottom. A statement that’s preceded by an apostrophe (′) is a comment. Comments are included only for your information and are ignored by Excel. In other words, Excel skips right over comments.

      The first VBA statement (which begins with the word Sub) identifies the macro as a Sub procedure and gives its name; you provided this name before you started recording the macro. If you read through the code, you might be able to make sense of some of it. You see your name, the formula you entered, and lots of additional code that changes the font. The Sub procedure ends with the End Sub statement.

      HEY, I DIDN’T RECORD THAT!

      Reviewing the actions you recorded with the macro recorder has the same effect as listening to a voice memo you recorded on your phone. When you play back the voice memo and listen to your own voice, you probably say, “I don’t sound like that.” And when you look at your recorded macro, you might see some actions that you didn’t think you recorded.

For example, if you change only one aspect of the font, such as the font size, the recorded code will show all sorts of font-changing statements (Strikethrough, Superscript, Shadow, and so on). Don’t worry; it happens all the time. Excel often records lots of seemingly useless code. In later chapters, you find out how to remove the extra stuff from a recorded macro.

Modifying the Macro

      As you might expect, you not only can view your macro in the VBE, you can change it. You can edit a macro directly in the Code pane like the one you opened in the previous section by double-clicking Module1.

      You modify a macro by typing new statements or modifying existing statements between the Sub and End Sub statements. For example, in the NameAndTime macro you recorded earlier in this chapter, you could change it to make the font size 18 instead of 16. Find the line .Size = 16 and change it to:

СКАЧАТЬ