Excel2000: Need help on UDF (working with arrays)

[Arvi Laanemets]

Hi

I'm trying to write an UDF which is an enchanced NETWORKDAYS function, with
syntax:
ENCHWORKDAYS(StartDate,EndDate,[Holidays],[Weekends])

As both optional parameters can be cell range references, or arrays, or
single values, then I decided to convert them to arrays, and do all
calculations with arrays later.

I haven't used arrays in VBA before. I have used them in FoxPro, where is a
lot of various functions and commands for working with arrays - compared
with this in VBA help I did find next to nothing about them. So maybe
someone explains, how to:
1) sort array elements;
2) compact the array (remove elements);
3) count elements in array.
4) Can array contain an empty (null) value?
....
Or someone is willing to have a look on the code below, I have at moment,
and to give some advice.

------
Public Function EnchWorkdaysN(StartDate As Date, _
EndDate As Date, _
Optional Holidays As Variant = Nothing, _
Optional Weekends As Variant = Array(1, 7))

Dim H() As Variant
Dim W() As Variant
Dim LenH As Integer
Dim LenW As Integer
Dim di As Date
Dim dn As Date

If Not (Holidays Is Nothing) Then
If VarType(Holidays) = vbArray Then
' Sort Holidays
' Remove double entries
' Remove empty elements
If Holidays Is Empty Then
' Can an array element have the Null (not 0!!!) value?
H(0) = Null
Else
' Copy all elements of Holidays to H()???
H() = Holidays
End If
ElseIf TypeName(Holidays) = "Range" Then
' Read all valid unique date format cell values from range
Holidays into array H()
' When no valid entries were found, then H(0)=Null
' otherwise sort H()

ElseIf VarType(Holidays) = vbDate Then
H(0) = Holidays
Else
H(0) = Null
End If
Else
H(0) = Null
End If
' calculate the number of elements in H() LenH=?

If VarType(Weekends) = vbArray Then
' Replace all elements Weekends(i)=INT(Weekends(i)
' Sort Weekends
' Remove double entries
' Remove empty elements
If Weekends Is Empty Then
W(0) = 0
Else
' Copy all elements of Weekends to W()
W() = Weekends
End If
ElseIf TypeName(Weekends) = "Range" Then
' Read integer part of all numeric cell values >=0 And <8 into array
W()
' When no valid entries were found, then W(0)=Null
' otherwise sort W()

ElseIf Int(Weekends) >= 1 And Weekends <= 7 Then
W(0) = Int(Weekends)
ElseIf Weekends = 0 Then
' The only way to have no weekends at all is set the parameter
Weekends:=0
W(0) = 0
Else
' default value is used
W(0) = 1
W(1) = 7
End If
' calculate the number of elements in W() LenW=?


EnchWorkdaysN = 0
di = Min(StartDate, EndDate)
dn = Max(StartDate, EndDate)
Do While di <= dn
x = False
i = 0
j = 0
Do While x = False And i <= LenH
x = (di = H(i))
i = i + 1
Loop
Do While x = False And j <= LenW
x = (Weekday(di) = W(i))
i = i + 1
Loop
If Not (x) Then EnchWorkdaysN = EnchWorkdaysN + 1
Loop
End Function

 

[Dick Kusleika]

Hi

I'm trying to write an UDF which is an enchanced NETWORKDAYS
function, with syntax:
ENCHWORKDAYS(StartDate,EndDate,[Holidays],[Weekends])

As both optional parameters can be cell range references, or arrays,
or single values, then I decided to convert them to arrays, and do all
calculations with arrays later.

I haven't used arrays in VBA before. I have used them in FoxPro,
where is a lot of various functions and commands for working with
arrays - compared with this in VBA help I did find next to nothing
about them. So maybe someone explains, how to:
1) sort array elements;

Here's how I do it
http://www.dicks-blog.com/archives/2004/05/12/sorting-listboxes/

2) compact the array (remove elements);

I don't think there's an elegant, built-in way to do that. You can round
trip through a collection to filter out uniques, but since you're moving it
to H() anyway, I think I would just take the sorted array and

For i = LBound(Holidays) to UBound(Holidays) - 1
If Holidays(i) <> Holidays(i+1) Then
Redim Preserve H(0 to j)
H(j) = Holidays(i)
j=j+1
End If
Next i

Redim Preserve H(0 to j)
H(j) = Holidays(UBound(Holidays))

Now H() should be a sorted, unique array from Holidays(). (Although I didn't
test, it's just conceptual).

3) count elements in array.

lCountElem = UBound(H) - LBound(H) + 1

4) Can array contain an empty (null) value?

Yes, you can use a statement like H(0)=Null.

 

[Arvi Laanemets]

Thanks! I'll give it a try next week.


Arvi Laanemets

Hi

I'm trying to write an UDF which is an enchanced NETWORKDAYS
function, with syntax:
ENCHWORKDAYS(StartDate,EndDate,[Holidays],[Weekends])

As both optional parameters can be cell range references, or arrays,
or single values, then I decided to convert them to arrays, and do all
calculations with arrays later.

I haven't used arrays in VBA before. I have used them in FoxPro,
where is a lot of various functions and commands for working with
arrays - compared with this in VBA help I did find next to nothing
about them. So maybe someone explains, how to:
1) sort array elements;

Here's how I do it
http://www.dicks-blog.com/archives/2004/05/12/sorting-listboxes/

2) compact the array (remove elements);

I don't think there's an elegant, built-in way to do that. You can round
trip through a collection to filter out uniques, but since you're moving it
to H() anyway, I think I would just take the sorted array and

For i = LBound(Holidays) to UBound(Holidays) - 1
If Holidays(i) <> Holidays(i+1) Then
Redim Preserve H(0 to j)
H(j) = Holidays(i)
j=j+1
End If
Next i

Redim Preserve H(0 to j)
H(j) = Holidays(UBound(Holidays))

Now H() should be a sorted, unique array from Holidays(). (Although I didn't
test, it's just conceptual).

3) count elements in array.

lCountElem = UBound(H) - LBound(H) + 1

4) Can array contain an empty (null) value?

Yes, you can use a statement like H(0)=Null.


--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com