CODEWITHSUNDEEP
excelvba
AvinEswar
AvinEswar

Reputation: 203

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 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'

The codes it is supposed to run is:

    Sub JumpToNextCtl(ws As Worksheet, ctlGrpName As String, Optional activeTbx As MSForms.TextBox, Optional activeCbx As MSForms.ComboBox, Optional chkBx As MSForms.CheckBox)

       Dim shp As Shape, i As Integer, ctlname As String
       Dim ctlColl As New Collection
    
         For Each shp In ws.Shapes.Range(ctlGrpName).GroupItems
                
           If shp.Type = msoOLEControlObject Then
        
               If TypeName(shp.OLEFormat.Object.Object) = "TextBox" Or _
                    TypeName(shp.OLEFormat.Object.Object) = "ComboBox" Or _
                        TypeName(shp.OLEFormat.Object.Object) = "CheckBox" Then
                
                      ctlColl.Add shp.OLEFormat.Object
                
               End If
        
           End If
    
         Next sh

    End Sub

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 guess Application.Run takes all the parameters as String. Is there anyway to get this method running?

Upvotes: 0

Views: 1226

Answers (1)

FaneDuru
FaneDuru

Reputation: 42256

Please, try using the next scenario:

  1. The main Sub which calls "SelectAppsToRun":
Sub MainCall()
  SelectAppsToRun "This is a call. It is ", ActiveSheet, ActiveWorkbook
  SelectAppsToRun "This is a call. It should be ", ActiveSheet, , True
End Sub
  1. A test "SelectAppsToRun" Sub may look like this:
Sub SelectAppsToRun(ctlGrpName As String, ws As Worksheet, Optional wb As Workbook, Optional boolOK As Boolean)
       Dim arrProc, El
       
       arrProc = Split("testCall1,testCall2", ",") 'this may be considered the equivalent of cell range values
           For Each El In arrProc
               Application.Run El, ctlGrpName, ws, wb, boolOK
           Next
End Sub
  1. The following Subs to be called (with object parameters) from "SelectAppsToRun":
Sub testCall1(strTest, sh As Worksheet, Optional wb As Workbook, Optional boolOK As Boolean)
   Debug.Print strTest & " It is the first", "The sheet name is " & sh.name
   If Not wb Is Nothing Then Debug.Print wb.Path
   If boolOK Then Debug.Print "The situation looks good..."
End Sub

Sub testCall2(strTest, sh As Worksheet, Optional wb As Workbook, Optional boolOK As Boolean)
   Debug.Print strTest & " It is the second", "Range ""A1"" value is " & sh.Range("A1").Value2
   If Not wb Is Nothing Then Debug.Print wb.fullName
   If boolOK Then Debug.Print "The situation is good..."
End Sub
  1. Open Immediate Window (Ctrl + G, being in VBE), click inside "MainCall" Sub and run the code line by line, pressing F8.

This Sub will call SelectAppsToRun, passing three parameters (one string and two objects). Then each of the two test Subs (testCall1 and testCall2) will be called with the three parameters.

Isn't it what you need achieving? If yes, glad I could help. If not, please describe where this scenario does not match your need.

Upvotes: 0

Related Questions