Wednesday, April 25, 2007

Excel 2003-2007 conversion project

Here is the email I am sending all my clients that we have written Excel tools for.

I want to ask about whether your IT department is looking at rolling out Office 2007 at all in the near future. When this happens there will be a LOT of work required to make existing excel tools work in that environment. The same will apply to all the work we have developed, and also potentially any other Excel files that you currently use.

I suggest that we start the work now. I suggest we make it code safe to work in 2003 and 2007 so that as you start to get a mixed environment to work with that it will continue to operate. I have a developer working for me now who is managing all my office 2003-2007 conversion projects.


If you have Excel tools that have any VBA in them chances are they will not convert to 2007 with out some effort.
Feel free to contact us to arrange a quote to analyse / convert your files.

Labels: , ,


Posted by: Jethro | 8:41 AM |

Tuesday, April 17, 2007

Excel 2007 and backward compatibility

Backward compatibility is important to anybody who writes Excel spreadsheets or uses VBA code. Backward compatibility means that features that are new in the new version will still work when run on the old version. This is not the case with Excel 2007, 2003, 2000, 97 or 95.

Excel 2007 allows you to save in a 2003 format. However being able to save in a previous version does not make something backward compatible. It simply means you can save in a previous version. Note to do this you will lose functionality of new features not supported under the old application, as well as possibly creating errors and breakages.

VBA Code differences
In each version change there have been changes to code, objects, interaction with these objects using VBA, libraries etc that have meant that VBA code written in the newer versions does not always work correctly or at all when run on the older versions. Often there are new arguments or options to functions that are introduced in new versions. These will cause errors if run in previous versions that don’t support those arguments or options. Using the Save As feature does not fix this.

Some specific examples include.
Excel 2007 - the new colour themes are not using the same colour codes that Excel 2003 used. Write code in 2007 and expect it to run on 2003 - no way. At least this forward compatible.

Use code in 2007 to open a CSV file and move the sheet into an existing Excel 2003 workbook. This will not work in Excel 2007. Excel 2007 opens the CSV file as a 2007 workbook with million rows. This cannot be moved into the 2003 workbook as the sheet is not compatible with this format. This is an example of a forward compatible issue

Of course these things can be fixed, like we fixed all the problems with all the previous "upgrades". Painstakingly going through thousands of lines of code and debugging all the error traps, all the function arguments and parameters that may be only used in certain circumstances.

For the average user who has recorded a macro to change some cells colours or open a CSV file (fairly common practices) these spreadsheets are NOT backward or forward compatible.

Spreadsheet differences
There are also numerous issues with the Save As feature. If you have a 2007 spreadsheet that is larger than the 2003 allowed size this cannot get saved as 2003 properly. If you are using the new conditional formatting these get badly converted when Saved As 2003. Even basic things like range names and filters don’t work the same way and don’t convert back properly.

In my opinion, treat Excel 2007 as a new application. Don’t use the compatibility feature unless you are able to test and retest everything you write on the old versions.
Assume that your users will open in 2003, or 2002 (XP) or even 2000, write your code accordingly and test, test, test for it.

Labels: , ,


Posted by: Jethro | 1:37 PM |