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!

Epitome

You'll probably need to write code that, itself, writes code to execute the command. You'll need to enable trusting the VBA project manually on any excel instance where you want this to occur. – Surculose

Can you please clarify what you're trying to do? Do you want to be able to run the VBA code directly from an Excel cell? – Dichromaticism

@Dichromaticism I want to run the VBA code directly from test variable. – Epitome

First, check how MsgBox works. It needs an argument in parenthesis. – Whittemore

@Masoud the 'MsgBox' works as I wrote in the post above MsgBox "Job Done!" and prints "Job Done!". But the thing is I can't change the test variable – Epitome

@Epitome can you get "Job Done!" extracted from the string. I know this is a pseudo-code, I am asking about the actual string that you have. Can you extract the arguments out of it and pass them to the functions. – Whittemore

@Masoud Can you give me examples of functions? – Epitome

@Epitome look at the my edits below. That's not a standard way for me to code, but since you have to deal with test as is, that may help. – Whittemore

That could be done with ScriptControl – Betseybetsy

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

Clausen

@ASH I have Compile Error: User-defined type not defined – Epitome

@Epitome Add reference to Microsoft Visual Basic For Applications Extensibility x.y – Clausen

@Epitome Or you can use late binding. I modified the code to use late binding, no need to add any references. – Clausen

@Clausen That's nice. I was trying to do something like this. – Whittemore

What if i'm expecting a return value from it. How to edit the above code ? – Aeonian

@Aeonian Haven't tried it, but it should work if you change the Sub to be a Function. – State

I was trying this. But there was error message about Trust Center Setting. I changed the excel settings and its working fine. Thanks. .. Excel 2016 -> File -> Options -> Trust Center -> Trust Center Setting -> Macro Setting -> Check the box beside "Trust access to the VBA project object model" – Amy

I think self-writing code is an over-engineered and risky solution. – Unseal

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

Whittemore

I know this, but in my situation, I can't change the variable, so it remain like test = "MsgBox" & """" & "Job Done!" & """" – Epitome

Isn't there an "eval" function, like in many other languages? – Llywellyn

@IesusSonesson eval is not an excel function (Access does have it). Excel VBA has Application.Evaluate which does not the job. That's unfortunate, but that's the way it is. Actually the reason that I started answering this question was the javascript tag. Java has eval and you know the rest. – Whittemore

@Masoud Unfortunately no, when you run your second subroute, you just skip the first 7 characters, you don't run the MsgBox as a command. – Epitome

I appreciate the detailed answer. Importantly I agree that this kind of "games" is not usually recommended unless "really really" it is needed. I posted an answer only because I find it somehow "fun" and shows some of the reflection power of VBA. – Clausen

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.

Veriee

This looks really interesting, but shouldn't your code assign the output of the functions to a variable? Also, even when I import the code for stdCallback, stdICallable and stdLambda and assign the outputs of the functions to a variable and, I still wasn't able to run the code without getting a few errors. Should I create an issue on GitHub for that? – Overstate

@Overstate I actually just tested it, and found a bug with super global declaration like above, thanks for the report 😊 I've modified the source code on github to fix this issue. If you download the latest version and run the above code it should all work fine. Happy for these to be raised as github issues in the future! 😊 – Veriee

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!

Benignant

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Amalgamate