在Excel VBA中,如何编写可以在数组公式中使用的函数? [英] In Excel VBA, how do I write a function that can work in an array formula?
问题描述
在Excel VBA中,我知道可以通过选择Col C的单元格范围,并且进行= A1:10 * B1:B10 Ctrl-shift-enter,在多个单元格上使用数组公式。
In Excel VBA, I know I can use an array formula on multiple cells, by selecting a range of cells of Col C, and doing =A1:10*B1:B10 Ctrl-shift-enter.
但是,假设代替乘法运算符,我希望它成为我的mymult。
But suppose instead of the multiplication operator, I want it to be my mymult.
mymult函数?
How would I write the mymult function?
Function MyMult(a As Integer, b As Integer)
MyMult = a * b
End Function
我没有工作
推荐答案
将参数声明为variant。然后使用 Application.Caller.HasArray
来检查UDF是否用作数组公式:
Declare the arguments as variant. Then use Application.Caller.HasArray
to check if the UDF is used as an array formula:
Public Function MyMult(a, b)
If Application.Caller.HasArray Then
Dim valsA(), valsB(), r&
valsA = a.Value
valsB = b.Value
For r = LBound(valsA) To UBound(valsA)
valsB(r, 1) = valsA(r, 1) * valsB(r, 1)
Next
MyMult = valsB
Else
MyMult = a * b
End If
End Function
请注意,您需要在按CTRL + SHIFT + ENTER之前选择C1:C3。
Note that you need to select C1:C3 before pressing CTRL + SHIFT + ENTER.
这篇关于在Excel VBA中,如何编写可以在数组公式中使用的函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!