Prevent Function calls in worksheet when running macro

[flea333]

I have a bunch of functions in a worksheet ( =MyFunc(A1) ) and when I am stepping through my macro in that same spreadsheet it keeps jumping back and running through the function calls. This is annoying and clearly adding overhead to my script. How should I stop this?
I was thinking of eliminating the live function calls in the spreadsheet and just writing the data in one time.

[Bob Phillips]

Turn calculation off, and reset afterwards.

[flea333]

thanks. Created a function to turn calculations and screenupdating on/off

Function CalcSet(x As Boolean)
If x Then
    With Application
        .ScreenUpdating = True 'Turns on screen updating
        .Calculation = xlCalculationAutomatic 'prevents function calls in spreadsheet during macro
    End With
Else
    With Application
        .ScreenUpdating = False 'Turns off screen updating
        .Calculation = xlCalculationManual 'prevents function calls in spreadsheet during macro
    End With
End If
End Function

[mdmackillop]

Remember to add error handling to call CalcSet and avoid other problems.

[flea333]

you mean, On Error Goto 0?

I haven't done much error handling, so far I've wanted the errors so I know where the code screwed up.

[mdmackillop]

Sub Test()
Call calcset(True)
On Error GoTo Exits
'Do your stuff
Exits:
Call calcset(False)
End Sub

[Ken Puls]

I use a function to record the state of the calculation before I start, and reset it to that when I'm done. (There are sometimes where I'm working in manual mode and want to keep it like that.)

Dim xlCalcState As Long
Public Sub Environ_RestoreSettings()
'Macro created 06/11/2005 22:33 by Ken Puls
'Macro Purpose: To restore application properties to user settings
'Restore screen updates, clear statusbar and set
'calculation back to user's initial setting
    With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalcState
    .StatusBar = False
    End With
    'Set the calculation state variable to 0
    xlCalcState = 0
End Sub

Public Sub Environ_SpeedBooster()
'Macro created 06/11/2005 22:29 by Ken Puls
'Macro Purpose:  To set application properties to maximize speed
    With Application
    'Add workbook if necessary to avoid calculation error messages
    If .Workbooks.Count = 0 Then .Workbooks.Add
    'Record current calculation state
    xlCalcState = Application.Calculation
    'Turn off screen updates and set calculation to manual
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
    End With
End Sub

Then when running in your macro:

On Error GoTo ErrHandler
'Do whatever your macro would normally do
Exit Sub
ErrHandler:
Call Environ_RestoreSettings
End Sub

[Bob Phillips]

I use a function to record the state of the calculation before I start, and reset it to that when I'm done. (There are sometimes where I'm working in manual mode and want to keep it like that.)

I do something similar, but I actually found a use fir the Type construct here

http://tinyurl.com/2fj628z

[Ken Puls]

Ah... nice stuff there, Bob.