A question on VBA compatibility between versions of Excel.

[John Thomas]

Is there some kind of compatibility issue with VBA when going between
different versions of Excel?

I recently wrote an Excel application for my daughter which relies
heavily on the use of Visual Basic macros. I tested it on both my
desktop machine and my laptop with no problems. However when I emailed
it to her, it would not run and went to a VBA debug screen. For a test I
emailed it to my wife’s computer and tried to run it there. On her
machine, it loaded and everything worked except one sort routine.

I am running Excel 2002, my daughter has the latest version, and my
wife’s machine has a much older version.

This is the second time I have encountered a problem distributing a
Excel workbook with macros. The first incidence involved one I sent to
about 50 co-workers. About a quarter of them could not get it to run
properly, while the rest had no problems.

Is there something I am missing when attempting to move between versions?

John Thomas

 

[Bob Phillips]

John,

As you might expect, later versions of Excel add new features, as do most
other products. Backwards compatibility is the best that can be achieved.
Because of this, the code will not always work with earlier versions.

The accepted practice is to developed your code in the lowest version of
Excel that you expect to distribute to (your wife's in this case), and then
you should have no problems. Therefore I am surprised to hear you say that
the version you developed in 2002 failed in 2003. Where did it fail?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[John Thomas]

Bob

I asked my daughter that and she said the message was "it couldn't find
some library, or some reference". This morning I saved the file in an
older version and and sent her that. She was able to open and navigate
through the thing, but none of the command button macros would work for
her. unfortunately she is several hundred miles from here, and she does
not use Excel enough to be as helpful with her explanations as one might
hope.

Thanks for the help, and I will take to her more extensively this afternoon.

John

 

[Bob Phillips]

John,

Get her to take a screen print when it happens.

Bob

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[John Thomas]

Bob

Just before reading your last message I talked with her about what is
happening. When she presses the control button she gets an error message
which says “project or library can not be found”. There is no change in
the screen display because the first thing the macro does is turn off
the screen update. Then I have it collect some data from one page, sort
it, place the results of that sort on another page, switch focus to the
sorted data page and then update the screen.

If you still feel there is value to seeing a screen grab I will call her
back and get one.

John

 

[delfiled]

One problem I've seen before is Microsoft saves the solver under a
different path for each new version of Excel, so if you access solver
in VBA, the reference you set at one version won't work in another (ha,
ha, that Bill Gates is a card!). So you need to take a look at the
paths of whatever references you use and see if they are changing
between versions. You can do this programmatically with VBE objects
but with the current version of Excel this is blocked unless you defeat
security by setting the 'Trust Access to Visual Basic Project'. You
can't even access VBE as read-only to check for this condition in code
if that flag is not set (ha, ha, that Bill Gates is a card!).

 

[Bob Phillips]

I think so, if possible get to check the references in the VBIDE (Alt-F11,
menu Tools>References), and see if any are MISSING.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[John Thomas]

A “card” is it? That’s one of the nicest things I’ve heard him called In
a long time. This is the kind of thing that keeps me adding to my “Why
I’m learning to hate Microsoft Library”. Every time I turn around I seem
to need some new reference book to figure out what they did this time.

Thank for you response to my question.

John