VBA Array multiplication, elementwise without looping

Question

Hi everybody: Following liitle issue:

Option Base 1
Sub Test()
Dim aa() As Integer
Dim bb() As Integer
ReDim aa(3)
ReDim bb(3)
For j = 1 To 3
    aa(j) = j * 2
    bb(j) = j * 3
Next j
End Sub

Now the only little thing that I want to do is to multiply the two one dimensional arrays elementwise without looping, and then to unload this new array (6,24,54) in a range. I'm sure this must be easily possible. A solution that I would see is to create a diagonal matrix (array) and then to use mmult, but I'm sure this is doable in a very simple manner. Thanks for the help.

Answer 1

There is no way to do multiplication on each element in the array without a loop. Some languages have methods that appear to do just that, but under the hood they are looping.

As you've mentioned in your comments, you have 2 choices:

• Loop through a range and do the multiplication
• Dump the range into an array, do the multiplication, then dump back onto a range

It all depends on your data, but almost always you'll find that dumping a range into a variant array, doing your work, and dumping it back will be much faster than looping through a range of cells. How you dump it back into a range will also affect the speed, mind you.

Answer 2

It is possible to multiply ranges without explicit looping, e.g. try:

sub try()
  [c1:c3].value = [a1:a3 * b1:b3]
end sub

Same logic as in: =sumproduct(a1:A3-b1:b3;a1:A3-b1:b3)