Page 30 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 1 INTRODUCING VISUAL BASIC FOR APPLICATIONS 7

Sub ScientificForrnat()
' ScientificForrnat Macro
' Macro recorded 6/22/2004 by Boston College
,
' Keyboard Shortcut: Ctrl+e
Selection.Num berFormat = "O.OOE+OO"
End Sub
Figure 1-8. Macro for scientific number-formatting, recorded in VBA.
This macro consists of a single line of VBA code. You'll learn about Visual
Basic code in the chapters that follow.
To run the macro, enter a number in a cell, select the cell, then choose
Macro from the Tools menu, choose Macros ... from the submenu, select the
ScientificForrnat macro from the Macro Name list box, and press Run. Or you can
simply press the shortcut key combination that you designated when you
recorded the macro (CONTROL+e in the example above). The number should be
displayed in the cell in scientific format.
The Personal Macro Workbook

The Record Macro dialog box allows you to choose where the recorded
macro will be stored. There are three possibilities in the "Store Macro In" list
box: This Workbook, New Workbook and Personal Macro Workbook. The
Personal Macro Workbook (PERS0NAL.XL.S in Excel for Windows, or Personal
Macro Workbook in Excel for the Macintosh) is a workbook that is automatically
opened when you start Excel. Since only macros in open workbooks are
available for use, the Personal Macro Workbook is the ideal location for macros
that you want to have available all the time.
Normally the Personal Macro Workbook is hidden (choose Unhide.. . from
the Window menu to view it). If you don't yet have a Personal Macro
Workbook, you can create one by recording a macro as described earlier,
choosing Personal Macro Workbook from the "Store Macro In" list box.
As you begin to create more advanced Sub procedures, you'll find that the
Recorder is a useful tool to create fragments of macro code for incorporation into
your procedure. Instead of poring through a VBA reference, or searching
through the On-Line VBA Help, looking for the correct command syntax, simply
turn on the Recorder, perform the action, and look at the code produced. You
may find that the Recorder doesn't always produce exactly what you want, or
perhaps the most elegant code, but it is almost always useful.
Note that, since the Recorder only records actions, and Function procedures
can't perform actions, the Recorder won't be useful for creating Function
procedures.