Arrays in User defined functions

[Guest]

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

 

[Bob Phillips]

Try

Evaluate("SUMPRODUCT(--(DATA!B1:B100=""AM""),--(DATA!E1:E100=" & Month(Date)
& "))")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

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

 

[Guest]

Thanks, very helpful! I'm new to 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