Excel help

[DanS]

Hi,

Can anyone here versed in Excel help out this thread.....

Original thread with problem:
<#[email protected]>

And here I was trying to help out a bit in this slightly later thread:
<[email protected]>

The OP is convinced that there is some problem with running any version of
Excel in WindowsXP, but my gut says there is something wrong with the code.

Thanks,

DanS

 

[Martin Fishlock]

Dan

What is the title for the original thread? I searched for your name but
didn't come up with anything? Or is it in another group?

 

[DanS]

Dan

What is the title for the original thread? I searched for your name but
didn't come up with anything? Or is it in another group?

It was in another group, microsoft.public.windowsxp.general. (Subjects:
'Can XP control performance of Excel', and something like 'Too many
macros'. I thought the msgID's would do it. This is Excel97 running on
XP. The issue was that while everything ran fine under Windows ME, it
would fail in XP.

There was a post in one of those threads from the OP saying the problem
was solved, by this MSKB article:
http://support.microsoft.com/kb/313935/en-us

The original error was 'Out of Memory' error, and looking at the original
code, it was a bunch (17) of nearly identical macros run successively,
only with one number changed in each macro.

I was looking at the code thinking the following may contribute to the
problem...

Each macro has 2 Dim's of a 'Range' variable, in scope of the individual
macros. The var's rng and rng2 are set using set rng =... & set rng2
=....

In each of these macro's these were set, but there was never any lines
saying Set rng = Nothing, or Set rng2 = Nothing. My thought was a cleanup
issue.

So I guess now, you'd be helping me out, since I have very little
experience in VBA, just a lot of just straight VB, in which I was taught
to explicitly set objects to nothing when done with them. So that's why I
was looking in that direction.

Is this necessary in VBA also ? Could the above fix be a band-aid that
masks a problem that may still exist ?

Thanks in advance,

DanS

 

[Martin Fishlock]

Dan,

I always used to use set object = nothing but it was explained in this NG
that that is not necessary as VBA should do the cleanup after you exit scope
or the code.

Now that may be the case in 2K++ but in 97 I would recommend the good old
set object = nothing, if nothing you recommend giving it a try and then see
how it changes the performance.

But thats only my experience of using 97 and the buggy problems with it.

 

[DanS]

Dan,

I always used to use set object = nothing but it was explained in this
NG that that is not necessary as VBA should do the cleanup after you
exit scope or the code.

Now that may be the case in 2K++ but in 97 I would recommend the good
old set object = nothing, if nothing you recommend giving it a try
and then see how it changes the performance.

But thats only my experience of using 97 and the buggy problems with
it.

Thanks for your time. I went and reread the KB article, and well, I don't
know if this applies to the OP's issue or not,

'This issue occurs when the program's Windows compatibility mode is set
to Microsoft Windows 98/Microsoft Windows Millennium Edition (Me), and
you leave the display settings for the program blank, or you select 640 x
800. Additionally, an "out of memory" message may be displayed in the
Office program if you select 256-color in the display settings.'

Seems like some weird conditions (except for black display settings).
Hopefully this will do it for the OP.

It then goes on to tell you how to turn off compatibility mode.

Still seems odd to me though. If you have 17 nearly identical macros run,
and you run through most of them then quit it appears to work fine. But
if you let all 17 run you get this error.

Prior to the KB article fix, this was said....
.....'the program still crashes at the first line of code after the
"DIM's" in the 11th macro of this group (XPHome).'

Just seems fishy. I would think that it would crash in the first macro,
if the macros all do nearly identical functions, right after the Object
Dims.

I'm sure this is my misunderstanding though of VBA.

Thanks again.