Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic arrays as an argument/result in programmed functions
Something that's puzzeling me for some time, that I can't find in any of the books, although it's probably pretty straighforward
How can one reference to dynamic arrays in function arguments? Suppose I want to program a function like MMULT to be used in Excel (for instance DETERMINANT to calculate the determinant of a matrix), that can uses a range of values as argument (a matrix). When using function MMULT in excel you select a (variable! So dynamic array?) range of values as it's argument, and press CTRL-SHFT-Enter to return again a range of values. How can I achieve this when programming myself - How is the function declared? Like Function Determinant(argument), what should argument be - How can one reference to the range in the argument? How can we determine it's size (max row, max column)? How does one reference to it's elements - When the result should be a range or matrix, how is this achieved I'm sorry if this problem is too simple, but I cannot find anything about this in the manuals or on-line help. It's frustrating.. Thanks in advance Rem |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic arrays as an argument/result in programmed functions
Public Function MyMatrixFunc(rng1 As Range, rng2 As Range)
Dim rwcnt1 As Long, rwcnt2 As Long Dim colcnt1 As Long, colcnt2 As Long Dim myarr As Variant rwcnt1 = rng1.Rows.Count rwcnt2 = rng2.Rows.Count colcnt1 = rng1.Columns.Count colcnt2 = rng2.Columns.Count If rwcnt1 < rwcnt2 Or colcnt1 < colcnt2 Then MyMatrixFunc = CVErr(xlErrRef) Else ReDim myarr(1 To rwcnt1, 1 To colcnt1) For i = 1 To rwcnt1 For j = 1 To colcnt1 myarr(i, j) = rng1(i, j) * rng2(i, j) Next Next MyMatrixFunc = myarr End If End Function select a range the same size as the arguments, enter =mymatrixfunc(A1:B3,C8:D10) with Ctrl+Shift+Enter to array enter the formula. In the example, select a 3 x 2 range of cells. -- Regards, Tom Ogilvy "AjaxRocks" wrote in message ... Something that's puzzeling me for some time, that I can't find in any of the books, although it's probably pretty straighforward: How can one reference to dynamic arrays in function arguments? Suppose I want to program a function like MMULT to be used in Excel (for instance DETERMINANT to calculate the determinant of a matrix), that can uses a range of values as argument (a matrix). When using function MMULT in excel you select a (variable! So dynamic array?) range of values as it's argument, and press CTRL-SHFT-Enter to return again a range of values. How can I achieve this when programming myself? - How is the function declared? Like Function Determinant(argument), what should argument be? - How can one reference to the range in the argument? How can we determine it's size (max row, max column)? How does one reference to it's elements? - When the result should be a range or matrix, how is this achieved? I'm sorry if this problem is too simple, but I cannot find anything about this in the manuals or on-line help. It's frustrating... Thanks in advance! Rem |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic arrays as an argument/result in programmed functions
Perhaps you should simplify your request.
If you're talking about an analog to the Excel MMULT Function, say MyMMult, the function will take two arguments. The second argument needs to have as many "rows" as there are "columns" in the first argument--that's true for MMULT and for MyMMult. The range into which it is array entered needs to have at least as many rows as there are "rows" in the first argument and at least as many columns as there are "columns" in the second argument--that's true for MMULT and for MyMMult. Now what's your question about a homemade MMULT function? Alan Beban AjaxRocks wrote: Something that's puzzeling me for some time, that I can't find in any of the books, although it's probably pretty straighforward: How can one reference to dynamic arrays in function arguments? Suppose I want to program a function like MMULT to be used in Excel (for instance DETERMINANT to calculate the determinant of a matrix), that can uses a range of values as argument (a matrix). When using function MMULT in excel you select a (variable! So dynamic array?) range of values as it's argument, and press CTRL-SHFT-Enter to return again a range of values. How can I achieve this when programming myself? - How is the function declared? Like Function Determinant(argument), what should argument be? - How can one reference to the range in the argument? How can we determine it's size (max row, max column)? How does one reference to it's elements? - When the result should be a range or matrix, how is this achieved? I'm sorry if this problem is too simple, but I cannot find anything about this in the manuals or on-line help. It's frustrating... Thanks in advance! Rem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function argument correct result but answer 0 in cell? | Excel Worksheet Functions | |||
Dynamic Arrays | Excel Discussion (Misc queries) | |||
Dynamic Range and arrays | Excel Discussion (Misc queries) | |||
Dynamic arrays | New Users to Excel | |||
Dynamic Arrays | Excel Worksheet Functions |