VBA, Matrix

[Enrst-Christian Lehmann]

Hello,

when I work with EXCEL, I sometimes use matrix functions. These functions,
in the German EXCEL they are called TREND or MMULT (just two examples), are
able to return more than one value to the EXCEL sheet.

Now, if I write a VBA function that calculates more than one number, would
it be possible to enable this function to behave like TREND or MMULT and
return more than one value to my EXCEL sheet?

If this is possible, then I would be happy to know about how to do this.
Thank you for any response in advance!

Christian Lehmann.

 

[Nick Hodge]

Christian

Why not use the worksheet functions within VBA???

Application.WorksheetFunction.Mmult(Arg1, Arg2)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

 

[Bob Phillips]

This is an example of a UDF that returns an array of results

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


This just picks up adate and increments it by a day, week, month, year, etc.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Enrst-Christian Lehmann]

Thank you for your response. I believe my question was not clear. Let me
try to write my question again:

Imagine I have my userdefined VBA function e.g.:

function MyFunction ()

This function calculates 4 values of type double. So I need to return all
the values to the EXCEL sheet. Right now I do the following:

MyFunction(1) as double (returns first value)
MyFunciton(2) as double (returns second value)
..
..
MyFunction(4) as double (returns forth value)

The numbers 1, 2, 3 and 4 tell the function, which value it shall return.
So all values are returned to the EXCEL sheet, but the function runs 4
times. Is it also possible to return all values to the EXCEL sheet and run
the function just one time?

I mean: can I create a userdefined VBA-function that I insert into my EXCEL
sheet with CTRL, SHIFT and RETURN and that will be able to return more than
one value to the EXCEL sheet? This is what(for example) the EXCEL function
RGP does: calculates slope and offset and returns the value for slope to
cell A1 (for example) and the value for offset to cell A2.

Thank you for any help!

Christian Lehmann

 

[Enrst-Christian Lehmann]

Hello Bob,

Thank you very much! Your example runs perfectly on my computer! Now I
know what do do!

Regards,
Christian

 

[Bob Phillips]

Christian,

Just be aware that my example was a simple one, it assumes a single column,
or a single row.

It gets trickier, not hard just trickier, if you have n rows x m columns you
have to handle it accordingly, and you also need to cater for situations
where the UDF may create less values than the worksheet array.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)