• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 32. Creating Interactive Excel A... > Why Write Macros Rather Than Record ...

Why Write Macros Rather Than Record Them?

The Macro Recorder enables you to create your own command macros in Excel by recording menu selections, keystrokes, and mouse movements. Recorded macros have their limitations, however. Following are a few things that can’t be recorded:

  • Interaction— You want the user to have input into how the macro will play back. Suppose that you create a workbook that charts sales data for 20 product segments. Rather than force users to print a chart for each segment (in this case 20 charts), you would have them select which segments they want to print.

  • Decisions— You want the macro to make decisions on how to play back based on what it encounters when playing back. Suppose that you have a worksheet with one thousand rows of product sales data at the product level. Your sales data provider gave you the data in Excel workbook form. The file contains some products with sales amounts equal to zero interspersed among the data rows. You want to eliminate these products.

  • Custom functions— You continually perform the same complex or lengthy calculation in your spreadsheets. Suppose that you have several worksheets that require you to display the number of year-to-date days. Rather than entering that complex formula every time you need the current number of year-to-date days, you want a simpler way to enter the calculation.

  • Efficiency— The Macro Recorder can be inefficient. For example, in most cases you don’t have to select a range of cells to do something to it, yet because the Macro Recorder can only record keystrokes, it knows of no other way to refer to a range. For this kind of operation, writing the macro yourself with VBA is much more efficient.


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint