array udf
 

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

array udf
 

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" wrote in message
...
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

array udf
 

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