Build Subroutine name then call it

based on programming, a variable is set that tells me what subroutine to call next.  Rather than a bunch of nested ifs or select case statement, is there a way to call the subroutine using that variable so it will know which one to call?

E.g., variable = "ListBox1"

Call variable & "_Assembly"

which would equate to the command:

Call ListBox1_Assembly()

Thanks!

Dave
LVL 42
dlmilleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
patrickabCommented:
dlmille,

The compiler will error at the line:

Call variable & "_Assembly"

because until the macro is run there isn't a valid subroutine to branch to.

Patrick
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
You should be ok if you use run instead of call

Chris
Dim var As String

    var = "ListBox1"
    Run var & "_Assembly"

Open in new window

0
 
patrickabCommented:
Chris,

Good one.

Run works well.

Patrick
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Chris BottomleySoftware Quality Lead EngineerCommented:
Patrick

Always a pleasure to get a compliment from an expert such as you.

Chris
0
 
dlmilleAuthor Commented:
Great idea!  But it doesn't work - I'm in the sheet codepage in a Private subroutine, and I'm getting an error cannot run this macro as it may not be available  in the workbook or macros are disabled....

Dave
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
The mechanism will work but are you 'breaking' the rules for example is the called sub private.  Essentially where are the target subs located and how are they defined?  If you let us know we can help or try to work around the issues.

Chris
0
 
dlmilleAuthor Commented:
I originally had nested if then statements, so am trying to simplify.  Essentially when a ListBox gets focus the variable lastLB has the name of that listbox.  The following routine used to be a set of nested if then elses, to fire the right LostFocus routine:

Private Sub fireListBox()
    If lastLB = "" Then
        Call ListBox1_LostFocus 'fire the graph - default fired
    Else
        Run lastLB & "_LostFocus"
    End If
End Sub

in the same codepage, the following routines (like the one below) are setup:
Private Sub ListBox1_LostFocus() 'when something else on the spreadsheet is selected

    lastLB = ListBox1.Name
    Call UpdateChart_ArrayOnLBLostFocus(ListBox1, "Executive_Range", "Executive Rollup Data", "Executive Rollup", "Chart 238", True)

End Sub

Again, this all works with if then else's -  I was trying to save all the code and simplify as a result.

Both subroutines are in a Sheet's codepage.


The details should not be important, but the app is set up this way because the user can select items on a listbox for graphing, and when anything is selected on the sheet, the lostfocus event is fired.  We've built "buttons" so the user doesn't have to find a place to click on the sheet to see a chart update as a result of the lostfocus event.  Just click the button and then it updates...

Dave
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Within your if then else you are in fact using run and call interchangeably already so noting teh error message has changed with use of run and a variable the issue must lie within the code as implemented .. can you advise what you are using now?

Chris
0
 
patrickabCommented:
Dave,

Call must have in the VBA a complete human-readable name of a subroutine. If it does not the compiler cannot identify the subroutine at the time it compiles. It is not good enough to create the name of the subroutine at runtime. It must be human-visible at compilation time. Thus the need to use Run. It has nothing to do with placing Call or Run within an If clause.

Patrick
0
 
patrickabCommented:
Dave,

The code below is in the attached file. Run really does work, but you cannot use it to pass arguments to subs.

Patrick
Sub fred()
Dim str1 As String
Dim str2 As String

str1 = "sid"
str2 = CStr(InputBox("Enter subroutine number - 1 or 2", "Sub number?", 1))

Run (str1 & str2)

End Sub

Sub sid1()
MsgBox "See it worked - sub#1"
End Sub

Sub sid2()
MsgBox "See it worked - sub#2"
End Sub

Open in new window

Using-RUN-01.xls
0
 
dlmilleAuthor Commented:
Chris - I didn't show you the routine with nested if then elses that worked, lol!  I was showing you how I used what you suggested and DIDN'T work!

Ok - I'll send you a demonstration file...

Dave

0
 
dlmilleAuthor Commented:
patrick - when I use your file, it works whether the code is in a module or sheet codepage.

However, I'm getting the same error when I just copy/paste your code into a new worksheet.  A ha!  It appears Chris' tip and your help have done 2 things:  1) Identified my solution, 2) Allowed me to see something's not quite right in my setup, so I can't implement the solution....

What could be causing the following?



Dave
Error-1004.png
0
 
dlmilleAuthor Commented:
Ok - your solution works if my file is Excel 97-2003 type.  If I'm working in Excel 2007 (.xlam) it gives the above error.

That should narrow down the fix, I think.

dave
0
 
dlmilleAuthor Commented:
Ok - two attached files.  Running fred from the Ribbon/Run Macro.  The .xls file works properly, and the .xlam doesn't.

Ideas?

Dave
test.xls
test.xlsm
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
In your version of te code can youi establish exacty what lastLB comprises ... i.e.

msgbox ">" & lastLB & "<"

What does it return ... including any hard returns between the components

Chris
0
 
dlmilleAuthor Commented:
lastLB is a string name, containing the name of the last ListBox that had attention.  The problem lies in the post http:Q_3498826.html, not with my coding,  I believe.  Did you look at that post?

Dave
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Did i look at that post ... no I didn't but then again our posts overlapped.

I am interested in the observation that it works pre 207 but not post.  I did modify the code as below and it works so ...

Chris
Option Explicit

Sub fred()
Dim str1 As String
Dim str2 As String

str1 = "sid_"
str2 = CStr(InputBox("Enter subroutine number - 1 or 2", "Sub number?", 1))
Run str1 & str2

End Sub

Sub sid_1()
MsgBox "See it worked - sub#1"
End Sub

Sub sid_2()
MsgBox "See it worked - sub#2"
End Sub

Open in new window

0
 
dlmilleAuthor Commented:
Ok that works just fine.  what gives?

Dave
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
I have no idea in all honesty ... even modifying the string to a single constanty doesn't work as sid1 only as sid_1.

Chris
0
 
patrickabCommented:
It works in Excel2002. I have no other edition of Excel to test it on so I can't comment on its behaviour in other editions.

Patrick
0
 
dlmilleAuthor Commented:
Strange - and I have a call that looks like:

Call ListBox1_LostFocus (or run "ListBox1" & "_LostFocus)....

It looks like I might just have to go with a case statement.

I'll leave this open a bit longer in case more ideas come in.

Dave
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
TAking the 'lesson' you could rename your list boxes as listbox_n and then the events will hopefully work via the variable call.

"ListBox_1" & "_LostFocus

Chris
0
 
dlmilleAuthor Commented:
seems more like an unexplained quirk than a lesson, lol
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Aye, tis why I put it into quote marks ... would have been better english to use observation instead of lesson but if I spoke english as my first language I would be worried  ... wait a minute it's my only language ;o)

Chris
0
 
dlmilleAuthor Commented:
in a private module like a sheet codepage, none of this works...
Option Explicit

Sub fred()

On Error Resume Next
Run "ListBox1_LostFocus"
Run "Sid1_LostFocus"
Run "CommandButton1_Click"
Run "CommandButton2_Click"


End Sub

Private Sub ListBox1_LostFocus()
MsgBox "See it worked - sub#1"
End Sub

Private Sub Sid1_LostFocus()
MsgBox "See it worked - sub#2"
End Sub

Private Sub CommandButton1_Click()
MstBox "See it worked - sub #3"
End Sub

Private Sub CommandButton2_Click()
MsgBox "See it worked - sub#4"
End Sub

Open in new window


however, if the subroutines being called are in a public module, all of them do
 
Public Sub ListBox1_LostFocus()
MsgBox "See it worked - sub#1"
End Sub

Public Sub Sid1_LostFocus()
MsgBox "See it worked - sub#2"
End Sub

Public Sub CommandButton1_Click()
MsgBox "See it worked - sub #3"
End Sub

Public Sub CommandButton2_Click()
MsgBox "See it worked - sub#4"
End Sub

Open in new window

0
 
dlmilleAuthor Commented:
So it appears Run cannot be used without some thought, if its going to be calling private subroutines, even if in the same module...
0
 
dlmilleAuthor Commented:
which means I mess with the names of my listboxes, create public versions of their LostFocus routines (for when I want to initate them, versus the interface and really losing focus), I create duplicate routines of what the LostFocus routine was doing in a public module, or just use a CASE statement and call the function with the CALL statement.

:)

I think I'm opting for the last, as I want someone to be able to debug this at some point in the future, when the user needs help!

Thanks for all your help - I'll close this out tomorrow unless some new ideas come in.

Dave
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Interesting that the orig names work in a normal module as public.  I fully appreciate the desire to keep it maintainable which was why I offered the option to rename the listboxes but if at the end of the day that process is too much then that is of course your decision.

BTW that was not a petulant statement ... I fully appreciate that whilst we can rename the listboxes, at the end of the day when someone adds a listbox or trys to dupliate the mechanism on their own PC it will have the default naming structure and they will clone the code yet it will not work for them causing ripples.

Chris
0
 
Rory ArchibaldCommented:
SID1 is a valid cell reference in XL2007 which is why it doesn't work.
For a sub in a worksheet, you need to specify the sheet name - e.g:
Run "sheet1." & str1 & str2

Open in new window

FYI, you can pass arguments using Run.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
Rory

SID1 is a misnomer, ( it was a simple demo from PAtrick) but your point is of course valid.  Note the orig issue was with:

ListBox1_LostFocus

as run "Listbox" & n & "_LostFocus"

Chris
0
 
patrickabCommented:
Rory & Chris

Only having Excel2002 seems to have some unexpected consequences. I tend to use silly names for simple VBA subs - thus Sid, Fred etc.

Patrick
0
 
Rory ArchibaldCommented:
Chris,
There were 2 separate issues:
1. In Dave's demo, Sid1 will not work, regardless of where it is located (you can't run it from the macros dialog either) because it's a cell reference.
2. The subs in the sheet module have to be prefixed with the sheet codename, regardless of the sub name.

I thought it best to address both issues, even if #1 was only a byproduct of the sample workbook.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
dlmilleAuthor Commented:
@rorya - unfortunately, the attached still doesn't work, even with the "sheet1." prefix...

Dave

code below in Sheet1 codepage
Option Explicit

Sub fred()
Dim str0 As String
Dim str1 As String
Dim str2 As String
Dim str3 As String

str0 = "Sheet1."
str1 = "Listbox"
str2 = CStr(InputBox("Enter subroutine number - 1 or 2", "Sub number?", 1))
str3 = "_LostFocus"
Run str1 & str2 & str3

End Sub

Private Sub ListBox1_LostFocus()
MsgBox "See it worked - sub#1"
End Sub

Private Sub ListBox2_LostFocus()
MsgBox "See it worked - sub#2"
End Sub

Private Sub CommandButton1_Click()
    Call fred
End Sub

Open in new window

test.xlsm
0
 
Rory ArchibaldCommented:
That's because you didn't use str0:

Run str0 & str1 & str2 & str3

Open in new window

:)
0
 
dlmilleAuthor Commented:
Thanks for the thoughtful comments and diligence.

The results can be used and documented/understandablel for user debugging in future.

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.