You should only check if it is a range.

then you process the range (regardless of whether it was Offset, index or
something like A1:A10, or B9)

for each cell in Range


If it was a single cell range, then vartype was whatever was contained in
that range

for a date, 7

if it was a multicell range, then vartype was 8204

If it was a single cell and empty (which is apparently what you are
testing), then vartype was zero.

--
Regards,
Tom Ogilvy



"Arvi Laanemets" <garbage@hot.ee> wrote in message
news:Ot2KZ6KTFHA.3952@TK2MSFTNGP15.phx.gbl...
> Hi
>
> The function is declared as
> Public Function EnchWorkdaysN(StartDate As Date, _
> EndDate As Date, _
> Optional Holidays As Variant = Nothing, _
> Optional Weekends As Variant = Nothing, _
> Optional WeekStart As Integer = 1)
> ...
>
> In following code, all values from parameter Holidays are read into an
> one-dimensional array. I am able to do this when the parameter Holidays is
> passed as array ({ValueList}), as cell reference (cell range or named
> range), or as a single numeric value or expression. But how to do the
same,
> when the parameter is passed as a range expression, like OFFSET($K$1,1,12)
?
>
> When Holidays is passed as a range expression, then VarType(Holidays)=0,
and
> TypeName(Holidays)="Range", but I wasn't able to find a way to list values
> in this range. The part of code reading passed parameter vales into array
is
> below, maybe someone has some advice :
> ....
> ' Initialize ArrayH
> If TypeName(Holidays) = "Variant()" Then
> ReDim arrayH(1 To UBound(Holidays)) As Variant
> For i = 1 To UBound(Holidays)
> arrayH(i) = IIf(VarType(Holidays(i, 1)) > 0 And
> VarType(Holidays(i, 1)) < 8, Holidays(i, 1), Null)
> arrayH(i) = IIf(arrayH(i) < 0, Null, arrayH(i))
> Next i
> ElseIf (VarType(Holidays) >= 8192 And VarType(Holidays) <= 8199) Or _
> VarType(Holidays) = 8204 Then
> ReDim arrayH(1 To UBound(Holidays.Value)) As Variant
> For i = 1 To UBound(Holidays.Value)
> arrayH(i) = IIf(VarType(Holidays(i)) > 0 And
> VarType(Holidays(i)) < 8, Holidays(i), Null)
> arrayH(i) = IIf(arrayH(i) < 0, Null, arrayH(i))
> Next i
> ElseIf (VarType(Holidays) = 0 And TypeName(Holidays) = "Range") Then
> ' !!! Here I'm stucked!!!
>
> ElseIf VarType(Holidays) < 8 Then
> ReDim arrayH(1) As Variant
> arrayH(1) = Holidays
> arrayH(1) = IIf(arrayH(1) < 0, Null, arrayH(1))
> Else
> ReDim arrayH(1) As Variant
> arrayH(1) = Null
> End If
> ....
>
>
> Thanks in advance
> --
> When sending mail, use address arvil<at>tarkon.ee
> Arvi Laanemets
>
>