LBound and Ubound in the case of an array that has been assigned a range

Question

CODE

height = objExcel1.Application.WorksheetFunction.CountA(ob3.Columns(1)) 'MsgBox(height) ReDim dataArray(height - 2, 0) ' -1 for 0 index, -1 for the first row as header row, excluded str = "" dataArray = ob3.Range(ob3.Cells(2, 1),ob3.Cells(height, 1)).Value Set d = CreateObject("scripting.dictionary") 'MsgBox(LBound(DeletArr) & ":" & UBound(DeletArr)) For i = LBound(DeletArr) To UBound(DeletArr) If Not d.exists(DeletArr(i)) Then d(DeletArr(i)) = 0 End If Next MsgBox(LBound(dataArray,1) & ":" & UBound(dataArray,1)) For i = LBound(dataArray, 1) To UBound(dataArray, 1) - 1 If d.exists(dataArray(i, 1)) Then str = str & (i+1) & ":" & (i+1) & "," 'ob3.Range(i & ":" & i).Delete Else 'found = False End If Next 

The VBScript array is 0. But why does LBound(dataArray,1) give an initial index of 1 , why not 0? Ubound indicates a number that I'm a little confused against, is this the last array index or size?

Thanks,

Answer 1

By default, the indexes / indexes of VBA arrays start at 0 (this is called the lower bound of the array) and run up to the number specified in the Dim statement (this is called the upper bound of the array). If you prefer the index numbers of the array to start at 1, include the following statement in top of the module.

Option Base 1

However, when the array is filled with the Range object using the Transpose method, the lower bound of the array is set to 1, even if you are in Zero baed mode by default. Thus, the array becomes 1 based.

eg. The following data is added using the Transpose method.

 Array(1) = "Hola" Array(2) = "Bonjour" Array(3) = "Hello" Array(4) = "Wei" 

It's good that this UBound array tells you the number of elements (4) that = UBound. Unlike whether it was based on zero, then the number of elements = Ubound + 1.

 UBound(Array) --> 4 LBound(Array) --> 1 

In the current 1-based scenario, Ubound refers to the total number of elements. Therefore, in such cases, you need to change your code to track data in LBound , UBound arrays in order to avoid data loss.

And by the way, adding Option Base 0 does not stop the array, which will be marked 1 based on the Transpose method. Which of my first comments is invalid.