Before you convert Excel 2003 macros to Excel 2010, make a backup copy of your macros. Either create a backup copy of the Excel workbook containing your macros or copy the Visual Basic code for each individual macro into a Notepad document. You can access the Visual Basic code in Excel 2003 by going to the Tools menu and selecting Macros. Choose Visual Basic Editor and select the name of your macro function. Copy all applicable code. The code can be pasted into Excel 2010 if problems occur during the conversion.
Please note that you should backup your personal workbook before uninstalling Excel 2003. If you created any macros to use universally with all your Excel workbooks, the macro is more than likely stored in the personal Excel workbook. This file is typically located at C:\Program Files\Microsoft Office\OFFICE11\XLSTART. You can also search for Personal.xls.
After you’ve made a backup copy, open your Excel 2003 workbook containing your macros in Excel 2010. For the most part, nothing has changed in the macro department, meaning Excel 2010 is able to recognize and run Excel 2003 macros. The biggest difference is setting security settings to allow macros to run.
For macros stored in individual Excel 2003 workbooks, follow this method. Skip to the Personal Workbook Macros section below for converting universal macros. If you’re lucky, your macros will run with no problem. Try running your macros as usual. If you have no problems, select File and Save As and choose Excel Workbook to save your workbook in the Excel 2010 format.
If you receive an error message or the macro doesn’t run at all, you must change your security settings. Go to File and select Options. Select Trust Center. Press the Trust Center Settings button. Choose Macro Settings. Select either Disable all macros with notification or Enable all macros. This will allow all macros to run, no matter what or you’ll be able to choose whether to disable macros or not when a new workbook is opened.
Another option is to store your workbook in a trusted location. By default, typical workbook locations are not trusted. Select Trusted Locations and press Add new location. Browse to the location the workbook is stored, such as My Documents to add the folder as a trusted location.
In the worst case scenario, copy the code from your Notepad backup and copy it directly into the Visual Basic editor in Excel 2010 to create your new macros. Select the Developer tab and choose Visual Basic to open the editor.
Personal Workbook Macros
In order to convert Excel 2003 macros to Excel 2010 that are stored in your personal workbook, open the workbook in Excel 2010 as stated above. When you save the file, save to the following directory: C:\Program Files\Microsoft Office\OFFICE14\XLSTART. This creates a new personal workbook in Excel 2010. You must do this before recording any macros in Excel 2010. Otherwise, copy each individual macro to the personal workbook in Excel 2010.