使用excel vba将单元格的公式转换为文本

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.

我在Excel2003中编写一个宏来查找工作簿中包含公式的所有单元格，并在不同工作表的几列中输出它们的地址和公式。

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?

将公式输出为文本而不是公式，我缺少什么？

1 个解决方案

#1

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)

#1