如何在 VBA 中将字符串作为命令运行

How to run a string as a command in VBA

提问by ECode

I have this simple VBA code below, and I don't know why is not working.

我下面有这个简单的 VBA 代码，我不知道为什么不起作用。

Sub Run()
    test = "MsgBox" & """" & "Job Done!" & """"
    Application.Run test     
End Sub

What I want to do is to put the VBA Command into a variable as text and run it as a command. In this case, I want to run like MsgBox "Job Done!"and print just:

我想要做的是将 VBA 命令作为文本放入变量并将其作为命令运行。在这种情况下，我想像这样运行MsgBox "Job Done!"并打印：

Job Done!

任务完成！

采纳答案by A.S.H

You may be tempted by adding your own string "Executer":

您可能会想添加自己的字符串“执行者”：

Sub StringExecute(s As String)
    Dim vbComp As Object
    Set vbComp = ThisWorkbook.VBProject.VBComponents.Add(1)
    vbComp.CodeModule.AddFromString "Sub foo()" & vbCrLf & s & vbCrLf & "End Sub"
    Application.Run vbComp.name & ".foo"
    ThisWorkbook.VBProject.VBComponents.Remove vbComp
End Sub

Sub Testing()
    StringExecute "MsgBox" & """" & "Job Done!" & """"
End Sub

回答by M--

Short answer is, you cannot do that (You should not do that) but ... read the following to find out why and see a work around!

简短的回答是，您不能那样做（您不应该那样做）但是……阅读以下内容以找出原因并查看解决方法！

As you know you are writing your code in a compiler. What you want to do is running human-legible line of text as a command which is not possible. While you run the program all of it is compiled to machine language. When you pass that line of text to it, it cannot recognize it as a command and you will end up getting an error. What you can do is passing arguments to it:

如您所知，您正在编译器中编写代码。您想要做的是将人类可读的文本行作为不可能的命令运行。当您运行程序时，所有程序都被编译为机器语言。当您将那行文本传递给它时，它无法将其识别为命令，您最终会收到错误消息。你可以做的是向它传递参数：

Sub Run()
 test = "Job Done" 
 MsgBox(test)
End Sub

You can also run an executable which can be written as a text file within a macroand then runs within the same Sub(extension needs to be taken care of).

您还可以运行一个可执行文件，该可执行文件可以在 a 中编写为文本文件macro，然后在同一文件中运行Sub（需要注意扩展名）。

If you cannot change the variable (i.e. test) then you need to take another approach towards it. I would suggest something like extracting the argument which can be passed to the function and use that. Something like below;

如果您无法更改变量（即test），那么您需要对它采取另一种方法。我建议像提取可以传递给函数的参数并使用它。像下面这样的东西；

Sub Run()

 test = "MsgBox" & """" & "Job Done!" & """"

 extest = Right(test, Len(test) - 7)

 MsgBox (extest)

End Sub

I believe there was a same question on SO but I couldn't find it. I will included it as a reference if found it.

我相信 SO 上也有同样的问题，但我找不到。如果找到它，我会将其包含为参考。

P.S. These two posts may help to find an answer:

PS这两个帖子可能有助于找到答案：

Access VBA - Evaluate function with string arguments

访问 VBA - 使用字符串参数评估函数

Excel VBA - How to run a string as a line of code

Excel VBA - 如何将字符串作为一行代码运行

ANOTHER SOLUTION

另一种解决方案

This needs to trust the VB project. Quoting from ExcelForumand referencing to Programmatic Access To Visual Basic Project Is Not Trusted - Excel

这需要信任VB项目。从ExcelForum引用并引用对Visual Basic 项目的编程访问不受信任 - Excel

Quote:

引用：

Place your Macro-Enabled Workbook in a folder which you can designate as macro friendly.

将您的启用宏的工作簿放在您可以指定为宏友好的文件夹中。

Then open the workbook.

然后打开工作簿。

Click on the Office Button -> Excel Options -> Trust Center -> Trust Center Setting -> Trusted Locations.

Click on the Office Button -> Excel Options -> Trust Center -> Trust Center Setting -> Trusted Locations.

Then you add your folder (where you have your Excel Macro-Enabled Workbook) as a trusted location.

然后将您的文件夹（您的 Excel 启用宏的工作簿所在的位置）添加为受信任位置。

Also you need to do this:

你还需要这样做：

File -> Options -> Trust Center -> Trust Center Setting -> Macro Setting -> Check the box beside "Trust access to the VBA project object model"

File -> Options -> Trust Center -> Trust Center Setting -> Macro Setting -> Check the box beside "Trust access to the VBA project object model"

Close and re-open your workbook.

关闭并重新打开您的工作簿。

Those who use your macro should go through the same steps.

那些使用你的宏的人应该经历同样的步骤。

Unquote.

取消报价。

Then you can use this which I got from VBA - Execute string as command in Excel(This is not tested)

然后你可以使用我从VBA得到的这个- 在 Excel 中执行字符串作为命令（这未经测试）

Sub test()
 Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
 VBComp.Name = "NewModule"
 Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule

 Dim test As String
 test = "MsgBox " & """" & "Job Done!" & """"

 With VBCodeMod
    LineNum = .CountOfLines + 1
    .InsertLines LineNum, _
    "Sub MyNewProcedure()" & Chr(13) & test & Chr(13) & "End Sub"
 End With
 'run the new module
 Application.Run "MyNewProcedure"
 UserForm1.Show
 'Delete the created module
 ThisWorkbook.VBProject.VBComponents.Remove VBComp
End Sub

@A.S.H answer does the thing that last solution intends to implement. I am including it here for the sake of completeness. You can refer to the original answer and up-vote it.

@ASH 回答做了最后一个解决方案打算实现的事情。为了完整起见，我将它包括在这里。您可以参考原始答案并对其进行投票。

Public Sub StringExecute(s As String)
    Dim vbComp As Object
    Set vbComp = ThisWorkbook.VBProject.VBComponents.Add(1)
    vbComp.CodeModule.AddFromString "Sub foo" & vbCrLf & s & vbCrLf & "End Sub"
    Application.Run vbComp.name & ".foo"
    ThisWorkbook.VBProject.VBComponents.Remove vbComp
End Sub

Sub Testing()
    StringExecute "MsgBox" & """" & "Job Done!" & """"
End Sub