Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 2 arrays, then use in worksheet function
Hi,
I have a macro which reads an excel range directly into an array, which I then can use in worksheet functions e.g.: DimMyArray As Variant Dim MyResult As Double MyArray = Range("A2:A21").Value MyResult = WorksheetFunction.NPV(0.1, MyArray) This seems to be really effective performance-wise, so my question is: is there a way to add together 2 identically sized arrays created in this way, but without just looping through the elements 1-by-1 (as this may be quite a drag on performance?). Thanks, Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 2 arrays, then use in worksheet function
The worksheet function MMULT gives the product of two arrays, but not sure
there is a sum option. You would need to code it. -- Regards, Nigel "Rich_84" wrote in message ... Hi, I have a macro which reads an excel range directly into an array, which I then can use in worksheet functions e.g.: DimMyArray As Variant Dim MyResult As Double MyArray = Range("A2:A21").Value MyResult = WorksheetFunction.NPV(0.1, MyArray) This seems to be really effective performance-wise, so my question is: is there a way to add together 2 identically sized arrays created in this way, but without just looping through the elements 1-by-1 (as this may be quite a drag on performance?). Thanks, Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 2 arrays, then use in worksheet function
If they're both coming from ranges, you could copy the first range into a
temporary range, then copy the second range and use copy|paste special|add and then pick up those new values. But that looks like it would be lots slower for summing 20 values. Rich_84 wrote: Hi, I have a macro which reads an excel range directly into an array, which I then can use in worksheet functions e.g.: DimMyArray As Variant Dim MyResult As Double MyArray = Range("A2:A21").Value MyResult = WorksheetFunction.NPV(0.1, MyArray) This seems to be really effective performance-wise, so my question is: is there a way to add together 2 identically sized arrays created in this way, but without just looping through the elements 1-by-1 (as this may be quite a drag on performance?). Thanks, Richard -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 2 arrays, then use in worksheet function
On Apr 1, 5:24*am, "Nigel" wrote:
The worksheet function MMULT gives the product of two arrays, but not sure there is a sum option. *You would need to code it. -- Regards, Nigel "Rich_84" wrote in message ... Hi, I have a macro which reads an excel range directly into an array, which I then can use in worksheet *functions e.g.: DimMyArray As Variant Dim MyResult As Double MyArray = Range("A2:A21").Value MyResult = WorksheetFunction.NPV(0.1, MyArray) This seems to be really effective performance-wise, so my question is: is there a way to add together 2 identically sized arrays created in this way, but without just looping through the elements 1-by-1 (as this may be quite a drag on performance?). Thanks, Richard Richard, Have you tried something along the lines of the following: Sub TestSumArray() Dim arrSumOne As Variant Dim arrSumTwo As Variant Dim dblResult As Double arrSumOne = Range("A1:A4").Value arrSumTwo = Range("B1:B4").Value dblResult = WorksheetFunction.Sum(arrSumOne, arrSumTwo) MsgBox dblResult End Sub Best, Matt Herbert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding 2 arrays, then use in worksheet function
Thanks for the responses, though its still not quite what I'm looking for. I
think what I'm trying to achieve is similar to an array formula as used in a sheet e.g.: ={A1:A3+B1:B3} I stumbled across something that may help utilse this calculation in VBA: ' add two 3-element ranges and store in array Dim a1 As Variant a1 = Evaluate("A1:A3+B1:B3") Range("D1:D3").Value = a1 This getting closer to what I need, but to do this dynamically looping over numerous ranges it seems I would have to generate the correct string argument for the evaluate command, which just seems a bit cumbersome if you ask me? Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
vba adding arrays | Excel Discussion (Misc queries) | |||
Array function, two dimensions?? and worksheet arrays | Excel Programming | |||
Adding arrays | Excel Programming |