VBA - Error 9: subscript out range - Why does this error occur in my code?

[elinoiz]

Hi all,
I'm a total newbie in VBA, so while trying to do a bubble sort routine, I was constantly getting an error 9: Subscript out of range. After I added an optional rank in LBound() function, it worked, but I don't understand why.
Could someone explain why does the second code work, while first one doesn't?

Array is: 59, 71, 82, 7, 10, 16, 25, 29, 1, 3, 49, 30

Original code which doesn't work:
Private Sub BubbleSrtTest()
Dim Temp As Variant
Dim i As Long
Dim j As Long
Dim SrtArray As Variant


SrtArray = Selection.Value


For i = LBound(SrtArray) To UBound(SrtArray) - 1
For j = i + 1 To UBound(SrtArray)
If SrtArray(i) > SrtArray(j) Then
Temp = SrtArray(j)
SrtArray(j) = SrtArray(i)
SrtArray(i) = Temp
End If
Next j
Next i

Selection.Value = SrtArray
End Sub

Code which does work:
Private Sub BubbleSrtTest()
Dim Temp As Variant
Dim i As Long
Dim j As Long
Dim SrtArray As Variant


SrtArray = Selection.Value


For i = LBound(SrtArray, 1) To UBound(SrtArray, 1) - 1
For j = i + 1 To UBound(SrtArray, 1)
If SrtArray(i, 1) > SrtArray(j, 1) Then
Temp = SrtArray(j, 1)
SrtArray(j, 1) = SrtArray(i, 1)
SrtArray(i, 1) = Temp
End If
Next j
Next i
Selection.Value = SrtArray
End Sub

 

[Norie]

When you populate an array from a range, which I'm assuming Selection is, you end up with an array with 2 dimensions.

In the code you posted that doesn't work you are missing the 2nd dimension when referring to items in the array.

 

[elinoiz]

Aaah, now I see. It is indeed taken from a range. Thank you, Norie, for explaining.