Styjun

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.

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!

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)

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