Adding 2 arrays, then use in worksheet function

R

Rich_84

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
 
N

Nigel

The worksheet function MMULT gives the product of two arrays, but not sure
there is a sum option. You would need to code it.
 
D

Dave Peterson

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

meh2030

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
(e-mail address removed)

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
 
R

Rich_84

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top