Solved

VBA ole object wrapping

Posted on 2010-08-17
10
922 Views
Last Modified: 2013-11-26
Hi,

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
End Property
Property Get ActiveXControl() As OLEObject
   Set ActiveXControl = p_oleCtl
End Property
Private Sub Class_Terminate()
MsgBox "end"
End Sub
 
Regular module:
 
Dim GlobalObj As Obj
Sub CreateButton()
Call ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
End Sub

Sub Test()
Call CreateButton
Set GlobalObj = New Obj
Set GlobalObj.ActiveXControl = ActiveSheet.OLEObjects(1)
End Sub

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?

Many Thanks
0
Comment
Question by:boncqdcaveut
  • 4
  • 4
10 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33483697
By adding an activex object you effectively trigger a code reset (because they become part of the vbproject). If you really need to do this, I would suggest using an OnTime macro to assign the variable after you have created it.
0
 

Author Comment

by:boncqdcaveut
ID: 33484058
My ultimate goal is to use the MVC pattern and be able to replace Excel by HTML should I need to.
As a result my controller(s) should not be system dependent (they could be implemented with OLE buttons, but also some other types of controls).

Thus I want to avoid using VBE extensibility (kind of meta programming) as that would create new worksheet modules, be dependent of the names of the controls, be less portable etc.

Thanks to your solution, I can create controls at run-time but still use code written statically  to handle the control events.

Below a simple example using your work-around:


1) Class module called "Obj"
Private WithEvents p_oleCtl As MSForms.CommandButton
Property Set ActiveXControl(ByVal oleCtl As MSForms.CommandButton)
    Set p_oleCtl = oleCtl
End Property
Property Get ActiveXControl() As MSForms.CommandButton
   Set ActiveXControl = p_oleCtl
End Property
Private Sub Class_Terminate()
MsgBox "end"
End Sub
Private Sub p_oleCtl_Click()
MsgBox "It works"
End Sub

2) Regular module:
Dim GlobalObj As New Obj
Sub CreateButton()
Call ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
End Sub
Sub AssignLater()
Set GlobalObj.ActiveXControl = ActiveSheet.OLEObjects(1).Object
End Sub
Sub Test()
Call CreateButton
Application.OnTime Now + TimeValue("00:00:01"), "AssignLater"
End Sub

Now running Test allow me to see "It works" be displaid whenever I click the new button.

So, thank you very much rorya.
I'm just waiting to see if somebody comes up with a maybe "cleaner" solution before granting you the points.

Best,
phil

0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 500 total points
ID: 33484086
There ain't one! It's a long-standing issue with programmatically adding ActiveX controls to worksheets. But feel free to wait. :)
0
 

Author Comment

by:boncqdcaveut
ID: 33484522
All right, too bad.

Thanks again.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33484839
On that basis you'll be waiting a very long time for another answer from me.
0
 

Author Comment

by:boncqdcaveut
ID: 33485084
rorya,

There has been a misanderstanding here (I haven't used this site before).

I tryed to give you the points but I got this strange "Close Request Pending" stuff.
Mybe because I gave "only" a grade B.

This grade is for the "Overall experience" and has nothing to do with you (and it isn't aim at complaining about this great website neither! it just that I didn't enjoy the experience 100% as I had to wait - probably as I didn't choose the best zones -)

I read your profile and really admire your skills and the fact you're helping other people for free.

Sincerely sorry if I offended you in any way.

Best regards,
Phil

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33488967
Not to worry - I was having a bad day, and hadn't noticed you weren't necessarily too familiar with the site.
For the record, the grade you give directly affects the number of points awarded to the answerer; it has no other effect at all that I am aware of. :)
0
 

Author Comment

by:boncqdcaveut
ID: 33491024
Hi,

I'd like to accept a solution and obviously "closing a question" does not mean that.

Please allow me to cancel my request to close this question.

Thanking you in advance,
Phil
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
location range 4 22
Cascading dropdown 9 26
VB 6 error 5 in windows 10 but not in XP 7 40
Excel for Mac - How make those Tabs larger? 2 31
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now