A question on VBA compatibility between versions of Excel.

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

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" <[email protected]> wrote in message
news:nq_Gf.19236$j07.13450@trnddc04...
> 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

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 wrote:
> 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" <[email protected]> wrote in message
> news:nq_Gf.19236$j07.13450@trnddc04...
>
>>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
>>
>
>
>

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" <[email protected]> wrote in message
news:dM3Hf.40$_D1.25@trnddc03...
> 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 wrote:
> > 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" <[email protected]> wrote in message
> > news:nq_Gf.19236$j07.13450@trnddc04...
> >
> >>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

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



Bob Phillips wrote:
> 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" <[email protected]> wrote in message
> news:dM3Hf.40$_D1.25@trnddc03...
>
>>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 wrote:
>>
>>>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" <[email protected]> wrote in message
>>>news:nq_Gf.19236$j07.13450@trnddc04...
>>>
>>>
>>>>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
>>>>
>>>
>>>
>>>
>
>

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!).

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" <[email protected]> wrote in message
news:8s5Hf.2767$3V4.1937@trnddc06...
> 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
>
>
>
> Bob Phillips wrote:
> > 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" <[email protected]> wrote in message
> > news:dM3Hf.40$_D1.25@trnddc03...
> >
> >>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 wrote:
> >>
> >>>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" <[email protected]> wrote in message
> >>>news:nq_Gf.19236$j07.13450@trnddc04...
> >>>
> >>>
> >>>>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
> >>>>
> >>>
> >>>
> >>>
> >
> >
>

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


[email protected] wrote:

> 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!).
>