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

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

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

    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?

  • AvinEswar

    Changed the title of the thread from “VBA Application.Run for Running Macros-with-Parameters Stored Inside an Excel Cell” to “VBA Application.Run for Running Macros-with-Parameters Stored Inside Excel Cells”.
  • Thanks Ken

    Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. We are here to help so help us help you!

    Read this to understand why we ask you to do this