Query on the Ubound function

[AMB16]

Hi All,

I am a novice to intermediate level VBA user. I had started by recording macros and then studying them and I am now at a level where I can write intermediate difficulty level macros given some time.

I am however having a hard time understanding the below code. "cols" has been declared as an long variable at the start of the code. Starscream, OptimusPrime...etc are column labels.

What I dont understand is what the ubound function does here and especially the "To 0 Step -1" part.

Would greatly appreciate if someone can explain so that it will add to my knowledge.

 cols = Split("Starscream, OptimusPrime,BuzzLighYear,CaptainKirk" & _
    ",Superman,Batman,Spiderman", ",")
For i = UBound(cols) To 0 Step -1
Set r = Rows(5).Find(cols(i), , , 1)
        If Not r Is Nothing Then
            r.EntireColumn.Cut
            Columns(1).Insert
            n = n + 1
        End If
Next
    If n > 0 Then Range(Cells(1, n + 1), Cells.SpecialCells(11)).EntireColumn.Delete

 

[JoePublic]

Ubound finds the upper boundary (hence the name ) of the specified array dimension (defaults to the first dimension). Here, the Split function returns a 7 element array with the dimension boundaries going from 0 to 6 and UBound returns the 6. It saves you needing to hardcode the number of elements into the code. Personally, I think the line should also use LBound:

    For i = UBound(cols) To LBound(cols) Step -1

The Step -1 part is because the code is looping backwards through the array - i.e. it process the last element first, then element 5, then 4 and so on.

 

[AMB16]

Thank you!!

It makes sense to me now.