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

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

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

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

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