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

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

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

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