Excel VBA 变量值等于公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15485054/
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
Excel VBA variable value equal to formula
提问by Jiminie.Glick
I'm struggling with assigning variable values in VBA. I understand how to set myVar equal to an ActiveCell.Value like this:
我正在努力在 VBA 中分配变量值。我了解如何将 myVar 设置为等于 ActiveCell.Value，如下所示：
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[7])"
myVar = ActiveCell.Value
MsgBox (myVar)
Is it possible to set myVar equal to a formula and skip the ActiveCell.Value line. I've tried this, but it does not work.
是否可以将 myVar 设置为等于公式并跳过 ActiveCell.Value 行。我试过这个，但它不起作用。
myVar = "=SUM(RC[1]:RC[7])"
Thanks for your help.
谢谢你的帮助。
回答by Kazimierz Jawor
I don't know what is a wider scope of your goal therefore I present idea which could be useful. However, it switches from relative reference of FormulaR1C1into relative reference of Range.Offset()techniques.
我不知道您的目标范围更广，因此我提出了可能有用的想法。但是，它从技术的相对参考FormulaR1C1转换为Range.Offset()技术的相对参考。
myVar = WorksheetFunction.Sum(Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7)))
MsgBox myVar
回答by Peter Albert
If you don't need your formula to be relative to the active cell, you can also use the short [..]to evaluate a statement, e.g.:
如果您不需要公式相对于活动单元格，您还可以使用 short[..]来评估语句，例如：
MsgBox [SUM(B1:B7)]
回答by Joseph
Yes, you can. You start with setting the myVarvariable first, then apply it to a range (ActiveCell or any other Range object).
是的你可以。首先设置myVar变量，然后将其应用于范围（ActiveCell 或任何其他 Range 对象）。
Dim myVar as String
Dim r as Excel.Range
Set r = ActiveCell
myVar = "=SUM(RC[1]:RC[7])"
r.FormulaR1C1 = myVar
