CODEWITHSUNDEEP
arraysexcelvba
fourthquartermagic
fourthquartermagic

Reputation: 135

ReDim Preserve 2-dimensional Variant Array

This seems to be the millionth question on this topic, but searching didn't help me.

I'm trying to resize the last dimension of a two-dimensional array, but always get an "index out of bounds" error in the ReDim Preserve line.

enter image description here

Dim arrCurrentDataset As Variant

For i = 0 To UBound(fileNames) - 1

    strPath = fileNames(i)

    Set wkbSource = Workbooks.Open(Filename:=strPath, UpdateLinks:=xlUpdateLinksNever, ReadOnly:=True, Notify:=True)
    Set wksSource = wkbSource.Sheets(1)

    Dim lngRows As Long
    lngRows = wksSource.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    'Store dataset to array and afterwards increase second dimension by 2 -> create space to add Materialart and Beschaffungsart
    arrCurrentDataset = wksSource.Range("A4:I" & lngRows).value
    ReDim Preserve arrCurrentDataset(UBound(arrCurrentDataset, 1), UBound(arrCurrentDataset, 2) + 2)

    '...

next i

Is there a problem with my declaration? Do I implicitly try to change the data type?

Thanks a lot for your help!

Upvotes: 1

Views: 2656

Answers (2)

FunThomas
FunThomas

Reputation: 29276

When you declare a variable as Variant, it can be anything. It may be an array, or an 2-dimensional array, but it could also by an object or an string or something else.

So when you want it to be an array, you have to use a Redim-command. But when you hit your Redim the first time, you don't have an array yet, so Ubound will fail (it's only valid for arrays) - and as it is not an array, there is also nothing to preserve.

You can solve this by either initialize the array before your real work starts, or add a check if the variable contains an array

If isArray(arrCurrentDataset) Then
   ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)
Else
   ' You have to make up your mind about the initial size of the array
   ReDim arrCurrentDataset(1 to 10, 1 to 10)
End If

Upvotes: 0

Rory
Rory

Reputation: 34045

Your array dimensions are 1 based, but the default is 0 (unless you have an Option Base 1 statement) so you have to specify that in the Redim:

ReDim Preserve arrCurrentDataset(1 to UBound(arrCurrentDataset, 1), 1 to UBound(arrCurrentDataset, 2) + 2)

Upvotes: 2

Related Questions