473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Class modules with OLEObject kill own instances

The goal : to have class which is able to add some OLE objects on the sheet runtime. Instances of the class must be accessible from any public/private module.

Okay, I've experimented for so long & getting quite tired (:huh: ) of it. So I think it's time to call some help. I hope there must be some solution. I NEED it.

Well actually what I need ? I need to have some class/object which create some OleObjects (images and buttons) runtime. Instances of the class must be accessible from any module as global object.

Object must becreated inside the Private Sub Workbook_Open() event or via Public Sub Auto_Open() in module. Inside the class are some function/procedures for adding CommandButtons on the sheet.

Sounds pretty easy and should be. But the reality is else (strange :-))...

Everytime I call some of the procedure which works with OLE objects (adding/deleting - I mean Add method) all instances of the class are killed. I'd REALLY like to know what's killing them ???

Without OLE procedures all seems to be working fine - the instances of the class stay alive for all the Excel/workbook session. So problem must be with OLEobjects. But I want to be able working with the OLE objects...

Now I'll provide simplified skeleton of the code of my project:

Modules :: Module1

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Explicit
  3.  
  4. Public ole_obj_img As TOle_object_image
  5.  
  6. Public Static Function New_TOle_object_image() As TOle_object_image
  7.     Set New_TOle_object_image = New TOle_object_image
  8. End Function
  9.  
  10. Public Sub Auto_Open()
  11.  
  12.     Set ole_obj_img = New_TOle_object_image
  13.  
  14.     ole_obj_img.sheet_idx = 1
  15.  
  16.     ole_obj_img.add_image                   'calling this cause destruction of the object (why ?)
  17.  
  18.     'ole_obj_img.inc_counter offset:=10  'this work fine; no OLE inside
  19.  
  20. End Sub
  21.  
  22.  
Modules :: Module2

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub alpha()
  3.  
  4.     ole_obj_img.sheet_idx = 2
  5.     'other code
  6.  
  7. End Sub
  8.  
  9. Private Sub betta()
  10.  
  11.     ole_obj_img.sheet_idx = 1
  12.     'other code
  13.  
  14. End Sub
  15.  
  16.  

Class Modules :: TOle_object_image
(Instancing = Private)

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Explicit
  3.  
  4. Private sh_idx As Integer
  5. Dim count As Integer
  6. Dim count_removed As Integer
  7. Private w As Worksheet
  8.  
  9. Private Sub Class_Initialize()
  10.     'constructor
  11.     sh_idx = 0
  12.     count = 0
  13.     count_removed = 0
  14.     MsgBox "TOle_object_image initialized"  'only for debug
  15. End Sub
  16.  
  17. Private Sub Class_Terminate()
  18.     'destructor
  19.     MsgBox "TOle_object_image terminated"  'only for debug
  20. End Sub
  21.  
  22. Property Let sheet_idx(uIdx As Integer)
  23.     sh_idx = uIdx
  24. End Property
  25.  
  26. Property Get sheet_idx() As Integer
  27.     sheet_idx = sh_idx
  28. End Property
  29.  
  30. Public Static Sub inc_counter(ByVal offset As Integer)
  31.     count = count + offset
  32. End Sub
  33.  
  34. Public Static Sub add_image()
  35.     Dim tmp_ole As OLEObject
  36.     Dim i As Integer
  37.     Dim wo As OLEObjects
  38.  
  39.     i = 0           
  40.  
  41.     Set wo = ThisWorkbook.Worksheets(sh_idx).OLEObjects
  42.  
  43.         wo.Add ClassType:="Forms.Image.1", _
  44.             DisplayAsIcon:=False, Left:=10 + 30 * i, Top:=10 + 30 * i, Width:=20, Height:=20
  45.  
  46.     Set wo = Nothing
  47.     count = count + 1
  48. End Sub
  49.  
  50.  
Workbook code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  3.        Set ole_obj_img = Nothing
  4. End Sub
  5.  
  6.  
  7. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  8.  
  9.     Application.EnableEvents = False
  10.     Application.ScreenUpdating = False
  11.  
  12.     If Sh.Index <> 1 And Sh.Name <> "system" And Sh.Name <> "runtime" Then
  13.  
  14.         ole_obj_img.inc_counter offset:=255
  15.  
  16.         'other code
  17.  
  18.     End If
  19.  
  20.     Application.ScreenUpdating = True
  21.     Application.EnableEvents = True
  22.  
  23. End Sub
  24.  
  25.  
What's killing the instances after inicialization ? If you know the solution provide some fix-code please too.

Any idea/solution what's wrong ?

TIA

Note: All test has been performed under Excel2k
Attached Files
File Type: zip ole_class_help.zip (36.4 KB, 58 views)
Aug 1 '07 #1
0 1209

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: Fred | last post by:
There is a setting in INIT.ORA that has the unintended side-effect of making sure the ALTER SYSTEM KILL SESSION command has immediate affect. Without this setting, I've seen some instances where...
15
by: Mon | last post by:
I am in the process of reorganizing my code and came across and I came across a problem, as described in the subject line of this posting. I have many classes that have instances of other classes...
42
by: WindAndWaves | last post by:
Dear All Can you tell me why you use a class module??? Thank you Nicolaas ---
9
by: MLH | last post by:
I need a fundamental explanation of Class Modules - something suitable for newbies. Access 2.0 didn't seem to focus on them very much. Now that I'm using Access 97, it seems they're everywhere. thx...
16
by: A_PK | last post by:
Hi, I am a VB.net beginner, I do not know what are the major difference between Module vs Class. Could someone guide me when is the best situation to use Module or Class. I have no idea...
8
by: John | last post by:
We are looking to converting our old vb 6.0 apps to vb.net. In vb6 you could create a standard set of forms or modules that you could share with each application. As a result, lets say you...
19
by: Jamey Shuemaker | last post by:
I'm in the process of expanding my knowledge and use of Class Modules. I've perused MSDN and this and other sites, and I'm pretty comfortable with my understanding of Class Modules with the...
2
by: mgoold2002 | last post by:
Hello. I've just begun programming in VB .NET, and I'm trying to turn all my modules into classes. In order to retrieve/exchange values from one class to another, I initiated New instances of the...
6
by: Tim Hunter | last post by:
I am using Access 2003 under WinXP. I currently support a huge Excel Wokbook that is a maintenance nightmare. There are about 15 worksheets each with lots of formulas...Anyway I am thinking of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.