VBA执行字符串中的代码 [英] VBA execute code in string

问题描述

我正在尝试执行字符串内的vba代码，而无需将代码写入临时文件中.

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

例如:

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

我尝试使用Eval，Evaluate，Run，executeGlobal并使用添加新模块

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 ='(.

谢谢！

推荐答案

您可以创建一个模块，并使用字符串中的子元素填充该模块.实际上，开发人员将其vba代码放入存储库的一种方式是这样做:从模块中将代码提取为字符串，然后从使用的任何版本控制软件中将其读回.

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

请注意，在键入"vbext_ct_StdModule"时应该发生的情况是excel intellisense将注意到该内容丢失，并会询问您是否要加载它-当然可以.

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.

还请注意，我在运行该子模块时故意为该子模块添加前缀-否则，如果要重复运行该子模块，则会创建一个具有相同名称的子模块的新模块，而excel不会知道哪个模块运行.

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执行字符串中的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！