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

[PachAvin]

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?

[p45cal]

You might get some help from the solutions here:

Excel-vba Application.Run alternative to run macro with named arguments - Stack Overflow
https://stackoverflow.com/questions/55952903/excel-vba-application-run-alternative-to-run-macro-with-named-arguments

Also, since it looks as though you might be passing an object (ws a sheet?) there might be something of interest here:

Application.Run and ByRef Arguments
http://tushar-mehta.com/publish_train/xl_vba_cases/1022_ByRef_Argument_with_the_Application_Run_method.shtml

While searching I came across:
https://www.ozgrid.com/forum/index.php?thread/1229628-vba-application-run-for-running-macros-with-parameters-stored-inside-excel-cells/
which is a cross post (that's fine) but not fine without supplying links to crossposts, see this:

Excelguru Help Site - A message to forum cross posters
https://www.excelguru.ca/content.php?184

for netiquette (many forums have it as a requirement, not just netiquette)

[Paul_Hossler]

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

Yes



Option Explicit


Sub test()

With Worksheets("Sheet1")
'need the .Text Edit -- or at least .Value. Can't leave as Range in the macro name
Call Application.Run(.Range("A1").Text, .Range("B1"), .Range("C1"), .Range("D1"), .Range("E1"))
End With


End Sub


Sub TwoTimes(P1 As Double, P2 As Double, P3 As Double, P4 As Double)


MsgBox 2 * P1
MsgBox 2 * P2
MsgBox 2 * P3
MsgBox 2 * P4


End Sub

[Kenneth Hobs]

Welcome to the forum!

You passed the wrong value types, I guess.

Your sub has:

Sub SelectAppsToRun(ctlGrpName As String, ws As Worksheet, activeTbx As MSForms.TextBox)
But I guess you did:

Application.Run "JumpToNextCtl", ws, ctlGrpName, activeTbx
You should have used:

Application.Run "JumpToNextCtl", ctlGrpName, ws, activeTbx

[SamT]

Assuming that the cells contain EITHER a macro name OR a Macro Name and 2 Parameters, and nothing else:

Something like...
Option Explicit

Sub SelectAppsToRun()
Dim rng As Range 'rng only contains Strings, your 'Macros" must deal with Strings
Dim Arr

For Each rng In Sheet1.Range("A1:A5")
Arr = Split(rng, ", ") 'The Split parameter depends on the textual formatting of the contents of the cell.
'I am assuming a Comma & Space separates each "value"
If Not IsArray(Arr) Then
Application.Run rng.Value
Else
Application.Run Arr(0), Arr(1), Arr(2)
End If
Next rng

End Sub



See how much easier it is to just use VBA:

Sub RunAllFive()
Macro1
Macro2 Sheets("Sheet1"), TextBox2
Macro3
Macro4 Sheets("Sheet2"), Texbox1
Macro5
End Sub

[Kenneth Hobs]

These forums makes it easy to get clear help. The best way is to attach a simple example file. Click the Go Advanced button on lower right of a reply box. Then click the Manage Attachments below that reply box.

One critical thing that is often overlooked is the Module name. Sometimes one also needs to Run a macro from another workbook. In that case, we would add that but since you did not say that, I did not explain or show that.

Here is a short example where both work for me. Normally, I use more distinctive module names than Module1.

Sub Test_Run()
Application.Run "Module1.Run1", Worksheets(2)
Application.Run "Module1.Run2", Worksheets(2).Name
End Sub


Sub Run1(ws As Worksheet)
MsgBox ws.Name
End Sub


Sub Run2(wsName As String)
MsgBox Worksheets(wsName).Name
End Sub

[PachAvin]

To SamT

The method via array does not work either. Here is a file attached. I have simplified my original query for more clarity,

[PachAvin]

To Kenneth Hobs

If I provide it directly as Application.Run "JumpToNextCtl", ws, ctlGrpName, activeTbx it works. But not when I pick that as a string from a cell in Excel.

[PachAvin]

To Paul_Hossler

Thanks for explaining through file. There is a difference in the case I explained. My macroname with its parameters lie in a single cell. Attached herewith is a file where I have simplified my original query.

[p45cal]

You're supposed to include the cross-post links yourself, here's another one:
https://stackoverflow.com/questions/67519832/vba-application-run-for-running-macros-with-parameters-stored-inside-an-excel-ce

[Kenneth Hobs]

Your #8 does not work in your example file. You have to post code for us to see how that "works".

In that file, you are passing this:

'DisplayNameofTextBox, ws, activeTbx
Sub DisplayNameofTextBox(ws As Worksheet, activeTbx As MSForms.TextBox)

Again, you are passing strings in Run while the Sub is expecting objects. ws is a string. activeTbx is a string. Your DisplayNameofTextBox should be coded to handle string inputs rather than objects.

[PachAvin]

That is what I am trying to find.:yes

How to pass objects from cells? OR how to make the sub interpret what I pass from cell as object?

[Paul_Hossler]

LOTS of disconnects, wrong type of parameters being passed, wrong name of TextBox, wrong passing order of parameters, wrong macro name, ......





Sub SelectAppsToRun()
Dim rng As Range, LR As ListRow, Arr, ws As Worksheet, txtctl As MSForms.TextBox

With MacroListSht.ListObjects("MacrosTable")
For Each LR In .ListRows

Set rng = LR.Range

Set ws = ThisWorkbook.Worksheets(rng.Cells(1, 1).Value)

Arr = Split(rng.Cells(1, 2).Value, ", ")
Set txtctl = ws.OLEObjects(Arr(2)).Object

Application.Run Arr(0), ws, txtctl
Next
End With


End Sub


Sub DisplayNameofTextBox(ws As Worksheet, activeTbx As Object)
MsgBox ws.Name
MsgBox activeTbx.Value
End Sub

[Kenneth Hobs]

You only have one valid value, the name of the macro, to parse out. You had already created the other two inputs.


Sub SelectAppsToRun()
Dim rng As Range, ws As Worksheet, txtctl As MSForms.TextBox

Set ws = ThisWorkbook.Worksheets("Macro List")
Set txtctl = TxtBoxSht.OLEObjects("TextBox1").Object

For Each rng In MacroListSht.ListObjects("MacrosTable").ListColumns(2).DataBodyRange
Application.Run Split(rng, ", ")(0), ws, txtctl
Next rng
End Sub


Sub DisplayNameofTextBox(ws As Worksheet, activeTbx As MSForms.TextBox)
MsgBox ws.Name
MsgBox activeTbx.Name
End Sub

Use Paul's file to achieve your goal.

Unless you know that the inputs will always exist, you will want to add error handling code for missing things like macro, worksheet, and textbox.

[SamT]

How to pass objects from cells? OR how to make the sub interpret what I pass from cell as object?
Cells don't contain Objects, they contain text or numbers. Your code must be able to use Strings.

Cell.Value = "Macro1, Sheet1, Tbox1"

Sub MacroName(wsName as String, TBoxName as String)
With Worksheets(wsName). Controls(TBoxName)...

Then


Application.Run Arr(0), Arr(1), Arr(2)

In your example Arr(0) = "DisplayNameofTextBox", Arr(1) = "ws", and Arr(2) = "activeTbx". Neither "ws" nor "activeTbx" are Objects

Reread the opening line of Paul's signature. "Tell us WHAT you want to do, not HOW you think you want to do it"

[Kenneth Hobs]

I added to Paul's file in #13. I also added another row to the table with just one value in column 2.

One can setup the Cases to act on the Sub's name or number of elements in the array or such.




Sub Main()
Dim rng As Range, LR As ListRow, Arr, ws As Worksheet, txtctl As MSForms.TextBox

With MacroListSht.ListObjects("MacrosTable")
For Each LR In .ListRows
Set rng = LR.Range
Arr = Split(rng.Cells(1, 2).Value, ", ")

Select Case True
Case UBound(Arr) = 0
Application.Run Arr(0)
Case Arr(0) = "DisplayNameofTextBox"
Set ws = ThisWorkbook.Worksheets(rng.Cells(1, 1).Value)
Set txtctl = ws.OLEObjects(Arr(2)).Object
Application.Run Arr(0), ws, txtctl
Case Else
'do something else if nothing is true...
End Select

Next LR
End With
End Sub


Sub ShowActiveSheetsUsedRangesAddress()
MsgBox ActiveSheet.UsedRange.Address(external:=True), vbInformation, "ShowActiveSheetsUsedRangesAddress"
End Sub

[snb]

Select Case True
Case UBound(Arr) = 0
Application.Run Arr(0)
Case Arr(0) = "DisplayNameofTextBox"
DisplayNameofTextBox ws, txtctl
End Select

[PachAvin]

Remembering: Tell us WHAT you want to do, not HOW you think you want to do it


I will try my best to explain in detail what I really want to do rather than detailing on my ideas.


I have a few sheets containing many shapes and ActiveX controls (file attached exhibiting simplified version of my problem). Selected Shapes and ActiveX controls have been grouped together (with groups being given a name). There are few such groups.


The ActiveX controls are assigned to Classes with KeyDown event at the Open event of workbook. One class for dealing with TextBoxes and another class for dealing with ComboBoxes. So when I press TAB while inside an ActiveX control, the cursor moves to the next ActiveX control in that group. The macro that facilitates this is JumpToNextCtl ws, ctlGrpName, txtctl


With respect to each group of ActiveX Controls / Shapes, separate macros are also supposed to run on pressing TAB like mentioned above. Taking examples of the attached file:
(1) For the currently active ActiveX control, If its sheet is Sheet1 and ActiveX group name is AddContGrp the macro that will run is DisplayContactsGroupSheetName.
(2) For the currently active ActiveX control, If its sheet is Sheet2 and ActiveX group name is AddProjGrp the macro that will run is DisplayProjectGroupSheetName.


I have given all these macro names in a sheet named 'Tables Sheet' in a table. To loop through these table range and pick the macro to run, I call the following macro inside the Class Module mentioned earlier.


SelectMacrosToRun ws, ctlGrpName


This macro finds the worksheet name (of currently active ActiveX control) in the first column of the table and ActiveX controls group name (of currently active ActiveX control) in the second column of the table. If both match it will run the macro in the third column of the table.


I could have directly called those macros in the KeyDown event of the classes. But avoided doing that for the following reasons:


(1) All macros will run whenever I press the TAB. I would like only those macros to run that are related to the currently active ActiveX Control.
(2) Want to keep Class Module compact without filling in all the macro names. Instead I prefer to keep those macro names in a separate sheet, choose from there according to the currently active ActiveX control and then run only those particular macros.
(3) Keeping the macro names in a separate sheet enables me easily narrow down into the problematic macros because beside the macro names in the sheet, there will also be the names of Worksheet and ActiveX Control Group names. So when I press TAB when inside an ActiveX control, I can easily refer to the sheet and check just the macros related to that ActiveX control.


For the time being, I am calling the macro [ JumpToNextCtl ws, ctlGrpName, txtctl ] directly from the Class Modules.

But what I want is, I want to choose this macro from the table and run using [ SelectMacrosToRun ws, ctlGrpName ]

[SamT]

I understand that you are trying to emulate the UserForm's Tab action on a Worksheet.

Why doesn't Setting the Sheet's ActiveX controls' TabKeyBehavior Properties to True work for you?

[PachAvin]

To SamT:

Because Ranges are also involved in the tab order and not just the ActiveX. I have not shown that in the file though.

My sole issue is getting the macros in the cells to run.

Is there a way to take the argument piece from the cell, check whether a similar parameter exists in the 'SelectMacrosToRun' and use that as parameter. If so the object would get passed as parameter like we wanted. Right?

[SamT]

Because Ranges are also involved in the tab order So TabToControl and or TabToRange. I suppose the Range could be on a different sheet than sheet Tabbed From.

If you take the 'Tables Sheet' Sheet and change its CodeName to 'TableSht', or whatever, then you can treat the 'TableSht' Code Page like a Class module with User Defined Properties that can return Objects and Arrays. Note that 'tableSht' is a Class Object, as well as a Worksheet. Use 'Tablesht&dot' (TableSht.) in code to reference any Custom Properties as well as any Worksheet Items. In Code, Sheets("Tables Sheet") should only reference Sheet Items.

My first thought was to set a global Variable to "CurrentControl" whenever a control is activated, then use a Select Case to call a 'TabKeyDown' sub with all required parameters. Those parameters can be returned by the TableSht Properties.

Unless there is a compelling reason to make all the Procedure names and parameters available to Users, I would just 'hardwire' it all in Code