target="_blank" rel="nofollow" href="#x6_c01_para_0087"> FIGURE 1-10: Use macros to build buttons that help users navigate your reports.
Creating a macro to navigate to a sheet is quite simple.
1. Start at the sheet that will become your switchboard or starting point.
2. Start recording a macro.
3. While recording, click the destination sheet (the sheet this macro will navigate to).
4. After you click in the destination sheet, stop recording the macro.
5. Assign the macro to a button.
Macros be used with any Excel object normally used in reporting. For instance, you can use a macro to give your audience a way to dynamically change a pivot table. In the example illustrated in Figure 1-11, macros allow a user to change the perspective of the chart simply by selecting any one of the buttons shown.
FIGURE 1-11: This report allows users to choose their perspective.
Figure 1-12 reveals that the chart is actually a pivot chart tied to a PivotTable. The recorded macros assigned to each button are doing nothing more than rearranging the PivotTable to slice the data using various pivot fields.
FIGURE 1-12: The macros behind these buttons rearrange the data fields in a PivotTable.
Here are the high-level steps needed to create this type of setup:
1. Create your PivotTable and a pivot chart.
2. Start recording a macro.
3. While recording, move a pivot field from one area of the PivotTable to the other. When you’re done, stop recording the macro.
4. Record another macro to move the data field back to its original position.
5. After both macros are set up, assign each one to a separate button.
You can fire your new macros in turn to see your pivot field dynamically move back and forth.
The last two examples demonstrate that you can record any action that you find of value. That is, if you think users would appreciate a certain feature being automated for them, why not record a macro to do so?
In Figure 1-13, notice that you can filter the PivotTable for the top or bottom 20 customers. Because the steps to filter a PivotTable for the top and bottom 20 have been recorded, users can get the benefit of this functionality without knowing how to do it themselves. Also, recording a specific action allows you to manage risk a bit. That is to say, you’ll know that your users will interact with your reports in a method that has been developed and tested by you.
FIGURE 1-13: Offering prerecorded views not only saves time and effort, but it also allows users that don’t know how to use advanced features to benefit from them.
This not only saves them time and effort, but it also allows users that don’t know how to take these actions to benefit from them.
Figure 1-14 demonstrates how you can give your audience a quick and easy way to see the same data on different charts. Don’t laugh too quickly at the uselessness of this example. It’s not uncommon to be asked to see the same data different ways. Instead of taking up real estate, just record a macro that changes the Chart Type of the chart. Your clients can switch views to their heart’s content.
FIGURE 1-14: You can give your audience a choice in how they view data.
Chapter 2
Getting Cozy with the Visual Basic Editor
IN THIS CHAPTER
❯❯ Understanding Visual Basic Editor components
❯❯ Working with the Project window
❯❯ Using the Code window
❯❯ Customizing the Visual Basic Editor
The Visual Basic Editor (VBE) is the environment where all Excel macros are stored and processed. Each workbook you create comes with this interconnected VBE environment free of charge. Even if you never record one macro, the VBE is in the background waiting to be used. When you create a macro, the VBE quietly comes to life ready to process the various procedures and routines you give it.
In this chapter, you'll take your first look behind the curtain to explore the Visual Basic Editor.
Working in the Visual Basic Editor
The Visual Basic Editor is actually a separate application that runs when you open Excel. In order 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 found on Excel’s Developer tab.
Figure 2-1 shows the VBE program with some of the key parts identified. Chances are your VBE program window won’t look exactly like what you see in Figure 2-1. The VBE contains several windows and is highly customizable. You can hide windows, rearrange windows, dock windows, and so on.
Конец ознакомительного фрагмента.
Текст предоставлен ООО «ЛитРес».
Прочитайте эту книгу целиком, купив полную легальную версию на ЛитРес.
Безопасно
