goto statement in VBA

[Laurie]

Hi everyone who may help,

I am trying to use the goto statement in VBA in Excel.

From the Help, goto statement can jump to a label line.

I am wondering that, under the label line, which the goto statement jumps
to, how many statements are allowed?

In the help, only one line of statement is under the label line. Can there
be a block of statements under the label line?

Any help would be really appreciated.

Thanks a lot,
Laurie

[Tom Ogilvy]

as many lines as you want.

if A = B then goto label1
LineA
LineB
LineC
LineD

Label1:
Line1
Line2
Line3
Line4

Usually it is better to do

if A <> B then
LineA
LineB
LineC
LineD
End if

Line1
Line2
Line3
Line4

--
Regards,
Tom Ogilvy

[Rick Rothstein (MVP - VB)]

There is no practical limit for how many statements can be in the block
following the GoTo statement. With that said, you will do yourself a big
favor if you learn not to use the GoTo statement (it makes code much harder
to read, especially several months from now when you come back to edit your
code). With the exception of On Error GoTo error handler, there is almost no
situation where GoTo is the correct solution. You can usually almost always
use an If-Then-Else or Select-Case block structure instead. Why don't you
outline what you are trying to code using GoTo and let's see if someone here
can show you a better way to code it.

Rick

"Laurie" <Lau...@discussions.microsoft.com> wrote in message
news:97B38AB3-F472-4562...@microsoft.com...

[Laurie]

First thanks both Rick and Tom for your confirmation, and Tom for your
demonstration!!!

To answer Rick's question, the coding is about a UDF for a very complicated
trading logic.
The main stream is already very long and it would be much cleaner and easier
to read if using goto statement to jump to the sub trading logic.

It's comforting to know under the label line, there can be as many
statements as necessary.

I guess my next question would be how to define the end of the block of

statements under the label line?

For the trading logic instance as demonstrated below, ideally, I would like
to retrieve the results from executing the statements under the label line to
be used in the statements following the "goto Label" line. Is this the case
by the code logic?

Also, after the block of statements, which are supposed to be under the
Label line, I would like to have another block of statements, like Select
Case statements, which are not belonging to the block of statements under the
Label line. Can this be done?

Can the VBA program automatically know how to handle the above two
situations or should I do something to help the VBA program recognize the
above two situations?

The UDF program flow is as below

Function Name1(...)

...(block of statements)...
if ... then
goto Label
... (more statements)...
end if

Label:
... (block of statements)...

Select Case Var1
....
End Select

End Function

[Jim Thomlinson]

The more complicated the coding logic required the less you want to use goto
statements. As Rick states anything you can do with a goto statement you can
do better with if then else and select case statements. Post what you have so
far and some indication of what you need and we can steer you in the right
direction.
--
HTH...

Jim Thomlinson

[Rick Rothstein (MVP - VB)]

> To answer Rick's question, the coding is about a UDF for a very
> complicated
> trading logic.
> The main stream is already very long and it would be much cleaner and
> easier
> to read if using goto statement to jump to the sub trading logic.

I disagree with your conclusion. Much cleaner and easier to read would be
separate functions and/or subroutines (depending on if values needed to be
returned or not) that performed the code work and were called from your main
UDF. I'm thinking of a structure like this...

Function MyUDF(ArgList)
If Condition1 Then
MsgBox MyFunc(ArgList)
ElseIf Condition2 Then
Call SomeSub(ArgList)
ElseIf ...etc...
' ...etc...
Else
Call CatchAllSubroutine()
End If
' Other code, maybe dependent on results from above
' or containing other If-Then structures like above.
End Function

Function MyFunc(ParameterList)
' Code to do something and return a value
End Function

Sub SomeSub(ParameterList)
' Code to do something
End Sub

Sub CatchAllSubroutine()
' Code to handle whatever the above didn't
End Sub

The key to the above is to give full, meaningful names to your function and
subroutines; that way, your main UDF function will read clearly, like a
story, and what is going on will be totally clear. The functions and
subroutines you are calling can, in themselves, be structured with call outs
to other functions and subroutines as necessary. Doing it this way make the
code clear and you don't have to scroll through monstrously long listings of
code trying to find sections you are jumping around to. Each function or
subroutine will be listed in the right-hand drop-down list when
(General)(Declarations) is selected from the left-hand drop-down, so
navigating to the need subroutines or functions is quite easy. Also, as each
subroutine and function is bundled unto itself, future maintenance of its
code is easy to do. Anyway, this is probably a person-preference kind of
thing and, I am assuming, you are probably too far along in your current
coding to stop and restructure everything.

Rick

[Charlie]

What you are suggesting is actually better laid out using subroutines than
GoTo's

Select Case Whatever
Case "Whatever1"
DoSomething1
Case "Whatever2"
DoSomething2
End Select

'-- or --

If Whatever Then
DoSomething1
Else
DoSomething2
End If

'-- etc. --

End Sub

Sub DoSomething1()

...

End Sub

Sub DoSomething2()

...

End Sub

This structure works out much nicer for organizing your code into logical
groups

Happy Programming!
Charlie

[Laurie]

Thanks for all your quick responses and very helpful suggestions!!!!!!

I need time to digest the ideas but I can see some way to solve the problem
now.

So, goto statement is universally not recommended if multiple statements
need to be put under the label line?

Thanks,
Laurie

[Rick Rothstein (MVP - VB)]

The big problem with GoTo is they are too easy to abuse. When you are
initially coding a solution, what you are doing is clear because it is fresh
in your mind. You might GoTo a new section of code and, within that section,
GoTo a different section of code and, within that section, GoTo yet another
section of code and so on. This is known as "spaghetti code". As I said,
while you are creating this mess (to my way of thinking), all seems clear
about how you are jumping around because it is all fresh in your mind.
HOWEVER, six months down the line (when your memory of the structure is not
so fresh anymore), when you decide to change something in your code, you
will be faced with what looks like a bunch of random jumps hither and
thither... it will be very hard to modify your code because you will not
remember what parts of a particular GoTo section is dependent on some
previous GoTo section. Worse, yet, is if you are doing this at work and a
co-worker is called upon to modify your code... trust me, he/she will "hate"
you for using those GoTo's. Using GoTo's is a habit best not to get involved
with in the first place.

Rick

"Laurie" <Lau...@discussions.microsoft.com> wrote in message

news:750C2746-6AC8-43F9...@microsoft.com...

[Tim Zych]

> will do yourself a big favor if you learn not to use the GoTo statement
> (it makes code much harder to read, especially several months from now
> when you come back to edit your code). With the exception of On Error GoTo
> error handler, there is almost no

Yep.

I suspect that everyone who has debugged something with GoTo cringes when
they hear the word (or GoSub, which is even more difficult to debug).

--
Tim Zych
www.higherdata.com
Compare data in Excel and find differences with Workbook Compare
A free, powerful, flexible Excel utility

[Charlie]

It's not about the number of lines following the label. In general GoTo
statements are not recommended at all, except for

On Error GoTo ...

to handle error trapping. GoTo statements are throwbacks from the '40s
(Fortran). They tend to make code less readable than using If-Then-Else or
calling subroutines and functions, but once in a while they can be used.

In all of my libraries I have only one old function I can think of where I
used GoTo's not in error trapping. It is a Heap Sort that proved to run
faster with GoTo's than two other versions where the GoTo's were replaced
with more structured code.

Other than that, GoTo's are Gone.