Guide to Converting Excel 2003 Macros to Excel 2010

When you decide to replace Microsoft Excel 2003 with Excel 2010, you may not give a second thought to your macros until you try to use them. However, with a little preparation, you can easily convert your Excel 2003 macros to Excel 2010, including those in your personal workbook.

Backing Up Macros

The first thing you need to do is backup your Excel 2003 macros. Under most circumstances, the macros stored in individual workbooks, meaning those you use in single spreadsheets or workbooks, can be converted easily without any extra work. However, there are times when the only way to make the macros work correctly is by manually copying and pasting them into Excel 2010.

Open any workbooks where your Excel 2003 macros are stored. Open the Visual Basic for Applications editor. Select the Tools menu, choose Macros and select Visual Basic Editor. Choose your macro's name. Copy all code for each individual macro. Open Notepad and paste the contents of each macro into a new Notepad document. Name the Notepad documents as the macro name. If you have multiple macros stored in a single workbook, copy them all into the same Notepad document for easily retrieval later.

Notepad can be opened by selecting All Programs from the start menu, choosing Accessories and selecting Notepad.

Now that you've backed up your single macros, it's time to backup your personal workbook. You only need to do this if you've stored macros which can be used universally in all Excel spreadsheets and workbooks. By default, the Personal.xls workbook is stored at C:\Program Files\Microsoft Office\OFFICE11\XLSTART. The directory will be different if you chose a different installation location when you installed Excel 2003. Copy the Personal.xls file to a new location, such as your desktop. To prevent any possible macro loss, copy any macros stored in this file to a Notepad document as well.

Convert Excel 2003 Macros to Excel 2010

Now that all your macros are backed up, it's time to install Excel 2010. Once Excel 2010 has been installed successfully, open one of your Excel 2003 workbooks in Excel 2010. Try to run your macros. If they run without any problems, your macros converted successfully. Go to the File menu and select Save As . Choose the Excel 2010 format to complete the conversion.

If you receive an error message or nothing happens, try one of the following methods:

Trust Center - Go to File , select Options and choose Trust Center . Press Trust Center Settings near the center of the screen. Select Macro Settings on the left side of the trust center window. Choose to either Enable all macros or Disable all macros with notification . With the first option, all macros will run regardless of the creator. The second option provides you with a notification which lets you enable or disable macros whenever you open a workbook.

Select Trusted Locations on the left side of the trust center window and select Add new location . Add the location where you store your Excel workbooks to the trusted locations list. This will prevent your macros from being blocked due to their location.

Copy and Paste - Open the Visual Basic Editor in Excel 2010. Select the Developer tab and choose Visual Basic . Select the sheet or workbook you want to add the macro to. Copy and paste the desired macros from your Notepad documents. Save your changes and close the Visual Basic editor. Run your macros to test them.

Convert Your Personal Workbook

Before you create any new macros in your Excel 2010 personal workbook, open the Personal.xls workbook you backed up earlier in Excel 2010. Go to File and select Save As . Press Browse and choose the following directory as your storage location: C:\Program Files\Microsoft Office\OFFICE14\XLSTART. The directory may vary based on the installation location you chose when you installed Excel 2010. However, be sure to name the workbook Personal , save it using the default Excel 2010 extension and store it in the XLSTART folder. This will allow you to use all your saved macros in Excel 2010 workbooks and spreadsheets like you did in Excel 2003.