+ Reply to Thread
Results 1 to 14 of 14

Using Call [sub procedure] where procedure name is passed through variable

  1. #1
    Registered User
    Join Date
    10-14-2021
    Location
    Michigan, United States
    MS-Off Ver
    Office 365
    Posts
    4

    Using Call [sub procedure] where procedure name is passed through variable

    Good morning, does anyone know a workaround for the below?

    Context: I have made a user form with a list box to allow the user to select which processes they need to run. The names of the processes are what the user calls them, not necessarily the sub procedure name itself. That information is currently placed within a spreadsheet that can then be retrieved when looping through the listbox with all of the processes on it. I have looked at the syntax rules of the Call statement, and it's looking like I can't use a variable.

    The Dilemma: I have several user processes. To use a select case statement and manually type out each sub procedure's name can be tedious. Not to mention, We will likely be adding more procedures and possibly getting rid of some as time goes on. So to do a select case statement doesn't seem optimal to me for maintenance purposes, not to mention it has the potential of becoming way to long for my liking.

    What I am trying to do: My idea was to pass the sub procedure name to a variable and then call the sub procedure using the string value stored in that variable. I have attached snippets of the code, populated listbox, and worksheet. But basically, the code looks like this.

    Code:

    If ListboxItem.selected (i) Then
    Do Until find ListboxItem value

    Loop

    ProcedureName = .cells(row,"B").value

    Call ProcedureName

    End if
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Using Call [sub procedure] where procedure name is passed through variable


    Use VBA function CallByName …

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using Call [sub procedure] where procedure name is passed through variable

    You can use:

    Please Login or Register  to view this content.
    Note: CallByName only works if you have an object method/property to call.
    Rory

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Re: Using Call [sub procedure] where procedure name is passed through variable


    As the object should be the module codename where the procedures are located so CallByName always works !

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using Call [sub procedure] where procedure name is passed through variable

    Care to make a demonstration?

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Using Call [sub procedure] where procedure name is passed through variable


    As I have an issue on my side with a general / standard module (via Components)
    but it well works on a class module like a worksheet module, the workbook module or an UserForm module just using Me statement,
    still wanna see a demonstration ?

  7. #7
    Registered User
    Join Date
    10-14-2021
    Location
    Michigan, United States
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Using Call [sub procedure] where procedure name is passed through variable

    Application.run works perfectly. The CallByName I must not be getting the syntax right. Would you mind showing how you would write that?

    I wrote CallByName lbProcessSelect.Selected(i), CallProc
    Last edited by HitTheRocs; 10-14-2021 at 10:38 AM.

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using Call [sub procedure] where procedure name is passed through variable

    Quote Originally Posted by Marc L View Post
    As I have an issue on my side with a general / standard module (via Components)
    but it well works on a class module
    That was precisely my point - you need an object, and a normal module will not work. So, no I don't need a demonstration.

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Using Call [sub procedure] where procedure name is passed through variable


    I'm prettry sure to have seen it working in a general module (was a trick) but as it was a long time ago …

    Quote Originally Posted by HitTheRocs View Post
    Would you mind showing how you would write that?
    So in which kind of module your procedures to call are located ? (drumroll)
    Last edited by Marc L; 10-14-2021 at 10:43 AM.

  10. #10
    Registered User
    Join Date
    10-14-2021
    Location
    Michigan, United States
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Using Call [sub procedure] where procedure name is passed through variable

    Quote Originally Posted by Marc L View Post

    I'm prettry sure to have seen it working in a general module (was a trick) but as it was a long time ago …

    So in which kind of module your procedures to call are located ? (drumroll)
    They're located in the traditional modules. I am not using any class modules.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Using Call [sub procedure] where procedure name is passed through variable

    You can still use CallByName this way, but no difference from Application.Run...
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    10-14-2021
    Location
    Michigan, United States
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Using Call [sub procedure] where procedure name is passed through variable

    Quote Originally Posted by jindon View Post
    You can still use CallByName this way, but no difference from Application.Run...
    Please Login or Register  to view this content.
    Awesome. Thank you.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Thumbs up Re: Using Call [sub procedure] where procedure name is passed through variable


    That's it but I could not remind it, thanks Jindon !

  14. #14
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Using Call [sub procedure] where procedure name is passed through variable

    Reminds me somewhat of people who use Range(Target.Address) to get a reference to Target...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] call variable from one procedure to in other procedure
    By shiva_raj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-20-2017, 02:19 AM
  2. [SOLVED] Create a time delay at end of procedure that will then call on next procedure
    By Clay Shooter in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-31-2013, 05:56 AM
  3. Using a call procedure
    By narrowgate88 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2010, 10:30 AM
  4. Using a variable calculated in a procedure in another procedure.
    By Trinidad3 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-06-2010, 04:51 AM
  5. [SOLVED] Variable procedure call
    By Art in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-16-2005, 09:20 AM
  6. [SOLVED] Call procedure using variable
    By donbowyer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-28-2005, 05:05 AM
  7. [SOLVED] Call Procedure
    By Ronbo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-23-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1