VBA: Writing an array function (like MMult)

[fxmolden]

Hi there,

I want to write a VBA-function, that uses several cells for its outpu
(like the array functions MMult or MInv do for exmple).

For illustration purposes I choose to write a simple matrix addition
which adds the corresponding elements of two matrices (tables) . Th
result of this addition (a matrix as well) needs multiple cells fo
output (see MMult).

|1 2| |5 6| |6 8|
| | + | | = | |
|3 4| |7 8| |10 12|

Actually, the function should behave exactly like the function MMul
does: especially, I want to use this function both in the spreadshee
(the result calls for several cells) and as function in other VB
codes.

My code only works as a function in VBA, i.e. the result of my functio
(a matrix) can be used for further calculations in VBA. Calling th
function in the spreadsheet does not work.

Does anybody know, how I could fix this problem?

Cheers and thanx in advance,
Felix


My code (see attachment):

Option Base 1 'indices in matrices start @ 1

Function MAdd(MatA As Variant, MatB As Variant) As Variant

Dim i As Integer, j As Integer
Dim m As Integer, n As Integer
Dim MatC As Variant 'dynamic array with resultin
matrix

On Error GoTo MAdd_Error

If Not (IsArray(MatA) And IsArray(MatB)) Then
Err.Raise vbObjectError + 11, "MAdd", "Please use matrices!"
End If

'Check dimensions (of the input arrays)
If Not (UBound(MatA, 1) = UBound(MatB, 1) And UBound(MatA, 2)
UBound(MatB, 2)) Then
Err.Raise vbObjectError + 10, "MAdd", "The matrices hav
different dimensions!"
End If

'Create resulting matrix
m = UBound(MatA, 1) 'number of rows
n = UBound(MatA, 2) 'number of columns
ReDim MatC(m, n) 'matrix with m rows and n columns

'Add the two matrices
For i = 1 To m
For j = 1 To n
MatC(i, j) = MatA(i, j) + MatB(i, j)
Next j
Next i

MAdd_Exit:

MAdd = MatC

Exit Function
MAdd_Error:
MatC = "#Value!"
MsgBox Err.Description
Resume MAdd_Exit
End Functio

 

[Bernie Deitrick]

Felix,

To call your function from a spreasheet, you should select an
appropriately sized range of cells, then array-enter your function
using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

You main problem is that MatA and MatB are ranges, but you are treating them
as if they are arrays.

I applied a quick fix, to get it working - but you need to revisit the code.
A range passes the isarray test, so you need to test for typename equal to
Range first. A range does not have an upper bound, so that is where you
run into problems.

 

[Tom Ogilvy]

Whoops,
Here is the quick fix:

Function MAdd(MatA As Variant, MatB As Variant)

Dim i As Integer, j As Integer
Dim m As Integer, n As Integer
Dim MatC As Variant 'dynamic array with resulting 'matrix
Dim MatA1 As Variant
Dim MatB1 As Variant

On Error GoTo MAdd_Error
If TypeName(MatA) = "Range" Then
MatA1 = MatA.Value
Else
MatA1 = MatA
End If
If TypeName(MatB) = "Range" Then
MatB1 = MatB.Value
Else
MatB1 = MatB
End If
If Not (IsArray(MatA1) And IsArray(MatB1)) Then
Err.Raise vbObjectError + 11, "MAdd", "Please use matrices!"
End If


'Check dimensions (of the input arrays)
If Not (UBound(MatA1, 1) = UBound(MatB1, 1) And UBound(MatA1, 2) =
UBound(MatB1, 2)) Then
Err.Raise vbObjectError + 10, "MAdd", "The matrices have different
dimensions!"
End If
'Create resulting matrix
m = UBound(MatA1, 1) 'number of rows
n = UBound(MatA1, 2) 'number of columns
ReDim MatC(m, n) 'matrix with m rows and n columns

'Add the two matrices
For i = 1 To m
For j = 1 To n
MatC(i, j) = MatA1(i, j) + MatB1(i, j)
Next j
Next i

MAdd_Exit:

MAdd = MatC

Exit Function
MAdd_Error:
MatC = "#Value!"
MsgBox Err.Description
Resume MAdd_Exit
End Function

 

[Alan Beban]

As you work with this function there are several factors you should keep
in mind relating to generality. E.g., Using the Option Base 1 Statement
does not insure that MatA (nor MatB) will be a 1-based array. Nor is
there anything in what you posted that assures that MatA and MatB are to
be restricted to 2-dimensional arrays, although the code will fail if
they are 1-dimensional.

If Mat A is 1-dimensional, UBound(MatA, 1) will not return the number of
"rows"; it will return the number of "columns" if MatA is 1-based, and
it won't even do that if MatA is not 1-based.

If MatA is 2-dimensional but not 1-based, UBound(MatA, 1) will not
return the number of "rows"; the general expression for the number of
"rows" of a 2-dimensional array of any base, say MatA, is
UBound(MatA, 1) - LBound(MatA, 1) + 1.

Alan Beban