VBA ole object wrapping
Posted on 2010-08-17
I'm trying to wrap a OLE object into a class (such as I can add my own events etc.)
Unfortunately, it seems to only work if the OLE Object is manually created.
If it is created in my class or even in the macro that use the object, it goes out of scope at the end of the macro and induces the termination of my wrapping object, even if my object is a global variable.
The following shows this point:
class module called Obj:
Private p_oleCtl As OLEObject
Property Set ActiveXControl(ByVal oleCtl As OLEObject)
Set p_oleCtl = oleCtl
Property Get ActiveXControl() As OLEObject
Set ActiveXControl = p_oleCtl
Private Sub Class_Terminate()
Dim GlobalObj As Obj
Set GlobalObj = New Obj
Set GlobalObj.ActiveXControl = ActiveSheet.OLEObjects(1)
when Test is run, the msgbox "end" is displaid.
It is not displaid if I run CreateButton first and then Test (without the call to CreateButton).
Strangely enough, The same issue occurs even if the wrapped OLE object is a global variable.
Any clue as to why this is like that and how to work around it?