百分请高手:触发WithEvents事件的奇怪问题

[liyangqiu]

问题描述:
1.使用VB建立一个与Excel的外接程序工程
2.使用同一个Excel.Application,每建立一个Workbook,同时定义各自独立的WithEvents响应事件
3.在一个表单窗口中使用按钮事件建立新的Workbook,Excel工作薄能建立,但是所有的事件均无响应
4.在AddinInstance_OnConnection事件里运行建立,一切都正常
请高手分析以下代码,指出不足及给个定论,谢谢.

==================================================
Connect.Dsr

Option Explicit
Private CCMenuAndBar As MenuAndBar
Private WithEvents xExcel As Excel.Application
Private xBook As ExcelWorkbook

'------------------------------------------------------
'增加菜单
'------------------------------------------------------
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
On Error GoTo Error_Handler
Set xExcel = Application

Set DefGlobal.UsingApp = xExcel
DefGlobal.Initialize

Set xBook = New ExcelWorkbook
xBook.Initialize '调用此处能产生一切正常的响应事件.

Set CCMenuAndBar = New MenuAndBar
CCMenuAndBar.Initialize
Exit Sub

Error_Handler:

MsgBox Err.Description

End Sub

'------------------------------------------------------
'删除菜单
'------------------------------------------------------
Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
On Error Resume Next
CCMenuAndBar.Dispose
End Sub

======================================================

Form1.frm

Private Sub Command1_Click()
Dim ThisWorkbook As ExcelWorkbook
Set ThisWorkbook = New ExcelWorkbook
ThisWorkbook.Initialize '调用此处能则没有一个新的响应事件
End Sub

=========================================================================
ExcelWorkbook.Cls

Option Explicit
Public WithEvents ThisBook As Workbook

Public Sub Initialize()
On Error Resume Next
Set ThisBook = New Workbook
Set ThisBook = DefGlobal.UsingApp.Workbooks.Open("C:\HowToUse.xls")
End Sub

Private Sub ThisBook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
MsgBox Sh.Cells(1, 1).Value
End Sub

===========================
DefGlobal.bas

Public UsingApp As Excel.Application

[liyangqiu]

还没人来解答,真是晕啊,MSDN所有资料我都查过了,还是没解决.要源码请发E-Mail:[e-mail],共同探讨一下,VB开发是否已经不适用了(本人现在做一个项目,已经用C#开发完整的框架,最后决定使用VB).

[iland9876543210]

顶一下

[ljk02079]

来学习的

[VBProfessional]

我模拟一下你的环境,不知是否有错：
1、创建 外接程序，创建MyAddIn
2、引用Excel类类型库
3、依次建立Connect.Dsr,ExcelWorkbook,DefGlobal,复制你的代码，运行
4、另开VB， 新建一 EXE工程，添加Command1
5、引用‘Sample AddIn Project’
6、在Command1_Click()下复制你的代码
7、在ExcelWorkbook类代码处做中断
8、运行form1点按键，我看到了ExcelWorkbook中Initialize方法的运行中断

我不知你所说的事件（实为方法）不能调用，是如何产生的，是不是没有引用，或者类不为MultiUse

[liyangqiu]

谢谢VBProfessional() ,可能我的问题不好读懂,现在问题已经解决,是"生命周期"问题,在以下加一全局变量则可
DefGlobal.bas
Public UsingApp As Excel.Application
Public UsingWB As ExcelWorkbook

在调用处
Form1.frm
Private Sub Command1_Click()
Dim ThisWorkbook As Workbook
Set ThisWorkbook = DefGlobal.UsingWB.ThisBook
ThisWorkbook.Initialize
End Sub

[南山明月]

恭喜