Excel VBA - assigning an array changes LBound and UBound

Question

I have a some very large data sets in Excel that I need to parse through - and doing it in an array is faster than looping through the data in the worksheet. Loading all of the data into an array is causing memory problems (the data sets ARE that large), so I plan on loading subsets of the data into an array, processing that, then loading another subset. I was hoping to use the array "feature" of defining the LBound and UBound to help me keep track of where I am in the worksheet. But I find that assigning the worksheet values to the array changes the bounds. The following code demonstrates the problem ...

    Sub myTest3()
    Dim myRange As Range
    Dim myArray As Variant
    Dim myOffset As Long

        myOffset = 10
        Set myRange = Worksheets("RawData").Range("A1").CurrentRegion
        ReDim myArray(myOffset To myRange.Rows.Count, myRange.Columns.Count)
        MsgBox LBound(myArray, 1) & " to " & UBound(myArray)

        Set myRange = myRange.Offset(myOffset, 0).Resize(myRange.Rows.Count - myOffset, myRange.Columns.Count)

        myArray = myRange.Value2

        MsgBox LBound(myArray, 1) & " to " & UBound(myArray)

    End Sub

The first MsgBox gives me "10 to 10931". The second MsgBox gives me "1 to 10921".

Any ideas on maintaining the bounds on the array as I originally defined them? I know looping through the worksheet to make the assignment would do it, but it would be slow.

Thanks in advance.

Assigning an array from a range using .Value always creates a 1-based array. If you need different behavior then you'd need to fill the array using a loop. — Tim Williams
Color me sad. Thanks Tim. I guess I will need to restructure my code so I can keep track of my location using a loop. — OldUgly
The problem is that you use a ReDim statement to redeclare the array with a lower bound of 10, here: ReDim myArray(myOffset to .... What exactly are you trying to do with the myOffset variable? I think that's the cause. — David Zemens
Actually David, that's the "feature" that I am trying to take advantage of. In VBA, you can set the LBound to be whatever you want. In my project (not necessarily the example code), I was hoping to assign the array lower and upper bounds to correspond to the rows in the worksheet the data was from. This would enable me to put it back in the same locations, after the appropriate changes. I think Tim's comment is it ... when assigning an array from a range, it appears to automatically redim the array with LBound = 1. — OldUgly

Mike Woodhouse Mike Woodhouse · Accepted Answer · 2013-10-21T08:50:54

Excel VBA doesn't work the way you want it to in this situation. When you execute myArray = myRange.Value2 the original content of myArray was replaced. The Redimmed array was thrown away. Excel/VBA doesn't look at the target, it replaces it, or, probably more correctly, it creates a new array and makes the myaArray variable point to that.

So you're going to need a bit more code to get you where you want to be. I'd consider putting the code to grab the next chunk into a separate function and doing the bookkeeping there:

Function ChunkAtOffset(rng As Range, rowsInChunk As Long, colsInChunk As Long, offsetRows As Long) As Variant
' Note: doesn't cater for the case where there are fewer than 'offsetRows' in the target    
Dim arr As Variant, result As Variant
Dim r As Long, c As Long

    arr = rng.offset(offsetRows).Resize(rowsInChunk, colsInChunk).Value2

    ReDim result(offsetRows To offsetRows + rowsInChunk - 1, 1 To colsInChunk)

    For r = 1 To rowsInChunk
        For c = 1 To colsInChunk
            result(offsetRows - 1 + r, c) = arr(r, c)
        Next
    Next

    ChunkAtOffset = result

End Function

If I run this:

Sub myTest4()

    Dim curReg As Range, ary As Variant, offset As Long
    With Range("A1")
        Set curReg = .CurrentRegion
        Do
            ary = ChunkAtOffset(.CurrentRegion, 10, .CurrentRegion.Columns.Count, offset)
            Debug.Print LBound(ary, 1) & " to " & UBound(ary)
            offset = offset + 10
        Loop Until offset >= .CurrentRegion.Rows.Count
    End With

End Sub

... I now get this:

0 to 9
10 to 19
20 to 29

Excel VBA - assigning an array changes LBound and UBound

1 Answers