Using an Excel 2010 document in Excel 2003 - VBA issues

1
votes

I have been involved in writing an excel workbook for data analysis in Excel 2010. However I recently found out that some of the machines on which this document needs to be used run Excel 2003. I'm aware of the compatibility issues with saving to .xls format see here, but haven't been able to find a good summary of changes to the VBA code, specifically how to change from 2010 to 2003 (rather than the other way).

I do have access to a computer with Excel 2002 for testing, but it's very slow, in a room at the opposite end of the building, and has no internet access. So to avoid having to go back and forth to look up issues as they arise, I was hoping for some sort of list or summary that I could look through to identify issues before they arise.

I am aware that a similar question has been asked about 2007-2003 compatibility:
However all the answers simply suggest the use of a virtual machine. I do not have permission to install programs on my work computer (I'm not primarily employed for programming/IT), nor do I have Office on my home computer, or access to a licence.

1
vbaexcelcompatibilityexcel-2003excel-2010
No matter how well you research it you will still have to test it to find out if it's going to work or not. It would probably take less time to take the hike, find the errors and fix them than to evaluate all the things that might not work. – Jesse

1 Answers

5
votes

The charting object model was substantially changed in Excel 2007, and then omitted from the macro recorder. Excel 2010 restored the macro recorder for charts, but you'll find the recorded code likely won't run on Excel 2003.

Tables aren't supported in Excel 2003. Use dynamic named ranges instead if you want a range object to grow automatically with the amount of data.

Excel 2003 uses command bars rather than the ribbon. If you put icons on the ribbon, you'll need to approach it differently for Excel 2003.

The Analysis ToolPak is an optional install in Excel 2003. If you use functions from it in your code, you'll need to make sure that it is installed and enabled.

If you save files in code, make sure that you use the FileFormat parameter (specifying the extension is not sufficient). Also make sure that you use integers rather than Enums for the FileFormat parameter (e.g. 51 instead of xlWorkbookDefault) because Excel 2003 won't recognize the newer Enums.