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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com