Memory Leak Automation error

[Dan777]

Good Day,
Something weird is happening…

I’ve a rather large program that I’ve broken up into little mod’s.
During an early phase of breaking things up I needed to reduce memory.
In an effort to reduce memory, I put things like
Set ws=nothing
Set msformz=nothing
Set msbuttonz=nothing
---I did it everywhere they were used.

I also kept the same name throughout the modules
Set wsCF = ThisWorkbook.Sheets(SheetCopyFrom); wsTo = ThisWorkbook.Sheets(SheetCopyTo)

I’ve a module that generates a temp-form.
It works fine in prior version of the program.
---doesn’t work in the newer version.

The weird thing:
In an effort to get things working again, I’ve removed all differences in the code between the modules. I’ve removed the ws=nothing….
I also removed:
With Application
.EnableEvents = False
End With

Now I have a program routine that works fine in one workbook; which does not work in the “newer” workbook.

I have now started over. I brought the first segment of the program into a totally new workbook; and it works fine. It does not have the Set x=nothing code at the end of the modules.


Question 1)
Is it good to have the same name for a worksheet throughout a large program.

Question2)
When should Set x=nothing be employed?

Question 3)
Dim NewTextBox As Msforms.TextBox
Dim NewCommandButton2 As Msforms.CommandButton 'button for "Okay" (write-to)
Should Set NewTextBox =Nothing be done?
Should Set NewCommandButton2 =Nothing be done?

Question 4)
Why in the world does the program work now… .did I flip a switch?
---I cannot get the one version to run… at all.
----I get an “out of memory error” or “automation error, object disconnected..”

 

[Dan777]

This is what I read: "Not destroying objects in VBA by setting the object variables to nothing in the
correct sequence so that you avoid "orphaned" objects. In theory this is not
neccessary, but sometimes problems occur when this is not done. Destroy in
inside-out container sequence, for example Range then Worksheet then Workbook"

Is there a place that has the order?

is this correct order:
-workbook
-worksheet
-object (any object, in any order?)

 

[Dan777]

I've a "convert_units" code where I define 3 arrays. I call this function a whole lot.

I take it from the first comment that I do not have to Erase the arrays.
... I'll not erase the arrays and see what happens.

?does anyone have a good discription of the excel garbage collector?

 

[NdNoviceHlp]

I was suggesting that "End" ends the program without unloading the ME which may leave the form in memory which may cause an automation error. ie. it should be Unload Me then End. If you create a wb then sheet(s) as objects you need to then set the sheet to nothing then the wb to nothing ie. reverse the order of their genertion. Same with other object variables... 1st produced is last destroyed. HTH. Dave

 

[Norie]

Dan

Have you considered using a listbox for this instead of multiple dynamically created checkboxes/labels?

It could be set up so each item in the list has a check mark against it.

Here's a very simple example, UFChemicalSearch.

 

[Dan777]

ahhh... yes. Very good point.

thanks

 

[Dan777]

That is pretty cool !

... I've not considered that.

good sugestions and Thanks for your time.

 

[Dan777]

I have decided to try to eliminate "tempforms."
---something weird is happening and I dont know what.


I have a problem with only one thing... passing variable data to a sum (or total) textbox.

I have a form which I dynamically modifiy during initialization.

If the user puts a value "5" into "textbox3" the totals box, "textbox22" needs to add 5 to its value.
---there may be more or less than textbox's

How can I do this?
---inserting lines during initialization doesnt seem to work; I cannot get Me.Repaint to fire

Example:

Private Sub TextBox2_Change()
TextBox9.Value = Val(TextBox6.Value) + Val(TextBox5.Value) + Val(TextBox4.Value) + Val(TextBox3.Value) + Val(TextBox2.Value) + Val(TextBox1.Value)
Me.Repaint
End Sub

If I place a value into textbox2, it doesnt repaint.
---?any ideas?

 

[RoryA]

If you use Andrew or Norie's suggestions, you don't need to add code or controls at runtime and you shouldn't have any problems getting events to trigger. Depending on exactly what you're doing (eg if you have multiple textbox's that should run the same code) you may be able to use one or more classes to store your code and then just hook any controls you add to the form up to instances of the class.

 

[Dan777]

With Andrew's suggestion I have eliminated tempforms; creating a perminate form and changing it dynamically.
With Norie's suggestion, simple and effective; quite nice.

I changed topics a bit without stating so... my bad.

Andrew mentioned Design mode; maybe I misunderstood?

NEW situation... kinda/sorta the same.

I have created a form with an "okay" button and an "exit" button.
I dynamically add textbox's for input.
Set NewTextBox = Controls.Add("forms.textbox.1")

I cannot use: Me.Designer.Controls.Add("forms.textbox.1") ->does not work

After generation of the form, the command button subroutines fire; the textbox's do not.

{example}
Private Sub TextBox4_AfterUpdate()
TextBox9.Value = Val(TextBox6.Value) + Val(TextBox5.Value) + Val(TextBox4.Value) + Val(TextBox3.Value) + Val(TextBox2.Value) + Val(TextBox1.Value)
Me.Repaint
End Sub

The next time the user hits the button, there may be 20 textbox's; and the 23rd is the "totals" textbox.

... I am befuddled... shuzbot.

?am I in design mode?
?how do I update the textboxs without adding code during initialzation?

 

[Andrew Poulsom]

I said design time not design mode. Add all the controls and code through the VBE user interface.