如何在 Excel VBA 中编写采用参数范围或列表的函数?
例如,使用 SUM
,我可以执行以下操作:
=SUM(F5:F7,F6:F8,A1,E7:G7,F7,2,7)
它采用多个范围和/或单个单元格,并将它们全部相加。我的问题是如何使用 VBA 函数执行此操作,例如概括二进制 XOR:
Function BXOR(A As Integer, B As Integer) As Integer
BXOR = CLng(A) Xor CLng(B)
End Function
当我必须 =BXOR(BXOR(BXOR(w,x),y),z)
=BXOR(BXOR(BXOR(w,x),y),z)
Marco 函数的广义版本:
Function BXOR(ParamArray vars() As Variant) As Long
Dim i,j As Long
BXOR = 0
For i = 0 To UBound(vars)
If Not IsObject(vars(i)) Then
' Handle explicitly passed integer arguments, e.g. BXOR(1,[...])
BXOR = BXOR Xor CLng(vars(i))
ElseIf IsArray(vars(i).Value2) Then
' Handle 1-D ranges of cells, e.g. BXOR(A1:A3,[...])
For j = 1 To UBound(vars(i).Value2)
BXOR = BXOR Xor CLng(vars(i)(j).Value2)
Next j
Else
' Handle individual cells, e.g. BXOR(A1,[...])
BXOR = BXOR Xor CLng(vars(i).Value2)
End If
Next i
End Function
With SUM
for instance, I can do things like:
=SUM(F5:F7,F6:F8,A1,E7:G7,F7,2,7)
Which takes multiple ranges and/or individual cells and it adds them all up. My question is how do I do this with a VBA function, e.g. to generalize a binary XOR:
Function BXOR(A As Integer, B As Integer) As Integer
BXOR = CLng(A) Xor CLng(B)
End Function
It gets old fast when I have to =BXOR(BXOR(BXOR(w,x),y),z)
Generalized version of Marco's function:
Function BXOR(ParamArray vars() As Variant) As Long
Dim i,j As Long
BXOR = 0
For i = 0 To UBound(vars)
If Not IsObject(vars(i)) Then
' Handle explicitly passed integer arguments, e.g. BXOR(1,[...])
BXOR = BXOR Xor CLng(vars(i))
ElseIf IsArray(vars(i).Value2) Then
' Handle 1-D ranges of cells, e.g. BXOR(A1:A3,[...])
For j = 1 To UBound(vars(i).Value2)
BXOR = BXOR Xor CLng(vars(i)(j).Value2)
Next j
Else
' Handle individual cells, e.g. BXOR(A1,[...])
BXOR = BXOR Xor CLng(vars(i).Value2)
End If
Next i
End Function
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我认为您正在寻找 ParamArray
您的代码应如下所示:
I think that you are looking for a ParamArray
Your code should look like this:
您可以使用 ParamArray 来传递可变数量的参数。类型必须是
Variant
。You can use
ParamArray
to pass a variable number of arguments. The type has to beVariant
.