Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
Redimming an array dynamically assigned from range (how to redim first dimension of a 2-D array? /or/ reverse the original array order) | Excel Programming | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming | |||
Array problem: Key words-Variant Array, single-element, type mismatch error | Excel Programming |