UDF - Hidden Cells Not Calculating?

[justhumm]

A few months ago, I wrote a UDF that has generally been working the way I want it to. The UDF is similar to the standard Excel FORMULATEXT() function, but it displays the cell values/contents instead of the cell address.

I just noticed, while working with a large table of data with some hidden columns, that the UDF "doesn't work" when it references hidden cells.

What I'm assuming is happening is that, since the UDF uses the formatting of the referenced cells, the referenced hidden cell values are just being hidden.

Off the top of my head, I'm thinking of throwing a statement into the code that has the effect of...

If Cell is Hidden​

Then Treat Cell Like It's Not Hidden​

Though never really having worked with hidden cells in VBA, I'm not sure what the best way to do that is.

Any suggestions? Thanks!

Public Function AddrToVal(rCell As Range) As String
    'Application.Volatile
    Dim i As Integer, p1 As Integer, p2 As Integer
    Dim Form As String, eval As String
    Dim r As Range
    
    Form = rCell.Formula
    Form = Replace(Form, "$", "")
    'AddrToVal = "="
    AddrToVal = " "
    
    p1 = 2
    For i = 2 To Len(Form)
        Select Case Mid(Form, i, 1)
            'Case "(", ")", ",", "+", "-", "*", "/", ":", "&", "^"
            Case "'*'!", "(", ")", ",", "+", "-", "*", "/", ":", "&", "^"
                GoSub Evaluate
        End Select
    Next
    GoSub Evaluate
    Exit Function
    
Evaluate:
    p2 = i - 1
    eval = Mid(Form, p1, p2 - p1 + 1)
    On Error Resume Next
    Set r = Range(eval)
    If Err.Number = 0 Then
        'AddrToVal = AddrToVal & Range(eval).Value & Mid(Form, i, 1)
        AddrToVal = AddrToVal & Range(eval).Text & Mid(Form, i, 1)
    Else
        AddrToVal = AddrToVal & eval & Mid(Form, i, 1)
        Err.Clear
    End If
    On Error GoTo 0
    p1 = i + 1
    Return

End Function

 

[combo]

It seems that Text property of hidden cell is "", use Value instead. At least this happens in some cases for numeric contents.

combo