Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I sum two arrays together?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I sum two arrays together?
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I sum two arrays together?
On Oct 22, 4:53*am, Mike S 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I sum two arrays together?
On 10/29/2010 01:54 AM, SantaClaus wrote:
On Oct 22, 4:53 am, Mike 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I sum two arrays together?
On 29/10/2010 09:54, SantaClaus wrote:
On Oct 22, 4:53 am, Mike 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with arrays (transferring values between two arrays) | Excel Programming | |||
Use of arrays | Excel Worksheet Functions | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Arrays | Excel Programming |