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

[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, 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.]

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?

The following are the links to my cross-posts.

http://www.vbaexpress.com/foru…de-an-Excel-Cell&p=409402

https://stackoverflow.com/ques…stored-inside-an-excel-ce