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..”

 

[RoryA]

Neither of their suggestions involved adding any controls at runtime (that was kind of the point ) - the idea is to have all the controls and code already in place and just show (or hide) whichever controls you need to.

 

[Dan777]

btw,
Set NewLabelBox = ThisWorkbook.VBProject.VBComponents("userformII")Designer.Controls.Add("forms.label.1")
doesnt work
I get error 91:Object variable or with block variable not set

 

[Dan777]

Thanks for the clarifiaction/s.

 

[Dan777]

I cannot think of a way to obtain user data without using dynamically generated textbox’s.
--- I need to sum-up "x" number of user inputs in a totals box
?what is the best way?


At time-step 1, the user has 6 chemicals, there may be more at time-step2.

I have created a userform and it generates textbox’s under “Private Sub UserForm_Initialize()”

I have also placed code like:

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

Within the same code-module.

I set the textbox’s :

        Set NewTextBox = Controls.Add("forms.textbox.1")
        With NewTextBox
            .Caption = "Stream Temperature ?"
            .TextAlign = fmTextAlignRight
            .Height = 10.5
            .Width = LeftPos - 15
            .Left = 8
            .Top = TopPos + 3 * dH + 2.2  '2.2 is to align text with TextBox
            .Font.Size = 10
            .BackColor = RGB(255, 255, 255)
            .AutoSize = False
        End With

I cannot: {because of error 91}

Set NewLabelBox = ThisWorkbook.VBProject.VBComponents("userformII"). Designer.Controls.Add("forms.label.1")

When I place a value into a textbox, such as “textbox4”, textbox9 does not update.
--When the “okay” button is pushed I can retrieve all textbox values; here textbox9=”” because it doesn’t update.

?any ideas on how to get the “AfterUpdate()” subroutine to fire?
---it does not with with “Change()” either

Does someone have an example of textbox values changing, and its total’s textbox changing accordingly?
---where the textbox's are generated during initializatioin

 

[Dan777]

Andrew, Norie and RoryA Thanks Much for your time.

I decided to add a commandbutton to do the totaling for the "totals" textbox

Private Sub RefreshTotalButton_Click()
Dim i As Integer
Dim NumChem As Long, Total As Double
    NumChem = ThisWorkbook.Sheets("Global Input1").Cells(10, 3).Value
    
    Total = 0
    For i = 1 To NumChem
        Total = Total + Val(Me.Controls("TextBox" & i).Value)
    Next i
    
'set the value of the totals textbox
    Me.Controls("TextBox" & NumChem + 3).Value = Total
    
    Me.Repaint
End Sub

In doing so, I had to add a small function to insure the proper total was being sent: An error check with respect to the "Okay" button.

Though not as elegant as I would've liked, this is very easy to implement. I have several modules to go and now, I do not have any temp-forms.... woot.