Hello Chip,

I think the correct order is

Pub = 12345
Debug.Print "befo " & CStr(Pub)


"Chip Pearson" wrote:

What you describe is not the standard and ubiquitous behavior. For
example, the following code sets a public variable, then inserts an
ActiveX text box on the active worksheet and then displays the value
of the public variable. This shows that the contents of the variable
are preserved.


Dim Pub As Long
Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet
Debug.Print "befo " & CStr(Pub)
Pub = 12345
Set WS = ActiveSheet
Set OleObj = WS.OLEObjects.Add("Forms.TextBox.1")
OleObj.Object.Text = "Pub: " & CStr(Pub)
MsgBox "after: " & CStr(Pub)
End Sub


As far as no warning goes, you may have the "Notify Before State Loss"
setting turned off. Also, you should have Error Trapping set to "Break
In Class Module".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 13 Mar 2009 14:14:04 -0700, Mark Burns
wrote:

Let me see if I understand this properly.

If you either add or delete an ActiveX control to a worksheet dynamically
via VBA code, you trigger an involuntary and automatic RECOMPILE of the VBA
environment, which instantly wipes out *ALL* the global variables stored in
the heap? (Oh, and WITHOUT TRIGGERING ANY WARNINGS, even for a WATCH set to
break on "any changes to the value"?!!!)

...Which would also explain why you get the "Cannot enter break mode at this
time" error rather abruptly if you hit f8 in the debugger on the line that
does something ilke this:
sheet1.oleobjects.add(msoPickAShapeConstantHere , top, left, height, width)
or this:
sheet1.oleobjects("MyShapeNameHere").delete

I have just ONE little question.
WHY THE HECK IS THIS NOT FEATURED *VERY PROMINENTLY* IN THE DOCUMENTATION
FOR THE OLEOBJECTS METHODS!!!???

This is a *SERIOUSLY limiting behavior* for anyone wanting to actually use
the capabilty to dynamically create/delete OLEObjects/ActiveX controls at
runtime!
...well, at least from a VBA application (I suppose that this wouldn't
matter too much for VSTO developers, since they don't usually have to care
about the VBA environment's heap objects very much.)

If I have this wrong in any way, could somebody please tell me?