VBA can speed worksheet data entry in several ways. Try this example to learn one basic way. We'll record a macro that you can use to rapidly enter a typical column name like "first name."
- Create a new workbook in Excel (press control-N).
- Begin macro recording: press the small, rectangular icon tucked in the southwest corner of the Excel workspace.
- Enter "myMac" for the name of the macro and click OK to begin recording.
- Select any clear worksheet cell and type "first name," followed by the Enter key.
- Stop recording by clicking the squarish icon that's replaced the record-macro icon.
- Clear the worksheet (control-A, delete), then replay the macro: press alt-F8, followed by double-clicking the "myMac" entry in the macro list.
- Notice the result: Excel re-inserts the "first name" text you typed.
Improving the macro
The problem with this macro is that it will insert the same text in only one specific place every time you run it. If you need to enter "first name" into several different worksheet cells, this macro, as it stands, is no good.
Revise the macro by first entering its habitat, the Visual Basic IDE (integrated development environment): press alt-f8 to call up the macro list containing your myMac macro. Select that macro and press the Edit button. You'll enter the IDE at the definition of myMac.
Change myMac to read as follows:
ActiveCell = "first name"
Return to the Excel worksheet and replay myMac using the instructions given earlier. Click several different worksheet cells, running myMac for each new selection. Notice that the macro now enters "first name" in the currently selected cell instead of the same cell each time.
Tip: Use keyboard shortcuts
Replaying macros by using alt-F8 all the time is not nearly as speedy as a keyboard shortcut. Make a shortcut key for myMac by going to alt-F8 (one last time!), selecting myMac, and clicking the Options button. Enter a key which, when pressed with Control, will fire your macro.
Learn more about recording Excel macros here.