Array multiplication in VBA
 

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

Array multiplication in VBA
 

Why not put the formula =A1*B1 in the first cell and copy it down/across as
needed?
What is wrong with a loop?
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

"Dan E" wrote in message
...
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

Array multiplication in VBA
 

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



--
Regards.
Tom Ogilvy

Dan E wrote in message
...
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

Array multiplication in VBA
 

If the functions in the freely downloadable file at
http://home.net.pacbell/beban are available to your workbook you can
avoid writing the loop (but not using it--it's prewritten):

=ArrayAdd(Temp1,Temp2)

Alan Beban

Dan E wrote:
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

Array multiplication in VBA
 

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" wrote in message
...
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

Array multiplication in VBA
 

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" wrote in message
...
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" wrote in message
...
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

Array multiplication in VBA
 

I figured I may have to use a loop, Thanks to everyone for their responses

Dan E

"Dan E" wrote in message ...
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

Array multiplication in VBA
 

But since it is the fastest approach, that is a disadvantage?

--
Regards,
Tom Ogilvy

"Dan E" wrote in message
...
I figured I may have to use a loop, Thanks to everyone for their responses

Dan E

"Dan E" wrote in message
...
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

Array multiplication in VBA
 

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

"Tom Ogilvy" wrote in message ...
But since it is the fastest approach, that is a disadvantage?

--
Regards,
Tom Ogilvy

"Dan E" wrote in message
...
I figured I may have to use a loop, Thanks to everyone for their responses

Dan E

"Dan E" wrote in message
...
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

Array multiplication in VBA
 

I think I may have solved the problem!

http://www.mathworks.com/access/help...matlabxl.shtml

I didn't even know it existed.

Dan E

"Dan E" wrote in message ...
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

"Tom Ogilvy" wrote in message ...
But since it is the fastest approach, that is a disadvantage?

--
Regards,
Tom Ogilvy

"Dan E" wrote in message
...
I figured I may have to use a loop, Thanks to everyone for their responses

Dan E

"Dan E" wrote in message
...
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

Array multiplication in VBA
 

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

--
Regards,
Tom Ogilvy


"Dan E" wrote in message
...
I think I may have solved the problem!


http://www.mathworks.com/access/help...matlabxl.shtml

I didn't even know it existed.

Dan E

"Dan E" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
But since it is the fastest approach, that is a disadvantage?

--
Regards,
Tom Ogilvy

"Dan E" wrote in message
...
I figured I may have to use a loop, Thanks to everyone for their
responses

Dan E

"Dan E" wrote in message
...
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