Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays in User defined functions
Hi,
I have the following in a function that is essentially the conditional sum, counting the number of occasions when one column has the value AM in it and the other has the date Curr_month. Is there any way of doing this so that it runs faster? Can array functions be used in VB? dim AM as string dim Curr_month as date dim meets, count_meetings as integer If Sheets("DATA").Cells(i, 2).Value = AM And Sheets("DATA").Cells(i, 5).Value = Curr_month Then meets = meets + 1 Else End If i = i + 1 Loop Count_meetings = meets |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays in User defined functions
Try
Evaluate("SUMPRODUCT(--(DATA!B1:B100=""AM""),--(DATA!E1:E100=" & Month(Date) & "))") -- HTH RP (remove nothere from the email address if mailing direct) "Naomi" wrote in message ... Hi, I have the following in a function that is essentially the conditional sum, counting the number of occasions when one column has the value AM in it and the other has the date Curr_month. Is there any way of doing this so that it runs faster? Can array functions be used in VB? dim AM as string dim Curr_month as date dim meets, count_meetings as integer If Sheets("DATA").Cells(i, 2).Value = AM And Sheets("DATA").Cells(i, 5).Value = Curr_month Then meets = meets + 1 Else End If i = i + 1 Loop Count_meetings = meets |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays in User defined functions
You don't share how you've declared the function, but I can guess...
It is a very bad idea to reference worksheet cells in a UDF that are not passed as an argument and you are doing that with Cells(i,2) and Cells(i,5). There are ways to access some XL functions in VBA through the Application.Worksheetfunction object and the Evaluate function gives even more flexible access to XL constructs. However, Evaluate, IMO, is less than reliable. Below is a generic way to work with a range passed as an argument to a UDF. It would be used as =myFunc(A1:A16,B1:B16) Function myFunc(inAMVals As Range, inDateVals As Range) Dim i As Integer, AMVals, DateVals If inAMVals.Columns.Count 1 Then AMVals = inAMVals _ Else AMVals = Application.WorksheetFunction.Transpose(inAMVals) 'should actually authenticate above (and below) are single _ row or single column ranges If inDateVals.Columns.Count 1 Then DateVals = inDateVals _ Else DateVals = Application.WorksheetFunction.Transpose(inDateVals ) 'should authenticate sizes of AMVals and DateVals are same For i = LBound(AMVals) To UBound(AMVals) Debug.Print AMVals(i) & "," & DateVals(i) If AMVals(i) = "AM" And DateVals(i) < #3/1/2005# Then _ myFunc = myFunc + 1 Next i End Function -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi, I have the following in a function that is essentially the conditional sum, counting the number of occasions when one column has the value AM in it and the other has the date Curr_month. Is there any way of doing this so that it runs faster? Can array functions be used in VB? dim AM as string dim Curr_month as date dim meets, count_meetings as integer If Sheets("DATA").Cells(i, 2).Value = AM And Sheets("DATA").Cells(i, 5).Value = Curr_month Then meets = meets + 1 Else End If i = i + 1 Loop Count_meetings = meets |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays in User defined functions
Thanks, very helpful! I'm new to functions!
;) "Tushar Mehta" wrote: You don't share how you've declared the function, but I can guess... It is a very bad idea to reference worksheet cells in a UDF that are not passed as an argument and you are doing that with Cells(i,2) and Cells(i,5). There are ways to access some XL functions in VBA through the Application.Worksheetfunction object and the Evaluate function gives even more flexible access to XL constructs. However, Evaluate, IMO, is less than reliable. Below is a generic way to work with a range passed as an argument to a UDF. It would be used as =myFunc(A1:A16,B1:B16) Function myFunc(inAMVals As Range, inDateVals As Range) Dim i As Integer, AMVals, DateVals If inAMVals.Columns.Count 1 Then AMVals = inAMVals _ Else AMVals = Application.WorksheetFunction.Transpose(inAMVals) 'should actually authenticate above (and below) are single _ row or single column ranges If inDateVals.Columns.Count 1 Then DateVals = inDateVals _ Else DateVals = Application.WorksheetFunction.Transpose(inDateVals ) 'should authenticate sizes of AMVals and DateVals are same For i = LBound(AMVals) To UBound(AMVals) Debug.Print AMVals(i) & "," & DateVals(i) If AMVals(i) = "AM" And DateVals(i) < #3/1/2005# Then _ myFunc = myFunc + 1 Next i End Function -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi, I have the following in a function that is essentially the conditional sum, counting the number of occasions when one column has the value AM in it and the other has the date Curr_month. Is there any way of doing this so that it runs faster? Can array functions be used in VB? dim AM as string dim Curr_month as date dim meets, count_meetings as integer If Sheets("DATA").Cells(i, 2).Value = AM And Sheets("DATA").Cells(i, 5).Value = Curr_month Then meets = meets + 1 Else End If i = i + 1 Loop Count_meetings = meets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Functions | Excel Worksheet Functions | |||
passing arrays to user defined functions | Excel Worksheet Functions | |||
User Defined Functions | Excel Programming | |||
user defined functions | Excel Programming | |||
excel functions and User defined functions | Excel Programming |