問題描述
Пераўтварэнне формулы ячэйкі ў тэкст з дапамогай excel vba (Converting a cell's formula to text using 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.
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:
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)