Converting a cell's formula to text using excel vba

Question

I am writing a macro in Excel2003 to find all cells with formulas in a workbook and outputting their address and formula in a couple of columns on a different sheet.

I know I can show the formula for an individual cell using

Public Function ShowFormula(cell As Range) As String

    ShowFormula = cell.Formula

End Function

which works just fine, but since I didn't want to have to find all the cells by hand, I wrote the following macro to find them all for me

Sub Macro2()


Dim i As Integer
Dim targetCells As Range
Dim cell As Range
Dim referenceRange As Range
Dim thisSheet As Worksheet

Set referenceRange = ActiveSheet.Range("CA1")

With referenceRange
    For Each thisSheet In ThisWorkbook.Sheets
        If thisSheet.Index >= referenceRange.Parent.Index Then
            Set targetCells = thisSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
            For Each cell In targetCells
                If cell.HasFormula Then
                    .Offset(i, 0).Value = thisSheet.Name
                    .Offset(i, 1).Value = cell.Address
                    .Offset(i, 2).Value = CStr(cell.Formula)
                    i = i + 1
                End If
            Next
        End If
    Next
End With

End Sub

It finds all the cells just fine, but instead of displaying the formula as text, the list displays the formula results.

What am I missing to output the formulas as text instead of formulas?

score 5 · Accepted Answer · answered Dec 04 '12 at 01:23

5

Try this:

.Offset(i, 2).Value = "'" & CStr(cell.Formula)

Also, this will make things a bit quicker. Instead of

For Each thisSheet In ThisWorkbook.Sheets
    If thisSheet.Index >= referenceRange.Parent.Index Then

try

For j = referenceRange.Parent.Index to Sheets.Count
    Set thisSheet = Sheets(j)

answered Dec 04 '12 at 01:23

Dale M

2,453
1
13
21

Thanks for the tip for speeding things up! Quick question on your answer. Whilst this works, it outputs `'=A1+B2+C3+...`. I am planning on plugging those formulas back into different cells later and this will require removing the "'". Is there a way to do this without forcing a text format? I really wonder why it works with the UDF and not within the macro...? – yu_ominae Dec 04 '12 at 01:56
Removing the "'" is trivial `Right(s, Len(s) - 1)` but I take your point. You could try setting the format of the destination cell to Text before pasting the unmodified formula and see how that goes. – Dale M Dec 04 '12 at 02:39
Formatting to text did that trick. So simple, yet it didn't occur to me :( Many thanks! – yu_ominae Dec 04 '12 at 05:45

Converting a cell's formula to text using excel vba

1 Answers1

Linked