XL VBA - Passing an array in a user defined function?

Question

How to pass an array as a parameter for a user defined function in MS Excel VBA?

Eventually I want to test that if a given date (dateDay) is in several ranges of dates (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! and I can find a way to get it to work.

Thank you for your help,

Answer 1

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.

Please ask if i can assist further.