Excel VBA procedures running at erratic speeds

[Gesualdo]

I have a system with a complex Excel interface, running under VBA, storing
lots of data in Access via ADO. Some processes take several minutes to
complete, but the actual speed varies a lot, and at the moment things are
running very slowly. I'm using Office 2003, on a 1.86Ghz Core Duo machine
with 1Gb RAM. The processor doesn't run at more than 54% speed, suggesting
there's some form of bottleneck somewhere, but it's the variable nature of
the problem that's baffling. Sometimes I can get it to speed up, but it then
settles down to a slow speed again. Any ideas?

 

[Norman Jones]

Hi Gesualdo,

See the MSKB Article # 319998
'BUG: Memory leak occurs when you query an open Excel worksheet
by using ActiveX Data Objects (ADO)'

http://support.microsoft.com/default.aspx?scid=kb;en-us;319998&Product=x

See also:

http://tinyurl.com/dpqqd

 

[Gesualdo]

Hi Norman,

Thanks for that. A memory leak sounds a likely cause - however, I only use
ADO to query Access, not data in Excel. This data is not pasted directly, but
transferred from recordsets to arrays or single variables which are then
pasted. Any other ideas?

Gesualdo

 

[Scott]

I'm not 100% sure but since you have a dual core processor I think the reason
why you don't get above 54% use is because of the two processors. i don't
think Excel 2003 has hyperthreading technology which would allow for both
processors to be used at the same time. So, next time you try running it,
check your CPU usage history. If one side is up near 100 and it's only
giving you 54% then it's because the other processor is doing little but
background stuff. I just tried running a test on my computer, dual processor
as well, and unless I'm running several programs at the same time I can see
that one processor is working much harder than the other.

Just a thing to check up on.

Cheers,
Scott

 

[Gesualdo]

That's an interesting aspect of dual core processors - worth looking into,
and thanks. However, I'm still baffled by the variability of the speed.

Philip

 

[Scott]

Just some things that I can think of that may help regarding the speed issue.
First to ensure it's as fast as possible try keeping the calculation command
as manual and screen updating as false. If you don't have these, it might
help a bit.

As for variability, does it slow down when you rerun the macro? or during a
one single run the computer is working harder then slowing down?

In case it's because of running the process several times, check and make
sure that you don't have any blank rows that excel might be including in the
data (control-end to check the last row). This could increase the memory
used by excel by quite a bit from one run to another.

If it is a memory issue go to an empty cell and type this in """"
=INFO("memused")/1024/1024 """" don't include the """". Anyways this will
give you the amount of memory that is being used in Mb. If you find that the
excel file gets bigger and bigger then you have at least found out your
problem is a memory issue and not a bottleneck.

cheers,
Scott

 

[tony h]

The first thing I would do is to try and find where the time is going.
would do this by writing a logging or trace function which output to
text file or to a sheet on the spreadsheet.

Then write the time to the log before and after each routine, o
wherever you think is appropriate. By analysing the log file you wil
at least know which routine is taking the time and if it varies fro
run to run.

I would be interested to know what you find.

regard

 

[Gesualdo]

That's very helpful - I have suspected the spreadsheets themselves of slowing
things down - for example, I 've found that resetting a large print area
takes longer each time you do it. Again, the macro processes that most vary
in speed are ones that dump data into a spreadsheet from a series of pasted
arrays. I've also found (I think) that hiding unused rows slows things down,
at least in the sort of thing I'm doing. Once again thanks for the help Scott.

Philip

 

[Gesualdo]

I've used start-and-finish time logging but you're right, it might be useful
to have a more detailed breakdown of time taken by each part of a procedure.

Cheers!

Philip

 

[tony h]

I include this story just for interest

I was recently asked to look at spreadsheets that had a macro which
took 20minutes to run but sometimes would run in just one minute. I
diagnosed the problem as : The main part of the code took about a
minute to run but it had a database query which took 19 minutes. But
sometimes the database query took almost no time at all.
My initial observation was that if you re-ran the macro almost
immediately it was quicker - but very occaisionally it was quick on the
first run!
The reason : SQL2005 cached some query results and this query was used
in a number of processes. If I re-ran immediately it got the cached
dataset; but sometimes I would get the cached data from a different
process running the query.

regards

 

[Scott]

Instead of hiding unused rows why not just delete them? it'll speed up your
process. there's a macro that will do it for you, type in "delete unused
rows" and I think you should get a hit on this site. If not I can always
post it here, it's rather short.

Another thing is when you're dumping data into the excel sheets are there
any formulai and if so, does it take a long time to recalculate these
formulai? On one sheet where I had a macro take a minute and a half I found
that recalculation took 30 seconds, so I was able to trim a third of the run
time off just because of that.

Last thing, when you say resetting a print area, you don't have your page
margins showing in excel do you? this alone takes up time and I have seen
numerous posts where it was the last thing checked and was the actual problem.

Just some more ideas,
Cheers,
Scott

 

[Krzysztof]

My Windows Task Manager (I hope this is correct English name, I am not sure
because I am using different language Windows, but I mean the one that
appears after pushing ctrl+alt+del) shows that Excel uses both cores! However
the total CPU usage sum up to 50%. This could mean that Excel is prepared for
hyperthreading, but Windows limits total usage for one application to 50%. I
am right? If it is true, is it possible to change Windows settings to use
full Core 2 Duo power?

I will be very grateful for your halp - I have complicted calculations that
take a lot of time (even if I turn off screanupdating and set calculation
manual.

Regards,
Krzysztof

 

[Charles Williams]

The Excel 2007 Calculation engine is the only part of any version of Excel
that supports multithreading.

VBA does not.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm