Fastest Way of Multiplying Arrays in Excel VBA and .NET

Question

I am trying to multiply arrays as quickly as I can. There will be very large arrays, multiplied a lot of times. I have the below code which uses a nested loop, is there any faster way of doing this? I have array a() and b(), and I want to make sum(1) = a(1)*b(1) + a(1)*b(2) + a(1)*b(3) etc..

Currently I am in Excel VBA but I will be converting to VBA.NET soon. How much faster should this be than running it in excel vba? Are there faster methods in .Net that I could use?

Sub Test()
 ' Just creating the arrays
 Dim a() as Integer, b() as Integer, sum() as Integer, i as Integer, j as Integer
 ReDim a(1 to 3)
 ReDim b(1 to 3)
 ReDim sum(1 to 3)

 For i = 1 to 3 
  a(i) = 2 * i
  b(i) = 3 * i
 Next i

 ' This is my code I am  interested in
 For i = 1 to 3
  For j = 1 to 3
   sum(i) = sum(i) + a(i) * b(j)
  Next j
 Next i

"very large" means what exactly (or at least approximately), and "multiplied a lot of times" means what ? If you're concerned about performance, it helps to state more precisely what scale of numbers you're going to be working with. — Tim Williams
– Tim Williams, Commented May 27, 2014 at 22:57
You sum seems to add up the rows of an outer product, and not matrix multiplication in the standard form. Standard matrix multiplication requires three loops with each element a sum s(i,j) = SUM(a(i,k)*b(k,j),k=1..n) — John Alexiou
– John Alexiou, Commented May 27, 2014 at 23:05
Where is sum(i) initialized? You are using un-initialized variables. — John Alexiou
– John Alexiou, Commented May 27, 2014 at 23:10

Tim Williams · Accepted Answer · 2014-05-28 03:57:44Z

2

Unless your example is over-simplified, you can remove the inner loop by summing up your b() values, since

a*p + a*q + a*r + ... + a*z

is equivalent to

a * (p+q+r+...+z)

So:

Sub Test()

    Dim a(), b(), sum(), i As Long, j As Long, tmp, t, n As Long, tmp2
    ReDim a(1 To 20000)
    ReDim b(1 To 50)
    ReDim sum(1 To 20000)

    For i = 1 To 20000
        a(i) = i / 100
    Next i

    tmp2 = 0
    For i = 1 To 50
        b(i) = i / 100
        tmp2 = tmp2 + b(i)
    Next i

    t = Timer
    For n = 1 To 100
        For i = 1 To 20000
            For j = 1 To 50
             sum(i) = sum(i) + a(i) * b(j)
            Next j
        Next i
    Next n
    Debug.Print Timer - t ' ~5.5sec

    ReDim sum(1 To 20000)

    t = Timer
    For n = 1 To 100
        For i = 1 To 20000
            sum(i) = a(i) * tmp2
        Next i
    Next n
    Debug.Print Timer - t ' ~ 0.1 sec

End Sub

edited May 28, 2014 at 3:57

answered May 28, 2014 at 0:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Alexiou · Accepted Answer · 2014-05-27 23:09:32Z

1

This should be faster:

' This is my code I am  interested in
For i = 1 to 3
  ai = a(i)
  sumi = 0#
  For j = 1 to 3
    sumi = sumi + ai * b(j)
  Next j
  sum(i) = sumi
Next i

Reducing the number of times you look up an array variable should improve things as modern processors take a lot longer to read from memory that to multiply values. I would consider also loop unrolling, but you have to test with a high performance timer to see if it helps.

answered May 27, 2014 at 23:09

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

1 Comment

Adam12344 Over a year ago

This is a good point, thanks. And I'll read up on loop unrolling

Collectives™ on Stack Overflow

Fastest Way of Multiplying Arrays in Excel VBA and .NET

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related