Search code examples
excelvbauser-defined-functionsvolatileworksheet-function

Result of volatile UDF isn't shown if I edit another Sheet and switch back

I'm using a volatile UDF which is working basically as it should. I pass a range to it and if a value in this range changes, it recalculates as it should. But when I switch to another sheet and edit for example a cell and then switch back, I see no output of this UDF.

I've I change a cell value on the sheet containing the UDF or save the file, it shows the output of the UDF again. I also can do a

' Refresh all Calculations if Sheet is activated
Private Sub Worksheet_Activate()
    Application.CalculateFull
End Sub

But I don't think this is a nice solution if not critically needed, as the sheet contains hundreds of formulas.

I've also checked UDF #Value! when switching sheets which seems to be similar problem (with no real answer).

' Returns Tags-String based on Range/Threshold
Public Function GetTagsString(rngRange As Range) As String
    ' Define Variables
    Dim strTags As String
    Dim strTagSeparator As String
    Dim strTag As String
    Dim intTagRow As Integer
    Dim intTagValue As Integer
    Dim dblTagMinScore As Double
    Dim rngCell As Range

    ' Initialize Values
    intTagRow = Sheets("Locations").Range("TagsRow").Value
    dblTagMinScore = Sheets("Settings").Range("TagMinScore").Value
    strTagSeparator = Sheets("Settings").Range("TagSeparator").Value
    strTags = ""

    ' Loop through all Cells in Range
    For Each rngCell In rngRange
        intTagValue = rngCell.Value
        strTag = Cells(intTagRow, rngCell.Column).Value

        ' Include Tag if equal/greater than Tag-Threshold
        If (intTagValue >= dblTagMinScore) Then
            If (Not strTags = "") Then
                ' String contains already Tags => append Tag-Separator
                strTags = strTags & strTagSeparator & strTag
            Else
                strTags = strTag
            End If
        End If
    Next rngCell

    ' Return Tags-String
    GetTagsString = strTags
End Function

I'm calling this UDF by:

=GetTagsString(INDIRECT(ADDRESS(ROW();COLUMN(TagAmusement);4)):INDIRECT(ADDRESS(ROW();COLUMN(TagFun);4)))

TagAmusement and TagFun are named cells. I know using INDIRECT is maybe not the best solutions, but because of several reasons I need to be this dynamic. I'm doing this in a lot formulas, but without using an UDF and without the same problem. The problem must have to do something with the UDF, but I don't think it's because of this function parameters. It must have something to do with changing another sheet and switching back to the original sheet.

And yes, I read some values from the sheet, but I've also tried to pass them too and it doesn't make a difference (I also don't change(d) them).

The only thing what works (on an automated base) is:

' Refresh all Calculations if Sheet is activated
Private Sub Worksheet_Activate()
    Application.CalculateFull
End Sub

The problem only occurs if I change the sheet and do "something" (like editing a cell) there.

Is this a bug of Excel or what do I overlook?

Solution

  • strTag = Cells(intTagRow, rngCell.Column).Value is the same as strTag = ActiveSheet.Cells(intTagRow, rngCell.Column).Value

    So if rngRange is on "Sheet1" but you then switch to "Sheet2" and edit a cell (which triggers a recalculation), the value read into strTag will come from Sheet2 and not from Sheet1. If the corresponding cells on Sheet2 happen to be empty then it will look as though the UDF isn't returning anything.

    To prevent this, specify the worksheet that the call to Cells applies to:

    strTag = rngRange.Worksheet.Cells(intTagRow, rngCell.Column).Value

    or wrap the whole For Each loop in a With...End With block and make the call to Cells use that object:

    With rngRange.Worksheet
    For Each rngCell In rngRange
    '...
        strTag = .Cells(intTagRow, rngCell.Column).Value
    '...
    Next rngCell
End With