How to run a string as a command in VBA

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

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:

Job Done!

回答1:

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

回答2:

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 macro and then runs within the same Sub (extension needs to be taken care of).

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;

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.

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

Access VBA - Evaluate function with string arguments

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

ANOTHER SOLUTION

This needs to trust the VB project. Quoting from ExcelForum and referencing to Programmatic Access To Visual Basic Project Is Not Trusted - 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.

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

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"

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)

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.

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