如何在微软脚本控件中实现事件

Question

我在MSScriptControl上读到的所有文档都说它可以响应添加到它的对象的事件。

脚本控件允许您编写自动执行的脚本当对象上的事件发生时触发。https://msdn.microsoft.com/en-us/library/ms974586.aspx

ScriptControl将能够接收对象生成的事件使用AddObject方法添加。http://flylib.com/books/en/1.492.1.154/1/

但是我没有成功过。我认为这意味着当添加的对象引发它的事件时，ScriptControl内部的代码将被触发。我没办法让他们工作。

寻找向ScriptControl添加任意对象的示例代码，并处理由该控件触发的事件。不要关心对象是自定义类、窗体、控件还是内置的Excel对象(如Worksheet)。

在windows Server 2008 64位上运行Office 2010 32位。

开放替代方法，如WSH，但Tushar Mehta在这里没有成功http://dailydoseofexcel.com/archives/2009/08/19/using-vbscript-to-monitor-office-eventsor-not/

我已经成功地将Excel应用程序对象添加到ScriptControl中，并在Excel应用程序对象上执行代码:

Function TestProc()
          Dim oScriptCtl As New MSScriptControl.ScriptControl
          With oScriptCtl
                    ' init
                    .Language = "VBScript"
                    .AllowUI = True
                    ' add Excel application object
                    .AddObject "app", Application, True
                    ' add procedure
                    Dim sCode As String
                    sCode = "Sub TestProc : MsgBox ""hi"" : End Sub"
                    .AddCode sCode
                    ' run procedure. Msgbox displays. 
                    .Run "TestProc"
          End With
          ' cleanup
          Set oScriptCtl = Nothing
End Function

失败:

在这个测试中，m_oScriptCtl是一个模块作用域的变量。当我点击表单

时什么也没发生

Function TestForm()
          Set m_oScriptCtl = New MSScriptControl.ScriptControl
          With m_oScriptCtl
                    ' init
                    .Language = "VBScript"
                    .AllowUI = True
                    MyForm.Show False
                    .AddObject "app", Application, True
                    .AddObject "frm", MyForm, True
                    .State = Connected
                    Dim sCode As String
                    sCode = "Sub frm_Click():   MsgBox Chr(14):   End Sub"
                    .AddCode sCode
          End With
End Function

下一个报告了.AddCode上的以下错误:

预期)的

Function TestSheet()
          Set m_oScriptCtl = New MSScriptControl.ScriptControl
          With m_oScriptCtl
                    ' init
                    .Language = "VBScript"
                    .AllowUI = True
                    .AddObject "app", Application, True
                    .AddObject "sheet", Sheet2, True
                    .State = Connected
                    Dim sCode As String
                    sCode = "Private Sub sheet_Change(ByVal Target As Range): MsgBox Target: End Sub"
                    .AddCode sCode
          End With
End Function

在下一个测试中，MyClass被定义为:

Public Event MyEvent()
Public Sub TestEvent()
          RaiseEvent MyEvent
End Sub

但是下面的报告"对象不支持属性或方法"。run。所以在这种情况下，不是事件失败——我只是不能在类中运行方法。

Function TestClassEvent()
          Set oScriptCtl = New MSScriptControl.ScriptControl
          Dim oClass As New MyClass
          With oScriptCtl
                    ' init
                    .Language = "VBScript"
                    .AllowUI = True
                    ' add objects
                    .AddObject "app", Application, True
                    .AddObject "oClass", oClass, True
                    .State = Connected
                    ' add code
                    Dim sCode As String
                    sCode = "Sub oClass_MyEvent() : MsgBox vbNullString : End Sub"
                    .AddCode sCode
                    .Run "oClass.TestEvent"
          End With
          ' cleanup
          Set oScriptCtl = Nothing
End Function

线索:

有人发帖:

如果您完全无法接收事件，请尝试调用"ScriptControl1.Modules("全球").CodeObject.Name_Of_Your_Event (ParameterList) 'http://computer-programming-forum.com/59-vbscript/4b059f9f6eacfaf0.htm

——但我不清楚这个解决方法:事件过程不应该被显式地"调用"，它们应该只是触发。在上面的TestClassEvent示例中，以下行都给出了"未找到方法或数据成员":

m_oScriptCtl.Modules("Global").CodeObject.MyEvent
m_oScriptCtl.Modules("Global").CodeObject.TestEvent

我没有测试以下内容，因为我不太确定如何测试:

脚本控件不能处理同一类中的事件项目作为它所在的应用程序https://diigo.com/08we68

不确定以下内容是否相关，我不太明白:http://www.programmersheaven.com/discussion/79452/me-activecontrol-and-events

Answer 1

使其工作的关键是 :您必须在将它们添加到脚本控件之后(而不是之前)在侦听器类中设置事件触发对象。也就是说，这一行必须在SC内执行:

Set oListener.EventFiringObject = oEventFiringObject

这是一个在脚本控件中触发和响应对象之间事件的工作示例。

在这个例子中:

我演示了两种事件触发对象:一个自定义类和一个工作表。
• 自定义类在添加到scriptcontrol ("sc")之前被实例化。
我调用自定义sc对象中的方法。

设置演示

• 启动一个新项目(即，在Excel中添加一个新的工作簿)。
• 在您的VB IDE中，添加一个引用Microsoft Script Control.
创建以下VB组件:

clsSheetListener类:

Public WithEvents oSht As Worksheet
Private Sub oSht_Change(ByVal Target As Range)
  ' show  changed cell
  MsgBox "Sheet Listener" & vbCrLf & "Changed: " & Target.Address _
          & vbCrLf & Target.Cells(1).Value2
End Sub

clsEventClass类:

Public Event MyEvent(sCaller As String)
Public Sub Raise_MyEvent(sCaller As String)
  RaiseEvent MyEvent(sCaller)
End Sub

clsClassListener:

Public WithEvents m_oEventClass As clsEventClass
Private Sub m_oEventClass_MyEvent(sCaller As String)
  ' show my execution-scope
  MsgBox "Class Listener, " & sCaller & " caller"
End Sub

Module Module1:

Function Main()
  ' init scriptcontrol
  Set m_oScriptCtl = Nothing
  Set m_oScriptCtl = New MSScriptControl.ScriptControl
  With m_oScriptCtl
    .Language = "VBScript"
    .AllowUI = True
    ' add Excel application object, needed for all Excel methods in script-control
    .AddObject "sc_Application", Application, True

    ' add Sheet2 to the sc
    ' code executed in sc refers to objects by name, as defined in .AddObject
    .AddObject "sc_oSheet", Sheet2, True
    ' init sheet event-listener, and add to sc
    Dim oSheetListener As New clsSheetistener
    .AddObject "sc_oSheetListener", oSheetListener, True
    ' register the sheet-object with its listener in the scriptcontrol
    ' so the listener can hear the sheet's events
    .ExecuteStatement "Set sc_oSheetListener.oSht = sc_oSheet"

    ' init custom event-firing class object, and add to sc
    Dim oEventClass As New clsEventClass
    .AddObject "sc_oEventClass", oEventClass, True
    ' init class-event listener, and add to sc
    Dim oClassListener As New clsClassListener
    .AddObject "sc_oClassListener", oClassListener, True
    ' register event-firing object with its listener inside the Script Control
    ' so the listener can hear the object's events
    .ExecuteStatement "Set sc_oClassListener.m_oEventClass = sc_oEventClass"

    ' cause event to be raised. 
    ' Call from local context, then sc-context.
    ' it's the same object instance in both cases
    oEventClass.Raise_MyEvent "Local"
    .ExecuteStatement "sc_oEventClass.Raise_MyEvent ""SC"""
  End With
End Function

测试

步骤通过Main()。您将看到，当Raise_MyEvent在clsEventClass中触发MyEvent时，clsClassListener用一个消息框响应该事件。

现在切换到Excel前端，并在Sheet2中的单元格中输入值。您将看到clsSheetListener用一个消息框响应Change事件。