I've been given the assignment to do some matrix calculations in excel using the built-in developer VBA editor. The goal of this program is to take the "Inner Product" of two arbitrary matrices provided in the excel spreadsheet by creating a function to use. This function multiplies each cell with the corresponding cell in the second matrix.
For Example: the multiplication of the following two matrices...
[A , B] [E, F]
[C , D] [G, H]
The function is supposed to multiply two arbitrary matrices even if the dimensions of the matrices do not match one another. The function must take the common dimensions of each and output the products into a new block of cells as a new matrix.
I had a difficult time wrapping my head around this assignment. What I decided to do is create two different arrays for the two matrices. I counted the number of rows and columns for each array and took the lesser amount of rows and columns between the two matrices. After that, I tried to multiply each cell by the corresponding cell in the second array. When all of this was over, however, I found myself looking at the dreaded #VALUE! error in excel. The function was running into some problems and I could not pinpoint at which point the function was derailed.
Here is the function:
Function InnerProduct(MatrixRange1, MatrixRange2) As Double Dim m As Integer, n As Integer Dim i As Integer, j As Integer Dim aRows As Integer, aCols As Integer Dim bRows As Integer, bCols As Integer Dim A() As Variant, B() As Variant, C() As Variant aRows = MatrixRange1.Rows.Count aCols = MatrixRange1.Columns.Count bRows = MatrixRange2.Rows.Count bCols = MatrixRange2.Columns.Count If aRows > bRows Then i = bRows Else i = aRows End If If aCols > bCols Then j = bCols Else j = aCols End If ReDim A(i, j) As Variant, B(i, j) As Variant, C(i, j) As Variant For m = 1 To i For n = 1 To j A(m, n) = MatrixRange1.Cells(m, n) B(m, n) = MatrixRange2.Cells(m, n) C(m, n) = A(m, n) * B(m, n) Next n Next m InnerProduct = C(m, n) End Function
If anyone has any input on where I might look to fix this problem, it would be GREATLY GREATLY appreciated.
My professor has given two example matrices to test out the function:
[5 , -4] [1 , 3]
[-7 , 1] [4 , 9]
[8 , -2] * [8 , -5]
[4 , 3] [3 , 6]
[9 , 1] [-5 , 4]
Problem #1 has also been attached