使用Excel VBA将单元格公式转换为文本

Question

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

我知道可以使用以下方法显示单个单元格的公式：

Public Function ShowFormula(cell As Range) As String

    ShowFormula = cell.Formula

End Function

这个功能已经很好用了，但由于我不想手动查找所有单元格，所以我编写了以下宏来代替我找到所有单元格。

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

它可以很好地找到所有单元格，但列表显示的是公式结果而非公式文本。我错过了什么可以将公式输出为文本而非公式？

- yu_ominae

Answer 1

试试这个：

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

此外，这将使事情变得更快。而不是

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

尝试

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

- Dale M

---

谢谢你加速的提示！我有一个快速问题关于你的答案。虽然这个方法可以工作，但是它输出的是=A1+B2+C3+...。我计划稍后将这些公式插入到不同的单元格中，这将需要删除“'”。有没有一种方法可以在不强制文本格式的情况下完成这个操作？我真的很好奇为什么它可以在UDF中工作，而不能在宏中使用...？ - yu_ominae

去掉“'”很简单Right(s, Len(s) - 1)，但我理解你的意思。您可以尝试在粘贴未修改的公式之前将目标单元格的格式设置为文本，然后查看效果如何。 - Dale M

格式化文本就解决了问题。如此简单，却没想到 :( 非常感谢！ - yu_ominae