Extremely Slow VBA Execution Speed

[Joe Adams]

I have written a large application in VBA which controls
the opening, closing, screen updating, calculation mode,
and displaying of workbooks and worksheets while moving
data between them and completing multiple calculations.
As with most applications, the users have requested more
and more features. Well, somewhere along the line I have
hit a wall of SLOW. The time to perform one line of code,
setting a cell’s value = an array’s element value, has
slowed over 200 times.

I have place timers within the code to give me some
guidance, but I am at a loss. For overall understanding,
I have written a test workbook and macro within which I
turn off screen updates and calculations, read 20 values
into an array, write the values into 20 cells (one at a
time, since I cannot push the array into my application),
then turn calculations and screen updating back on.

If I open this test workbook and execute the macro as the
only workbook open, the macro will take .032 seconds to
execute on a 733Mhz machine with 256M of RAM and 384M of
Virtual RAM using Windows 2000 and Excel 2000 SR-1. If I
have the application open and then open this test
workbook, the macro will take 7.235 seconds to execute!

We took this computer and installed a 2.4Ghz motherboard
into it. So, it is running the same software on the same
network using the same hard drive (i.e.: same print
drivers, virus protection, …). Now, if I open the test
workbook and execute the macro as the only workbook open,
the macro will take .015 seconds to execute, and if I have
the application open and then open this test workbook, the
macro will take 4.421 seconds to execute. Yes, it is 40%
faster, but …

So, we added 1Gig of RAM for a total of 1,280Meg of RAM
and Windows recommended 2,762Meg of Virtual Memory which
we are running with a Page File Space of 1,500Meg. Now, if
I open the test workbook and execute the macro as the only
workbook open, the macro will take .015 seconds to
execute, and if I have the application open and then open
this test workbook, the macro will take 4.453 seconds to
execute. Essentially, NO CHANGE or improvement!

The time to recalculate the application is almost the same
(within timer error) as the time to insert the element
value from an array. Could Excel have lost the
Application.Calculation = xlCalculationManual switch?

I have observed that as the application has grown (and
slowed down) the it has become faster to copy and paste
values than to read them into an array and then insert the
value. This is why we tried the additional RAM, but it
had no effect. (Also, inserting an array of data with
about 200 elements into a worksheet takes about the same
time as entering one value into one cell, approx. 0.314
sec.)

Any Ideas of how to regain some of this 200X speed loss?
Why didn’t additional RAM help?

Thanks in advance,
Joe

 

[steveB]

Joe,

Without seeing any of your code, one of the things to look at is whether or
not you are using the select method on ranges, workbooks, and worksheets.
This really slows down code.

Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.Paste

can be replaced with:

Sheets("Sheet1").Range("A1").Copy _
Destination:= Sheets("Sheet2").Range("A2")

There is also a
With Object
.<whatever>
End With
structure that also speeds things up...

hth

 

[Joe Adams]

I am using the

With WorksheetObject
.range("aRangeName").value = arrList(23,2)
End With

construct and I agree it is faster if you are on the good
side of the "Wall". I just do not know what causes
the "Wall".

Thanks for the input,
Joe

 

[pfsardella]

Charles Williams has the best collection of information that I've seen
on speed, bottlenecks, etc. Maybe the answer will reveal itself by
perusing his WebSite.

http://www.decisionmodels.com/

HTH
Paul