When To use GOTO Sub?

I sometimes see people seperating their code into many different subroutines then joining it together with goto ____. What is the purpose of this? Will it slow down or make the macro faster? or is it used just for organization purposes?

IMO it should be used as little as possible. I think it makes the code hard
to read. VBA has plenty of loops and condition statements that make
excessive use of Goto unnecessary.

The only time I use it is to set up an ErrorHandler or CleanUp procedure,
which can not only trap an error and decide what to do (resume or exit or
something else), but can also reset any Application settings the macro may
have changed, such as setting Calc to manual. If an error occurs somewhere
in the middle of the code, I usually want the calc set back to automatic
before exiting the procedure.

On Error GoTo ErrorHandler








"MC82" wrote:

>
> I sometimes see people seperating their code into many different
> subroutines then joining it together with goto ____. What is the
> purpose of this? Will it slow down or make the macro faster? or is it
> used just for organization purposes?
>
>
> --
> MC82
> ------------------------------------------------------------------------
> MC82's Profile: http://www.excelforum.com/member.php...o&userid=18682
> View this thread: http://www.excelforum.com/showthread...hreadid=540083
>
>

As a general rule, you should use Goto only with On Error. While
using Goto is theoretically faster than calling a sub, it leads
quickly to code that is difficult to follow and maintain. It
leads to what is called "spaghetti code" where you GOTO one label
then GOTO another then another. If you need a Goto, you can
almost always use a separate procedure instead. This makes the
code much simpler to maintain.

In summary, don't use Goto.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"MC82" <[email protected]> wrote
in message
news:[email protected]...
>
> I sometimes see people seperating their code into many
> different
> subroutines then joining it together with goto ____. What is
> the
> purpose of this? Will it slow down or make the macro faster?
> or is it
> used just for organization purposes?
>
>
> --
> MC82
> ------------------------------------------------------------------------
> MC82's Profile:
> http://www.excelforum.com/member.php...o&userid=18682
> View this thread:
> http://www.excelforum.com/showthread...hreadid=540083
>

Thanks for the explanation guys. What about the usage of "Call" Sub? Is this also something I should refrain from using much?

Depends. It is particularly useful to set up a separate sub that can be
called by other subs if those other subs need to perform similar functions.
If you have to ever make changes to the code, it is easier because you only
have to do it once.

For example, I have one template that has to perform a dozen or so different
filter operations on a particular table. Instead of writing 12 filter
procedures, I have one sub that actually performs the filtering. The other
12 pass parameters to it (filter criteria, etc) and perform any other
procedures/setup that is specific to their own filter operation (one filter
operation has to get user input, so it does that and passes the info along to
the filter sub - but getting user input is specific only to that particular
filter operation).

I usually go with what appears organized and logical to me. Using
additional subs can also help w/testing and debugging. You can set up some
dummy data and a test macro that calls the sub you want to test/debug and
step through it without having to run/step through one giant macro in order
to test/debug only one particular part of it. OTOH I avoid setting up
another sub when it would require too much duplicate work, or a lot of
parameter passing, or require most of my variables to be declared at the
module level.



"MC82" wrote:

>
> Thanks for the explanation guys. What about the usage of "Call" Sub?
> Is this also something I should refrain from using much?
>
>
> --
> MC82
> ------------------------------------------------------------------------
> MC82's Profile: http://www.excelforum.com/member.php...o&userid=18682
> View this thread: http://www.excelforum.com/showthread...hreadid=540083
>
>