Excel Made Easy

Excel VBA GOTO Jump or Branch Statement

How can I jump to another part of the VBA program? This can be done by using the GOTO statement. GOTO can also be used to make a loop when combined to a conditional statement like IF. This is an easy tutorial to Microsoft Excel".

Let's do scan through a row or a range and calculate the square of all the items in the range. The range will finish with the word end. Of course you can end it with something else or just detect the first empty cell.

vba scan defined range and square

You can see in the next code the GOTO statement and the FLAGS associated with this GOTO statement. Here they are called BEGINNING: and END:

Sub square_Range()

Dim myWS As Worksheet
Set myWS = ThisWorkbook.ActiveSheet

i = 2 ' counter
Beginning:                                 ' flag for the start of the GOTO loop

i = i + 1
Value = myWS.Range(Cells(3, i), Cells(3, i)).Value
If Value = "" Or Value = "end" Or i > 100 Or IsNumeric(Value) = False Then GoTo Out
                        ' this statement test if the value is a numerical value, but also makes sure that we get out of the loop
                        ' when i is bigger than 100. This is a safety you can implement or not in a loop if you wish.

square = Value * Value
myWS.Range(Cells(5, i), Cells(5, i)).Value = square

GoTo Beginning                     ' we go back to the beginning of the loop

Out:                                     ' this is the exit road of the GOTO Loop statement
i = i - 3                              ' setting i to the real value as we started in the second column
Cells(7, 3).Value = "we have squared all the " & i & " values in your table"

End Sub

This is how the code could look like.

Beware, with the GOTO statement, not to have an endless loop. Once you are stuck in such a loop, you will have to do CTRL-ALT-DELETE to stop Excel because it will be stuck in a loop. There are different ways to make sure you get out of the loop is to use the IF THEN statement to stop the loop after a certain number of loops have been done, you can use a timer or the now() funtion that counts the time the program is in the loop.

vba scan defined range and square

Congratulation, you made it until the end of this VBA Code Example.