Solved

Build Subroutine name then call it

Posted on 2011-02-20
35
307 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:dlmille
  • 15
  • 11
  • 6
  • +1
35 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34936721
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
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 167 total points
ID: 34936817
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34936859
Chris,

Good one.

Run works well.

Patrick
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34936874
Patrick

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

Chris
0
 
LVL 41

Author Comment

by:dlmille
ID: 34936890
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34936916
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
 
LVL 41

Author Comment

by:dlmille
ID: 34936944
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34937166
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34937209
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
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 167 total points
ID: 34937297
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
 
LVL 41

Author Comment

by:dlmille
ID: 34938746
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
 
LVL 41

Author Comment

by:dlmille
ID: 34938788
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
 
LVL 41

Author Comment

by:dlmille
ID: 34938818
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
 
LVL 41

Author Comment

by:dlmille
ID: 34938826
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34938830
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
 
LVL 41

Author Comment

by:dlmille
ID: 34938869
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34938924
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 41

Author Comment

by:dlmille
ID: 34938970
Ok that works just fine.  what gives?

Dave
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34938992
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34939104
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
 
LVL 41

Author Comment

by:dlmille
ID: 34939216
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34940575
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
 
LVL 41

Author Comment

by:dlmille
ID: 34940706
seems more like an unexplained quirk than a lesson, lol
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34940761
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
 
LVL 41

Author Comment

by:dlmille
ID: 34940779
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
 
LVL 41

Author Comment

by:dlmille
ID: 34940781
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
 
LVL 41

Author Comment

by:dlmille
ID: 34940784
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34940821
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34941883
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 34941991
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
 
LVL 45

Expert Comment

by:patrickab
ID: 34942012
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 166 total points
ID: 34942115
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
 
LVL 41

Author Comment

by:dlmille
ID: 34945790
@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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34945922
That's because you didn't use str0:

Run str0 & str1 & str2 & str3

Open in new window

:)
0
 
LVL 41

Author Closing Comment

by:dlmille
ID: 34946444
Thanks for the thoughtful comments and diligence.

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

Dave
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now