How to return a limited section of an array?

[Kelvin Stott]

I have the following formula which sums the first i rows and j columns of a range ("MyRange") using Resize:

WorksheetFunction.Sum(MyRange.Resize(i, j))

Now is there an equivalent VBA function which can be used with a general VBA array (and not just a range)?

 

[GTO]

If you are asking about 'resizing' an array, read the help topic on ReDim and Preserve.

 

[Kelvin Stott]

Thanks, but ReDim physically changes the size of an array, whereas I only want to select, return or "read" a section of an array without changing the original.

 

[GTO]

Ahh, my bad. You can loop using a For n = [some number] To [some other number], where you can set the low number to higher than the array's base and/or set the upper limit to below the array's upper bound. Does that make sense?

 

[Kelvin Stott]

Yes, it does make sense, but looping is what I was trying to avoid (too slow). There must be a VBA function that can select only a sub-section of an array, as you can with a range, no?

For example: MyArray(1 to i, 1 to j)

Isn't there something similar that works?

 

[GTO]

Not AFAIK. You can you a worksheet function (INDEX) to return a column or row of an array.

I would mention that in your thinking that .Resize is returning a 'sub-section', I would think of it returning a new/additional range object.

BTW, whilst looping thru cells in a range is slow, looping one actual array into another seems pretty quick to me.

 

[Kelvin Stott]

OK, thanks. It's a pity that, while Offset can also be used to reset height and width (number of rows and columns) as well as absolute offset as a worksheet function, it can only be used to set an absolute offset in VBA.