Save the Macros!


Each time you enter a VBA code in Excel you should save it for future use. In Windows 7, a Personal Macro Workbook is saved on your C drive at: C:\Users\[user name]\AppData\Roaming\Microsoft\Excel\XLSTART

. . . named PERSONAL.XLSB which will run in the background each time you open Excel. (When looking in this folder, be sure to make hidden files visible by going to Tools . . . Folder Options . . . , click the View Tab, and check 'Show hidden files, folders, and drives'.) If you have never recorded a macro in Excel before you need to prompt this file to be created by doing the following [note that I'm making reference to the steps which must be performed in Excel 2010]:

1. If the Developer tab in Excel 2010 is not visible, go to File Options . . . Customize Ribbon , and check off Developer on the right.

2. On the Developer tab, click on Record Macro. At this point be sure to select, 'Store Macro in . . . Personal Marco Workbook'. See Fig. 1 below. Click okay, and record any kind of dummy macro. For example, press CTRL + A , and CTRL + B to boldface the enter worksheet.

3. Click 'Stop Recording'. Save and close the draft workbook.

4. When you exit Excel you will be prompted to save changes to the Personal Macro Workbook. See Fig. 2

5. Now when you reopen Excel, and go into Visual Basic (Alt + F11), you will see 'PERSONAL.XLSB' listed in the Project explorer pane on the left. See Fig. 3. You can right click where it says, "VBAProject (PERSONAL.XLSB)" and insert modules to contain any macros you want to add in the future.

. . . after you exit Excel, you will be prompted to save any changes made to the Personal Macro Workbook.