How to speed up VBA program in Excel?

[shineofleo]

Hi Everyone,

I wrote a Macro in VBA that calculate something, and there are three
loops in it. In short, it runs terribly slow... which is obvious...
because the program displays all the change of certain values on screen
during the processing...

My question is how to speed up it? How to put the computing behind
screen just like C programm... such as 'compile first then run' style?

I really need some help because I was a C/C++/VC programmer other than
VBA programmer... any idea would be welcomed.

Thank you in advance!



Leon

[Thyagaraj]

At the Beginning of your code add - Application.Screenupdating = false
At the End of your code add - Application.Screenupdating = True
Now Run your macro

Regards
Thyagaraj

shineofleo wrote:
> Hi Everyone,
>
> I wrote a Macro in VBA that calculate something, and there are three
> loops in it. In short, it runs terribly slow... which is obvious...
> because the program displays all the change of certain values on screen
> during the processing...
>
> My question is how to speed up it? How to put the computing behind
> screen just like C programm... such as 'compile first then run' style?
>
> I really need some help because I was a C/C++/VC programmer other than
> VBA programmer... any idea would be welcomed.
>
> Thank you in advance!
>
>
>
> Leon

[okelly]

do they go inside the Function & Sub tags as follows???

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Bernie Deitrick]

Better is this:

Sub YourSub()
With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
End With

'Your code here

With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
.Calculation = myCalc
End With
End Sub

HTH,
Bernie
MS Excel MVP


"shineofleo" <[email protected]> wrote in message
news:[email protected]...
> Hi Everyone,
>
> I wrote a Macro in VBA that calculate something, and there are three
> loops in it. In short, it runs terribly slow... which is obvious...
> because the program displays all the change of certain values on screen
> during the processing...
>
> My question is how to speed up it? How to put the computing behind
> screen just like C programm... such as 'compile first then run' style?
>
> I really need some help because I was a C/C++/VC programmer other than
> VBA programmer... any idea would be welcomed.
>
> Thank you in advance!
>
>
>
> Leon
>

[shineofleo]

Thanks a lot! it does helps!

[Kim Greenlee]

If the looped code is parallelizable (ie. there are no dependencies between
iterations), then executing the code on a compute grid is an option. On
this page (http://www.digipede.net/products/whitepaper.html) is a link to a
whitepaper that defines 4 different ways to use a compute grid to speed up
Excel. The whitepaper is called "Running Microsoft Excel on the Digipede
Network".

If you are a C/C++ developer then the pattern of moving the computation to a
DLL might work for you. It depends on whether there is any Excel specific
calls in that code section.

Good luck,

Kim

--
digipede - Many legs make light work.
Grid computing for the real world.
http://www.digipede.net
http://krgreenlee.blogspot.net