Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Need help on UDF (working with arrays)
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 ----- Thanks in advance -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Need help on UDF (working with arrays)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel2000: Need help on UDF (working with arrays)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with arrays as arguments | Excel Worksheet Functions | |||
SUMPRODUCT with 3 arrays not working | Excel Worksheet Functions | |||
Working with Arrays | Excel Worksheet Functions | |||
Excel2000 A working formula has {braces} which disappear. Why? | Excel Worksheet Functions | |||
working on 2 different worksheets....arrays? | Excel Programming |