Prevent functions from running while macro is running

**palpha32** · 03-23-2009, 10:36 PM

My worksheet contains several custom functions. The functions calculate, as usual, when data is entered. How do I prevent the functions from recalculating while a macro is running? The macro carries out calculations as well, and each time data is entered by the macro the functions recalculate. I have tried

Application.EnableEvents = False

and

Application.Volatile False

but they do not stop the functions. Are there any suggestions that may help.
Thankyou
Peter

**Greg M** · 03-23-2009, 10:52 PM

Hi Peter,

It's not particularly elegant or high-tech, but the following approach might be suitable for preventing your user-defined functions from recalculating while your macro is executing:

Option Explicit


Dim bMacroRunning As Boolean


Function MyFunction(iMyInput As Integer) As Integer

    If bMacroRunning = True Then Exit Function

'   This is where MyFunction is defined
    MyFunction = iMyInput ^ 2

End Function


Sub MyMacro()

    Dim i As Integer

    bMacroRunning = True

'       This is where all the MyMacro code is entered
        For i = 1 To 10
            MsgBox "This is iteration no " & i
        Next i

    bMacroRunning = False

End Sub

Hope this helps - please let me know how you get on.

Regards,

Greg M

**palpha32** · 03-24-2009, 12:25 AM

Thanks for the reply Greg. I can't make it work so could you give me an explanation of the process
Thanks
Peter

**DonkeyOte** · 03-24-2009, 04:24 AM

You could set Calc to Manual prior to running the routine, this should not invoke the function at all.

Public Sub YourMacro()
Dim xlCalc As XlCalculation
With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
End With
...your code
With Application: .Calculation = xlCalc: End With
End Sub

**Greg M** · 03-24-2009, 08:42 PM

Hi again,

The statement:

If bMacroRunning = True Then Exit Function

must be inserted in EACH of the user-defined functions which you wish to prevent from calculating while the MyMacro routine is executing.

The variable bMacroRunning is defined as a module-level variable (i.e. at the top of the Code Window) - this means that it is available to all routines contained in the module.

As soon as the MyMacro routine is executed, the bMacroRunning variable is set to TRUE. Whenever your user-defined functions are called (from the worksheet), the above If statement causes control to exit immediately from the user-defined function.

When the MyMacro routine has executed, the bMacroRunning variable is set to FALSE - this means that user-defined functions will be calculated, as the result of the above If statement will be FALSE.

Hope this helps - please let me know how you get on.

Regards,

Greg M