• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Good practise steps in addition to Application.ScreenUpdating = False

ThrottleWorks

Excel Ninja
Hi,

This might appear as silly post. However I need guidance on below.
I use below mentioned four lines at start of every macro I write (and kill at the end).

ScreenUpdating I know will run macro faster.

DisplayAlerts I know, will not throw un-necessary prompts.

I wanted to know if I should anything more as good practise to below lines.
Please help if you get time.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.CutCopyMode = False
Application.StatusBar = "Macro running"
 
Hi !

I you have any event code whatever in workbook or worksheet module
and in case this event must not raises during the execution of your code
so you need to disable events while running :
see Application.EnableEvents

For formulas and big data (even if Excel is not the appropriate choice)
see Application.Calculation to avoid any slow down during processing
when formulas are calculated …
 
i use two subroutines

Code:
Public Sub TurnOffFunctionality()
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
End Sub
&
Code:
Public Sub TurnOnFunctionality()
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

so in use you use

Code:
Sub myMacro()
TurnOffFunctionality

'your code here

TurnOnFunctionality
End Sub

This allows the functions to be turned on/off as required and they can be called from multiple subroutines within the one project if required
 
Hi @Hui sir,

One doubt about Application.Calculation = xlCalculationManual.
If I do this, I need force calculate worksheet every time I apply formula.

Please correct me if I am wrong. Thanks for the help.
Have a nice day ahead. :)
 
Hi @Hui sir,

One doubt about Application.Calculation = xlCalculationManual.
If I do this, I need force calculate worksheet every time I apply formula.

Please correct me if I am wrong. Thanks for the help.
Have a nice day ahead. :)
Not really unless you want to see calculation for each cell as it is getting applied at runtime.

Kenneth Hobson had posted a KB entry in VBAExpress on this which is similar to what Hui has posted above:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035
 
Hi @shrivallabha nice to see you after long time !

I will try to explain my doubt, I have formula as C1 = A1+B1.
My next code line is if C1 is greater than X then do something else do nothing.

In this case I need to calculate my sheet. I tried this using dummy values.
And I was required to calculate sheet before going to if C1 is greater than X then do something else do nothing.

Suppose C1:C5 is range and C1 = 2 then without calculating worksheet C1:C5 will reflect as 2.
 
Please see below values for your reference. Formula used in cell C1 is =A1+B1.
I am not saying you or @Hui sir is wrong. However I believe I need to calculate sheet every time I enter the formula.
Especially if result in the formula will be used in the next code line.

? range("A1").Value
1

? range("B1").Value
1

? range("C1").Value
2


? range("A2").Value
2

? range("B2").Value
2


? range("C2").Value
2

activesheet.calculate

? range("C2").Value
4
 
If your code is changing values and subsequent code relies on the calculations involving those cells, yes, you need to calculate before you do the comparisons
 
Hi @Hui sir, thanks a lot for the help. :)

In this case, I would need to change my habit and make sure that I calculate sheet without failing, else my macro will fail. :)
 
When you posted your question it appeared as if you wanted response to general case scenario.

In above scenario, you can do sheet calculate or you can choose to implement the same formula recursively like you are changing inputs in the cell and excel will calculate result for the cell.

Code:
Sub Test()
Application.Calculation = xlCalculationManual
Range("A1").Value = 1
Range("B1").Value = 1
Range("C1").Formula = "=A1+B1"
Range("A1").Value = 2
Range("B1").Value = 2
Range("C1").Formula = "=A1+B1"
End Sub

It will show you correct results. But overall, it is risky proposition.
 
In my opinion, it is better to store the current values of those settings at the start of your routine and then reset them to those initial values at the end. Otherwise, if you call one routine from another, it will slow your code down since the called routine will reset them to the defaults, not to whatever your initial routine had done.
 
Back
Top