How to run a string as a command in VBA

Question

I have this simple VBA code below and I don't know why it does not work.

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

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

The task is completed!

Answer 1

You may be tempted to add your own "Executer" line:

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

Answer 2

The short answer is: you cannot do this (you should not do this), but ... read the following to find out why and see the work!

, . , , , . , . , , . , :

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

, macro, Sub ( ).

(.. test), . - , . - ;

Sub Run()

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

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

 MsgBox (extest)

End Sub

, SO, . , .

P.S. :

VBA -

Excel VBA -

VB. ExcelForum Visual Basic - Excel

:

​​,   .

.

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

( Excel Macro-Enabled Workbook),   .

:

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

.

, , .

Unquote.

, 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 , . . .

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