Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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<attarkon.ee Arvi Laanemets |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Reading values from range, passed to function as parameter using an expression
Sorry, my mistake! Forget it!
-- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Reading values from range, passed to function as parameter using an expression
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" wrote in message ... 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<attarkon.ee Arvi Laanemets |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Reading values from range, passed to function as parameter using an expression
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<attarkon.ee Arvi Laanemets "Tom Ogilvy" wrote in message ... 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" wrote in message ... 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<attarkon.ee Arvi Laanemets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel2000: How to check for Optional parameter=Nothing | Excel Programming | |||
Excel2000: Declaring function parameter as an array | Excel Programming | |||
Excel2000: UDF's parameter as cell range OR array | Excel Programming | |||
Translate range name passed as string to a custom function to range addresses! | Excel Programming | |||
Excel2000: Reading Named Range value from VBA | Excel Programming |