PYA_Ferro


Hi all, In my macro I used this sentences to speed it up:

Application.ScreenUpdating = False
Application.EnableEvents = False

But I've also created some functions and they are beeing recalculated every time my macro do a change in a worksheet.

Is there any way to disable the calculation of functions

Thanks and really sorry for my English.




Re: SpeedUp a Macro in Excel - Disable Functions

MyLady


Hey,

With ActiveSheet

.EnableCalculation = False

.EnableCalculation = True

.Calculate

End With

Best Regards

Cathrine






Re: SpeedUp a Macro in Excel - Disable Functions

PYA_Ferro

Nope sorry, that only stops the Functions in the Worksheet, but no the Functions I've created in my module

This is a pice of my colde:

Code Block

Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.EnableCalculation = False

Range("N6").Select

While (ActiveCell.Value <> "FIN")
If ActiveCell.Value = 0 Then
Selection.EntireRow.Hidden = True
End If

ActiveCell.Offset(1, 0).Select
Wend

Application.EnableEvents = True
ActiveSheet.EnableCalculation = True
ActiveSheet.Calculate
Application.ScreenUpdating = True

Every time the line "Selection.EntireRow.Hidden = True" is executed, all my Funcions (created in another module) are also executed, I want to temporary disable them.






Re: SpeedUp a Macro in Excel - Disable Functions

MyLady

well...

With ActiveSheet

.EnableCalculation = False

.EnableCalculation = True

.Calculate

End With

Code Block

Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.EnableCalculation = False

Range("N6").Select

While (ActiveCell.Value <> "FIN")
If ActiveCell.Value = 0 Then
Selection.EntireRow.Hidden = True
End If

ActiveCell.Offset(1, 0).Select
Wend

Application.EnableEvents = True
ActiveSheet.EnableCalculation = True
ActiveSheet.Calculate

Application.ScreenUpdating = True





Re: SpeedUp a Macro in Excel - Disable Functions

PYA_Ferro

I have added those lines after reading your post of course

It does not stop the functions I've acreated in my modelues from runing.





Re: SpeedUp a Macro in Excel - Disable Functions

bi-lya

Hi!
You may declare Public variable as Boolen, and enable function using it
See the simple example

Code Block

Public flag As Boolean

Sub MySub()

' flag = True

If flag = True Then MsgBox MyFunction(1, 2)
End Sub

Function MyFunction(a, b)
MyFunction = a + b
End Function

'''''''''OR'''''''''''''

Function MyFunction(a, b)
If flag = True Then
MyFunction = a + b
End If
End Function





Re: SpeedUp a Macro in Excel - Disable Functions

PYA_Ferro

Thanks, I'll try that.

I have a lot of functions so I'll have to add a flag in every one of them, but it is better than letting the function to run.

Also, the flag doesn't "disable" the function, they are still getting called, but once inside the function, they exit immidiatly.

(sorry again for my English)

Cheers.





Re: SpeedUp a Macro in Excel - Disable Functions

Mezhick

You can use .Calculation property for this. For example:

Code Block

Application.Calculation = xlCalculationManual

Range("A1:B5", "C9").Calculate

And after the macro is done:

Code Block

Application.Calculation = xlCalculationAutomatic






Re: SpeedUp a Macro in Excel - Disable Functions

PYA_Ferro

Works Works Works!!

Application.Calculation = xlCalculationManual

It dissable ALL the functions, also the ones I've created in my Subs.

Many Thanks Mezhick !!!