从单元格中取回公式 - VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27899507/
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
Get back a formula from a cell - VBA
提问by Bramat
I'm writing a VBA code, in which I need to reference in one sheet a formula from a different sheet for instance: in sheet A in cell (1,3) there's a formula
我正在编写一个 VBA 代码，其中我需要在一张表中引用不同表中的公式，例如：在单元格 (1,3) 中的表 A 中有一个公式
"=R[-1]C*R[-2]C"
and I want in sheet B in cell (1, 3) to put the formula
我想在单元格 (1, 3) 的工作表 B 中放置公式
"=A!R[-1]C*A!R[-2]C"
I want to save in a string only the formula -
我只想将公式保存在字符串中 -
"=R[-1]C*R[-2]C"
And manually do the adjustments I need.
并手动进行我需要的调整。
So my question is:
所以我的问题是：
- How can I get the formula in the cell? when I write
"temp=worksheets("A").cells(1,3)",
I get back the value from the cell, and not the formula...
- 如何在单元格中获取公式？当我写时
"temp=worksheets("A").cells(1,3)"，
我从单元格中取回值，而不是公式...
回答by paxdiablo
You can get (and set) the formula in a cell (A1in this case) using, strangely enough:
A1奇怪的是，您可以使用以下方法获取（并设置）单元格（在本例中）中的公式：
Range("A1").Formula
(see herefor details).
（有关详细信息，请参见此处）。
That just comes back as a string so you can evaluate/manipulate it with the regular VBA string functions like left, right, mid, instrand so on.
这只是回来作为一个字符串，所以你可以评估/与正规VBA字符串函数一样操纵它left，right，mid，instr等等。
As per the linked page, the formula returned for a constant cell is just the constant itself, and a blank cell returns "".
根据链接页面，为常量单元格返回的公式只是常量本身，而空白单元格返回"".
You can use that information to decide if it's actuallyformulaic or not.
您可以使用该信息来确定它是否真的是公式化的。
回答by sancho.s ReinstateMonicaCellio
You can use code like
您可以使用类似的代码
Dim ws1 As Worksheet
Set ws1 = Worksheets("A")
Dim ws2 As Worksheet
Set ws2 = Worksheets("B")
Dim rng1 As Range
Set rng1 = ws1.Range("C1")
Dim rng2 As Range
Set rng2 = ws2.Range("C1")
Dim myf As String
myf = "=R[-1]C*R[-2]C"
rng1.FormulaR1C1 = myf ' The property you were asking about
Adapt it as needed.
Note the use of variables to refer to Ranges.
根据需要对其进行调整。请注意使用变量来引用Ranges。
