vba Excel：在用户定义的函数中传递数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1847803/
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: Passing an array in a user defined function?
提问by Nicolas
How to pass an array as a parameter for a user defined function in MS Excel VBA?
如何在 MS Excel VBA 中将数组作为用户定义函数的参数传递？
Eventually I want to test that if a given date (dateDay) is in several ranges of dates (arrayVacation):
最后，我想测试给定日期 (dateDay) 是否在多个日期范围内 (arrayVacation)：
Function CB_IsInRangeArr(dateDay As Date, ParamArray arrayVacation() As Variant) As Boolean
' Test that the array is in the form of 2 columns and n rows / if not send back an error
If (UBound(arrayVacation, 1) <> 2) Then
CB_IsInRangeArr = CVErr(xlErrNA)
Else
CB_IsInRangeArr = TRUE
End If
End Function
Yet already at this stage, the function does not work properly. It returns #VALUE!
然而，在这个阶段，该功能无法正常工作。它返回#VALUE！
回答by Adriaan Stander
OK, i added a function
好的，我添加了一个功能
Public Function CB_IsInRangeArr(c As Date, range As range) As Boolean
Dim iRow As Integer
For iRow = 1 To range.Rows.Count
Dim startDate As Date, endDate As Date
startDate = range.Cells(iRow, 1)
endDate = range.Cells(iRow, 2)
If (startDate <= c And endDate >= c) Then
CB_IsInRangeArr = True
Exit Function
End If
Next iRow
End Function
this allows you to add the date ranges, and a cell for the date to check.
这允许您添加日期范围和用于检查日期的单元格。
then the formula in the cell is
那么单元格中的公式是
=CB_IsInRangeArr(C1,A1:B2)
with c1 being the date to check, and a1:b2 the date ranges.
c1 是要检查的日期，a1:b2 是日期范围。
Please ask if i can assist further.
请问我是否可以提供进一步的帮助。
回答by Charles Williams
Paramarray creates an array of variants with each element holding the parameter: Try something like this
Paramarray 创建一个变量数组，每个元素都包含参数：尝试这样的事情
Function CB_IsInRangeArr(dateDay As Date, ParamArray arrayVacation() As Variant) As Variant
Dim nParams As Long
Dim vRangeValues As Variant
Dim jParam As Long
Dim j As Long
nParams = UBound(arrayVacation) - LBound(arrayVacation) + 1
If nParams &le 0 Then Exit Function
On Error GoTo Fail
For jParam = LBound(arrayVacation) To UBound(arrayVacation)
vRangeValues = arrayVacation(jParam).Value
For j = LBound(vRangeValues) To UBound(vRangeValues)
If (vRangeValues(j, 1) &le dateDay And vRangeValues(j, 2) &ge dateDay) Then
CB_IsInRangeArr = True
Exit Function
End If
Next j
Next jParam
Exit Function
Fail:
CB_IsInRangeArr = CVErr(xlErrNA)
End Function
