UDF - Auto refresh issue

Greetings,

I have a UDF that uses data from one sheet to display result in a second sheet. I added the “Application.Volatile” statement so that the result in the second sheet is automatically refreshed if data in the first sheet changes.

However, I have now noticed that if I run any macro in the workbook, the UDF gives “#VALUE!”. I have to manually click in the cell and hit the ENTER button in order to get the result.

A solution offered was to add the line

Calculate

before the "End Sub" to calculate the workbook before the macro ends.

However, I have several macros in the my workbook and was wondering if something can be done at the workbook level instead of the adding the “calculate” line in every macro?

The UDF is

Function pFindRowPos(sText As Variant, _
  Optional SearchDirection As XlSearchDirection = xlNext, _
  Optional SearchOrder As XlSearchOrder = xlByRows) As Long

Application.Volatile

Dim lResult As Long, oRg As Range


With Worksheets("PDFDump").Cells
    Set oRg = .Find(What:=sText, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=SearchOrder, _
                 SearchDirection:=SearchDirection, _
    MatchCase:=False, SearchFormat:=False)

    If Not oRg Is Nothing Then lResult = oRg.Row

    pFindRowPos = lResult

    Set oRg = Nothing
End With
End Function


' Input param: Text we want to look for
' Optional input params:
'     Search direction (forward, backward),
'     Search order (in row or column)
' Output: row position of the text being searched

'Asha: Source http://excelvbamacro.com/how-to-find-row-position-of-a-particular-text/

Many thanks
Asha