Query on the Ubound function

AMB16

New member
Joined
Jul 10, 2013
Messages
2
Reaction score
0
Points
0
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.

Code:
 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

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
234
Reaction score
3
Points
18
Location
UK
Excel Version(s)
2016
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:
Code:
    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.
 
Top