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.

Answer 1

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