GOTO sucks, but can I "GOTO Variable"?

[smpita]

Hello!

About a year ago I started writing a macro. Because of my coding nubiness, I used a lot of GOTO commands. Over the year, my macro has grown to be quite large and I have learned the errors of my ways. I am in the process of rewriting the code to remove GOTO from my code.

As I rewrite this long macro into functions and subroutines, I still need to use some GOTO commands to connect to old code bits. I made a function called "DetectReport" that returns the GOTO label name. Can I use GOTO with a variable?

Like I will have a variable (strReportName) that could have the values "REPORT1" or "REPORT2" or "REPORT3"

And in my code I will have some labels that look like:

Please Login or Register  to view this content.

I want to do a GOTO strReportName

Thank you!

[6StringJazzer]

Relevant thread here. Apparently not.

One way:

Please Login or Register  to view this content.

[TMS]

Short answer: good luck with that.

http://www.vbaexpress.com/forum/show...Sub-GoTo-Point


Regards, TMS

[smpita]

Thank you 6StringJazzer and TMS. I think I'll use select case for the time being.

[TMS]

You're welcome. Thanks for the rep.

[protonLeah]

Please Login or Register  to view this content.

You could use DetectReport to load the names into an array, then loop through it, incrementing ReportNum and branching if the name meets some condition...

[smpita]

Thank you, protonLeah. I've never used GoSub. I think I get how it works from your example. I might be able to use it during the recoding transition.

[Norie]

Why not put the code for each report in it's own sub?

[smpita]

Norie, I intend to be doing that but there is a lot of code to parse and separate and my time per day to work on it is limited so I will be doing it in stages. First step was to break up my "If Range(whatevercell).value = UniqueThingOnTheReport then Goto ReportName" block at the start of the macro.

Several pieces do similar tasks rewritten for the individual report. I'm going to try to make more generic functions that each report can use, for example saving the file to a folder for the folder with a naming convention specific to the report type. I'm doing this both for practical usage as well as learning.

[Norie]

If you are using Goto then it should be easy to separate out the code.

For example this,

Please Login or Register  to view this content.

would become this.

Please Login or Register  to view this content.

[smpita]

It's not going to be too difficult to break into subs but a little time consuming. I do want to rewrite several portions into generic functions, especially save handling and error handling. I was going to post my code in it's entirety for you to see what I mean but posts are apparently limited to 15000 characters.

[6StringJazzer]

You can attach your entire file.

1. Under the text box where you type your reply click the Go Advanced button.
2. In the next screen look above the reply box and click the "paper clip" icon. You will get a pop-up screen.
3. In the pop-up, click Add then Select to select a file from your local drive
4. Click the Upload button to upload the file
5. Click Done to attach it.

It will be displayed as an attachment underneath your post text. Alternatively as the last step you can click the Insert Inline button and a hyperlink to the file will be inserted directly into your post text in the spot where your cursor is set at the moment you click the button.

[smpita]

Everything starts with a keystroke to run AutoReportResize(). code.txt