i have written a series of complex VBA macros in excel as part of a system to generate hundreds (even thousands) of data reports from large data sets.
on my old desktop (p4 @ 3.2; 3gb ram) some of the macros take hours to execute - i even let them run overnight.
i wanted a new computer that could run them in much less time. so we invested 5k in a new desktop (dual core xeon @ 2.8; 4gb ram; twin 15k rpm hard drives).
i ran a simple macro for 1 minute on both computers - the new one is about one third SLOWER than the old.
my hypothesis: processor clock speed is what determines the speed at which a macro runs AND the fact that it's a dual core and xeon vs pentium doesn't affect speed.
any input/advice is very much appreciated
thanks, bret
Fastest Processor for Running Excel VBA Macros
ModemGeek
thanks for your response
i don't think other applications/services that may be running on the computer is what's slowing it down - i have more memory (4gb) in the 5k machine
also, because i'm running the identical macro on both computers, innefficiencies in the code cannot be the problem - i'm concerned about processing speed, not optimal code at this point
here's the test code i ran in excel:
-------------------------------------------------
Sub speedTest()
Dim x, i
Application.ScreenUpdating = False
For x = 1 To 200
Cells(1, x).Activate
For i = 1 To 30000
ActiveCell.Value = "Hello World!"
ActiveCell.Offset(1, 0).Activate
Next i
Next x
End Sub
------------------------------------------------
after running for 60 seconds on each comp - the old computer made it halfway through column AJ whereas the new 5k comp only made it to column Y - i would've predicted the new comp could finish the entire macro in 60sec
the new 5k comp is a dell precision 670 - it's a monster - huge and loud - but it's a complete waste (to me) of $ and hardware if it cannot execute macros MUCH faster
if there's anything else at all you (or anyone else) can think of - i would be indebted
thanks again!
******
hi Bret,
VBA doesn't support multiple theads while Xeon processors I believe run as though its multiple processors. I'm surprised it's that much slower but I wouldn't have thought an great increase would of happened as your only using part of the processor rather than all of it. Thats what I think is happening.
You could maybe speed up the process by checking what else is running on the computer. Could be there is another application/service that kicks in during the macro and slows the process down. Also where is the data stored Could be that there is a bottleneck in getting the data if it's downloaded over the network or if the database is fairly well used.
Could also be something in the code that is slowing the macro down. A small flaw in the logic. Read a blog entry the other week in which the guy wrote about a situation where a simple change in his codes logic improved the speed by ten fold.
It's a bit difficult to say what is happening so these are just theories but if you want you can send me the 5k desktop computer I'll give it a go.
Fex
Thats was all I could think of. Without seeing the system set up there's not much else I can offer. Just wanted to highlight other areas that could have affected the speed of the software.
How much data are you processing
Chris Chubb
Hi Bret,
I'm not sure if you were able to work out this issue or not, but here's some info from the support engineer:
According to your problem, I know you have two PCs, 1st p4 @ 3.2; 3gb ram and 2nd ual core xeon @ 2.8; 4gb ram. You found it takes more time by 2nd when using them to proceed a VBA macro.
It isn’t abnomal, for the PC with the same CPU Clock Speed, whatever P4 or xeon, if you use it for the same work that xeon processers don’t optimise, the result will be nearly the same.
So, your 1st pc with faster CPU Clock Speed than the 2nd one, and since xeon doesn’t optimise the operation you take. The result 1st do work faster then 2nd is acceptable.
-brenda (ISV Buddy Team)