Macros for different versions of Excel - within same file

Given:

• Have existing Excel 2003 files with macros
• Company is migrating to Office 2010, but will take minimum 1 year to complete entire rollout, therefore files will need to be able to be shared across versions.
• Office Migration Planning Manager (OMPM – free tool from Microsoft) scan indicates 5% of Excel files will have macros issues.
• Interviews with business to identify business critical files indicate ~20% of business critical Excel files have macro issues that cannot convert/upgrade without issue.

Request:
Can Excel 2003 files with macros be “updated” (whatever that may look like) such that upon opening the file a prompt will appear (or possible auto-detect) to determine the version of Excel being using, then only use macros compatible with that version?

An example might help clarify the request:

Person in HR using Excel 2003 opens a file Team_Expense_Report.xls (corporate template) and the macros within the file determine the person is using E2003 and run the applicable code. All existing buttons and calculations continue to work as they do today.

Person in Engineering using Excel 2010 opens a file Team_Expense_Report.xls (corporate template) and the macros within the file determine the person is using E2010 and run the applicable code. All existing buttons and calculations continue to work as they do today.

*We know the corporate template has macros that fail when using E2010. Can we now embed macros for each version of Excel within these templates that will function properly for each group?


We have 15+ million Office files and ~100K files with macro issues so not everything can be fixed this way. But if we know we have a small group of files (I.E. corporate templates, budget forecasting worksheets) that we can apply this sort of “compatibility fix” to we would like to investigate the option.

I would appreciate some comments or feedback.
Greg