About Us

Naomi

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
User Defined Functions	CH	Excel Worksheet Functions	4	September 22nd 08 10:16 AM
passing arrays to user defined functions	ramki	Excel Worksheet Functions	2	February 15th 06 08:34 AM
User Defined Functions	Chris Gorham	Excel Programming	1	November 24th 04 04:04 PM
user defined functions	Alexander Bogomolny	Excel Programming	4	July 25th 04 07:54 PM
excel functions and User defined functions	Kanan	Excel Programming	4	May 20th 04 11:21 PM

Menu

About Us