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

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

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