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.

--

Regards,
Nigel
(E-Mail Removed)



"Rich_84" <(E-Mail Removed)> wrote in message
news:A3D2890B-879B-4533-9D4F-(E-Mail Removed)...
> 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
>

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

On Apr 1, 5:24*am, "Nigel" <nigel-...@nosupanetspam.com> 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
> nigelnos...@9sw.co.uk
>
> "Rich_84" <Rich...@discussions.microsoft.com> wrote in message
>
> news:A3D2890B-879B-4533-9D4F-(E-Mail Removed)...
>
> > Hi,
>
> > I have a macro which reads an excel range directly into an array, whichI
> > 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

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("D13").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