Convert Cell References To Values

Question

Is there any way (code) to have the values of cell references used in the formulae of a range be converted to their respective values? For example a cell like:

D1: = B1 & " is " & A1 + A2 + (C1 + C2) / 45

with

B1=My Number is (i.e. a Text)

A1=2

A2=3.5

C1=4

C2=10

being converted with respective cells' values like:

D1: = "My Number is" & " " & 2 + 3.5 + (4 + 10) / 45

Answer 1

Again an XClent and superb collection of valid arguments. However, the main goal has remained to satisfy my boss who is a formula hater specially while the same have references. I think the current code would convert the formulas to work in the same fashion as had been the original ones with references, otherwise would result into a #Name Error in case of a string.

But sure would love to hear from u in respect of displayed values as well. Might come in handy anytime.

Okay, here is a new modification of my code to which I added an optional second argument which controls whether the function returns the actual value in the cell or the formatted value for the cell. The optional argument is a Boolean which defaults to False, meaning display the actual value in the cell (True means display the formatted value for the cell). I took the liberty to change how and when quote marks are displayed. When the optional argument is False (display actual values), Text is quoted and so are dates. I chose to quote dates because I figured without them, it might be hard to tell if 6/15/12 was a date or a division of three numbers. When the optional argument is True (display formatted value), text, dates and numbers that are not "pure" are quoted. A pure number would be one that is composed of only digits, thousands separators (if present) and a single decimal point. If you disagree with any of these decisions, let me know and I'll modify the code accordingly.

Function ExpandFormula(Cell As Range, Optional UseCellFormatting As Boolean) As String

  Dim IsText As Boolean, CellNavArrow As Range

  Dim X As Long, Z As Long, OldShapeCount As Long, ArrowCount As Long

  Dim CurrentSheetName As String, PreviousCell As String, StartSheet As String, CellVal As Variant

  StartSheet = ActiveSheet.Name

  Application.ScreenUpdating = False

  Cell.Parent.Activate

  CurrentSheetName = ActiveSheet.Name

  If Cell.HasFormula Then

    ExpandFormula = Replace(Cell.Formula, "$", "")

    OldShapeCount = ActiveSheet.Shapes.Count

    Cell.ShowPrecedents

    ArrowCount = ActiveSheet.Shapes.Count - OldShapeCount

    On Error Resume Next

    Do

      X = X + 1

      IsText = False

      If Cell.NavigateArrow(True, X).Parent.Name <> CurrentSheetName Then

        Do

          Z = Z + 1

          Set CellNavArrow = Cell.NavigateArrow(True, X, Z)

          If CellNavArrow.Count = 1 Then

            CellVal = CellNavArrow.Value

            If Not WorksheetFunction.IsNumber(CellVal) Then IsText = True

            If IsText And Not CellVal Like "*[!0-9.]*" And Not CellVal Like "*.*.*" And _

               Len(CellVal) > 0 And CellVal <> "." Then IsText = False

            If UseCellFormatting Then

              CellVal = WorksheetFunction.Text(CellNavArrow.Value, CellNavArrow.NumberFormat)

            End If

            If IsText Then CellVal = """" & CellVal & """"

            ExpandFormula = Replace(ExpandFormula, "'" & CellNavArrow.Parent.Name & _

                            "'!" & CellNavArrow.Address(0, 0), CellVal)

            ExpandFormula = Replace(ExpandFormula, CellNavArrow.Parent.Name & _

                            "!" & CellNavArrow.Address(0, 0), CellVal)

          End If

        Loop While Err.Number = 0

      Else

        Set CellNavArrow = Cell.NavigateArrow(True, X)

        If CellNavArrow.Count = 1 Then

          CellVal = CellNavArrow.Value

          If UseCellFormatting Then

            CellVal = WorksheetFunction.Text(CellNavArrow.Value, CellNavArrow.NumberFormat)

          End If

          If Not WorksheetFunction.IsNumber(CellVal) Then IsText = True

          If IsText And Not CellVal Like "*[!0-9.]*" And Not CellVal Like "*.*.*" And _

             Len(CellVal) > 0 And CellVal <> "." Then IsText = False

          If IsText Then CellVal = """" & CellVal & """"

          ExpandFormula = Replace(ExpandFormula, CellNavArrow.Address(0, 0), CellVal)

        End If

      End If

      Worksheets(CurrentSheetName).Activate

    Loop While X <= ArrowCount

    Cell.ShowPrecedents Remove:=True

  Else

    If UseCellFormatting Then

      ExpandFormula = WorksheetFunction.Text(Cell.Value, Cell.NumberFormat)

    Else

      ExpandFormula = Cell.Value

    End If

  End If

  Worksheets(StartSheet).Activate

  Application.ScreenUpdating = True

End Function

Answer 2

However, it has occurred to me that the references to sheets with spaces in names would require application of chr(39) (i.e. single quotes usage) and the text entries won't be returned with double quotes?

Excellent point! I was concentrating on getting the code to work at all that I completely overlooked that. As it turns out, the fix is easy... I just do two replacements, once with the single quotes and then follow it without the single quotes... one of them has to work and the other won't. I also took the opportunity to change the outer Do..Loop's While control to a more accurate method. Here is the reworked code...

Function ExpandFormula(Cell As Range) As String

  Dim X As Long, Z As Long, OldShapeCount As Long, ArrowCount As Long, CellNavArrow As Range

  Dim CurrentSheetName As String, PreviousCell As String, OriginalSheet As String

  OriginalSheet = ActiveSheet.Name

  Application.ScreenUpdating = False

  Cell.Parent.Activate

  CurrentSheetName = ActiveSheet.Name

  If Cell.HasFormula Then

    ExpandFormula = Replace(Cell.Formula, "$", "")

    OldShapeCount = ActiveSheet.Shapes.Count

    Cell.ShowPrecedents

    ArrowCount = ActiveSheet.Shapes.Count - OldShapeCount

    On Error Resume Next

    Do

      X = X + 1

      If Cell.NavigateArrow(True, X).Parent.Name <> CurrentSheetName Then

        Do

          Z = Z + 1

          Set CellNavArrow = Cell.NavigateArrow(True, X, Z)

          ExpandFormula = Replace(ExpandFormula, "'" & CellNavArrow.Parent.Name & _

                          "'!" & CellNavArrow.Address(0, 0), CellNavArrow.Value)

          ExpandFormula = Replace(ExpandFormula, CellNavArrow.Parent.Name & _

                          "!" & CellNavArrow.Address(0, 0), CellNavArrow.Value)

        Loop While Err.Number = 0

      Else

        Set CellNavArrow = Cell.NavigateArrow(True, X)

        ExpandFormula = Replace(ExpandFormula, CellNavArrow.Address(0, 0), CellNavArrow.Value)

      End If

      Worksheets(CurrentSheetName).Activate

    Loop While X <= ArrowCount

    Cell.ShowPrecedents Remove:=True

  Else

    ExpandFormula = Cell.Formula

  End If

  Worksheets(OriginalSheet).Activate

  Application.ScreenUpdating = True

End Function

Answer 3

Hi,

Maybe this

Dim Mystring As String

Mystring = "My Number is "

Range("D1") = Mystring & WorksheetFunction.Round(Range("A1") + Range("A2") + (Range("C1") + Range("C2")) / 45, 2)

Answer 4

Sorry  Jaeson,

But code refers to VBA.

Answer 5

Is there any way (code) to have the values of cell references used in the formulae of a range be converted to their respective values?

Hi,

Code for? VBA? or simply formula???

Formula: =B1&" "&ROUND(A1+A2+(C1+C2)/45,2)

Used round to shorten the decimal points.

Thanks,

~jaeson