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)


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