Excel VBA GoTo Statement
VBA Goto Statement is used for overcoming the predicted errors while we add and create a huge code of lines in VBA. This function in VBA allows us to go with the complete code as per our prediction or assumptions. With the help Goto we can go to any specified code of line or location in VBA. There is two way of doing it which we will see in upcoming examples.
How to Use Excel VBA Goto Statement?
We will learn how to use Excel VBA Goto Statement with a few examples.
Example #1
The first way of using VBA Goto is by Application.Goto method. With the help of Application.Goto statement we can to any specific location, workbook or worksheet which is opened currently. This statement will look like as below.
- [Reference]: This is nothing but a specified cell reference. If the reference is not provided by default it will take you to the last used cell range.
- [Scroll]: This a logical statement of TRUE or FALSE. If the value is TRUE it will scroll through the window, if the value if FALSE it will not scroll through the window.
For this go to the VBA window and click on Module which is in Insert menu as shown below.
Now it will open a new Module. There write the Subcategory of a macro with the name of Goto as we are performing that code as shown below.
Code:
Sub VBAGoto() End Sub
Now write Application.Goto to enable to application or place where we want to go.
Code:
Sub VBAGoto() Application.Goto End Sub
After that give Reference to any worksheet or workbook and range of cells. Here we have given the range of Cell B3 and Worksheets of named as “VBA Goto”.
Code:
Sub VBAGoto() Application.Goto Reference:=Worksheets("VBA_Goto1").Range("B3"), End Sub
Now for actually going to the mentioned cell we need to add Scroll argument which will directly take us to the specified cell. Now we will change the scroll argument from FALSE as shown below.
Code:
Sub VBAGoto() Application.Goto Reference:=Worksheets("VBA_Goto1").Range("B3"), Scroll:=False End Sub
After running the code using F5 key or manually, we will see cursor will get shifted to cell B3 without changing the orientation of the selected sheet as shown below.
Now we will change Scroll argument from FALSE to TRUE.
Code:
Sub VBAGoto() Application.Goto Reference:=Worksheets("VBA_Goto1").Range("B3"), Scroll:=True End Sub
Initially, we are keeping the cursor at cell A5 as shown below. Now run the complete code using F5 key or manually. We will see the cursor which was at cell A5, is now scrolled up and shifted to cell B3 and the whole table has moved to the top level of sheet starting with the same cell which B3 as shown in below screenshot.
Example #2
There is another way of using VBA Goto argument. Using Goto in VBA by this example, we can skip the argument which is causing an error. For this, insert a new module in VBA and start Subcategory with the name of argument used as shown below. You can use any other name.
Code:
Sub VBAGoto() End Sub
For this, we will consider 3 integers X, Y, and Z by opening Sub category in VBA as shown below.
Code:
Sub VBAGoto() Dim X As Integer, Y As Integer, Z As Integer End Sub
Now also consider some mathematical division where we will divide 10, 20 and 30 with 0, 2 and 4 as shown below.
Code:
Sub VBAGoto() Dim X As Integer, Y As Integer, Z As Integer X = 10 / 0 Y = 20 / 2 Z = 30 / 4 End Sub
If we run the code we will get the same error message of Run-time error 11.
Above error message Run-time error ‘11’ comes only when the written mathematical expression is incorrect. Now to overrule this error, we will use text On Error GoTo with word YResult to skip error message and get the output which works fine as shown below.
Still, our code is not complete. Using Goto with statement “YResult:” will only skip the error line of code. But it will again show the error as Labe not defined as shown below.
Code:
Sub VBAGoto() Dim X As Integer, Y As Integer, Z As Integer On Error GoTo YResult: X = 10 / 0 Y = 20 / 2 Z = 30 / 4 End Sub
Now to complete it, we need to define the Label. Label is the part of statement in VBA Coding, which is used when we want to skip a certain portion of code to any defined applicable line of code. As we already have YResult with Goto argument. Then we will insert the same just before integer Y. Now run the code again.
Code:
Sub VBAGoto() Dim X As Integer, Y As Integer, Z As Integer On Error GoTo YResult: X = 10 / 0 YResult: Y = 20 / 2 Z = 30 / 4 End Sub
As seen and done, we have not got any error message which means that our code is correct and it is skipping that line of code which was causing an error and giving the output where the correct code has been written. Now to print the result of code need to insert message boxes for each Integer with the help argument MsgBox as shown below.
Code:
Sub VBAGoto() Dim X As Integer, Y As Integer, Z As Integer On Error GoTo YResult: X = 10 / 0 YResult: Y = 20 / 2 Z = 30 / 4 MsgBox X MsgBox Y MsgBox Z End Sub
Once done then run the complete code to see the output. We will the output of division of each defined integers as 0, 10 and 8 as shown in below screenshot as well.
On Error GoTo YResult statement helped us to directly jump to mentioned result point integer as we did for integer Y. And the output for X as 0 shows that there was incorrect statement argument written. We can the Label even before Z but that would give us the result of Z integer only. For X and Y it will again show 0 as output.
Pros of VBA On Error
- We can calculate any mathematical formula even if it is incorrect.
- For bigger coding structures where there are chances or having an error, using GoTo may give correct result even among the line of codes.
- This gives better result as compared to the result obtained from normal excel calculations in spite of having an incorrect argument.
Things To Remember
- Remember to the file in Macro-Enabled Excel file so that we can use created VBA code many and multiple times.
- Compile the written code before implementing with any excel requirement.
- You can assign the written code to any button so that we can quickly click on the button and run the code.
- Use Label as shown in example-2 appropriately so that we will get the result for the complete correct code.
Recommended Articles
This has been a guide to VBA GoTo Statement. Here we discussed how to use Excel VBA GoTo Statement along with some practical examples and downloadable excel template. You can also go through our other suggested articles –
