Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Matrix
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Matrix
Christian
Why not use the worksheet functions within VBA??? Application.WorksheetFunction.Mmult(Arg1, Arg2) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "Enrst-Christian Lehmann" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Matrix
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Matrix
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 "Nick Hodge" schrieb im Newsbeitrag ... Christian Why not use the worksheet functions within VBA??? Application.WorksheetFunction.Mmult(Arg1, Arg2) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007 www.officeusergroup.co.uk "Enrst-Christian Lehmann" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Matrix
Hello Bob,
Thank you very much! Your example runs perfectly on my computer! Now I know what do do! Regards, Christian |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA, Matrix
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) "Enrst-Christian Lehmann" wrote in message ... Hello Bob, Thank you very much! Your example runs perfectly on my computer! Now I know what do do! Regards, Christian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a value out of a Matrix | Excel Discussion (Misc queries) | |||
How can I transpose nXm matrix to mXn Matrix | Excel Worksheet Functions | |||
Matrix Help | Excel Worksheet Functions | |||
Help with Matrix | Excel Programming | |||
How to get the value in a matrix? | Excel Programming |