How to get UDF to calculate on WB open?

[jbaich]

Hey all, I have this UDF to emulate the Subtotal function for calculating the median on visible cells only within a range, but it seems to rarely (if ever) calculate when I open the workbook... I have to enter or tab through each cell containing the function in order to get a value rather than the #NUM error. Is there anything I can do to fix this? here is the function:

Option Explicit

Function MEDIANSUBTOTAL(r As Range)
    Dim v As Variant
    v = Evaluate("MEDIAN(IF(SUBTOTAL(2,OFFSET(" & r.Address & ",ROW(" & r.Address & ")-MIN(ROW(" & r.Address & ")),0,1))," & r.Address & "))")
    If IsError(v) Then
        MEDIANSUBTOTAL = CVErr(xlErrNum)
    Else
        MEDIANSUBTOTAL = v
    End If
End Function

I was hoping i could use the IFError function with it to prompt it to calculate, but that doesn't seem work...

Thanks,
joe

 

[Ruddles]

Try putting an Application.Calculate in your Workbook_Open or maybe put Application.Volatile in your UDF:-

Excel VBA Volatile Functions - EASY Excel Macros