PDA

View Full Version : [SOLVED] Prevent Function calls in worksheet when running macro



flea333
06-25-2010, 12:05 PM
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
06-25-2010, 12:09 PM
Turn calculation off, and reset afterwards.

flea333
08-25-2010, 01:27 PM
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
08-25-2010, 02:16 PM
Remember to add error handling to call CalcSet and avoid other problems.

flea333
08-25-2010, 02:26 PM
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
08-25-2010, 02:39 PM
Sub Test()
Call calcset(True)
On Error GoTo Exits
'Do your stuff
Exits:
Call calcset(False)
End Sub

Ken Puls
08-25-2010, 02:47 PM
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
08-25-2010, 05:17 PM
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
08-25-2010, 09:32 PM
Ah... nice stuff there, Bob. :)