如何在动态添加函数后“同步”VB项目？

Question

我正在编写一个Excel VBA项目，该项目在代码执行期间创建新函数。

新添加的函数需要能够立即执行。

虽然我确实看到添加了新函数，但我在第一次执行时收到一个"1004“运行时错误。

下面的代码将一个新函数testMethod添加到现有的DynamicFunctions模块中(如果该函数尚未存在)并执行它。

摘自Dynamic Code Generate & Execute@VBA和Programming The VBA Editor的代码片段

错误消息：“运行时错误'1004'：无法运行宏'testMethod‘。该宏可能在此工作簿中不可用，或者所有宏都可能被禁用”

(注意在第二次执行时，函数已经在模块中，所以代码只是执行它并弹出消息框)

Dim code As String
code = "Public Function testMethod()" & vbNewLine & _
            vbTab & "MsgBox """ & Time & """" & vbNewLine & _
            "End Function"

Dim methodExist As Boolean
methodExist = checkProcName("testMethod")

If (methodExist = False) Then
    Dim VBComp As VBIDE.VBComponent
    Set VBComp = ThisWorkbook.VBProject.VBComponents("DynamicFunctions")
    Call VBComp.CodeModule.AddFromString(code)
End If

Application.Run "testMethod"



Function checkProcName(sProcName As String) As Boolean
' ===========================================================================
' Found on http://www.cpearson.com at http://www.cpearson.com/excel/vbe.aspx
' then modified
'
' USAGE:
' to check if a procedure exists, call 'checkProcName' passing
' in the target workbook (which should be open), the Module,
' and the procedure name
'
' ===========================================================================
Dim oVBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind

checkProcName = False

Set VBProj = ThisWorkbook.VBProject
Set VBComp = VBProj.VBComponents("DynamicFunctions")
Set CodeMod = VBComp.CodeModule

With CodeMod
    LineNum = .CountOfDeclarationLines + 1
    Do Until LineNum >= .CountOfLines
        ProcName = .ProcOfLine(LineNum, ProcKind)
        If ProcName = sProcName Then
            checkProcName = True
            Exit Do
        End If
        Debug.Print ProcName
        LineNum = .ProcStartLine(ProcName, ProcKind) + .ProcCountLines(ProcName, ProcKind) + 1
    Loop
End With


End Function

Answer 1

除非有特定的原因，你需要在你的项目中有一个DynamicFunctions模块，你总是可以动态创建一个，运行代码并在完成后删除。

Public Sub CreateModuleRunMethodAndDelete()
    Dim code As String
        code = "Public Function testMethod()" & vbNewLine & _
                vbTab & "MsgBox """ & Time & """" & vbNewLine & _
                "End Function"

    'Create and append code
    With ThisWorkbook.VBProject
        With .VBComponents.Add(vbext_ct_StdModule)
            .Name = "Temp"
            .CodeModule.AddFromString code
        End With
    End With

    'Run
    Application.Run "testMethod"

    'Delete module
    With ThisWorkbook.VBProject
        .VBComponents.Remove .VBComponents("Temp")
     End With
End Sub

编辑：

如果不存在，则创建过程并运行。

Public Sub RunMethod()
    Dim code As String
        code = "Public Function testMethod()" & vbNewLine & _
                vbTab & "MsgBox """ & Time & """" & vbNewLine & _
                "End Function"

    If Not checkProcName("testMethod") Then
        ThisWorkbook.VBProject.VBComponents("DynamicFunctions").CodeModule.AddFromString code
    End If

    Application.Run "testMethod"

End Sub