Скачать книгу

buttons

      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.

Snapshot of Use macros to build buttons that help users navigate the reports.

      FIGURE 1-10: Use macros to build buttons that help users navigate your reports.

It’s useful to know that Excel has a built-in hyperlink feature, allowing you to convert the contents of a cell into a hyperlink that links to another location. That location can be a separate Excel workbook, a website, or even another tab in the current workbook. Although using a hyperlink may be easier than setting up a macro, you can’t apply a hyperlink to form controls (such as buttons). Instead of a button, you’d use text to let users know where they’ll go when they click the link.

      Dynamically rearranging PivotTable data

Snapshot shows this report allows users to choose their perspective.

      FIGURE 1-11: This report allows users to choose their perspective.

Snapshot of the macros behind these buttons rearrange the data fields in a PivotTable.

      FIGURE 1-12: The macros behind these buttons rearrange the data fields in a PivotTable.

      1 Create your PivotTable and PivotChart.

      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 run your new macros in turn to see your pivot field dynamically move back and forth.

      Offering one-touch reporting options

      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?

Snapshot of 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.

      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.

Snapshot of giving the audience a choice in how they view data.

      FIGURE 1-14: You can give your audience a choice in how they view data.

      Getting Cozy with the Visual Basic Editor

      IN THIS CHAPTER

      

Understanding the Visual Basic Editor components

      

Working with the Project Explorer

      

Using a Code pane

      

Customizing the Visual Basic Editor

      The Visual Basic Editor (VBE) is the environment where all Excel macros are written or recorded. The VBE is included with Excel 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 take your first look behind the curtain to explore the VBE.

      The VBE is actually a separate application that runs when you open Excel. To see this hidden VBE environment, you need to activate it. With Excel open, one of these two ways does the trick:

       Press Alt+F11.

       Click the Visual Basic button on the Ribbon’s Developer tab.

      To return to Excel, press Alt+F11 or click the Close button in the VBE.

Скачать книгу