Part VI: Programming Excel with VBA
808
FIGURE 39.10This TimeStamp macro works correctly.
Testing the macro
When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA mod-
ule or in any other workbook.) Select a cell and press Ctrl+Shift+T. The macro immediately enters
the current date and time into the cell. You may need to widen the column to see the date and
time. To widen the column automatically, just add this statement to the end of the macro (before
the End Sub statement):ActiveCell.EntireColumn.AutoFitMore about recording VBA macros
If you followed along with the preceding examples, you should have a better feel for how to record
macros — and also a good feel for problems that might occur with even simple macros. If you find
the VBA code confusing, don’t worry. You don’t really have to be concerned with it as long as the
macro that you record works correctly. If the macro doesn’t work, rerecording the macro rather
than editing the code often is easier.A good way to learn about what gets recorded is to set up your screen so that you can see the code
that is being generated in the VB Editor windows. To do so, make sure that the Excel window isn’t
maximized; then arrange the Excel window and the VB Editor window so both are visible. While
you’re recording your actions, make sure that the VB Editor window is displaying the module in
which the code is being recorded. (You may have to double-click the module name in the Project
window.)Tip
If you do a lot of work with VBA, consider adding a second monitor to your system. Then you can display Excel
on one monitor and the VB Editor on the other. n
Absolute versus relative recording
If you’re going to work with recorded macros, you need to understand the concept of relative ver-
sus absolute recording modes. In a previous example in this chapter, I showed how even a simple
macro could fail because of an incorrect recording mode.