How to speed up VBA program in Excel?
 

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

How to speed up VBA program in Excel?
 

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

How to speed up VBA program in Excel?
 

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


Code:
--------------------

Function MyFunction() As String
Application.Screenupdating = false

....code goes here

Application.Screenupdating = True
End Function

--------------------



Code:
--------------------

Sub CommandButton1_Click()
Application.Screenupdating = false
....code goes here
Application.Screenupdating = True
End Sub



--------------------


--
okelly
------------------------------------------------------------------------
okelly's Profile: http://www.excelforum.com/member.php...o&userid=36708
View this thread: http://www.excelforum.com/showthread...hreadid=572195

How to speed up VBA program in Excel?
 

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" wrote in message
ups.com...
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

How to speed up VBA program in Excel?
 

Thanks a lot! it does helps!