HWL
Active Member
- Joined
- Dec 1, 2009
- Messages
- 462
Mr.Excel members have been so great answering my questions, I thought I should share some things to help people.
To improve functional calculation speed, I use a formula that I can put at the start of some complex formula so that it will only calculate when that reliant sheet is active.
What this will do is everytime automatic calculation happens, with each press of enter key or input/change of a cell; this will NOT "run" the complex formula unless the correct, reliant sheet is active. This saves alot of processor time.
To improve VBA/Macro speed, I use the following code at the start and end of my macro. Keep in mind that you should consider if your code is going to require calculation as it runs. If it does, you will need to place the start code after that calculation need.
To improve functional calculation speed, I use a formula that I can put at the start of some complex formula so that it will only calculate when that reliant sheet is active.
Code:
=IF(RIGHT(CELL("filename"),3)<>"insert last 3 letters of reliant sheet name here","",else complex formula here)To improve VBA/Macro speed, I use the following code at the start and end of my macro. Keep in mind that you should consider if your code is going to require calculation as it runs. If it does, you will need to place the start code after that calculation need.
Code:
'Display wait for a moment
Application.StatusBar = "****Please Wait***** Macro processing"
'opitmize macro by disabling all processes that slow it down.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
' bulk of code here
'Re-enable screenupdating (before END SUB)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.StatusBar = False
