VBA 应用程序.运行，用于运行存储在 Excel 单元格中的带有参数的宏

Question

我在Excel单元格中存储了一些宏名称。

我遍历这些单元格并调用编写在其中的宏，如下所示。

[下面的子例程是通过正确提供参数从另一个子例程调用的。

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

当宏没有参数时，它可以正常工作。但是，当它尝试运行存储在单元格中的以下宏时，会生成错误。

JumpToNextCtl， ws， ctlGrpName， activeTbx

这个宏应该从子例程"SelectAppsToRun"中获取其参数 - ws，ctlGrpName 和 activeTbx

它应该运行的代码是：

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

我收到的错误消息是：

无法运行宏"JumpToNextCtl"， ws， ctlGrpName， activeTbx。宏可能在此工作簿中不可用，或者可能禁用了所有宏。

我猜Application.Run将所有参数作为字符串。有没有办法让这个方法运行？

Answer 1

请尝试使用下一个场景：

1. 调用"SelectAppsToRun"的主要Sub：

Sub MainCall()
SelectAppsToRun "This is a call. It is ", ActiveSheet, ActiveWorkbook
SelectAppsToRun "This is a call. It should be ", ActiveSheet, , True
End Sub

2. 测试 "SelectAppsToRun"Sub可能如下所示：

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

以下
3. Subs从"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

4. 打开即时窗口(Ctrl + G，在 VBE 中)，在"MainCall"Sub内单击并逐行运行代码，按F8键。

这个Sub将调用SelectAppsToRun，传递三个参数(一个字符串和两个对象)。然后，将使用三个参数调用两个测试Subs(testCall1 和 testCall2)中的每一个。

这不是你需要实现的吗？如果是，很高兴我能帮忙。如果没有，请描述此方案不符合您的需求的地方。