help to call a function and return control of program flow to calling function

[JOHNNYC]

Can anyone help with calling a function (B) and return control of program flow to the calling function (A)?

These functions are in different modules of the same workbook's VBE.

The modules contain only those functions.

Ideally, control would return to function (A) at the point where function (B) was called after function (B) does its work.

I guess it would work like a gosub...return.

Here's why:

The code to locate a value (from an input box) in a column (C) has been written but this code has to be run for each task the user will perform, (ie: new entry, delete entry, edit entry, etc.);

All tasks are accomplished through some sort of dialogue box (msgbox, inputbox and/or userform), there is no direct entry into a cell.

Each task is different enough to require its own code but they all require finding an inputted value in col. C to begin to determine where the task is to take place.

So is anyone that can help?

[Ken Puls]

Hi Johnny,

To call a routine from another you simply use it's name in the calling routine. Although I always include it for clarity, the Call statement is entirely optional, but if you use it it looks like this:

Sub RoutineA()
     Call RoutineB
     Call RoutineC
End Sub

Sub RoutineA()
     'Do something
End Sub

Sub RoutineB()
     'Do something
End Sub

Program flow will start at A, go to B, return to A when B is complete, go to C, return to A when C is complete, then end.

HTH,

[Bob Phillips]

I would also add that having separate modules for each procedure is not best practice, lots of modules makes things hard to find. Better to organise similar procedures into functional modules.

[JOHNNYC]

Ken,

It was a poorly asked question.

1) The calling function is a user form with 7 option buttons in a frame that is launched from workbook_open. Each button launches a different task (enter new appointment, cancel existing appointment, edit existing, etc.). Don't want to return there.

2) The called function is code that locates an apartment number after error checking. This code, itself, ends with a call to another function the allows the user to input(box) an appointment_date. Then the appointment_date function calls the appointment_time function which calls the doctor's_name function , and so on.

3) This was dandy as long as all that was being done was to enter a new appointment, but what if cancel_appointment was the chosen task? The code to enter a date wasn't needed but code to find a date had to be found and plagiarized.

4) But locating an apartment number was needed in all the tasks. Initially it was just copy_paste the apt. locate code at the beginning of the all the code no matter the task. Didn't think that was the way to go even though it worked.

5) What was actually needed was a way to send flow from the apt. num. function to the next function dependent on which task option was selected in the initial user form.


What I wound up doing was declaring a public variable at the tippy_top of this_workbook. Then, in the user form code, giving that variable a different value to correspond to the chosen task. Then testing that value at the end of the apt. num. code in order to send control to the appropriate function. It also became handy for changing the prompts in a msgbox to reflect the task at hand.

I'm sorry that you had to waste your time answering a dysfunctional question. I'll try, in the future, to think things more thoroughly through.

Yours, in the Single Malt Brotherhood,

John

[JOHNNYC]

I would also add that having separate modules for each procedure is not best practice, lots of modules makes things hard to find. Better to organise similar procedures into functional modules.

Bob,

In the beginning of writing this program all the code went into this_workbook. It was getting longer and longer until it began to look like Hands_Across_the_World. Also, everything was a problem that entailed going to to Google and trying to find solutions. In the course of those searches I got the impression that putting different things in different modules was the way to go, but I'm sure that now I am at the other extreme.

One thing was to rename the module after the function that was in it. That made things easy to find: Function1 was in a module named Function1. However, when I tried to call Function1 it produced an error. The error message was that VBA was expecting a function and not a module. The solution, it turned out, was to call the function by its full name: Function1.Function1.

I am sure that as I become more comfortable with coding that your advice will make perfect sense but right now I'm like a cat: afraid of change.

Thanks for your advice and please keep it coming,

John

[Ken Puls]

I'm sorry that you had to waste your time answering a dysfunctional question. I'll try, in the future, to think things more thoroughly through.

Not a worry.

As Bob says though, it is a way better practice to group similar types of procedures in the same module. So long as the module is well named, then it shouldn't be too hard to find them. I often have a module setup like:

• modDeveloperTools --> Procedures I use to save my addins and increment version numbers
• modErrorHandler --> Global error handling procedures and functions
• modGlobalFunctions --> Utility type functions that are referenced from any/all modules
• modGlobalProcedures --> Utility type subs that are referenced from any/all modules
• modRibbonXCallbacks --> Routines for controlling the Ribbon
• modRibbonXFucnctions --> Functions for use with the ribbon (like creating dynamicMenu XML that is passed back to a function

Within each of those modules you'll find various subs and functions. Those that are used in a specific module only are scoped with the Private keyword, those that can be called from other modules are scoped with the Public keyword. I try to keep everything I can in modules, not the This_Workbook and Worksheet class modules. Those I reserve for routines that MUST be there (like Workbook_Open or Worksheet_Calculate).

To be sure, some modules are long, some are short. The key is that I know where to find things.

As a hint, when you go into a code module you'll see (General) and (Declarations) listed at the top of the code module. Pull down the (Declarations) drop down and you'll see a list of all the procedures in there.

Biggest advice I'd have for you when you decide to go there is... save a backup of the workbook, then try moving your procedures around. Nothing to be afraid of that way. Create a new module, start pulling in the routines you think belong there, then test. IF you bugger it, you can always fall back.

[Bob Phillips]

Also, it is not a good practice to call a module the same as any procedure. Far better to use a naming convention such as Ken shows, with a letter or letters prefix.

I would also be interested in seeing your code, a structure like A calls B calls C calls D calls ... doesn't sound good to me, a better structure would be

A calls B and receives control back
If B successful, A calls C and receives control back
If C successful, etc.

This way, A acts as the controlling procedure, which is more structured and gives you better control.

[JOHNNYC]

Gents,

I have begun the process of following Bob's suggestion to do "A calls B and receives control back
If B successful, A calls C and receives control back
If C successful, etc."

It feels right and may allow for the elimination of a bunch of redundant code. Makes me feel all programmy and slick.

The latest roadblock is here:

http://www.excelguru.ca/forums/showt...-with-modality

if you care to take a look.

Thanks for your help so far,

john