Dynamically adding ActiveX controls via VBA kills global VBA heap?

  • Thread starter Thread starter Mark Burns
  • Start date Start date
M

Mark Burns

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?
 
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 "before: " & 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)
 
I don't think so.

Chip is showing that that since Pub is declared outside any Sub/function, it
retains its value between calls to the subroutine.

It may be easier to see what happens with this simplified code:

Option Explicit
Dim Pub As Long
Sub AAA()
Debug.Print "before: " & CStr(Pub)
Pub = Pub + 1
Debug.Print "after: " & CStr(Pub) & vbLf & "-----------"
End Sub


If I run it a few times, I get this in the immediate window:

before: 0
after: 1
 
Hello Dave,

First let me say that Chip is one of the Excel GODs as far as I am concerned.

I thought he wanted to show that value assinged IN the Sub does not change...
 
"Retains its value between calls" was the phrase I used. I thought by adding 1
to it each time the routine is run would be a good way to show that the value is
static. I guess it wasn't.

But if you put the assignment before the first use, you can't tell if its value
is what it is because of the assignment or because it retained its value.

The first time through Chip's routine Pub's value would be 0. Any future run
would show 12345 -- until the variable was reset (for some reason).
 
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 "before: " & 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
 
The ordering within the original call is correct, as intended. The
first Debug.Print statement is there to show that the Pub variable
retains its value between calls to procedure AAA. Then Pub is set to
12345 (a value that it already has except the very first time). The
MsgBox is there to show that Pub kept its value after the ActiveX
control was created.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
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)
Click to expand...

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)
 
Sorry - I got interrupted before finishing that last post...

At the (after) line, I (would) see the MyLoginUserForm_Initialize() execute
because the form object variable was suddenly = Nothing, and the first
reference to a form object variable's properties/members will re-load the
UserForm - from scratch.

(ps, I'm a well-seasoned Access developer, so I do know about manipulating
and using public variables, classes, object instances & such in a VBA
environment.)
 
Chip,

First, thank You for taking the time to respond. I sincerely appreciate your
insights.

Yes, my original post was a rant, as i had just wasted many hours figuring
out what the heck was happening to my public variable values. (and I was
rather shocked and ticked about it once I read the explanation you will see
below). I traced the disapperance of the values down to the line where I
inserted a new checkbox into a form.
Then I went looking for an explanation, because I figured that I couldn't
possibly have been the first person to notice this happening.
I found this:
http://support.microsoft.com/kb/231089
then I found this:
http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_23574623.html
(note the answer down at the bottom which I'll quote in case the link
doesn't show it to you - weird site that way)

Answer: "rorya:When you add OLEobjects to a worksheet, the OLEobject becomes
a property of the worksheet (which is why you can use Me.Combobox1 etc in a
worksheet code module) so the project recompiles. This resets your global
variables. If possible use Forms objects instead."

After reading that is when I got annoyed enough to rant here.
So, sorry about the tone of my initial message, but I did try and do my
homework first.

You captured the spirit of what i'm doing here.
The only other factors that might be relevant to this discussion is for me
to add that the vba code is running from a tempate file, (which shouldn't
make any difference here, should it? ...and if it does, then my rant still
applies), and the control i'm trying to add dynamically is a checkbox that is
NOT bound to a cell reference.

I will try and boil down the smallest possible example so that I can
reliable reproduce the behavior I am seeing with the actual code I'm using.
It will probably not be until Monday that I post that.
Oh, and BTW, Excel 2003 Sp3 is the flavor involved here.

Thanks again for taking the time to respond, Chip.
 
This is an old thread but a current issue. I'm sure a lot of people like me end up here looking for an explanation only to be a bit confused by the example given.

This example gives a better coverage of the issue:

Code: 
'In a Standard Module Dim Pub As Long
Public Sub AAA()
Dim OleObj As OLEObject
Dim WS As Worksheet
    MsgBox "before: " & CStr(Pub)
    Pub = 12345
    
'   Replace
'   Set WS = ActiveSheet                               'DOES not Reset
'   with
'    Set WS = ThisWorkbook.Sheets("Sheet1")            'same behaviour
'   with
    Set WS = ThisWorkbook.Sheets(ActiveSheet.Name)     'Resets current state after AAA terminates
    
    Set OleObj = WS.OLEObjects.Add("Forms.TextBox.1")
    OleObj.Object.Text = "Pub: " & CStr(Pub)
    MsgBox "after: " & CStr(Pub)
End Sub
It should be noted that the inclusion of ANY reference to the worksheet on which the controls are being placed WILL trigger a re-compile and subsequent dumping of the active state.

In the example above, I reference the Name property of the ActiveSheet. I guess this makes the above code dependent on the target WorkSheet Object thus triggering a re-complile.
This will also happen under the following conditions:

  1. If the AAA routine is called by an event handler in the target sheet
  2. The original code is copied into the target sheet module and executed from there
This is not an all-inclusive list of possibilities, just a couple of illustrative examples.
The original example was indeed one small speck on the surface of possibilities.


For anybody searching this issue, its helpful to think about the WorkSheet as an Object and think about the compiler's perspective and the expected behavior of VBA if the structure of an Object is changed: of course it needs to re-compile.

Can anybody confirm the explanation or add to it?
 
Last edited:
Back
Top