Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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 "Laurie" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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 "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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 "Laurie" wrote: 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 "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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" wrote: 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 "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
goto statement in VBA
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. "Laurie" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
? IF and GOTO ? | Excel Worksheet Functions | |||
Goto | Excel Programming | |||
Goto statement in an IF function | Excel Worksheet Functions | |||
On Error Goto doesn't goto | Excel Programming | |||
On Error Goto doesn't goto | Excel Programming |