Excel2000: Reading values from range, passed to function as parameter using an expression

[Arvi Laanemets]

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

 

[Arvi Laanemets]

Sorry, my mistake! Forget it!

 

[Tom Ogilvy]

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.

 

[Arvi Laanemets]

Hi Tom

I simply did a mistake in OFFSET function - so that a cell range with empty
was passed as parametes. Now i corrected my function, so such cases are
processed too.

It looks like my UDF is OK now. It calculates the number of workdays between
2 dates like NETWORKDAYS, but in addition to optional holidays, weekends
list and 1st day of week are optionally passed too.