Functions that return a Range

[MLMeneses]

To put the quetion simple: Is it possible to design Excel Funtions similar
to the MMULT function using exclusively VBA.

Thank you for any help

 

[Bob Phillips]

This is an example of a function that takes an input and creates an array of
output cells, it should get you started

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



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)