将代码添加到新的Excel工作簿中。

Question

我正在尝试更改我的外接程序，以便当您打开一个新的工作簿时，它将打开一个新工作表，然后在新工作表中创建一个新的事件过程。

我已经到了可以使用宏按钮组合"Sheets.Add“和".CreateEventProc”的地步，但是当尝试使用外接程序的工作簿来自动化过程时，问题就出现了。首先加载外接程序，因此"Set VBProj = ActiveWorkbook.VBProject“无法找到新的活动工作簿。

有可能这样做吗？如果是这样的话，是否需要在周围工作，还是我只是遗漏了一些显而易见的东西？

我现在拥有的是：

Option Explicit
Private WithEvents App As Excel.Application

Private Sub Workbook_Open()
    Set App = Excel.Application
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)

    Range("T2").Value = 100
    ActiveWorkbook.Sheets.Add
    Call CreateEventProcedure

End Sub

Public Sub CreateEventProcedure()

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim NumLines As Long
Dim LineNum As Long
Dim ProcName As String
Dim ProcKind As VBIDE.vbext_ProcKind
Const DQUOTE = """" ' one " character

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet2")
Set CodeMod = VBComp.CodeModule
With CodeMod
    LineNum = .CountOfDeclarationLines + 1
    Do Until LineNum >= .CountOfLines Or ProcName = "Worksheet_Change"
        ProcName = .ProcOfLine(LineNum, ProcKind)
        LineNum = .ProcStartLine(ProcName, ProcKind) + _
                .ProcCountLines(ProcName, ProcKind) + 1
    Loop

    If ProcName = "Worksheet_Change" 
        GoTo Exi
    End If

    'Now, create a Change Event on Sheet2
    LineNum = .CreateEventProc("Change", "Worksheet")

Answer 1

对于那些关心此事的人，我让这个过程处理一个小的复杂问题。我所做的是：

Option Explicit
Private WithEvents ExApp As Excel.Application

Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook)
Dim StrPrompt As String
Dim strTitle As String
Dim iRet As Integer
StrPrompt = "Want to create event?"
strTitle = "Event?"
On Error GoTo 0
iRet = MsgBox(StrPrompt, vbYesNo, strTitle)
If iRet = vbYes Then
    ActiveWorkbook.Sheets.Add
    Call CreateEventProcedure
End If
    On Error GoTo 0
        ActiveWorkbook.Sheets.Add
        Call CreateEventProcedure
    End If

End Sub

Private Sub Workbook_Open()
    Set ExApp = Excel.Application
End Sub  

我创建了Yes/No提示符，以绕开第一个‘运行时_Global’错误，然后excel工作簿实际加载，提示再次问我，然后单击yes，一切都正常。此外，在excel中打开工作簿时只需要一个提示符。