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

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

Sorry, my mistake! Forget it!


--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets

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

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.

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:eTacVkLTFHA.3840@tk2msftngp13.phx.gbl...
> 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
> >
> >
>
>