All docs i've read on MSScriptControl say that it can respond to events of objects added to it.
the script control allows you to write script that will automatically fire when an event on an object occurs. https://msdn.microsoft.com/en-us/library/ms974586.aspx
the ScriptControl will be able to sink events generated by objects added using the AddObject method. http://flylib.com/books/en/1.492.1.154/1/
But i've not had success doing so. I assume this means code inside the ScriptControl will fire when the added object raises it's event. I'm unable to get any to work.
Seeking example code of adding any object to the ScriptControl, and handling events fired by that control. Don't care if the object is a custom class, a form, a control, or a built-in Excel object such as Worksheet.
Running Office 2010 32bit on Win Server 2008 64bit.
Open to alternate methods, such as WSH, but Tushar Mehta did not have success with that here http://dailydoseofexcel.com/archives/2009/08/19/using-vbscript-to-monitor-office-eventsor-not/
I've successfully added the Excel Application object to the ScriptControl, and executed code on the Excel Application object:
This works no problem:
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
Fails:
In this test, m_oScriptCtl is a module-scoped variable. Nothing happens when i click the form:
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
This next one reports the following error on .AddCode:
Expected ')'
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
In the next test, MyClass is defined as:
Public Event MyEvent()
Public Sub TestEvent()
RaiseEvent MyEvent
End Sub
But the following reports "object does not support property or method" on .Run. So in this case, it's not the event that fails-- i just can't run a method inside the class.
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
Clues:
Someone posted:
If you totally fail to sink your events, try calling 'ScriptControl1.Modules("Global").CodeObject.Name_Of_Your_Event(ParameterList)' http://computer-programming-forum.com/59-vbscript/4b059f9f6eacfaf0.htm
-- but that workaround is unclear to me: Event procedures are not supposed to be "called" explicitly, they're supposed to just fire. The following lines both give "Method or data member not found", in the above TestClassEvent example:
m_oScriptCtl.Modules("Global").CodeObject.MyEvent
m_oScriptCtl.Modules("Global").CodeObject.TestEvent
I've not tested the following, because i'm not quite sure how:
the script control can't handle events from a class in the same project as the application it's being hosted in https://diigo.com/08we68
Not sure if the following is relevant, don't quite understand it: http://www.programmersheaven.com/discussion/79452/me-activecontrol-and-events
The key to making it work is: You must set the event-firing-object in the listener-class after adding both to the Script Control-- not before. Meaning, this line must be executed inside the SC:
Set oListener.EventFiringObject = oEventFiringObject
Here's a working example of firing, and responding to, events between objects inside a Script Control.
In this example:
To setup the demo
Code
Class 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
Class clsEventClass:
Public Event MyEvent(sCaller As String)
Public Sub Raise_MyEvent(sCaller As String)
RaiseEvent MyEvent(sCaller)
End Sub
Class 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
Testing
Step through Main(). You'll see when Raise_MyEvent fires MyEvent in clsEventClass, clsClassListener responds to the event with a message box.
Now switch to Excel front-end, and enter a value in a cell in Sheet2. You'll see clsSheetListener respond to the Change event with a message box.