Can I use UDF in an array formula?

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Is it possible to use my own functions in an array formula? Is there a
special way I need to code such a function? I've tried writing it so
that it accepts a variant array as a parameter, but it doesn't seem to
work when I use it in an array formula.

For example:

Function MonthStart(ADate)

Dim i As Integer

If IsArray(ADate) Then
For i = LBound(ADate) To UBound(ADate)
ADate(i) = DateSerial(Year(ADate(i)), Month(ADate(i)), 1)
Next
MonthStart = ADate
Else
MonthStart = DateSerial(Year(ADate), Month(ADate), 1)
End If

End Function
 
Gary,

What do you mean when you say it doesn't work, what are you trying to
achieve.

I tried it with this formula
=MATCH(DATE(2003,9,1),monthstart({"12/08/03","12/09/03"}))
and it returned 2 as I expected.
 
Public Function MonthStart(ADate As Range)
bdate = ADate.Value
Dim i As Integer
brow = ADate(1).Row
bcol = ADate(1).Column
If ADate.Count > 1 Then
For Each cell In ADate
i = cell.Row - brow + 1
j = cell.Column - bcol + 1
bdate(i, j) = DateSerial(Year(cell), Month(cell), 1)
Next
MonthStart = bdate
Else
MonthStart = DateSerial(Year(ADate), Month(ADate), 1)
End If

End Function

Worked for me. I set it so you have to pass a contiguous range as the
argument of the function.
 
Back
Top