VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell

[PachRedoc]

Aim: To Loop Through Cells Containing Macro Names and Run Those Macros via Application.Run

Issue: The code used to loop through cells and run macros works perfectly only for macros without parameters. For those macros with parameters, it fails.

Problem in Detail:

I have stored a few macro names inside Excel cells.

I loop through those cells and call macros (names) written inside them as follows.

[The subroutine below is called from another subroutine by providing the parameters correctly.]

Sub SelectAppsToRun(ctlGrpName As String,  ws As Worksheet, activeTbx As MSForms.TextBox)

       Dim rng as Range

           For each rng in Sheet1.Range(“A1:A5”)

               Application.Run rng.value

           Next rng

    End Sub

It works correctly when there are no parameters for the macros. But generates error when it tries to run the following macro stored in a cell.

JumpToNextCtl, ws, ctlGrpName, activeTbx

This macro is supposed to take its parameters - ws, ctlGrpName and activeTbx - from the subroutine 'SelectAppsToRun'
ws as Worksheet, ctlGrpName as String, activeTbx As MSForms.TextBox

The error message I get is:

Cannot run the macro '"JumpToNextCtl", ws, ctlGrpName, activeTbx'. The macro may not be available in this workbook or all macros may be disabled.

I understand Application.Run considers the whole as a single string and the fact that there are commas in it does not treat them as separate parameters.

Is there any way to accomplish what I aim to accomplish?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[PachRedoc]

The following are the links to my cross-posts for the same query.

VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell

Aim: To Loop Through Cells Containing Macro Names and Run Those Macros via Application.Run Issue: The code used to loop through cells and run macros works perfectly only for macros without parameters. For those macros with parameters, it fails. Problem in Detail: I have stored a...

www.vbaexpress.com

VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell

I have stored a few macro names inside Excel cells. I loop through those cells and call macros written inside them as follows. [The subroutine below is called from another subroutine by providing the

stackoverflow.com

VBA Application.Run for Running Macros-with-Parameters Stored Inside Excel Cells - OzGrid Free Excel/VBA Help Forum

[xpost] [/xpost] Aim: To Loop Through Cells Containing Macro Names and Run Those Macros via Application.Run Issue: The code used to loop through cells and run macros works perfectly only for macros without parameters. For those macros with parameters,…

www.ozgrid.com