how do I create a UDF (VBA) that returns an array (ctrl+shift+enter)

[Fadi]

I need to create a worksheet function that can return an
array i.e. that can
be used with the Ctrl+Shift+Enter.

for example :
- I need to be able to select a range (say A2:A10)
- Enter my formula using the function for example
=myfunction(now(), "week")
- Press Ctrl+Shit+Enter ,and the function would
automatically fill the cells
A2 to A10 with today's date, the date one week from now,
the date two weeks
from now, etc....

I know this can be done as a macro, as simple worksheet
function, but I
can't find out how to do it using UDFs.

Thanks

Fadi

 

[Sherpa]

check this out :
http://www.exceltip.com/st/Return_every_n-
th_item_using_VBA_in_Microsoft_Excel/528.html for the
structure of the function. the embedded logic is similar
to what you have in mind.

 

[Robin Hammond]

Fadi,

Is this what you are after?

Public Function WeekCounter(dtStart As Date) As Variant
Dim nItems As Integer
Dim vOutput As Variant
Dim nCounter As Integer
nItems = Application.Caller.Cells.Count
nCounter = 0
ReDim vOutput(0 To nItems)
For nCounter = 0 To nItems - 1
vOutput(nCounter) = CDate(CLng(dtStart) + (nCounter * 7))
Next nCounter
WeekCounter = vOutput
End Function

Robin Hammond
www.enhanceddatasystems.com

 

[Bob Phillips]

Hi Fadi,

I think this does what you want

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 = Selection.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


--

HTH

RP
(remove nothere from the email address if mailing direct)