Error on UDF for matrix multiplication

[arrun]

Hi all,

I wanted to write my own function for matrix multiplication. My code is below :


Function matrix_mult(matrix1() As Double, matrix2() As Double, row1 As Byte, col1 As Byte, row2 As Byte, col2 As Byte) As Range
ReDim result_matrix(row1, col2) As Double
Dim i As Integer, j As Integer, k As Integer
Dim sum As Double

For i = 1 To row1
For j = 1 To col2
sum = 0
For k = 1 To col1
sum = sum + matrix1(i, k) * matrix2(k, j)
Next k
result_matrix(i, j) = sum
Next j
Next i
matrix_mult = result_matrix
End Function


Here 1st two arguments are two Matrices and next four are their respective row and col numbers. For example if I want to multiply 2 square matrices with order 4, then last four input will be : 4,4,4,4.

My intention is to use this function as UDF. However when I use it in escel sheet using following syntax : =matrix_mult(A1:D4,F1:I4,4,4,4,4), I get #Name? error.

I did usual way on array handling. That is after selecting resulting cells (here 4X4) and then writing the code in formula bar, I pressed shift+control+enter.

Can anyone here please tell me, what additional things I need to have?

Regards,
:banghead:

[Jan Karel Pieterse]

Make the function public:

Public Function matrix_mult(.......

[Bob Phillips]

I have derived the last 4 arguments rather than haing to input them



Function matrix_mult(matrix1, matrix2) As Variant
Dim m1 As Variant
Dim m2 As Variant
Dim m1Rows As Long, m1Cols As Long
Dim m2Cols
Dim i As Long, j As Long, k As Long
Dim sum As Double

m1 = matrix1
m2 = matrix2
m1Rows = UBound(m1, 1) - LBound(m1, 1) + 1
m1Cols = UBound(m1, 2) - LBound(m1, 2) + 1
m2Cols = UBound(m2, 2) - LBound(m2, 2) + 1
ReDim result_matrix(1 To m1Rows, 1 To m2Cols) As Double
For i = 1 To m1Rows
For j = 1 To m2Cols
sum = 0
For k = 1 To m1Cols
sum = sum + matrix1(i, k) * matrix2(k, j)
Next k
result_matrix(i, j) = sum
Next j
Next i
matrix_mult = result_matrix
End Function

[arrun]

Noh. Still I am getting #VALUE! error. I have pasted those syntax in the code window of "module1". Any suggestion?

[arrun]

Ya thats it :D I got where the errors are. I put very few elements than required.

Thanks all

[arrun]

I have one more question. In excel default functions, when I type function name in formula bar, a pop-up opens to display the function arguments. For example when I type 'sum' function in formula bar, a pop-up opens showing "SUM(number1, [number2],...)". My question is how to create same thing in UDF?

Regards,

[Paul_Hossler]

When you create your Sub or Function, use the ParamArray argument as the last (or only) input

Online Help under Sub has a good writeup and example

ParamArray Optional. Used only as the last argument in arglist to indicate that the final argument is an Optional array (http://vbaexpress.com/forum/ms-help://MS.EXCEL.DEV.12.1033/EXCEL.DEV/content/HV01200929.htm) of Variant elements. The ParamArray keyword allows you to provide an arbitrary number of arguments. ParamArray can't be used with ByVal, ByRef, or Optional.

Use the LBound ( ) and UBound( ) to loop through, since I recall Paramarray always starts with index = 1, regardless of the Option Base setting

Paul

[arrun]

Hi Paul,

Thanx for this reply. Howeevr this is not probably what I am looking for. I want to add TIPS for each arguments in that function. I also wnat those tips will be visible when user starts entering the arguments for that UDF in formula bar, just like dafault excel functions

[Bob Phillips]

Ya thats it :D I got where the errors are. I put very few elements than required.

Thanks all

I thought of that, it can be coded around



Function matrix_mult(matrix1, matrix2) As Variant
Dim m1 As Variant
Dim m2 As Variant
Dim m1Rows As Long, m1Cols As Long
Dim m2Cols
Dim i As Long, j As Long, k As Long
Dim sum As Double
Dim rng As Range
Dim resultRows As Long, resultCols As Long

Set rng = Application.Caller
resultRows = rng.Rows.Count
resultCols = rng.Columns.Count
m1 = matrix1
m2 = matrix2
m1Rows = UBound(m1, 1) - LBound(m1, 1) + 1
m1Cols = UBound(m1, 2) - LBound(m1, 2) + 1
m2Cols = UBound(m2, 2) - LBound(m2, 2) + 1
ReDim result_matrix(1 To resultRows, 1 To resultCols) As Variant
For i = 1 To m1Rows
For j = 1 To m2Cols
sum = 0
For k = 1 To m1Cols
sum = sum + matrix1(i, k) * matrix2(k, j)
Next k
result_matrix(i, j) = sum
Next j
For j = j To resultCols
result_matrix(i, j) = ""
Next j
Next i
For i = i To resultRows
For j = 1 To resultCols
result_matrix(i, j) = ""
Next j
Next i
matrix_mult = result_matrix
End Function

[Jan Karel Pieterse]

http://www.jkp-ads.com/Articles/RegisterUDF00.htm

Warning: very complex stuff.

[Bob Phillips]

I have one more question. In excel default functions, when I type function name in formula bar, a pop-up opens to display the function arguments. For example when I type 'sum' function in formula bar, a pop-up opens showing "SUM(number1, [number2],...)". My question is how to create same thing in UDF?

Regards,


This is not provided within Excel, but Laurent Longre's has written a DLL utility, named Funcustomize, which provides these.

It is free, and you can find it at

http://xcell05.free.fr/