VBA execute code in string

Question

I am trying to execute vba code that is inside a string WITHOUT writting the code in a temp file.

For exemple :

Dim code As String
code = "n = 0 : e_i_e = 0 : For e_i_e = 0 To 100 : n+=1 : Next"

I have tried Eval, Evaluate, Run, executeGlobal and adding a new module with

Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    VBComp.Name = "NewModule"
 Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("NewModule").CodeModule
    With VBCodeMod
        LineNum = .CountOfLines + 1
        .InsertLines LineNum, _
        "Sub MyNewProcedure()" & Chr(13) & _
        code & Chr(13) & _
        "End Sub"
    End With
    Application.Run "MyNewProcedure"

but all of these are returning errors ='(.

Thank you !

@BruceWayne OP cannot break in the generated procedure, that makes it much harder to debug than normal code. That said, OP should try to execute their code string in a normally-compiled procedure before trying to execute ad-hoc strings. The error would be trivially revealed. — Mathieu Guindon, Feb 14 '17 at 17:20

score 4 · Answer 1 · edited May 23 '17 at 10:29

You cannot break in code that's been generated after you've compiled your project, so you need to make sure you build that dynamic module with valid, compilable code.

You know before you hit that F5 button that your code is going to look like this:

Sub MyNewProcedure()
    n = 0 : e_i_e = 0 : For e_i_e = 0 To 100 : n+=1 : Next
End Sub

Why not just take that snippet and paste it somewhere and see what the VBE complains about?

Compile error: syntax error

Wow. See, this is why cramming half a dozen instructions on the same line of code is a bad idea - if it was one instruction per line you wouldn't be wondering which one is broken.

As was already mentioned, n += 1 is not VBA syntax (it's not specifically C# syntax either); incrementing a value in VBA needs to access the current value, so n = n + 1.

It's not clear where n and e_i_e are coming from. If both are locals, then your procedure accomplishes essentially nothing. If n is declared outside MyNewProcedure, then you should consider passing it as a ByRef parameter, or better, leaving it out completely and making a Function with the result of which the calling code assigns n to.

Sub MyNewProcedure(ByRef n As Long)
    Dim i As Long
    For i = 0 To 100
        n = i
    Next
End Sub

Which boils down to:

Function MyNewFunction() As Long
    MyNewFunction = 100
End Function

Which makes me wonder what the heck you're trying to accomplish.

If there is a bug in your generated code, you're going to have to debug it in a string, because the VBE's debugger won't let you break on generated code - this means it's crucially important that you generate code in a readable and maintainable way. There's currently nowhere in your code where you have the actual full generated code in a clear string - it's concatenated inline inside the InsertLines call.

Consider:

Dim code As String
code = "'Option Explicit" & vbNewLine & _
       "Public Sub MyNewProcedure()" & vbNewLine & _
       "    n = 0" & vbNewLine & _
       "    e_i_e = 0" & vbNewLine & _
       "    For e_i_e = 0 To 100" & vbNewLine & _
       "        n = n + 1 ' fixed from n += 1" & vbNewLine & _
       "    Next" & vbNewLine & _
       "End Sub" & vbNewLine
'...
'Debug.Print code
.InsertLines LineNum, code

It's much easier to get the full code back while debugging, and much easier to review and fix as well. Note that there's a limit to how many line continuations you can chain though.

score 3 · Answer 2 · answered Feb 14 '17 at 17:06

3

Your code is c# addition, it needs to be n=n+1

answered Feb 14 '17 at 17:06

Nathan_Sav

8,466
2
13
20

Amorpheuses · Accepted Answer · 2017-02-15T16:15:02.237

You can create a module and populate it with a sub from a string. In fact one of the way developers place their vba code into a repository is to do just that: extract the code from modules as strings and then read them back in from whatever version control software they're using.

Here's a full example to do what you're looking to do (assuming you want your sub in a new separate module):

Sub make_module_sub_and_run():
    Dim strMacro As String
    Dim myModule As VBComponent

    Set myModule = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
    strMacro = "Public Sub exampleSub()" & vbCrLf & _
               "  n=0" & vbCrLf & _
               "  For e_i_e = 0 to 100:" & vbCrLf & _
               "    n=n+1" & vbCrLf & _
               "  Next" & vbCrLf & _
               "  MsgBox(""Hello World. Oh and n="" & n)" & vbCrLf & _
               "End Sub"

    myModule.CodeModule.AddFromString strMacro
    Application.Run myModule.Name & ".exampleSub"

End Sub

Note that what should happen as you type "vbext_ct_StdModule" is that excel intellisense will note that this is missing and will ask whether you want to load it in - which you, of course, do.

Also note that I've deliberately prefixed the sub with the module name when running it - otherwise if you were to run it repeatedly you'll create new modules with a sub of the same name and excel wouldn't know which one to run.

VBA execute code in string

3 Answers3

Linked