Use of goto

Steve001

Board Regular
Joined
Apr 13, 2017
Messages
55
Hi all

is there a better way of doing this ? as I was always taught to avoid the "goto" instruction

MSG1 = MsgBox("Pressing this will COMPLETELY replace ALL of the data in the 'Tabs'" & vbCrLf & "Continue ?", vbYesNo, "Sort raw Data")
If MSG1 = vbYes Then
GoTo jump
Else
End

End If
jump:
MsgBox "here we go ... "


Steve
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Roxxien

Active Member
Joined
Jul 5, 2017
Messages
289
Hello,

by the way I'm reading your code, you have 2 options to delete your "goto".

1. your goto have no purpose so we just delete it

Code:
[COLOR=#333333]MSG1 = MsgBox("Pressing this will COMPLETELY replace ALL of the data in the 'Tabs'" & vbCrLf & "Continue ?", vbYesNo, "Sort raw Data")[/COLOR]
[COLOR=#333333]If MSG1 = vbYes Then[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]End[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]MsgBox "here we go ... "[/COLOR]

2. we reverse your if

Code:
[COLOR=#333333]MSG1 = MsgBox("Pressing this will COMPLETELY replace ALL of the data in the 'Tabs'" & vbCrLf & "Continue ?", vbYesNo, "Sort raw Data")[/COLOR]
[COLOR=#333333]If MSG1 = vbNo Then[/COLOR]
[COLOR=#333333]End
[/COLOR]End If
[COLOR=#333333]MsgBox "here we go ... "
[/COLOR]

You should try the second option in a COPY of your file. If it's not working, use the first option even if it's less logical.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Personally, I avoid using GoTo as much as possible. It can create some nasty code that is not only difficult to follow, but difficult to debug.

Try having the code operate inside the IF statement.

Code:
MSG1 = MsgBox("Pressing this will COMPLETELY replace ALL of the data in the 'Tabs'" & vbCrLf & "Continue ?", vbYesNo, "Sort raw Data")
If MSG1 = vbYes Then
    MsgBox "here we go ... "
    'code you want to operate when they click yes
Else
    'code you want to operate when they click no
End If
 

Beyond_avarice

Board Regular
Joined
Nov 13, 2012
Messages
195
Office Version
  1. 2007
Platform
  1. Windows
Goto is often referred to as "Spaghetti code" as you can bounce all over the place and it can make coding hard to read. Avoid it with a passion.

Code:
Dim MSG1 As String

MSG1 = MsgBox("Pressing this will COMPLETELY replace ALL of the data in the 'Tabs'" & vbCrLf & "Continue ?", vbYesNo, "Sort raw Data")

If MSG1 = vbYes Then
    MsgBox ("here we go.....")
End If
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Along with what Roxxien has said, I would also recommend replacing
Code:
End
with
Code:
Exit Sub
As End can cause problems
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,121
Office Version
  1. 365
Platform
  1. Windows
For that snippet, you can just include jump's MsgBox in the Then. Don't get too worried about using GoTo unless you use more than 1 or 2.

For multiply IFs, one can often use Select Case. The choice depends on your goals.

End is frowned on by some too. Exit Sub will usually suffice.
Code:
Sub Test()
 If MsgBox("Pressing this will COMPLETELY replace ALL " _
  & "of the data in the 'Tabs'" & vbCrLf & "Continue ?", _
  vbYesNo, "Sort raw Data") = vbYes Then
    GoTo jump
  Else
End If
Exit Sub

jump:
MsgBox "here we go ... "
End Sub
 

Steve001

Board Regular
Joined
Apr 13, 2017
Messages
55
cheers guys

will have a go swapping my if statements around.

is there a more elegant way of stopping my sheets running then instead of "end" - I don't want anything to continue running I want to exit al macros and stop.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top