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

Asha;

I didn't look at your UDF(), but this will calculate your entire workbook everytime you change anything in the workbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Me.Calculate
End Sub

Or if you only want to calculate the sheet that was changed:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Sh.Calculate
End Sub

Correct, Any Subs that have names starting with "Workbook_...." go into ThisWorkbook Module
Any Subs having names starting with "Worksheet_..." go into the module for the worksheet you want then to work on.
There are exceptions, but that's a pretty safe bet.

Asha;

I get a compile error "Method or data member not found" when I run the macro after adding your code.

My mistake. I didn't test it first.
The Line

Me.Calculate

Should be

Sh.Calculate

Instead of making the UDF volatile, you could pass the range of interest. Then it would recalculate only when needed.

Or, instead of recalculating all sheets on a change, you could calculate only the cell(s) in which the UDF appears.

If you want to calculate all the sheets in the workbook when any change is made on any sheet.

Try this in the workbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim i As Integer
    
    For i = 1 To Sheets.Count
        Sheets(i).Calculate
    Next i
End Sub

This will calculate all open workbooks

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.Calculate  'or just Calculate.
End Sub

Cheers