Can XP control performance of Excel?

[CLR]

Hi All........
I have never installed XP and do not have administrative rights at work
where I must use it. Could anyone tell me if during the installation or
setup of XP, the Administrator can control the performance of Applications,
such as MS Excel? Or, if the default settings of XP might induce such
control. By control, I mean the restricting of the amount of memory Excel
can use, or the number of consecutive macro operations it is permitted to
perform......I have run into a problem with a program I have written in VBA
for Excel. It runs fine up to a point and then crashes, whereas the same
program runs fine in WinMe. If I cut back the number of macro operations,
then the program will again run fine in XP...but of course I need those
additional operations.

Anybody know what's up with this?

Vaya con Dios,
Chuck, CABGx3

 

[DanS]

Hi All........
I have never installed XP and do not have administrative rights at
work where I must use it. Could anyone tell me if during the
installation or setup of XP, the Administrator can control the
performance of Applications, such as MS Excel? Or, if the default
settings of XP might induce such control. By control, I mean the
restricting of the amount of memory Excel can use, or the number of
consecutive macro operations it is permitted to perform......I have
run into a problem with a program I have written in VBA for Excel. It
runs fine up to a point and then crashes, whereas the same program
runs fine in WinMe. If I cut back the number of macro operations,
then the program will again run fine in XP...but of course I need
those additional operations.

Anybody know what's up with this?

Vaya con Dios,
Chuck, CABGx3

I've read your other thread, and it seems to me that there has to be
something in the VBA code that is causing this. It could be some other
libraries that have changed. I'm not sure, because I don't use Excel, I
rather desipe it actually, does it use outside libraries, like MDAC or
similar. Are those versions the same ?

I know you posted your code in that other thread, but, not doing VBA
w/Excel, it's nearly Greek to me, even though I do a LOT of VB.

IIRC, you said it would run all the macro's fine if you skipped the last
set (?). There's got to be something about the last set.

 

[Rock]

Hi All........
I have never installed XP and do not have administrative rights at work
where I must use it. Could anyone tell me if during the installation or
setup of XP, the Administrator can control the performance of
Applications,
such as MS Excel? Or, if the default settings of XP might induce such
control. By control, I mean the restricting of the amount of memory
Excel
can use, or the number of consecutive macro operations it is permitted to
perform......I have run into a problem with a program I have written in
VBA
for Excel. It runs fine up to a point and then crashes, whereas the same
program runs fine in WinMe. If I cut back the number of macro operations,
then the program will again run fine in XP...but of course I need those
additional operations.

Have you posted to an excel newsgroup?

 

[CLR]

The last set of macros are exactly the same as the preceeding
dozen....actually copied and pasted.........only to change the Department
numbers for which they are retrieving the data. If I cut out the last
couple of macros, the program will run fine on XP......but I need those
additional macros. The entire thing runs fine in WinMe so the code seems
ok. Other than the initial import of an Excel file, I make no references to
anything outside the program......It just won't run in XP. We use XPpro at
work, and I feel about XP like you do about Excel, so at home I still use
Me. But, I did purchase a copy of Xphome a long time ago from a friend who
was disgusted with it, and tonight installed it on an old harddrive I don't
care anything about, using all defaults, and put Excel97 on there and
behold, ......my program would not run on the Home version either.....soooo,
it has got to be something that XP is doing differently.

Thanks for your interest..
Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Indeed I have..........although I don't believe it to be an Excel problem
because the code all runs fine in WinMe......

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

It runs fine in Me? But what versions of Excel do you have? I bet the one at
work is newer.

Indeed I have..........although I don't believe it to be an Excel problem
because the code all runs fine in WinMe......

Vaya con Dios,
Chuck, CABGx3

 

[DanS]

We use XPpro at work, and I
feel about XP like you do about Excel, so at home I still use Me.

I can understand not being comfortable about upgrading to a 6 year old OS
(XP), but Windows Me was by far THE worst OS released by M$ to date.

I myself hate change too, but from Me to XP was a giant step forward,
especially in terms of stability, and usability.

 

[CLR]

Thanks for your thoughts, but the version of Excel has nothing to do with
the problem. The program will NOT run on an XP machine (6 tested) with
either Excel97, 2000, or 2002. It WILL run on an Me machine (2 tested) with
either Excel 97 or 2000.

Vaya con Dios,
Chuck, CABGx3

It runs fine in Me? But what versions of Excel do you have? I bet the one at
work is newer.

 

[DanS]

The last set of macros are exactly the same as the preceeding
dozen....actually copied and pasted.........only to change the
Department numbers for which they are retrieving the data. If I cut
out the last couple of macros, the program will run fine on
XP......but I need those additional macros. The entire thing runs
fine in WinMe so the code seems ok. Other than the initial import of
an Excel file, I make no references to anything outside the
program......It just won't run in XP. We use XPpro at work, and I
feel about XP like you do about Excel, so at home I still use Me.
But, I did purchase a copy of Xphome a long time ago from a friend who
was disgusted with it, and tonight installed it on an old harddrive I
don't care anything about, using all defaults, and put Excel97 on
there and behold, ......my program would not run on the Home version
either.....soooo, it has got to be something that XP is doing
differently.

Thanks for your interest..
Vaya con Dios,
Chuck, CABGx3

So Chuck, to further this thread, you have said that it errors out at the
same place....what is that place, and what is the code.

The error you get 'Out of Memory' has nothing to do with the amount of
RAM in the machine's, it a process stack error.

(Reminding you that I don't use VBA/Excel) I looked back at the code you
have posted. Here's one possibiliy.....

In that code, there are 2 Dim statements....

Dim rng As Range
Dim rng2 As Range

And then there are several lines that have 'Set rng = ...' and 'Set rng2
= ...'

I'm assumning that a 'Range' in Excel VBA is considered an Object ? If
so, (and at least in VB) Object need to be destroyed after they are used
to release resources that it holds. Nowhere in that code is there a 'Set
rng = Nothing' or a 'Set rng2 = Nothing' after you are done using it.

Each macro then would have the call's to 'AddTopFormulas' & 'Goal' as
well. Do you have object references created in those as well that aren't
set to nothing when done with ?

A quick websearch yielded nothing specifically in regards to Excel97, but
apparently it was a known bug in Access97, so......

Personally, I can see this being a POSSIBLE cause if indeed as you say
this is just one of the string of macros that were copied & pasted.

This all makes sense to me, w/o trying it, since you say there are a LOT
of these macros run successively, and they all have the same code, but if
you skip the last few, it runs OK. That could be because you are just not
hitting the high-water mark in Me yet.

So here's my conclusion....IF the above applies, then YES, there is
something XP does differently than Me, it manages memmory better (well it
does a million things better regardles of the above, and why XP is 1000
times more stable), so that is why you would see this problem. And if
that is it, all versions of the app must be updated no matter what OS
they are running it on.

Let us know.....

DanS

 

[CLR]

Hi DanS.......
I made a small macro...

Sub UnSetDims()
set rng = nothing
set rng2 = nothing
end sub

then I added a CALL in each of my 13 identical macros to this sub and
nothing changed.....the program still crashes at the first line of code
after the "DIM's" in the 11th macro of this group (XPHome). Was my shortcut
method sufficient, or must I go back and include those two lines in all 13
macros?

You said, "The error you get 'Out of Memory' has nothing to do with the
amount of
RAM in the machine's, it a process stack error."........this is the point I
am getting at. Can this limit be set during install/setup of XP or adjusted
afterwards?.....or can we clear it occasionally in the code?......also, is
this limit intentionally set lowet in XP by default than it was in Me?

There are no DIMS in the subs "AddTopFormulas" and "Goal".....they merely
insert formulas into the same cells in each sheet.

This problem is scary. If we cannot determine what is the cause, then we
don't know how big we can make Excel programs to run in XP......until they
crash and then have to redesign just accomodate this difference in
OS's............

Thanks again for your assistance......

Vaya con Dios,
Chuck, CABGx3

 

[DanS]

Hi DanS.......
I made a small macro...

Sub UnSetDims()
set rng = nothing
set rng2 = nothing
end sub

then I added a CALL in each of my 13 identical macros to this sub and
nothing changed.....the program still crashes at the first line of
code after the "DIM's" in the 11th macro of this group (XPHome). Was
my shortcut method sufficient, or must I go back and include those two
lines in all 13 macros?

Well, each of the macros Dims it's own copy of rng and rng2. So yes, you
have to add it to each macro. The way you did it above, rng and rng2 are
out-of-scope to all the other macros.

In the VBA code window, a Variable Dim'd within a sub is in-scope only to
the sub it is dim'd in. You could handle it by either setting rng & rng2
to nothing in each macro. Or, you could Dim rng & rng2 at the top of the
code window, and NOT inside any sub. Then call the UnSetDims sub you have
written above. For time sake, the easiest thing to do is just copy and
paste it into each macro.

(By all rights, running the UnSetDims sub as you have it should have
produced a runtime error, since it will be an unknown variable. This
could be proven by adding the line: Option Explicit as the first line in
the code window, not in any sub. This will now require Variables to be
dim'd in order for it to use. I theorize that the Option Explicit is not
there, and as so, running the UnSetDims doesn't error because of this.)

You said, "The error you get 'Out of Memory' has nothing to do with
the amount of
RAM in the machine's, it a process stack error."........this is the
point I am getting at. Can this limit be set during install/setup of
XP or adjusted afterwards?.....or can we clear it occasionally in the
code?......also, is this limit intentionally set lowet in XP by
default than it was in Me?

And what I'm getting at is that this is not some variable setting of any
type. It is the way XP handles memory better, which is why a program that
crashes under XP typically doesn't affect everything else. In 9x/Me, when
a program error like this occured, most of the time you could not recover
and had to re-boot.

There are no DIMS in the subs "AddTopFormulas" and "Goal".....they
merely insert formulas into the same cells in each sheet.

This problem is scary. If we cannot determine what is the cause, then
we don't know how big we can make Excel programs to run in
XP......until they crash and then have to redesign just accomodate
this difference in OS's............

If this is the cause, there is no choice but to fix any applications that
are coded like this.

 

[CLR]

Ok........went in and changed all my CALL UnSetRanges lines and put in the
two lines
set rng = nothing
set rng2 = nothing

Same problem, program crashes in the same place, at the beginning part of
the same macro, just as before. Acts the same if I use the "Option
explicit" or not..........this in XPHome.

There is definitely a problem here, and I'm surprized that more people have
not run in to it.
I'm afraid to sit in a meeting now and tell people what automation can be
done in Excel on an XP system because I just don't know how XP will handle
it..........

Thanks for all your trying anyway.........I did learn a thing or two

Vaya con Dios,
Chuck, CABGx3

 

[DanS]

Ok........went in and changed all my CALL UnSetRanges lines and put in
the two lines
set rng = nothing
set rng2 = nothing

Same problem, program crashes in the same place, at the beginning part
of the same macro, just as before. Acts the same if I use the "Option
explicit" or not..........this in XPHome.

There is definitely a problem here, and I'm surprized that more people
have not run in to it.
I'm afraid to sit in a meeting now and tell people what automation can
be done in Excel on an XP system because I just don't know how XP will
handle it..........

Thanks for all your trying anyway.........I did learn a thing or two

Vaya con Dios,
Chuck, CABGx3

Sorry, my knowledge of Excel VBA is nil, though I've done quite a bit of
Access programming, but then that was done thru VB6 and not VBA.

My gut feeling is that it's something in the code, and not an XP issue.
If the code is corrected, you'll be able to do all the automation you can
do in XP as any other OS.

Keep looking to the Excel newsgroups.

 

[CLR]

Hi DanS
You said:

My gut feeling is that it's something in the code, and not an XP issue.
If the code is corrected, you'll be able to do all the automation you can
do in XP as any other OS.

I disagree, I think modifying the code will only move me slightly below this
newfound limit.

Keep looking to the Excel newsgroups.

I will do that, and thanks muchly for your attempts to help. Even tho we
didn't get there this time, I still learned something........

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

PROBLEM SOLVED!!!!!!

Jim Cone came up with a suggestion that did the trick.....

http://support.microsoft.com/kb/313935/en-us
"Office Programs Stop Responding After You Upgrade to Windows XP"

Many thanks Jim, and to all the others who gave of their time....

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Follow-up note:
The Compatibility adjustment procedure worked fine in my case on Excel 97
and on Excel 2002 for running my program on a WinXP machine. HOWEVER, it
would NOT work on Excel 2000-SP3. In fact, it disabled Excel 2000-SP3 so it
would not even work at all.......this on 2 different WinXP machines..just a
heads-up for anyone trying the Compatibility adjustment.

Vaya con Dios,
Chuck, CABGx3