1 Replies - 5533 Views - Last Post: 05 October 2013 - 10:06 PM Rate Topic: -----

#1 SweetBabyLou92   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-July 13

Excel/Visual Basic Array Multiplication

Posted 03 October 2013 - 04:25 PM

Hi everyone,
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]


[AE, BF]
[CG, DH]

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
        i = aRows
    End If
    If aCols > bCols Then
        j = bCols
        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

Attached image(s)

  • Attached Image

Is This A Good Question/Topic? 0
  • +

Replies To: Excel/Visual Basic Array Multiplication

#2 BobRodes   User is offline

  • Lovable Curmudgeon
  • member icon

Reputation: 600
  • View blog
  • Posts: 3,071
  • Joined: 19-May 09

Re: Excel/Visual Basic Array Multiplication

Posted 05 October 2013 - 10:06 PM

If I understand correctly, you're making the assumption that all the cells will have numbers that can be multiplied. If you have, say, five rows in one column and six in another, you'll have a blank cell in the last row of the second column. If you try to perform a multiplication on that cell, you'll get the value error. Your code doesn't take this into account; you take the largest number of rows and the largest number of columns and set your for loop to that.

I would suggest that you check for a blank cell in your loop and use 0 if you find one.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1