Adding 2 arrays, then use in worksheet function

  • Thread starter Thread starter Rich_84
  • Start date Start date
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
 
The worksheet function MMULT gives the product of two arrays, but not sure
there is a sum option. You would need to code it.
 
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.
 
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)
Click to expand...

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
 
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
 
Back
Top