array udf

[Imda14u]

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

 

[Bob Phillips]

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.

 

[Doug Jenkins]

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