Build Subroutine name then call it

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

Chris,

Good one.

Run works well.

Patrick

Patrick

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

Chris

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

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

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

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

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

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

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

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

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

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

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

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

Select allOpen in new window

Ok that works just fine.  what gives?

Dave

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

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

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

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

seems more like an unexplained quirk than a lesson, lol

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

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

Select allOpen 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

Select allOpen in new window

So it appears Run cannot be used without some thought, if its going to be calling private subroutines, even if in the same module...

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

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

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

Select allOpen in new window

FYI, you can pass arguments using Run.

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

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

@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

Select allOpen in new window

test.xlsm

That's because you didn't use str0:

Run str0 & str1 & str2 & str3

Select allOpen in new window

:)

Thanks for the thoughtful comments and diligence.

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

Dave