title: Converting a cell's formula to text using excel vba
body: <p>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.</p>
  
  <p>I know I can show the formula for an individual cell using</p>
  
  <pre><code>Public Function ShowFormula(cell As Range) As String
  
      ShowFormula = cell.Formula
  
  End Function
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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 &gt;= 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
  </code></pre>
  
  <p>It finds all the cells just fine, but instead of displaying the formula as text, the list displays the formula results.</p>
  
  <p>What am I missing to output the formulas as text instead of formulas?</p>