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


7 Select cell A16 and type Total in the cell.

8 Select the first empty cell in Column D (D16), type = COUNTA(D2:D15), and then press Enter.

9 Click Stop Recording on the Developer tab to stop recording the macro.

At this point, you have recorded two macros. Take a moment to examine the code for your newly created macro.

Select Macros from the Developer tab to open the Macro dialog box. Here, choose the AddTotalRelative macro and click Edit.

Sub AddTotalRelative() ActiveCell.Offset(15, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Total" ActiveCell.Offset(0, 3).Range("A1").Select ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)" End Sub

Notice that there are no references to any specific cell ranges at all (other than the starting point "A1"). Let's take a moment to look at what the relevant parts of this VBA code really mean.

Notice that in line 2, Excel uses the Offset property of the active cell. This property tells the cursor to move a certain number of cells up or down and a certain number of cells left or right.

The Offset property code tells Excel to move 15 rows down and 0 columns across from the active cell (in this case, A1). There's no need for Excel to select a cell explicitly, as it did when recording an absolute reference macro.

To see this macro in action, delete the total row and do the following:

1 Select cell A1.

2 Select Macros from the Developer tab.

3 Find and select the AddTotalRelative macro.

4 Click the Run button.

5 Now select cell F1.

6 Select Macros from the Developer tab.

7 Find and select the AddTotalRelative macro.

8 Click the Run button.

Notice that this macro, unlike your previous macro, works on both sets of data. Because the macro applies the totals relative to the currently active cell, the totals are applied correctly.

For this macro to work, you simply need to ensure that

 You've selected the correct starting cell before running the macro.

 The block of data has the same number of rows and columns as the data on which you recorded the macro.

Ideally, this simple example has given you a firm grasp of macro recording of both absolute and relative references.

Other macro recording concepts

At this point, you should feel comfortable recording your own Excel macros. Next are some of the other important concepts you'll need to keep in mind when writing or recording macros.

By default, Excel workbooks are given the standard file extension .xlsx. Be aware that files with the .xlsx extension cannot contain macros. If your workbook contains macros and then you save that workbook as an .xlsx file, all VBA code is removed automatically. Luckily, Excel will warn you that your macro content will be removed when saving a workbook with macros as an .xlsx file.

If you want to retain the macros, you must save your file as an Excel Macro-Enabled Workbook. This gives your file an .xlsm extension. The idea is that all workbooks with an .xlsx file extension are automatically known to be safe, whereas you can recognize .xlsm files as a potential threat.

Alternatively, you can save your workbook as an Excel 97-2003 Workbook (with the .xls extension). The .xls file type can contain macros, but it doesn't support some of the modern features of Excel such as conditional formatting icons and pivot table slicers. You would typically use this file type only if there is a specific reason, such as that you need to have your workbook interact with an add-in that works only with .xls files.

Macro security in Excel

With the release of Office 2010, Microsoft introduced significant changes to its Office security model. One of the most significant changes is the concept of trusted documents. Without getting into the technical minutiae, a trusted document is essentially a workbook that you have deemed safe by enabling macros.

If you open a workbook that contains macros, you will see a yellow bar message under the Ribbon stating that macros (active content) have, in effect, been disabled.

If you click Enable, it automatically becomes a trusted document. This means that you no longer are prompted to enable the content as long as you open that file on your computer. The basic idea is that if you told Excel that you trust a particular workbook by enabling macros, it is highly likely that you will enable macros each time you open it. Thus, Excel remembers that you've enabled macros before and inhibits any further messages about macros for that workbook.

This is great news for you and your clients. After enabling your macros just one time, they won't be annoyed by the constant messages about macros, and you won't have to worry that your macro-enabled dashboard will fall flat because macros have been disabled.

Trusted locations

If the thought of any macro message coming up (even one time) unnerves you, you can set up a trusted location for your files. A trusted location is a directory that is deemed a safe zone where only trusted workbooks are placed. A trusted location allows you and your clients to run a macro-enabled workbook with no security restrictions as long as the workbook is in that location.

1 Select the Macro Security button on the Developer tab. This activates the Trust Center dialog box.

2 Click the Trusted Locations button. This opens the Trusted Locations menu (see Figure 2.6), which shows you all the directories that are considered trusted.FIGURE 2.6 The Trusted Locations tab allows you to add directories that are considered trusted.

3 Click the Add New Location button.

4 Click Browse to find and specify the directory that will be considered a trusted location.

After you specify a trusted location, any Excel file that is opened from this location will have macros automatically enabled.

Storing macros in your Personal Macro Workbook

Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all of your work. You can store these general-purpose macros in the Personal Macro Workbook so that they're always available to you. The Personal Macro Workbook is loaded whenever you start Excel. This file, named Personal.xlsb, doesn't exist until you record a macro using Personal Macro Workbook as the destination.

If you store macros in the Personal Macro Workbook, you don't have to remember to open the Personal Macro Workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the Personal Macro Workbook.

NOTE

The Personal Macro Workbook normally is in a hidden window to keep it out of the way.

Assigning a macro to a button and other form controls

When you create macros, you may want to have a clear and easy way to run each macro. A basic button can provide a simple but effective user interface.

As luck would have it, Excel offers a set of form controls designed specifically for creating user interfaces directly on spreadsheets. There are several different types of form controls, from buttons (the most commonly used control) to scrollbars.

The idea behind using a form control is simple. You place a form control on a spreadsheet and then assign a macro to itthat is, a macro you've already recorded. When a macro is assigned to the control, that macro is executed, or played, when the control is clicked.

Take a moment to create a button for the AddTotalRelative macro you created earlier. Here's how:

1 Click the Insert button on the Developer tab (see Figure 2.7).FIGURE 2.7 You can find the form controls on the Developer tab.

2 Select the Button control from the drop-down list that appears.

3 Click the location where you want to place your button.When you drop the button control onto your spreadsheet, the Assign Macro dialog box, as shown in Figure 2.8, activates and asks you to assign a macro to this button.FIGURE 2.8 Assign a macro to the newly added button.

4 Select the macro that you want to assign to the button and then click OK.

At this point, you have a button that runs your macro when you click it. Keep in mind that all the controls in the Form Controls group (shown in Figure 2.7) work in the same way as the command button in that you right-click and choose Assign Macro to specify a macro to trigger with the control.

NOTE

Notice the form controls and ActiveX controls in Figure 2.7. Although they look similar, they're quite different. Form controls are designed specifically for use on a spreadsheet, and ActiveX controls are typically used on Excel user forms. As a general rule, you should always use form controls when working on a spreadsheet. Why? Form controls need less overhead, so they perform better, and configuring form controls is far easier than configuring their ActiveX counterparts.

Placing a macro on the Quick Access toolbar

You can also assign a macro to a button in Excel's Quick Access toolbar. The Quick Access toolbar sits either above or below the Ribbon. You can add a custom button that will run your macro by following these steps:

1 Right-click your Quick Access toolbar and select Customize Quick Access Toolbar. This will open the dialog box illustrated in Figure 2.9.FIGURE 2.9 Adding a macro to the Quick Access toolbar

2 Select Macros from the Choose Commands From drop-down list on the left.

3 Select the macro that you want to add and click the Add button.

4 Click the Modify button to choose an icon for your macro and provide a friendly display name.

5 Click the OK button.

Working with the Visual Basic Editor

The Visual Basic Editor is a separate application that runs when you open Excel. To see this hidden VBE environment, you'll need to activate it. The quickest way to activate the VBE is to press Alt+F11 when Excel is active. To return to Excel, press Alt+F11 again.

You can also activate the VBE by using the Visual Basic command on Excel's Developer tab.

Understanding VBE components


Menu bar

The VBE menu bar works just like every other menu bar you've encountered. It contains commands that you use to do things with the various components in the VBE. You will also find that many of the menu commands have shortcut keys associated with them.

The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.

Toolbar

The Standard toolbar, which is directly under the menu bar by default, is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you're so inclined, use the View Toolbars command to work with VBE toolbars. Most people just leave them as they are.

Project window

The Project window displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them. You'll explore this window in more detail in the Working with the Project Window section later in this chapter.

If the Project window is not visible, press Ctrl+R or use the View Project Explorer command. To hide the Project window, click the Close button in its title bar. Alternatively, right-click anywhere in the Project window and select Hide from the shortcut menu.

Code window

A Code window contains VBA code. Every object in a project has an associated Code window. To view an object's Code window, double-click the object in the Project window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project window. Unless you've added some VBA code, the Code window will be empty.

Назад Дальше