返回范围值乘积的简单 VBA 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20320662/
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
Simple VBA function that returns product of range value
提问by user3056006
I want the function to take a range of cells as an argument and return their product. Let's assume the following value for cells:
我希望该函数将一系列单元格作为参数并返回它们的乘积。让我们假设单元格的以下值：
A1=5
A2=2
A3=3
Let's call the function Multiply.
=Multiply(A1:A3)will return 30 (=5×2×3).
让我们调用函数Multiply。
=Multiply(A1:A3)将返回 30（=5×2×3）。
What is the code for this? I'm just trying to familiarize myself with the syntax and this will help out a lot.
这是什么代码？我只是想让自己熟悉语法，这会很有帮助。
Edit: figured it out:
编辑：想通了：
Function multiply(rng As Range)
multiplied = 1
For Each cell In rng
multiplied = multiplied * cell.Value
Next
multiply = multiplied
End Function
回答by brettdj
You can use the VBAversion of PRODUCTdirectly, ie
可以直接使用的VBA版本PRODUCT，即
MsgBox WorksheetFunction.Product([a1:a3])
回答by Jorge Sampaio
OK for =PRODUCT, which does the job (SUMPRODUCTwill NOT do what the initial fellow asked).
好的=PRODUCT，它可以完成这项工作（SUMPRODUCT不会做最初的人问的事情）。
But, just for fun, using the properties of the math functions EXP and LN we have that if
但是，只是为了好玩，使用数学函数 EXP 和 LN 的属性我们有如果
X = A1*B1*C1 then
LN(X) = LN(A1)+LN(B1)+LN(C1)
and
X = EXP(LN(X)) or
X = EXP(LN(A1)+LN(B1)+LN(C1))
so just put in a cel: =EXP(SUM(LN(A1:C1)))and voilà! (you must use CTRL+SHIFT+ENTER)
所以只需输入一个cel: =EXP(SUM(LN(A1:C1)))，瞧！（你必须使用CTRL+ SHIFT+ ENTER）
回答by ApplePie
You can use excel worksheet functions.
您可以使用 Excel 工作表函数。
The default behaviour of SUMPRODUCTif there is only a single range provided is to return the sum so you can just pass the range to SUMPRODUCTthis way:
SUMPRODUCT如果只提供一个范围的默认行为是返回总和，因此您可以将范围传递给SUMPRODUCT这种方式：
WorksheetFunction.Sumproduct(**your range goes here**)
回答by ja72
If you insist of writing your own function then try the following. It loads all the values into memory first and thus should work faster.
如果您坚持要编写自己的函数，请尝试以下操作。它首先将所有值加载到内存中，因此应该工作得更快。
Public Function RngProduct(ByRef r as Range) as Double
Dim vals() as Variant, res as Double
vals = r.Value
Dim i as Long, N as Long
N = r.Rows.Count
res = #1
For i=1 to N
res = res * vals(i,1)
Next i
RngProduct = res
End Function
