Search code examples
vbaexcelvolatile

Retrieving Sheets/Worksheets objects without making the function volatile

I'm trying to make a non volatile function that accepts an index and two ranges and makes certain operation on the sheet's ranges.

Unfortunately the Sheets or Worksheets functions make the function volatile thus each time I edit the worksheet the function starts recalculation.

Here's some pseudocode:

Function FooFunc(sheetIndex As Integer, xRange As Variant, yRange As Variant) As Variant    

   Dim operatingSheet As Variant

   'This makes the function volatile
   Set operatingSheet = Sheets("Bar " & sheetIndex)

   [...]

End Function

Apparently declaring Application.Volatile (False) doesn't seem to change anything.

As per title: is there a way to retrieve Sheets/Worksheets object without making the function volatile?

Solution

  • I can't reproduce your observation that accessing a worksheet from a VBA UDF causes that UDF to be volatile (Excel 2013).

    Simple UDF:

    Function Tester(num, rng As Range)

    Dim ws As Worksheet, rv

    Set ws = ThisWorkbook.Sheets(num)

    rv = num & ":" & rng.Value & ":" & ws.Name
    Debug.Print rv

    Tester = rv

End Function

    enter image description here

    I only see debug output from this UDF when editing the input ranges A2 and A3: other edits or calculations on the same sheet do not trigger output.

    The only way i can see what you describe it to add Application.Volatile to the UDF.