How to run a string as a command in VBA

Question

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!

Answer 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

Answer 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

Answer 3

If you need a solution which doesn't require special permissions, and has significantly more power than hard coding code, I maintain a library, stdLambda from stdVBA, for this kind of thing:

'Ensure module name is "mainModule"
Public Sub Main()
   Call stdLambda.bindGlobal("msgbox", stdCallback.CreateFromModule("mainModule","msgbox"))
   x = stdLambda.Create("msgbox(""hello world"")").Run()
End Sub
Public Function msgbox(ByVal sMessage as string) as long
   msgbox = VBA.msgbox(sMessage)
End Function

stdLambda syntax is vba-like but is a fully embedded programming language in its own right. See the documentation for more details.

Answer 4

I was up against a similar variation: the macro name was in a 'control specification' worksheet table. A custom ribbon was added with an 'onAction' parameter in the ribbon XML. The 'tag' Name was returned in the Ribbon call back macro that was then used to lookup the macro Name to run based on the XML tag name. Makes sense so far!!!! I already had the handling subs in an existing Code Module=[m0_RibbonCallBack]. In the [m0_RibbonCallBack] module I wanted to run the sub name=[mResetToCellA2] when I clicked the ribbon button with tagName=[Reset2CellA2]. In the 'control specification' worksheet table I did a vLookUp() on the tagname=[Reset2CellA2] and returned the string value from column 3 (onAction) ="mResetToCellA2". Now I need to run the macro string (macroName) name on the VBA side!!! I wound up solving the challenge with this simple line: Application.Run "m0_RibbonCallBack." & macroName
Cheers!