vba 如何将实际的 excel 公式（不是结果）转换为文本字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16026525/
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
How to convert the actual excel formula (not the result) to a text string
提问by user330739
I have been searching all over for a solution and I couldn't find one.
我一直在寻找解决方案，但找不到。
Let's say I type into cell A1: =if(A2>1,1,0)
假设我在单元格 A1 中输入：=if(A2>1,1,0)
Excel will interpret that and either return 1 or 0.
Excel 将解释它并返回 1 或 0。
However, what I would like to do is literally convert that formula into the string: '=if(A2>1,1,0)'
但是，我想做的是将该公式从字面上转换为字符串：'=if(A2>1,1,0)'
If you press Ctrl+~ into Excel, you will see all the formulas. Basically what I want to do, in conclusion, is take all those formulas and make them strings.
如果按 Ctrl+~ 进入 Excel，您将看到所有公式。总之，我想要做的基本上就是采用所有这些公式并使它们成为字符串。
(The purpose, if you care, is to compare these strings with another workbook to make sure I didn't delete anything)
（如果您关心，目的是将这些字符串与另一个工作簿进行比较以确保我没有删除任何内容）
回答by Brad
You can make a user defined function for this.
您可以为此创建一个用户定义的函数。
Put this code into a Module(not a worksheet code sheet, class, or form)
将此代码放入Module（不是工作表代码表、类或表单）
Function GetFormula(rng As range, Optional asR1C1 As Boolean = False) As String
If asR1C1 Then
getFormula = rng.FormulaR1C1
Else
getFormula = rng.Formula
End If
End Function
and call it in your worksheet like so
并像这样在您的工作表中调用它
=GetFormula(A1)
or if you want the results as R1C1
或者如果你想要结果 R1C1
=GetFormula(A1,TRUE)
回答by tfeyereisen
Use the FORMULATEXT function. It's built into Excel.
使用FORMULATEXT 函数。它内置于 Excel 中。
回答by Jeremy Thompson
what I would like to do is literally convert that formula into the string: '=if(A2>1,1,0)'
我想做的是将该公式从字面上转换为字符串：'=if(A2>1,1,0)'
Simply use the Formula Property of the cell, here is a code example:
只需使用单元格的公式属性，这里是一个代码示例：
Range("A1").Select
Dim strFormula As String
strFormula = ActiveCell.Formula
MsgBox (strFormula)
回答by smac
You can also replace the equal sign with blank using find and replace. In effect removing the equal sign and leaving only the text of the formula as a string.
您还可以使用查找和替换将等号替换为空白。实际上删除等号，只留下公式的文本作为字符串。
回答by Agus Gunawan
Just follow this way.
就按照这条路走。
Select the range (contain formula) you want to convert to text, also you can select All range on your sheet.
Use Find and Replace command by pressing Control-F then replace "=" with "^" press replace All.. All done....
If you want to use it again as formula... Just use Find and Replace, then replace "^" with "=" just look....All formula in text format converted to formula...
选择要转换为文本的范围（包含公式），也可以选择工作表上的所有范围。
按 Control-F 使用查找和替换命令，然后将“=”替换为“^”，然后按全部替换...全部完成...
如果你想再次使用它作为公式......只需使用查找和替换，然后将“^”替换为“=”就看......所有文本格式的公式都转换为公式......
回答by dryphi
Just right-click on the column header (i.e. A) and select "Format Cells" then under the "Number" tab select "Text". Then at each cell press enter and the formula will be displayed instead of the result.
只需右键单击列标题（即 A）并选择“设置单元格格式”，然后在“数字”选项卡下选择“文本”。然后在每个单元格按回车键，将显示公式而不是结果。
