Your example is grossly inadequate to demonstrate the issue.
Given that your original post was little more than a rant and rave, I
chose a simple example to illustrate that what you wrote was not
necessarily the case.
Try something more like this: (warning "air code" caveats apply)
If you're trying to make a point, don't use "air code" and assert
caveats. Write real, compilable code that works and substantiates your
assertions.
Assume you have a UserForm named UserForm1 that contains the following
code:
Public Prop1 As String
Public Prop2 As String
Private Sub btnClose_Click()
Me.Hide
End Sub
Then, in Module1, use
Public UForm As UserForm1
Public ModValue As String
Sub AAA()
Dim WS As Worksheet
Dim TBX As OLEObject
Set UForm = New UserForm1
Set WS = ActiveSheet
UForm.Prop1 = "hello"
UForm.Prop2 = "world"
ModValue = "abcd"
Debug.Print "Before Show:", UForm.Prop1, UForm.Prop2, ModValue
UForm.Show
Debug.Print "After Show:", UForm.Prop1, UForm.Prop2, ModValue
Debug.Print "Creating TextBox..."
Set TBX = WS.OLEObjects.Add("Forms.TextBox.1")
TBX.Object.Text = "Text Box"
Debug.Print "After TextBox", UForm.Prop1, UForm.Prop2, ModValue
End Sub
And in Module2 use
Sub BBB()
Debug.Print "BBB: ", UForm.Prop1, UForm.Prop2, ModValue
End Sub
There are two Public variables, one a simple string and the other an
reference to a UserForm. The code sets the string and instantiates
UserForm1 into UForm. UForm's public variables are set and the form is
shown modally. When the form is then hidden by btnClose, the variables
are printed, and this shows that they retained their values. Then, a
Forms.TextBox.1 ActiveX is created and the Public variables are
printed again and again they retained their values. Once AAA ends, you
can run BBB and see that the public variables retained their values
after the form is shown, after the ActiveX is created, and after the
first sub ends.
If you close the UserForm by clicking the "X" in the upper right
corner of the form, you are unloading the form, so the form's
properties revert to strings, and you'll get an error 91 in BBB.
If you have some real, compilable, code that shows different behavior,
by all means post it. But the bottom line is that, contrary to your
original post, creating an ActiveX control doesn't (necessarily) dump
global variables.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Sat, 14 Mar 2009 09:40:01 -0700, Mark Burns
wrote:
Chip,
Your example is grossly inadequate to demonstrate the issue.
Try something more like this: (warning "air code" caveats apply)
Public myObject as myLoginUserForm
Public sub Workbook_open() 'to make it clear _when_ we're initializing a
global/public variable value here
set myObject = NEW MyLoginUserForm
myObject.show
'a bunch more stuff happens, but we never "unload myobject" we only
"myobject.hide" so that the myobject.securityid and myobject.userid values
are always available as the applicaton runs. got it? (ps. and do NOT tell me
to just "put these things into a hidden worksheet" mkay?)
end sub
'later on we have code like this...
Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet
Debug.Print "befo " & CStr(myobject.uerid)
Set WS = ActiveSheet
Set OleObj = WS.OLEObjects.Add("Forms.TextBox.1")
OleObj.Object.Text = "Pub: " & CStr(Pub)
MsgBox "after: " & CStr(myobject.userid)
End Sub
"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?