Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

UDF - Hidden Cells Not Calculating?

UDF - Hidden Cells Not Calculating?

UDF - Hidden Cells Not Calculating?

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
    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
    GoSub Evaluate
    Exit Function
    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)
        AddrToVal = AddrToVal & eval & Mid(Form, i, 1)
    End If
    On Error GoTo 0
    p1 = i + 1

End Function 

RE: UDF - Hidden Cells Not Calculating?

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close