Array multiplication in VBA

  • Thread starter Thread starter Dan E
  • Start date Start date
D

Dan E

I am trying to simulate the behaviour of an array multiplication such as
{=(A1:A10)*(B1:B10)} in VBA. The resulting array should contain:
={A1*B1, A2*B2, . . . , A10*B10}.

I was hoping to avoid writing a loop if it is at all possible?

I don't think there is a worksheetfunction which does this and I was
hoping to avoid the loop:

For i = 0 to Ubound(Temp1)
Temp3(i) = Temp1(i)*Temp2(i)
Next

Is there an easy way to do this?

Dan E
 
Why not put the formula =A1*B1 in the first cell and copy it down/across as
needed?
What is wrong with a loop?
 
there are no array operators or array functions in VBA. You would need to
loop.

if you want to use worksheet functions

Sub ArrayMult()
For i = 1 To 10
Range("A1")(i).Value = i
Range("B1")(i).Value = i ^ 2
Next
varr = Application.MMult( _
Range("A1:A10"), Application.Transpose( _
Range("B1:B10")))
For i = LBound(varr, 2) To UBound(varr, 2)
Debug.Print varr(1, i)
Next
End Sub
 
Dan. This is not the best, but it's the only non-looping way I know of.
:>)

Sub Demo()
'//Dana DeLouis
Dim v, w, z

v = Array(1, 2, 3, 4)
w = Array(10, 11, 12, 13)

ActiveWorkbook.Names.Add "t_", Array(v, w)
z = [Index(t_,1,0)*Index(t_,2,0)]
End Sub
 
Just as a point of interest, in light testing using the arrays provided, the
method suggested by Dana is more than 200 times slower than looping through
the arrays and doing the multiplication. Using MMULT was 10 times slower.

--
Regards,
Tom Ogilvy

Dana DeLouis said:
Dan. This is not the best, but it's the only non-looping way I know of.
:>)

Sub Demo()
'//Dana DeLouis
Dim v, w, z

v = Array(1, 2, 3, 4)
w = Array(10, 11, 12, 13)

ActiveWorkbook.Names.Add "t_", Array(v, w)
z = [Index(t_,1,0)*Index(t_,2,0)]
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Dan E said:
I am trying to simulate the behaviour of an array multiplication such as
{=(A1:A10)*(B1:B10)} in VBA. The resulting array should contain:
={A1*B1, A2*B2, . . . , A10*B10}.

I was hoping to avoid writing a loop if it is at all possible?

I don't think there is a worksheetfunction which does this and I was
hoping to avoid the loop:

For i = 0 to Ubound(Temp1)
Temp3(i) = Temp1(i)*Temp2(i)
Next

Is there an easy way to do this?

Dan E
Click to expand...
Click to expand...
 
For projects like this I would normally use matlab, and the coding
is much simpler

temp3 = temp2*.temp1

and it's quick. But this code needs to interact with excel, so . . .

Note: I don't do a lot of numerical programming with VBA, so my
knowledge of VBA's capabilities and strengths in these areas is
limited. All I do know is matlab is much more suited for these
applications (as it should be since that is the use it was primarily
designed for).

I could use C++ but have a no knowledge of how to build add-ins.
If you know of a good resource that you could direct me towards
that would be extremely helpful also...

Dan E
 
I would suspect that is the proper solution for you, but again, even looping
through a fairly big array is pretty fast. At some point, even Matlab is
doing that, even if it is doing it for you (and the implementation is faster
for sure).
 
Back
Top