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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:04:15  来源:igfitidea点击:

Excel: Passing an array in a user defined function?

arraysexcelvbaparametersuser-defined-functions

提问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