Results 1 to 8 of 8

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

  1. #1
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:
    Code:
    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,
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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.

  4. #4
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0
    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

  5. #5
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0
    Quote Originally Posted by Bob Phillips View Post
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,268
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by JOHNNYC View Post
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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.

  8. #8
    Seeker JOHNNYC's Avatar
    Join Date
    Jul 2012
    Location
    Texas
    Posts
    14
    Articles
    0
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •