Why does not field with custom function to get name recalculate?

Question

I use a vba function to get cell name (taken from Retrieving Cell name in Excel)

Public Function CellName(oCell As Range) As Variant
  Dim oName As Name

  For Each oName In ThisWorkbook.Names
    If oName.RefersToRange.Parent Is oCell.Parent Then
        If Not Intersect(oCell, oName.RefersToRange) Is Nothing Then
            CellName = oName.Name
            Exit Function
        End If
    End If
  Next
  CellName = CVErr(xlErrNA)
End Function

When the name of the cell does not exist, it shows error -- and that is of course intended behaviour. However, when I then name the other cell (the one which name I want to get), the error in my cell is still active. Recalculating does not help. I need then to change the value of the other cell (I can change its value or change value of yet another cell that is in its formula), or its formula, so the value in that cell would be recalculated, so my cell with CellName function gets properly refreshed.

I don't see the point why, and what can I do to simple make the cell refresh when I name the cell I point to?

This is Excel 2007, file type xlsm.

Answer 1

I think you need to set the function as Volatile

at the beginning of your UDF, add this code:

application.volatile

see MSDN Library: Volatile Method [Excel 2003 VBA Language Reference]