Hi all,

some worksheetfunctions need to be confirmed as Array function with Ctrl
+ Shift + Enter. Same thing for formulaconstructions where you have only
one fomrula for a range.

Can you also write your own UDF as array function?

Any idea where I can find a reference?

greets,

Sybolt

Here is an example

Function myFunction(inDate As Date, inType As String) As Variant
Dim nextDate As Date
Dim i As Long
Dim cCells As Long
Dim tmpArray() As Date

nextDate = inDate
cCells = application.caller.Cells.Count
ReDim Preserve tmpArray(0 To cCells)
For i = 1 To cCells
tmpArray(i - 1) = nextDate
Select Case LCase(inType)
Case "day": nextDate = nextDate + 1
Case "week": nextDate = nextDate + 7
Case "month": nextDate = nextDate + 30
Case "year": nextDate = nextDate + 365
End Select
Next i

If Application.Caller.Rows.Count = 1 Then
myFunction = tmpArray
Else
myFunction = Application.Transpose(tmpArray)
End If

End Function

You would select the target range, and array enter
=MyFunction(TODAY(),"week") as an example.

--
__________________________________
HTH

Bob

"Imda14u" <(E-Mail Removed)> wrote in message
news:4a599ed3$0$191$(E-Mail Removed)...
> Hi all,
>
> some worksheetfunctions need to be confirmed as Array function with Ctrl +
> Shift + Enter. Same thing for formulaconstructions where you have only one
> fomrula for a range.
>
> Can you also write your own UDF as array function?
>
> Any idea where I can find a reference?
>
> greets,
>
> Sybolt

In short, you can make a UDF an array function by declaring it as a Variant,
and assigning the function return value to an array:

Function FunctionName(..) as variant
...
FunctionName = ArrayName
End Function




"Imda14u" wrote:

> Hi all,
>
> some worksheetfunctions need to be confirmed as Array function with Ctrl
> + Shift + Enter. Same thing for formulaconstructions where you have only
> one fomrula for a range.
>
> Can you also write your own UDF as array function?
>
> Any idea where I can find a reference?
>
> greets,
>
> Sybolt
>