vba 使用excel vba将单元格的公式转换为文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13694345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting a cell's formula to text using excel vba
提问by yu_ominae
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?
将公式输出为文本而不是公式时，我缺少什么？
采纳答案by Dale M
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)
