Sum portion of array without loop

Thanks, DonkeyOte,

As you can see I manged to place all 10 values into range F1:F10 and from there I would have known how to sum a part of a that range.

My intention is NOT to go through and/or use a Worksheet as an helper.

If there is no way to accomplish my request with "pure" VBA commands without relaying on Worksheets or Worksheets Objects that would be preferable.

However, as long as the solution will rum through something that is fully transparent to the user even if it needs a Worksheet Object - it will be fine - but as I'm not familiar with "worksheet objects" - please lighten up my eyes with a sample code.

Thanks, Elm

If you don't want to use a Worksheet to store the Array temporarily (such that you can sum quickly as per any standard range) then you will AFAIK need to iterate the Array... and I stress again, AFAIK...

What exactly is the problem with looping?

You could familarize yourself with VBA's SafeArray data structure, use VarPtr to get the base address of the array, compute the offset to the data of interest, and use the CopyMemory API to copy the data to a new array.

Happy hunting.

Option Explicit

Sub x()
    Dim v1 As Variant
    Dim iBeg As Long
    Dim iEnd As Long
    
    ' however v1 gets initialized ...
    v1 = Range("A1:A10").Value
    iBeg = Range("C2").Value
    iEnd = Range("D2").Value
    
    ' then ...
    With Range("B1").Resize(UBound(v1)) ' or any other convenient place
        .Value = v1
        MsgBox WorksheetFunction.Sum(Range(.Cells(iBeg), .Cells(iEnd)))
    End With
End Sub