Excel 2000 VBA very slow, but same code fast in later versions

[THA]

We have an application which feeds data to an excel workbook using COM. While
the data is fed into the sheet excel is hidden.

After the data has been fed the excel workbook macros are called, there are
app. 5000 lines of vba code so there are lots and lots of macros...

The excel workbook contains 10+ sheets some of which have between 500 and
1000 lines and there are references between them.

Now we have the problems that when using the workbook with office 2000 as
soon as the macros start to execute CPU usage drops to between 0-3% whereas
office 2002/2003 stays on 90-99%.

I found out that if I make the excel sheet visible while running the macros
and press alt+F11 excel is ready within 10 seconds using excel 2000, but I
have no clue as to what is causing this behavior..

So the questionis , does anyone have an idea as to why excel 2000 uses so
little cpu and is so slow compared to the later versions ?

We have done stuff like disabling screenupdating and setting calculation to
manual etc.

Thanks

 

[Howard Kaikow]

That type of performance difference may be due to different environment in
which each version of Office is running, e.g., other apps and 3rd party
add-ins/macros, as well as whether an auntie virus office plug-in is
enabled.

 

[THA]

Thanks, I know..

However the machines are based on a standard image where the primary
difference is the office version...

I have a suspicion that it is something that works different from office
2000 and the later versions....

It could, I guess, also be that the template which we use is corrupted and
that office 2002/2003 are better at recovering ? But how to find out I'm not
sure..

 

[THA]

Further info:

It seems like the spreadsheet always run fast if we don't hide the workbook
while executing the macros... if the workbook is hidden excel 2000 uses 0-3%
CPU whereas excel 2002/2003 uses 90+ %CPU..

is anybody aware of changes between excel 2000 and later versions which
could cause this ?

 

[Howard Kaikow]

I would not suspect corruption.
Corruption usually leads to outright errors.

It could be that there are some inefficiencies in the code that are exposed
in the later versions of Office.
Only a careful examination of the code can determine this.

 

[THA]

Found a hint on a site suggesting that .Find is very slow when excel is not
in focus.. This could very well be it as we're using .Find several times.

We're trying to remove it and I will post the result

 

[THA]

Removing all .find from the macro code did the trick... The spreadsheets is
flying now

So a warning don't use .find in macro code (in excel 2000) if the
application is not visible when the macros are executed !

 

[Howard Kaikow]

Were using the Find worksheet function or the Find object?

 

[THA]

Hi

It was code like this

Set currentLine = Worksheets("Rates").Range("A:A").Find(lineId,
LookIn:=xlValues)

and this

Dim c As Range
Set c = .Find(myCurrency, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True

which caused the problem with Excel 2000, getting them recoded sped things
up from 30+minutes to 20 seconds !!!.

Thanks for you help

 

[Howard Kaikow]

I suspect that you have too many references to objects such as
Wotksheets("Rates").
Use With statements.

 

[THA]

We did and had to introduce some extra variables to keep track of where we
are as we loop through the code numerous times for each line and sometimes
for a cell value also..

Anyways without the .Finds it's much faster