Clever Geek Handbook

VBA array multiplication, individually without looping

Greetings to you: Follow the line:

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 I want to do is to multiply the two one-dimensional arrays on elements without loops, and then dump this new array (6,24,54) in the range. I'm sure it should be easy. The solution I would see is to create a diagonal matrix (array) and then use mmult, but I'm sure it's very easy to do this. Thanks for the help.

+3


source to share


3 answers


It is not possible to perform multiplication on every element of an array without a loop. Some languages ​​have methods that seem to do just that, but they get stuck under the hood.

As you mentioned in your comments, you have 2 options:



  • Scroll through the range and multiply
  • Cast a range to an array, do a multiplication, then cast back to a range

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

+4


source


It is possible to multiply ranges without an explicit loop, for example. try:

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


The same logic as in: = SUMPRODUCT (a1: A3-B1: B3; A1: A3-B1: b3)

+4


source


I'm not entirely sure if this is possible in any language (except maybe some functional languages). Perhaps if you told us why you wanted to do this, we could help you more?

0


source






More articles:


All Articles