Array multiplication in VBA

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
 
B

Bernard Liengme

Why not put the formula =A1*B1 in the first cell and copy it down/across as
needed?
What is wrong with a loop?
 
T

Tom Ogilvy

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
 
D

Dana DeLouis

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
 
T

Tom Ogilvy

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...
 
Top