Dynamically adding ActiveX controls via VBA kills global VBA heap?
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? |
Dynamically adding ActiveX controls via VBA kills global VBA heap?
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? |
Dynamically adding ActiveX controls via VBA kills global VBA h
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? |
Dynamically adding ActiveX controls via VBA kills global VBA h
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 "befo " & 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: befo 0 after: 1 ----------- befo 1 after: 2 ----------- befo 2 after: 3 ----------- befo 3 after: 4 ----------- befo 4 after: 5 ----------- Sheeloo wrote: 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? -- Dave Peterson |
Dynamically adding ActiveX controls via VBA kills global VBA h
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... "Dave Peterson" wrote: 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. |
Dynamically adding ActiveX controls via VBA kills global VBA h
"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). Sheeloo wrote: 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... "Dave Peterson" wrote: 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. -- Dave Peterson |
Dynamically adding ActiveX controls via VBA kills global VBA h
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? |
Dynamically adding ActiveX controls via VBA kills global VBA h
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) On Fri, 13 Mar 2009 18:32:08 -0700, Sheeloo just remove all As... wrote: 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? |
Dynamically adding ActiveX controls via VBA kills global VBA h
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? |
Dynamically adding ActiveX controls via VBA kills global VBA h
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.) "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? |
Dynamically adding ActiveX controls via VBA kills global VBA h
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/Soft..._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. "Chip Pearson" wrote: 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) |
All times are GMT +1. The time now is 04:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com