vba 将公式的值分配给变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16809767/
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
Assigning the value of a formula to a variable
提问by Thomas Dang
I am learning coding in vba and I recently encountered a serious problem: I cannot assign the value of a formula to a varialbe without putting the variable to a cell first.
我正在学习 vba 中的编码，最近我遇到了一个严重的问题：如果不先将变量放入单元格，我就无法将公式的值分配给变量。
My point is that I need those value to do some other task, but I don't want to to put those formula to a cell (if I do, it will takes a lot more coding).
我的观点是我需要这些值来完成其他一些任务，但我不想将这些公式放入单元格中（如果我这样做，将需要更多的编码）。
I did try this:
我确实尝试过这个：
Dim Total As Integer
Total = FormulaR1C1 = "=sum(R1C1,R3C2)"
But I realize that this only return bolean value which obviously not my attention
但我意识到这只是返回 bolean 值，这显然不是我的注意力
So my main question here is: How can I assign the value of a formula (in string or integer type) to a variable
所以我的主要问题是：如何将公式的值（字符串或整数类型）分配给变量
In addition, as using FormulaR1C1 requires a refernce cell, do I need to select a cell before assigning to make it work
此外，由于使用 FormulaR1C1 需要一个引用单元格，我是否需要在分配之前选择一个单元格才能使其工作
采纳答案by Kazimierz Jawor
There is one option which could be interesting for you. You could call SUMworksheet function calling it from VBA through WorksheetFunctionobject. How it works?
有一个选项可能对您感兴趣。您可以调用SUM工作表函数，通过WorksheetFunction对象从 VBA 调用它。这个怎么运作？
Based on your example you could get sum of your R1C1& R3C2(A1 & B3) this way:
根据您的示例，您可以通过这种方式获得R1C1& R3C2(A1 & B3) 的总和：
Dim Total As Integer
Total = WorksheetFunction.Sum(Range("A1,B3"))
As you can see it is required to use Range objectreferenced to cell which you need to sum up. You can't use R1C1references here. But I think it's not a problem.
如您所见，需要使用Range object引用到您需要总结的单元格。您不能R1C1在此处使用引用。但我认为这不是问题。
One more tip. It's allowed to call it also this way:
还有一个技巧。也可以这样称呼它：
Dim Total As Integer
Total = Application.Sum(Range("A1,B3"))
The biggest difference of two above examples is the way of error handling. Working with WorksheetFunction- any error will stop your procedure, working with Application-error will be thrown as a text without procedure brake.
上面两个例子最大的区别在于错误处理的方式。使用WorksheetFunction- 任何错误都会停止您的程序，使用Application-error 将作为没有程序刹车的文本抛出。
The other tip- if you write WorksheetFunction.(dot at the end is essential) you'll get list of approx 200 function available for you to use. If you start with Application.there will not be a list of function but just methods and properties of Application Object.
另一个提示 - 如果您编写WorksheetFunction.（末尾的点是必不可少的），您将获得大约 200 个可供您使用的功能列表。如果您开始，Application.将不会有函数列表，而只有Application Object.
Finally, see this linkfor more information regarding using WorksheetFunction.
最后，看到这个链接使用有关的更多信息WorksheetFunction。
EDITOne more thing, this could be obvious but make it clear. To sum continuous range from A1 to B3 you need just small change:
编辑还有一件事，这可能很明显，但要清楚。总结从 A1 到 B3 的连续范围，你只需要很小的改变：
Dim Total As Integer
Total = WorksheetFunction.Sum(Range("A1:B3"))
