Call Subroutine from a string value VBA

Question

trying to call this subroutine using a string. I have tried Application.Run like I have read online but that doesn't seem to be working. The variable element will loop through and represents different state codes. So I have subs called "CA_Config", "GA_Config" "AZ_Config" etc.

Dim strSubToCall As String
strSubToCall = element & "_Config()"
Application.Run strSubToCall

State subs are very different therefore need to be different subroutines. The other subs and the main sub calling the other ones are all public.

Example for CA sub below

Public Sub CA_Config()

Dim rngLastHeader As Range
Dim intLastRow As Integer
Dim i As Integer
intLastRow = Sheet1.currWS.UsedRange.Rows.Count
Set rngLastHeader = Sheet1.currWS.Range("A1").End(xlToRight)

rngLastHeader.Offset(, 1).Value = "Use Tax Reversal Needed"

Sheet1.currWS.Range("X:X").EntireColumn.Copy
Sheet1.currWS.Range("Y:Y").PasteSpecial xlPasteFormats
Sheet1.currWS.Range("Y:Y").Columns.AutoFit

End Sub

Instead of having different subs for each state, have one sub with a parameter.... ie., Sub StateConfig("CA") How different are each of these "state subs"? — ashleedawg
– ashleedawg, Commented Dec 15, 2017 at 17:59
Prepend your Sub with module name. For instance, Application.Run "Module1.MySub" — JohnyL
– JohnyL, Commented Dec 15, 2017 at 18:00

JohnyL · Accepted Answer · 2017-12-15 18:33:43Z

4

Remove parenthesis and prepend your Sub name with module name. For instance, Application.Run "Module1.MySub".

edited Dec 15, 2017 at 18:33

answered Dec 15, 2017 at 18:02

JohnyL

7,1845 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

JohnyL Over a year ago

@Mat'sMug Please, follow comments under the question - the parenthesis were the first thing I mentioned.

Mathieu Guindon Over a year ago

Indeed, looks like a bug. Not in the VBE though - Application.Run is entirely Excel's object model. So, it's a bug in Excel, not in the VBE. But if you tell Microsoft I bet they'll call it a feature ;-)

Mathieu Guindon Over a year ago

Another interesting bit: Public Sub Sub1234() is listed in Excel's "macros" list, but the macro dialog's "Run" button is disabled when it's selected, which confirms my long-standing suspicion that Excel's "Run Macro" tool uses Application.Run under the hood. Yet another good reason to use meaningful names!

ThunderFrame Over a year ago

Sub1 through Sub1048576 won't work, but use a numbering scheme above 2^20, and Sub1048577 will work just fine. It's obvious, right?

ThunderFrame Over a year ago

@MathieuGuindon Oh wait, duh, "Sub1" is a valid cell address, where the Macro argument to Application.Run can be a Range (presumably to facilitate Excel4 Macros?), and (according the help) when that argument is a string, it is in the context of the active sheet. Yet another instance of name clashes once Excel bumped the row/column counts in 2007. There are a bunch of new inspections for Rubberduck VBA to handle ;-)

|

Collectives™ on Stack Overflow

Call Subroutine from a string value VBA

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related