Altering a Recorded Macro to Add Functionality


When you record a macro, Excel creates a module that stores the recorded steps of your actions. These recorded steps are actually lines of VBA code that make up your macro. You can add some interesting functionality to your pivot table reports by tweaking your macro's VBA code in order to achieve various effects.

To get a better understanding of how this works, start by creating a new macro that will extract the top five records by customer. Go up to the application menu and select Tools, Macro, Record New Macro. The dialog box shown in Figure 11.7 will be activated.

Figure 11.7. Name your new macro "GetTopNthCusts" and specify that you want to store the macro in This Workbook. Click OK to start recording.


After you have started recording, right-click on the Customer field and select Field Settings. Click the Advanced button and enter the settings you see in Figure 11.8. These settings will give you the top five customers by revenue.

Figure 11.8. In the Advance Options for the Customer field, enter the settings you see here to get the top five customers by revenue. Then click OK.


After successfully recording the steps to extract out the top five customers by revenue, go up to the application menu and select Tools, Macro, Stop Recording.

You now have a macro that, when played, will filter your pivot table to the top five customers by revenue. The plan is to tweak this macro to respond to a scrollbar. That is, you will force the macro to base the number used to filter the pivot table on the number represented by a scrollbar in your user interface. In other words, a user will be able to get the top five, top eight, or top 32, simply by moving a scrollbar up and down.

To get a scrollbar onto your spreadsheet, activate the Forms toolbar, select a scrollbar, and place it onto your spreadsheet. Unlike a command button, the scrollbar control requires some initial setup before you can use it. Right-click on the scrollbar and select Format Control. You will see the Format Object dialog box shown in Figure 11.9.

Figure 11.9. After you have placed a scrollbar on your spreadsheet, right-click on the scrollbar and select Format Control.


While you're in the Format Control dialog box, make the following setting changes: Set Minimum Level to 1 so the scrollbar cannot go below 1, set Maximum Level to 200 so the scrollbar cannot go above 200, and set the Cell Link to $K$2 so that the number represented by the scrollbar will output to cell K2. Click the OK button to apply to your changes.

Next, right-click on your newly formatted scrollbar and select Assign Macro to activate the dialog box shown in Figure 11.10. Assign the GetTopNthCusts macro you just recorded to your scrollbar. This will ensure that this macro will play each time the scrollbar is clicked.

Figure 11.10. Right-click on the scrollbar and select Assign Macro. Select the GetTopNthCusts macro from the list and then click OK.


At this point, test your scrollbar by clicking on it. When you click on your scrollbar, two things should happen: The GetTopNthCusts macro should play, and the number in cell K2 should change to reflect your scrollbar's position. The number in K2 is important because that is the number you are going to use to tie your macro to your scrollbar.

The only thing left to do now is to tweak your macro to respond to the number in cell K2, effectively tying it to your scrollbar. In order to do this, you will have to get to the VBA code that makes up the macro. There are several ways to get you there, but for the purposes of this example, select Tools, Macro, Macros. This will open up the Macro dialog box shown in Figure 11.11, exposing several options. From here, you can run, delete, step into, or edit a selected macro. To get to the VBA code that makes up your macro, select the macro and then click Edit.

Figure 11.11. To get to the VBA code that makes up the GetTopNthCusts macro, select the macro and then click Edit.


As you can see in Figure 11.12, the Visual Basic Editor will open up with a detailed view of all the VBA code that makes up this macro. Your goal here is to replace the hard-coded number 5, as specified when you originally recorded your macro, with the value in cell K2, which is tied to your scrollbar.

Figure 11.12. You will notice that the number 5 is hard-coded as part of your macro. This is because you originally recorded your macro to filter the top five customers by revenue.


You will delete the number 5 and replace it with the following:

 ActiveSheet.Range("K2").Value 

Your macro's code should look similar to the code shown in Figure 11.13.

Figure 11.13. Simply delete the hard-coded number 5 and replace it with this reference to cell K2: ActiveSheet. Range("K2"). Value.


Close the Visual Basic Editor to get back to your pivot table report. Test your scrollbar by setting it to 11. Your macro should play and filter out the top 11 customers by revenue, as shown here in Figure 11.14.

Figure 11.14. After a little formatting, you have a clear and easy way for your clients to get the top customers by revenue.


CASE STUDY: Synchronize Two Pivot Tables with One Combo Box

The pivot table report in Figure 11.15 contains two pivot tables. Each pivot table has a page field for allowing you to select a market. The problem is that every time you select a market from the page field in one pivot table, you have to select the same market from the page field in the other pivot table to ensure you are analyzing the correct Units Sold versus Revenue.

Figure 11.15. With two pivot tables containing page fields that filter out a market, you will have to ensure that you synchronize both pivot tables when analyzing data for a particular market.


Not only is it a bit of a hassle to have to synchronize both pivot tables every time you want to analyze a new market's data, but there is a chance you, or your clients, may forget to do so.

One way to synchronize these pivot tables is to use a combo box. The idea is to record a macro that selects a market from the Market field of both tables. Then create a combo box and fill it with the market names that exist in your two pivot tables. Finally, alter your macro to filter both pivot tables, using the value from your combo box.

Here are the steps in performing this task:

1.

Create a new macro and call it SynchMarkets. When recording starts, select the California market from the Market field in both pivot tables and then stop recording.

2.

Activate the Forms toolbar and place a combo box onto your spreadsheet.

3.

Create a hard-coded list of all the markets that exist in your pivot table. Note that the first entry in your list is (All). You must include this entry if you want to be able to select all markets with your combo box.

At this point, your pivot table report should look similar to the one shown in Figure 11.16.

Figure 11.16. You now have all the tools you needa macro that changes the Market field of both pivot tables, a combo box on your spreadsheet, and a list of all the markets that exist in your pivot table.


4.

Right-click on your combo box and select Format Control to perform the initial setup.

First, specify an input range for the list you are using to fill your combo box, as shown in Figure 11.17. In this case, this means the market list you created in step 3. Next, specify a cell link. That is, the cell that will show the index number of the item you select (cell I1 is the cell link in this example). Finally, click OK.

Figure 11.17. The settings for your combo box should reference your market list as the input range and specify a cell link close to your market list. In this case, the cell link is cell I1.


As you can see in Figure 11.18, you should now be able to select a market from your combo box and see the associated index number in cell I1.

Figure 11.18. Your combo box, now filled with market names, will output an index number in cell I1 when a market is selected.


NOTE

The only output of a combo box form control is an index number. In Figure 11.18, the selection of Shenandoah Valley from the combo box resulted in the number 17 in cell I1. This means that Shenandoah Valley was the 17th item in the combo box. In order to make use of this index number, you will have to pass it through the INDEX function. The INDEX function converts an index number to a value that can be recognized.

5.

Enter an INDEX function that converts the index number in cell I1 to a value.

An INDEX function requires two arguments in order to work properly. The first argument is the range of the list you are working with. In most cases, you will use the same range that is feeding your combo box. The second argument is the index number. If the index number is in a cell (for example, cell I1 in Figure 11.19), you can simply reference the cell.

Figure 11.19. The INDEX function in cell J1 will convert the index number in cell I1 to a value. You will eventually use the value in cell J1 to alter your macro.


6.

Edit the SynchMarkets macro using the value in cell J1 instead of a hard-coded value.

7.

Go up to the application menu and select Tools, Macro, Macros. Select the SynchMarkets macro shown in Figure 11.20 and then click Edit.

Figure 11.20. To get to the VBA code that makes up your macro, select the SynchMarkets macro and then click Edit.


When you recorded your macro originally, you selected the California market from the Market field in both pivot tables. As you can see in Figure 11.21, the subsequent result is that California is hard-coded in your macro's VBA code.

Figure 11.21. The California market is hard-coded in your macro's VBA code.


8.

Replace "California" with ActiveSheet.Range("J1").Value, which references the value in cell J1. At this point, your macro code should look similar to that shown in Figure 11.22. After you have edited the macro, close the Visual Basic Editor to get back to the spreadsheet.

Figure 11.22. Replace "California" with ActiveSheet. Range("J1"). Value and then close the Visual Basic Editor.


9.

All that is left to do is to ensure that the macro will play when you select a market from the combo box. Right-click on the combo box and select Assign Macro. Select the SynchMarkets macro and then click OK.

10.

Hide the rows and columns that hold the page fields in your pivot tables, hide the market list you created, and, finally, hide the index formulas.

Figure 11.23 shows your final result. You now have a user-friendly interface that allows your clients to select a market in both pivot tables simply by selecting the market in one combo box.

Figure 11.23. Your pivot table report is ready to use!




    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net