Can I use UDF in an array formula?
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.
--
Regards,
Tom Ogilvy
"Gary" wrote in message
...
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
|