Use of If Then Goto in VBA Excel

[Daerk]

Hi all

I have several macros on the go at the moment for work and I am trying to improve them somewhat.

The macros go into other spreadsheets and extract data from them.

The other spreadsheets all have general name formats based on their purpose/results/date or week of the year.

However, since they are manually saved files from other people, the file names are subject to errors etc. I generally try and account for this with multiple nested if statements to check for the correct file name based on the length function.

Example

If Len(Dir(ivrPath + FE)) = 0 Then 
    MsgBox "Finance Export Not Found - please make sure the file name is 'Finance Export DD.MM.YYYY.xls"
    Exit Sub
Else
    Workbooks.Open ivrPath + FE, , 1
End If

What I am wondering is if it possible to incorporate the GOTO command into the IF statement, eg set labels on each file open process, and then if the file name returns a zero length the GOTO command can be used to skip all the code associated with that worksheet and instead of exiting, open the next sheet in the list and start working on that...

My questions are...1) is this possible? & 2) I saw some comments that say the use of the GOTO command in programming is not best practice, so is there another way I should do this instead?

Thanks in advance for any help / information.

Regards
Patrick

 

[mathchick]

Hi all

I have several macros on the go at the moment for work and I am trying to improve them somewhat.

The macros go into other spreadsheets and extract data from them.

The other spreadsheets all have general name formats based on their purpose/results/date or week of the year.

However, since they are manually saved files from other people, the file names are subject to errors etc. I generally try and account for this with multiple nested if statements to check for the correct file name based on the length function.

Example

If Len(Dir(ivrPath + FE)) = 0 Then 
    MsgBox "Finance Export Not Found - please make sure the file name is 'Finance Export DD.MM.YYYY.xls"
    Exit Sub
Else
    Workbooks.Open ivrPath + FE, , 1
End If

What I am wondering is if it possible to incorporate the GOTO command into the IF statement, eg set labels on each file open process, and then if the file name returns a zero length the GOTO command can be used to skip all the code associated with that worksheet and instead of exiting, open the next sheet in the list and start working on that...

My questions are...1) is this possible? & 2) I saw some comments that say the use of the GOTO command in programming is not best practice, so is there another way I should do this instead?

Thanks in advance for any help / information.

Regards
Patrick

couldn't resist: (from xkcd)

 

[Daerk]

 

[VoG]

Also http://en.wikipedia.org/wiki/Spaghetti_code

 

[Domski]

The code gods will severely punish you if you do

Dom

 

[Daerk]

Im not really sure it would qualify as spaghetti code, since the code is still linear, it simply bypasses sections associated with spreadsheets it cant open (and displays a warning message to the user)...

From the comments I gather that GOTO is a no-no, so any other suggestions on how I can do this?

Thanks

 

[mathchick]

Im not really sure it would qualify as spaghetti code, since the code is still linear, it simply bypasses sections associated with spreadsheets it cant open (and displays a warning message to the user)...

From the comments I gather that GOTO is a no-no, so any other suggestions on how I can do this?

Thanks

why can't you just put all the code you want skipped in the else part of the if statment?

 

[Jonmo1]

Im not really sure it would qualify as spaghetti code, since the code is still linear, it simply bypasses sections associated with spreadsheets it cant open (and displays a warning message to the user)...

From the comments I gather that GOTO is a no-no, so any other suggestions on how I can do this?

Thanks

It's a "Gateway Drug" similar to the grass like substance..
doing it one time is not spaghetti...It's just one Noodle.
Once you say "it's okay to do it once"...
"Well, if it's okay once, then 2 or three is fine"
"if 2 or 3 is fine, why not a dozen...
Then you have several noodles.
Before you know it, you have spaghetti and a full blown addiction to fractures in concrete.

That's why it's best to follow "Best Practices" all the time, even for the quick simple codes.

 

[mathchick]

It's a "Gateway Drug" similar to the grass like substance..
doing it one time is not spaghetti...It's just one Noodle.
Once you say "it's okay to do it once"...
"Well, if it's okay once, then 2 or three is fine"
"if 2 or 3 is fine, why not a dozen...
Then you have several noodles.
Before you know it, you have spaghetti and a full blown addiction to fractures in concrete.

That's why it's best to follow "Best Practices" all the time, even for the quick simple codes.

plus, if you can figure out how to avoid a goto in an easy bit of code, then you're building the experience to be able to write well-structured code when you have larger coding projects.

 

[Domski]

'On Error Goto ErrorHandler' is more than acceptable if you can set up a suitable trap and handler.

Dom