Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use UDF in an array formula?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use UDF in an array formula?
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. -- HTH Bob Phillips "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |