Hi all, I'm using Excel 2003.

Is there a way to quickly sum two arrays together in VBA, so that

Array3(r,c)=Array1(r,c)+Array2(r,c) ?

where r = row number, c = column number, Array1 and Array2 are the
input arrays and Array3 is the output of the calculation

I searched this and other forums, and tried several things, but the
only solution I found was to write a function which loops through all
the items one by one. It works, but it's pretty slow and I was hoping
for a faster solution, as I have to run this on a rather large file.

In Matlab and other environments it's as easy as writing

Array3 = Array1 + Array2

Is there really no equivalent in VBA?

Thanks!

On 10/19/2010 8:52 AM, SantaClaus wrote:
> Hi all, I'm using Excel 2003.
>
> Is there a way to quickly sum two arrays together in VBA, so that
>
> Array3(r,c)=Array1(r,c)+Array2(r,c) ?
>
> where r = row number, c = column number, Array1 and Array2 are the
> input arrays and Array3 is the output of the calculation
>
> I searched this and other forums, and tried several things, but the
> only solution I found was to write a function which loops through all
> the items one by one. It works, but it's pretty slow and I was hoping
> for a faster solution, as I have to run this on a rather large file.
>
> In Matlab and other environments it's as easy as writing
>
> Array3 = Array1 + Array2
>
> Is there really no equivalent in VBA?
>
> Thanks!

How many elements are in your arrays, and how long does it take to sum
them?

On Oct 22, 4:53*am, Mike S <ms...@yahoo.com> wrote:
> On 10/19/2010 8:52 AM, SantaClaus wrote:
>
>
>
>
>
> > Hi all, I'm using Excel 2003.
>
> > Is there a way to quickly sum two arrays together in VBA, so that
>
> > Array3(r,c)=Array1(r,c)+Array2(r,c) *?
>
> > where r = row number, c = column number, Array1 and Array2 are the
> > input arrays and Array3 is the output of the calculation
>
> > I searched this and other forums, and tried several things, but the
> > only solution I found was to write a function which loops through all
> > the items one by one. It works, but it's pretty slow and I was hoping
> > for a faster solution, as I have to run this on a rather large file.
>
> > In Matlab and other environments it's as easy as writing
>
> > Array3 = Array1 + Array2
>
> > Is there really no equivalent in VBA?
>
> > Thanks!
>
> How many elements are in your arrays, and how long does it take to sum
> them?- Hide quoted text -
>
> - Show quoted text -

My array is ca. 16,000 x 600 but may grow bigger.
On a fast PC, the whole script takes ca. 4 minutes to run. This is
still aceptable, but:
1) I'd like a faster way because the array will grow bigger and bigger
2) I'd like a cleaner and neater way to write my code, although I'm
not sure this is even possible - VBA is not Matlab!

Thanks

On 10/29/2010 01:54 AM, SantaClaus wrote:
> On Oct 22, 4:53 am, Mike S<ms...@yahoo.com> wrote:
>> On 10/19/2010 8:52 AM, SantaClaus wrote:
>>
>>
>>
>>
>>
>>> Hi all, I'm using Excel 2003.
>>
>>> Is there a way to quickly sum two arrays together in VBA, so that
>>
>>> Array3(r,c)=Array1(r,c)+Array2(r,c) ?
>>
>>> where r = row number, c = column number, Array1 and Array2 are the
>>> input arrays and Array3 is the output of the calculation
>>
>>> I searched this and other forums, and tried several things, but the
>>> only solution I found was to write a function which loops through all
>>> the items one by one. It works, but it's pretty slow and I was hoping
>>> for a faster solution, as I have to run this on a rather large file.
>>
>>> In Matlab and other environments it's as easy as writing
>>
>>> Array3 = Array1 + Array2
>>
>>> Is there really no equivalent in VBA?
>>
>>> Thanks!
>>
>> How many elements are in your arrays, and how long does it take to sum
>> them?- Hide quoted text -
>>
>> - Show quoted text -
>
> My array is ca. 16,000 x 600 but may grow bigger.
> On a fast PC, the whole script takes ca. 4 minutes to run. This is
> still aceptable, but:
> 1) I'd like a faster way because the array will grow bigger and bigger
> 2) I'd like a cleaner and neater way to write my code, although I'm
> not sure this is even possible - VBA is not Matlab!

Do I understand you correctly that one array is this big:
Dim Array1 (16000, 600)

If so can I ask what the data is, just out of curiosity?

As far as possible solutions, is it feasible to store the summed Array3
in a file? I'm thinking that would be a lot faster than the 4 minutes
you're seeing now.

On 29/10/2010 09:54, SantaClaus wrote:
> On Oct 22, 4:53 am, Mike S<ms...@yahoo.com> wrote:
>> On 10/19/2010 8:52 AM, SantaClaus wrote:
>>
>>> Hi all, I'm using Excel 2003.
>>
>>> Is there a way to quickly sum two arrays together in VBA, so that
>>
>>> Array3(r,c)=Array1(r,c)+Array2(r,c) ?
>>
>>> where r = row number, c = column number, Array1 and Array2 are the
>>> input arrays and Array3 is the output of the calculation
>>
>>> I searched this and other forums, and tried several things, but the
>>> only solution I found was to write a function which loops through all
>>> the items one by one. It works, but it's pretty slow and I was hoping
>>> for a faster solution, as I have to run this on a rather large file.
>>
>>> In Matlab and other environments it's as easy as writing
>>
>>> Array3 = Array1 + Array2
>>
>>> Is there really no equivalent in VBA?

No. If you need to do it a lot you could write a subroutine to do
Sub MatrixAdd(Array3, Array1, Array2)

Though you may be able to add a few go-faster stripes and gain some
speed by explicitly declaring the types of your variables. Otherwise XL
has to determine the type of each variable at runtime (high overheads).

Dim r & c as Integer and the Arrays presumably as Double or Integer.
There is some extra mileage in arranging things so that you sweep
linearly through consecutive memory locations inside the loop, and also
put the nest them with the small dimension as the outer loop.
>>
>>> Thanks!
>>
>> How many elements are in your arrays, and how long does it take to sum
>> them?- Hide quoted text -
>>
>> - Show quoted text -
>
> My array is ca. 16,000 x 600 but may grow bigger.
> On a fast PC, the whole script takes ca. 4 minutes to run. This is
> still aceptable, but:
> 1) I'd like a faster way because the array will grow bigger and bigger
> 2) I'd like a cleaner and neater way to write my code, although I'm
> not sure this is even possible - VBA is not Matlab!

You might be better off doing this in Visual Basic itself (or C). These
array sizes must be pushing what VBA can sensibly handle in XL2003.

Regards,
Martin Brown