GOTO(a,b,c,d)X

[mud2]

Is there a VBA command such as GOTO(a,b,c,d...)X, where the code goes to a given line (label?) depending on the value of variable x?
Seems simpler than a series of If's.....

[OBP]

Yes it is On Goto or On Gosub form the Old BASIC. There is a help topic on it in the VB Editor help

[Norie]

It's just Goto or GoSub and most people avoid using then.

If you have multiple If's perhaps you should be looking at a Select Case structure.

[mud2]

Thanks OBP. I haven't used a similar construction since my FORTRAN IV Days!
To Norrie! Problem is I would need multiple (imbedded) Select Case's

[Norie]

But you still shouldn't use Goto, it really is frowned on by most people who code.

In fact I don't understand why you would need it with a Select Case structure.

PS I've been programming for more than 20 years starting on a ZX Spectrum where using Goto/GoSub was used all the time, as well as many other bad practices.:bat2:

[OBP]

Norie, I disagree totally with your stand. I started programming before the ZX Spectrum was produced.
Gosub is the old fashioned way of doing what VBA does now. Every Seperate piece of code is in a Sub short for Subroutine, modules are just another name for them and Functions are just special Subroutines that return a value.
When you have a piece of code that you want to use repeatedly a Subroutine makes a lot of sense because it can be called from anywhere and as many times as is required in the current Sub.
The Goto was always frowned upon by programmers as it can lead to spaghetti code and it is easy to lose the flow, but not subroutines which have a return, the Structured aproach developed by the BBC computer team still used subs but they were named rather numbered.
You are also wrong about the correct function being Goto and Gosub, in mud2's case he wants to go to a sub based on a previous result and the correct function for that is On Goto or On Gosub.
From the Accees VB Editor help -
GoSub...Return Statement


Branches to and returns from a subroutine within a procedure (http://javascript%3Cb%3E%3C/b%3E:hhobj_4.Click%28%29).
Syntax
GoSub line
...
line
...
Return
The line argument (http://javascript%3Cb%3E%3C/b%3E:hhobj_5.Click%28%29) can be any line label (http://javascript%3Cb%3E%3C/b%3E:hhobj_6.Click%28%29) or line number (http://javascript%3Cb%3E%3C/b%3E:hhobj_7.Click%28%29).



On...GoSub, On...GoTo Statements


Branch to one of several specified lines, depending on the value of an expression (http://javascript%3Cb%3E%3C/b%3E:hhobj_4.Click%28%29).
Syntax
On expression GoSub destinationlist
On expression GoTo destinationlist
The On...GoSub and On...GoTo statement syntax has these parts:
Part Description expression Required. Any numeric expression (http://javascript%3Cb%3E%3C/b%3E:hhobj_5.Click%28%29) that evaluates to a whole number between 0 and 255, inclusive. If expression is any number other than a whole number, it is rounded before it is evaluated. destinationlist Required. List of line numbers (http://javascript%3Cb%3E%3C/b%3E:hhobj_6.Click%28%29) or line labels (http://javascript%3Cb%3E%3C/b%3E:hhobj_7.Click%28%29) separated by commas.
Remarks
The value of expression determines which line is branched to in destinationlist. If the value of expression is less than 1 or greater than the number of items in the list, one of the following results occurs:

[mud2]

Thank you OBP! The "non-use" of "Go To's" is (was) a politically correct misunderstanding. It was often misused, as a Band Aid by programmers who didn't account for the consequences (Spaghetti code). I struggled for over 2 years with the code 'KENO", a horrible example of Spaghetti coding before I gave up!...BUT now,
In my case, I have a 5-choice and a 4-choice combo box, giving 20 combinations. Nested Select Cases get cumbersome, whereas an On (choice) GoSub(1,2.....20) looks very clean. But still requires 20 separate subs!

[Norie]

OBP

So you think GoSub is a good idea?

PS you are right about the On part, slightly misread the OP's post.:oops:

[OBP]

Norie, like most things it has it's place in programming, sometimes it is the best way to achieve something, in mud2's case it may be "cleaner" to have 1 line to direct the program to 20 subroutines than have 20 Case statements.
Being an old fashioned programmer I am comfortable with subroutines.
I have used them in Excel to process lots of different ranges that have to be selected for processing where the "process" is the same for each one, in which case the only other alternative is to repeat the code for each range.

[Norie]

OBP

I'm also comfortable using subroutines, but in the case of GoSub/Goto I wouldn't really call that using them since you don't leave the procedure.

Perhaps that's just my view but it seems to be backed up by the VBA help files.

Maybe what the OP needs is 'real' subroutines, perhaps being called using Applicaition.Run or some other method.

Anyways, whatever works and whatever the OP is happy with.:)

PS There isn't normally a need to select ranges etc in Excel to work with them.

PPS check this Tip from the On...Goto, On...GoSub help.:)


Tip Select Case provides a more structured and flexible way to perform multiple branching.

[mud2]

Norie has a point: You never leave the procedure! Take care of this with a "Rteurn' statement after each Gone-to Sub!
My Keno problems (and I suspect many other's) was that one GoTo lead to another...to another...! You couldn't figure out where you were! Coming back to a common point would help!