Functions executed for no reason?

[chamdan]

Hi Hans and/or Rudi,

Happy Easter first, I have a workbook, which has many worksheets and in each worksheet in cell "G2" I want the Sheet Name to appear and next to it in cell "H2" appears the Year of the project start, meanwhile in the Modules ("Macros") I have functions created to calculate Easter Date and more of the holidays referring to the "Canada".
Now the problem is that in the Object ("Sheet name") I have a Worksheet event such as "Worksheet_Activate" Event and when I debug the Worksheet_Activate Event works fine but for some weird reason the Public Functions that do the Holidays calculation get triggered?????

I have inserted the Worksheet_Activate Event and the Functions here below for you to see what is making the function to be triggered?

Code: Select all

'Microsoft Excel Objects
Private Sub Worksheet_Activate()
Dim ShtName As String
ShtName = ActiveSheet.Name
Range("$G$2").Value = ShtName
End Sub

Why would the function get triggered when they are not called????

Regards,

Chuck

Code: Select all

'Modules
Public Function NDow(Y As Integer, M As Integer, _
     N As Integer, DOW As Integer) As Date

'Function to Calculate Victoria Day
'=NDow(Year,Fifth Month,Fourth Week, Monday) =NDow(TheYear,5,4,2)
'Where 5 is the fifth month
'Where 4 is the fourth week
'Where 2 is the Monday

NDow = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1), _
  (DOW + 1) Mod 8)) + ((N - 1) * 7))

End Function

Public Function EasterDate(Yr As Integer) As Date
' Easter Date calculation

    Dim D As Integer
    D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
    EasterDate = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + _
        D + (D > 48) + 1) Mod 7)
End Function

[sdckapr]

If the workbook has calls to those functions and the values change the functions will be called. Are you using a formula like NOW() or TODAY() in any of the parameters when those functions are used in the worksheet? If so, those values are being re-evaluated and when they do the function is re-evaluated.

Steve

[chamdan]

Hi Steve,

Yes within the worksheet the function Today() is executed. Now I understand why. Thanks for the tip. So, any function that is within the Worksheet will be triggered when Worksheet_Event is activated, Changed or even a selection change is triggered. Make sense! I did not think about this.



Chuck

[HansV]

Any formula involving one or more of the functions RAND, NOW, TODAY, OFFSET, CELL, INDIRECT or INFO will always be recalculated when any cell value on the sheet is changed. Since the Worksheet_Activate code changes the value of cell G2, your formulas that use TODAY() will be recalculated.

[chamdan]

Hans.