UDF - Function Address to Values - Trig functions issues?

[justhumm]

I recently put together a UDF function similar to the native FORMULATEXT() function in excel. Instead of displaying the formula with the referenced cell addresses, the UDF displays the formula with the referenced cell values.

So far, it seems to be functioning like I want it to, for now (only works for single column names - "D", NOT "DA", etc. - and rows between 1-999).

However there's something weird going on when working with TRIGONOMETRY functions (sin, cos, radians, etc.). The UDF is working, until it gets to the last cell reference and it seems to be jumping out of the loop and just displays the last cell address in the function. (will post picture)

Does anyone know if there's some property/behavior of Excel's native trig functions that would be causing this?

Thanks!

Public Function AddrToVal(CellRng As Range) As String
'==================================================================
' This UDF is similar to the standard Excel FORMULATEXT() function,
' but it displays the cell values instead of the cell addresses.
' version 001: August 2017
'==================================================================
    
    Dim i As Integer, j As Integer, k As Integer
    Dim xChar1 As Integer, xChar2 As Integer
    Dim xChar3 As Integer, xChar4 As Integer, xChar5 As Integer
    Dim Form As String
    Dim CellAddr As String
    'Application.Volatile

    Form = CellRng.Formula
    Form = Replace(Form, "$", "")

    For i = 1 To Len(Form)
    xChar1 = 0: xChar2 = 0: xChar3 = 0: xChar4 = 0: xChar5 = 0
    
        If i < Len(Form) Then   'IF01==========
            xChar1 = Asc(Mid(Form, i, 1))
            xChar2 = Asc(Mid(Form, i + 1, 1)): On Error Resume Next
            xChar3 = Asc(Mid(Form, i + 2, 1)): On Error Resume Next
            xChar4 = Asc(Mid(Form, i + 3, 1)): On Error Resume Next
            xChar5 = Asc(Mid(Form, i + 4, 1)): On Error Resume Next
   
            If xChar1 < 65 Or xChar1 > 90 Then  'IF02==========
                GoTo GoToHere01
            ElseIf 3 = 3 Then
                If xChar2 >= 48 And xChar2 <= 57 _
                And xChar3 >= 48 And xChar3 <= 57 _
                And xChar4 >= 48 And xChar4 <= 57 Then
                    CellAddr = Chr(xChar1) & Chr(xChar2) & Chr(xChar3) & Chr(xChar4)
                    Form = Replace(Form, CellAddr, Range(CellAddr).Text)
                    i = 1
                ElseIf 2 = 2 Then
                    If xChar2 >= 48 And xChar2 <= 57 _
                    And xChar3 >= 48 And xChar3 <= 57 Then
                        CellAddr = Chr(xChar1) & Chr(xChar2) & Chr(xChar3)
                        Form = Replace(Form, CellAddr, Range(CellAddr).Text)
                        i = 1
                    ElseIf xChar2 >= 48 And xChar2 <= 57 Then
                           CellAddr = Chr(xChar1) & Chr(xChar2)
                           Form = Replace(Form, CellAddr, Range(CellAddr).Text)
                           i = 1
                    End If
                End If
            End If  'IF02==========
        End If 'IF01==========
GoToHere01:
    
    'Debug.Print Form
    Next i

AddrToVal = Form
End Function

 

[Norie]

Can you post an example formula that causes the UDF to display the behaviour you describe?

 

[justhumm]

 

[justhumm]

This is what I'm expecting:

FORMULATEXT = 100+$A$3+$A$4-$A$6/$A$10*($A$11/10)^$A$13/$A$100^$A$101+$A$103

ADDRTOVAL (UDF) = 100+10.00+5-57.00/10.00*(5/10)^57.00/10.000^10.001+10.003


This is an example of the issue that's coming up:

FORMULATEXT = =COS(RADIANS($A$107))*SIN(RADIANS($A$107))/$A$3*2*$A$13

ADDRTOVAL (UDF) = =COS(RADIANS(172.00))*SIN(RADIANS(172.00))/10.00*2*A13

...thus far, it only seems to have this behavior for formulas involving trigonometry functions.

 

[Norie]

What are the formulas in column A?

Without them it's hard to test your UDF.

 

[Rick Rothstein]

Also your code will not work if the formula references a cell on a different worksheet. The following code is a macro, not a UDF (because a UDF cannot do the things necessary to visit another worksheet in order to get the cell values from there). You would use this macro by selecting a single cell or a range of cells within the same column and then running the macro... the output is placed in the next column adjacent to the originally selected cell or cells.

[table="width: 500"]
[tr]
	[td]Sub CheckCellReferences()
  Dim ShapeCount As Long, Arrow As Long, Link As Long, Addr As String, Frmla As String
  Dim Cell As Range, CurrentCell As Range, OriginalSheet As String, OriginalCell As String
  Application.ScreenUpdating = False
  OriginalSheet = ActiveSheet.Name
  OriginalCell = ActiveCell.Address
  ShapeCount = ActiveSheet.Shapes.Count
  For Each Cell In Selection
    Set CurrentCell = Cell
    Frmla = Replace(CurrentCell.Formula, "$", "")
    If CurrentCell.HasFormula Then
      CurrentCell.ShowPrecedents
      Link = 1
      For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
        On Error Resume Next
        Do
          CurrentCell.Parent.Activate
          CurrentCell.Activate
          Addr = CurrentCell.NavigateArrow(True, Arrow, Link).Address
          If Err.Number Then
            Link = 1
            Exit Do
          End If
            If OriginalSheet <> ActiveCell.Parent.Name Then
              Frmla = Replace(Frmla, ActiveCell.Parent.Name & "!" & ActiveCell.Address(0, 0), ActiveCell.Value)
              Frmla = Replace(Frmla, "'" & ActiveCell.Parent.Name & "'!" & ActiveCell.Address(0, 0), ActiveCell.Value)
            Else
              Frmla = Replace(Frmla, ActiveCell.Address(0, 0), ActiveCell.Value)
            End If
          Link = Link + 1
        Loop
        Cell.Offset(, 1).NumberFormat = "@"
        Cell.Offset(, 1).Value = Frmla
      Next
      CurrentCell.ShowPrecedents Remove:=True
    End If
    Worksheets(OriginalSheet).Activate
    Range(OriginalCell).Activate
  Next
  Application.ScreenUpdating = False
End Sub[/td]
[/tr]
[/table]

NOTE: If your formula has a text value that looks like a cell reference, for example the A12 in "Serial Number A12-345", and one of the cell references in the formula is actually A12, then the A12 inside the text constant will be replace along with the actual cell reference in the formula... I do not know a way around this should it occur.

 

[justhumm]

Norie:

For the screenshot above

the A-column cells just contains randomly entered constants or randomly entered formulas.
A113: =ABS($A$111)+A115

the C-column contains my UDF function that references the A-column
C113: =AddrToVal(A113)

and the D-column contains a FORMULATEXT() reference to the A-column.
D113: =FormulaText(A113)

 

[justhumm]

Rick:

Thanks for the reply and thanks for the code.

I realize the UDF is local to the active worksheet. What I'm looking for, right now, is a simple-to-use UDF that can be placed in a variety of cells around a worksheet and would be updated as the referenced cells are updated.

The idea behind this UDF is to assist with / simplify the checking of hardcopy prints of engineering calculations that were done in Excel...
A print-out that say "5 = 2 + 3"
Is more useful than a print-out that just says "5"