Can you record a macro in VBA?

Excel VBA - Record and Run Macros using the Excel Macro Recorder

Applicable to Excel 2007

------------------------------------------------------------------------

Contents:

Start Recording a Macro - the Record Macro dialog box

Naming a Macro

Assign a Keyboard Shortcut

Store a Macro

Add a Description

Recording a Macro

View, Edit & Run a Recorded Macro

Use Relative References

Limitations of Recording a Macro

Storing a Macro in Personal Macro Workbook

------------------------------------------------------------------------

An easy way to learn VBA is to record your own macros with the Excel Macro Recorder tool, and then read, run and edit the code of the macro which you have recorded in Visual Basic Editor. A macro is the instructions given in the form of a set of codes to make the computer perform an action. In the beginning when not very conversant with vba, you can record a macro while performing a specific task, and then going to the VBE you can view the required set of codes.

Start Recording a Macro - the Record Macro dialog box

To start the macro recorder for recording a macro in Excel 2007, click View tab on the ribbon, click Macros in the Macros group, and then click Record Macro (refer Image 1a) which will open the dialog box of Record Macro (refer Image 2a). Another way to open the Record Macro dialog box is to click the Developer tab on the ribbon, and click Record Macro in the Code group. You can also open the Record Macro dialog box by clicking on the icon appearing on the Status Bar (refer Image 1b) which appears at the bottom of the Microsoft Office Excel 2007 window. In Excel 2003, in the Tools drop-down menu click  Macro and then click Record New Macro which will open the dialog box of Record Macro. Using the Excel built-in macro recorder tool enables you to record a macro, assign a shortcut, add a description, store / view / edit & run the macro, as elaborated below.

Naming a Macro

In the Record Macro dialog box, you can enter a name for the macro you wish to record (refer Image 2a, macro name entered is CellColor), else Excel will assign a default name like Macro1, Macro2, and so on. A macro name must start with a letter as the first character while subsequent characters can be letters, numbers or an underscore (which is a good way of separating words). Spaces and most special characters are not allowed. It can have a maximum of 255 characters. A macro name cannot use keywords / reserved words such as If, And, Or, Loop, Do, Len, Close, Date, ElseIf, Else, Select, ... that VBA uses as part of its programming language. It will help to assign a name which is reflective of the action you wish to perform with the macro.

Assign a Keyboard Shortcut

In the Record Macro dialog box, you can assign a Keyboard Shortcut to your macro, but do not use any predefined key combination (like Ctrl+b) else it will be overwritten (ie. the assigned shortcut key for macro will prevail over the predefined key combination) and your macro could get triggered inadvertently on using the predefined key combination. Refer Image 2a where the keyboard shortcut assigned is Ctrl+w. Note that the excel predefined key combination of Ctrl+w which closes the selected workbook window is overwritten by using the same as the keyboard shortcut, so that pressing the keys Ctrl & w simultaneously will run the macro and not close the workbook.

Store a Macro

In the Record Macro dialog box, select where you want to store the Macro viz. This Workbook (macro is stored in your current workbook), New Workbook (this creates a new workbook, default name Book1, Book2, and so on, where the macro gets stored), or Personal Macro Workbook. Refer Image 2a of the Record Macro dialog box which stores the macro in "This Workbook". At the end of this chapter, we have explained how to Store a Macro in Personal Macro Workbook.

Add a Description

You can also add a description for your macro, which will appear as a comment [a comment appears as a line(s) preceded with an apostrophe, and is ignored by vba] in your code which gets recorded. Click Ok in the dialog box and start performing tasks in Excel which you want to record as macro code. Refer Image 2a in which the description added is "Macro recorded on Oct 10, 2012 by Amit Tandon" and this appears as a comment in the code (refer Image 3).

Recording a Macro

All your actions or commands performed (mouse clicks and keystrokes) while your are in record mode are remembered by Excel, and saved as vba code as a subroutine into a Module in the Visual Basic Editor (refer Image 3). When the macro is run, it plays back the commands in the same sequence and order as they were performed while recording. To stop recording the macro, click Macros in the Macros group and then click Stop Recording (whose icon appears as a blue button - refer Image 2c) which appears instead of the Record Macro command visible before you had started recording. Note that while the macro is being recorded the icon on the Status Bar which appears at the bottom of the Microsoft Office Excel 2007 window, also appears as a blue button (refer Image 2b) on clicking which the macro stops recording. You can also stop recording by clicking Stop Recording (which appears instead of the Record Macro command visible before you had started recording) in the Code group on the Developer tab. A new module is inserted in your project (refer Image 3 wherein Module1 is inserted) when you record a new macro and this module also stores the subsequent macros recorded till your excel file is closed, in which case after re-opening the file, in the same manner the new & subsequent macros recorded will all be stored in a new module inserted in your project. Refer Image 3 to view the recorded macro (sub-procedure) in VBE, wherein the red color has been filled in the interior of the selected cell of the active sheet.

View, Edit & Run a Recorded Macro

To view the recorded Macro, on the View tab on the ribbon, click Macros in the Macros group, click View Macros (refer Image 1a) which will open the Macro dialog box (refer Image 4). Another way to open the  Macro dialog box is to click the Developer tab on the ribbon, and click Macro in the Code group. In the Macro dialog box, select the Macro name and you can Run (to execute macro), Step Into (run macro in break mode by pressing F8), Edit (to read VBA code) and Delete the macro, and on clicking Options you can change the macro's Shortcut key and description. The recorded macro can also be run, viewed, read, edited & deleted in the Visual Basic Editor by going to Modules and select the Module in which the vba code is saved by default as a subroutine.

To run a macro in the Macro dialog box, select the Macro name and click on Run. You can run a macro by using the Shortcut key associated with the macro. To run a macro in the Visual Basic Editor: click within the sub procedure and press F5; or click Run in the menu bar and then click Run Sub/Userform; or click Tools in the menu bar, then click Macros which opens the Macros dialog box, wherein you can select and run. When the macro is run, it plays back the commands in the same sequence and order as they were performed while recording.

Use Relative References

You have an option to use Relative References before starting to record a macro - click View tab on the ribbon, click Macros in the Macros group, and then click on Use Relative References (refer Image 1a). By using this option, while recording a macro all cell references will be relative. Example - while recording a macro, if you fill red color in the active cell A1 and then select cell C2 to fill yellow color: not using the relative reference option will always fill yellow color in cell C2 when you run the macro, whereas using the relative reference option will fill yellow color by using "ActiveCell.Offset(1, 2)" from the cell where you have filled red color (meaning the cell in which yellow color is filled will always be relative to the cell where red color is filled and will be Offset by 1 row and 2 columns - if red color is filled in cell D18, then yellow color will be filled in cell F19 on running the macro). For live code of this example, click to download excel file.

Limitations of Recording a Macro

It may be noted that the procedure of recording a macro, though an easy way and a useful tool to learn vba and create vba codes, can only be used for simple and basic codes and not for creating advanced and complex codes due to certain limitations. While recording a macro you can create only sub-procedures and not functions which return a value, you cannot store information in memory while recording a macro and cannot use variables, or use conditional statements like If-Then, or use loops, or create error handling, you cannot call other procedures or functions, a recorded macro code is usually inflexible and not the most efficient (it might run slowly), and also a clean up of unnecessary code lines is required in the recorded macro.

Storing a  Macro in Personal Macro Workbook:

While recording a macro, in the Record Macro dialog box you can select where you want to store the Macro. On selecting Personal Macro Workbook, a hidden Personal Macro Workbook (PERSONAL.XLSB) is created in which the macro is saved. This makes the macro available at all times (ie. in all the Excel Workbooks) when Excel is opened. The Personal.xlsb workbook is saved: (i) in Microsoft Windows XP, in the folder C:\Documents and Settings\UserName\Application Data\Microsoft\Excel\XLStart; (ii) in Windows Vista, in the folder C:\Users\UserName\AppData\Local\Microsoft\Excel\XLStart; and (iii) in Windows 8, in the folder C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART.

Note that all workbooks present in the XLStart folder automatically open when Excel is opened anywhere. If you are creating Personal.xlsb for the first time to store your macro, then after closing all Excel workbooks when you are exiting Excel by closing the blank Excel window you will be asked - “Do you want to save the changes you made to the Personal Macro Workbook? If you click Yes, the macros will be available the next time you start Microsoft Office Excel.” Click on Yes to create and save Personal.xlsb. If you attempt to edit a macro stored in Personal.xlsb in the Macro dialog box (on the View tab on the ribbon, click Macros in the Macros group, then click View Macros which opens the Macro dialog box), you will get the message "Cannot edit a macro on a hidden workbook. Unhide the workbook using the Unhide command." To unhide the Personal.xlsb workbook: on the View tab on the ribbon, click Unhide in the Window group, which opens the Unhide dialog box - select Personal.xlsb and click OK. However you should hide the Personal.xlsb workbook after editing the macro stored in it. An easy way to edit a macro stored in Personal.xlsb is in the Visual Basic Editor [go to Modules under VBAProject (PERSONAL.XLSB) ] and in this case you will not need to Unhide the Personal.xlsb workbook. You can also delete the Personal.xlsb workbook from the folder (as detailed above) in which it is saved.

Refer Images 5a to 5c which show the process of recording and storing a macro in Personal Macro Workbook. Image 5a shows the Record macro dialog box; Image 5b shows the message "“Do you want to save the changes you made to the Personal Macro Workbook? ....." on exiting Excel; Image 5c shows the recorded macro (vba code) stored in a module in Personal.xlsb. The macro has recorded the actions of entering of text "Hello" in the selected cell in the active sheet and then selecting the cell D10.

How do I record VBA code?

How do I record an existing macro?

How do I record a macro edit?