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


Arvi Laanemets


"Dick Kusleika" wrote in message
...
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