Arvi Laanemets wrote:
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/2...ing-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