Excel 2003 to Excel 97 macros

[daveginboav]

My supplier has written some macros in Excel 2003 without checking with us
first what version we were running. He has managed to get them working on
Excel 2000 but we need them to work in Excel 97. Upgrading is not an option
for all sorts of reasons. Do we send him back to the drawing board or is
there a reasonable quality converter that could do the majority of work for
him - tweaking the bits later that still did not work?

 

[Harlan Grove]

Upgrading is not an option for all sorts of reasons. Do we send him
back to the drawing board or is there a reasonable quality converter
that could do the majority of work for him - tweaking the bits later
that still did not work?

It may not be possible for your supplier to know what did and didn't
change between Excel 97 and Excel 2000. There were some changes in the
Excel object model, but the big change was in VBA itself. Excel 97
uses VBA 5.x while Excel 2000 and later use VBA 6.x. The difference is
mostly in added functions in VBA itself, such as Join, Split, Replace,
InStrRev. There are work-alikes written in VBA that could be found in
the Google Groups archives for this newsgroup.

 

[Tom Ogilvy]

No converter. He need to get xl97 and run the macro - making corrections
when it halts.

Depending on how sophisticated the code is:

Most will be eliminating arguments that are not supported in xl97 (assuming
he did a lot of macro recording). He will need replacements for things like
Split, Replace and some other functionality introduced in VBA 6 (xl97 uses
VBA 5).

 

[daveginboav]

Thanks Tom and Harlan too. I'm currently working from home just now and only
have this third hand but I'm told that when running in a 97 environment the
macros trashed the machine!!!! I think I'm going to have to ask the guy to
get in touch with these forums etc to see if he can find an easy way through
this!
DaveG

 

[Ronald Dodge]

The first big issue you going to face as I faced it directly when I was sent
Office 2000 free of charge from MS as a fix to the chart bug in Excel 97, is
the fact that Excel 2000 and later compiles data very much differently from
Excel 97. Just a tid bit on this fact, Excel 2000 VBA and calculations
combined ran about 3 times faster than Excel 97 VBA and calculations did
combined. Now this is assuming Calculation mode is set to manual and it's
VBA that's controlling when and in what order which worksheets are
calculated. According to others, Calculations takes longer in Excel 2000
than they did in Excel 97, but for me, it's been just the opposite.

Also, as a result of this compilation issue, anything done in Excel 2000 VBA
or later is not viewable in Excel 97, even after getting around the Windows
security issue that involves having power users (or higher) open up an Excel
file with a macro in it one time before a standard window user can use an
Excel file with VBA code in it. This is assuming you are using Excel 97 on
a Windows 2000/ME, or later on the system, as far as the windows VBA
permission issue is about. Even if you are using Windows 98SE/NT or
earlier, you still wouldn't be able to view the VBA code created by Excel
2000 or later in an Excel 97 application.

Sometimes, using VBA code created in Excel 2000 VBA or later, even if all
code using in it matches to all code used in Excel 97, it will at times just
quit in the middle of execution as I also ran into that issue, which I was
told it was due to the compilation difference between Excel 97 VBA and Excel
2000 VBA.

I only know of one way to get around this issue.

First, the developer needs to make sure the code that is in Excel 2003 VBA
must be compatible to the Excel 97 VBA code as there are feature differences
between the 2 set asside compilation differences.

Copy that code into a text file and save it.

Create a new Excel 97 file.

Copy the code from the text file and paste it into the appropriate
location(s) in the newly created Excel 97 file.

In my opinion, there were a lot of technical changes going from Excel 97 to
Excel 2000 as I personally found Excel 97, SR2 to be too unstable for
various reasons, but then Excel 2000 was very stable as a lot of the bugs
that's in 97, SR2 was worked out in 2000. Other than minor changes, found
no real difference between 2000, 2002, and 2003.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Tom Ogilvy]

that sounds dramatic, because almost before they could begin to run, you
would probably get an error message.

 

[Ronald Dodge]

If you got a VBA permission denial messagebox when attempting to open the
file, see my other message as we ran into that issue, which involves writing
to the registry file on the system. However, the fix that I mentioned
should only have to be done one time.

While many may think Excel 97, SR2 is stable, I have dug so far down into
Excel and it's mostly at the deeper levels that I found Excel 97, SR2 to be
unstable, which only becomes even more unstable when intermixed with OS
2000/ME or later as that gets into security and landscape printing issues.
Due to the fact that we couldn't landscape print using Excel 97 on Windows
2000, we were forced to upgrade to Office XP.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Tom Ogilvy]

Just so you don't frighten the naive, here are some different first hand
experiences:

I go between xl2003, xl2000 and xl97 all the time and don't have a problem
looking at the code or executing it as long as I don't use commands/arguments
that are not supported in xl97.

Not to say that xl97 doesn't have more bugs than xl2000 and xl2002.

 

[exceluserforeman]

Could you send details of what purpose the macros do or purpose of the macros.
Maybe someone here can herlp to clean it up for you.
Here are some sample macros the old fashion way
Excel 95 http://au.geocities.com/excelmarksway/default.html
Excel 98 http://www.geocities.com/geo_marksway/excel_solutions/index.html