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

 

[Andrew Poulsom]

Generally it's not necessary to set objects to Nothing as that's done by VBA's garbage collector. If you are going to do it you must do it in the right order. You may find this helpful:

Excel Memory Leaks - Decision Models

To get more help we would need to see your offending code.

 

[Dan777]

I wish I could share the offending code... I dont know where it is.

Whatever I did, it seems that I cannot execute a form button (commandbutton) and a tempform runs out of memory or an object gets disconnected.

One second the stuff works, the next, nothing in the workbook works. When I create a new workbook and place the code in, it works???


Thanks for the link.. I do appreciate your time.

 

[Dan777]

oh... another weird thing that happens ...

at one point I set a variable to a cell value:
x=.cell(2,2).value

when the program hits this point, it goes to another module: pressure... then Cp... etcetera...
the program seems to get lost????????????

 

[Andrew Poulsom]

There is no such property as cell. Maybe you meant Cells.

What do you mean by you don't know where the offending code is?

 

[Dan777]

yes, I Set wsGlobal1 = ThisWorkbook.Sheets("Global Input1")
... then later:
with wsGlobal1
...
x=.Cells(2.2).value
..
end with

Here is the code, where the program stops:
... if I copy and paste the module into a new workbook, it will work.

Sub GenChemSearchTempForm(NumBoxes As Integer)
'TempForm Generation:  Generates a temporary   CheckBox  form
'Generates check boxes for approval then dumps them into the necessary worksheet
'NumBoxes = the number of check boxes for approval
'GenChemSearch = 2-D Array PUBLIC ARRAY
'The code allows 2 segments to the "checkbox.caption"  parts "a" & "b"
'GenChemSearch(0,x)= row number (i.e. 452, or 586... ect:  an integer value)
'   Example:  GenChemSearch(0, 7) = 589  an integer value
'GenChemSearch(1,x)= "caption part a"
'   Example:  GenChemSearch(1, 7) = Methane
'GenChemSearch(2,x)= "caption part b"
'   Example:  GenChemSearch(2, 7) = CH4
    
    Dim TempForm 'As VBComponent/Object
    Dim NewTextBox As MSForms.TextBox
    Dim NewCheckBox As MSForms.CheckBox
    
    Dim NewCommandButton2 As MSForms.CommandButton 'button for "Okay" (write-to)
    Dim NewCommandButton1 As MSForms.CommandButton 'button for "EXIT"
    Dim x As Integer, i As Integer, dH As Integer
    Dim formHeight As Long, TopPos As Long
'turn-off screen updating
   With Application
        .ScreenUpdating = False
        '.EnableEvents = False
    End With
    
'   Create the UserForm
    Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)  'vbext_ct_MSForm the VBA object
    
    dH = 18 'the height increment (dy) for checkboxes :  NEEDS to be EVEN
    formHeight = dH * (NumBoxes + 5)  'set form height
    
    With TempForm  'Set-up main form
        .Properties("Caption") = "Choose Chemicals"
        .Properties("Width") = 250
        If formHeight > 360 Then  'if greater than 15 boxes
            .Properties("Height") = 360 'limit to 15 boxes
            
'ADD  SCROLLBAR  for large list
            .Properties("ScrollBars") = 2 'vertical scroll bar on form
            .Properties("ScrollTop") = 6 'top-position of the scroll bar
            .Properties("ScrollLeft") = 235 'top-position of the scroll bar
            .Properties("ScrollHeight") = formHeight + 10 'top-position of the scroll bar
        Else
            .Properties("Height") = formHeight 'height from number of boxes wanted
        End If
    End With  'With TempForm
    
    Set NewLabelBox = TempForm.Designer.Controls.Add("forms.label.1")  'first lable box
    
    With NewLabelBox  'first label box  Lablebox =1
        .Caption = "Which Chemicals are in the waste-stream ?"
        .Width = 250
        .Left = 8
        .Height = 100  'initial height
        .Font.Name = "Times New Roman"
        .Font.size = 12
        .BackColor = RGB(255, 255, 255)
        .Top = 6  'the top position of question box
        .AutoSize = True
    End With  'With NewLabelBox
    
'   Add the OptionButtons
    TopPos = 30 'set the top position of the first checkbox
    
    For i = 1 To NumBoxes  'begin the generation of the boxes & lables & such
        Set NewCheckBox = TempForm.Designer.Controls.Add("forms.checkbox.1")  'first checkbox
'Set NewCheckBox = TempForm.Designer.Controls.Add("forms.label.1")
        With NewCheckBox  'Input the Stream Info (Name & Chemical)
            .Width = 240
            .Caption = GenChemSearch(1, i) & " : " & GenChemSearch(2, i)
            .Height = dH
            .Left = 8
            .Top = TopPos
            .Tag = i  '(i)  tracks the tag integer for the boxes
            .AutoSize = True
        End With  'With NewCheckBox
    
        TopPos = TopPos + dH
    Next i
    
'   Add the EXIT button
    Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewCommandButton1
        .Caption = "EXIT"
        .Height = dH
        .Width = 38
        .Left = 180
        .Top = TopPos + dH / 2
    End With
    
'   Add the OKAY button
    Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
    With NewCommandButton2
        .Caption = "OKAY"
        .Height = 21
        .Width = 60
        .Left = 36
        .Top = TopPos + dH / 2
    End With
    
'   Add event-hander subs for the CommandButtons:  Create the code for the Tempform
    With TempForm.CodeModule
        x = .CountOfLines   'counts lines of code in Tempform
' write sub for button 1: the EXIT
        .InsertLines x + 5, "Sub CommandButton1_Click()"
'        .InsertLines x + 10, "  MsgBox (""Exiting Program"")    "
'        .InsertLines x + 15, "  End"    'exit program
        .InsertLines x + 15, "  Unload Me"    'exit temp form
        
        .InsertLines x + 20, "End Sub"
' write the subfunction for button 2, the Write-to (Okay) button
' use with user defined functions such, with unknown "tags"  Writes a subfunction for button
        .InsertLines x + 100, "Sub CommandButton2_Click()"
        .InsertLines x + 105, "  Dim i As Integer"
        .InsertLines x + 110, "  Dim NULLCnt As Integer"
        .InsertLines x + 115, "  Dim NumberOfChemicals As Long"
        .InsertLines x + 120, "  Dim RowCount As Integer"
        
        .InsertLines x + 125, "  Dim wsStIN As Worksheet"
        .InsertLines x + 130, "  Dim wsChem As Worksheet"
        
        .InsertLines x + 135, "  Set wsStIN = ThisWorkbook.Sheets(""Input Waste Stream"")"
        .InsertLines x + 140, "  Set wsChem = ThisWorkbook.Sheets(""Chem Data"")"
        .InsertLines x + 145, "      NumberOfChemicals =" & NumBoxes    'Last Row in wsOff, counts row A in sheet wsOff (no chemicals exist in lines 1-19)"
        .InsertLines x + 150, "      RowCount = wsStIN.Cells(Rows.Count, ""A"").End(xlUp).Row + 1"  'begin to copy info into Last available row
'Error check:  Check for EMPTY input
        .InsertLines x + 155, "     NULLCnt=0"
        .InsertLines x + 160, "For i = 1 To NumberOfChemicals"
        .InsertLines x + 165, "   If Me.Controls(""CheckBox"" & i).Value Then NULLCnt = NULLCnt + 1"
             ' if the combobox is checked, then NULLCnt <>0
        .InsertLines x + 170, "Next i"
        .InsertLines x + 175, "    If NULLCnt=0 Then"
        .InsertLines x + 180, "        MsgBox (""Nothing was checked"")"
        .InsertLines x + 185, "        Unload Me"
        .InsertLines x + 190, "        Exit Sub"
        .InsertLines x + 195, "    End If"
    
        .InsertLines x + 200, " With wsStIN"   'write to worksheet wsStIN
        
        .InsertLines x + 201, " For i = 1 To " & NumBoxes  'number of chemicals found
            .InsertLines x + 205, "    If Me.Controls(""CheckBox"" & i).Value Then "
            .InsertLines x + 210, "          .Cells(RowCount,1).Value=GenChemSearch(1, i) "  'the chemical name
            .InsertLines x + 215, "          .Cells(RowCount,4).Value=GenChemSearch(2, i) " 'the chemical formula
            .InsertLines x + 220, "          .Cells(RowCount,3).Value=wsChem.Cells(GenChemSearch(0, i),4).Value" 'column 4 is gram/mole
            .InsertLines x + 225, "          .Cells(RowCount,2).Value=wsChem.Cells(GenChemSearch(0, i),2).Value" 'column 2 is CAS#
            .InsertLines x + 230, "          If IsNumeric(wsChem.Cells(GenChemSearch(0, i),14).Value) Then"  'the GenChemSearch(0, i)=(Row or Cell Number) : column 14 is HOC
            .InsertLines x + 240, "             .Cells(RowCount,7).Value=wsChem.Cells(GenChemSearch(0, i),14).Value" 'the GenChemSearch(0, i)=(Row or Cell Number) : column 14 is HOC
            .InsertLines x + 250, "          Else"
            .InsertLines x + 260, "             .Cells(RowCount,7).Value=0" 'if the value is non-numeric, the value is zero
            .InsertLines x + 270, "          End If"   'If IsNumeric(wsChem.Cells("...
            .InsertLines x + 280, "          RowCount=RowCount+1" 'the GenChemSearch(0, i)=(Row or Cell Number) : column 14 is HOC
            .InsertLines x + 290, "    End If" 'If Me.CheckBox" & i & ".Value
        .InsertLines x + 290, "Next i"  'For i = 1 To NumBoxes
        
        
        .InsertLines x + 300, "  End With"  'wsStIN
      '  .InsertLines x + 301, "Set wsStIN = Nothing"  'wsStIN
      '  .InsertLines x + 302, "Set wsChem = Nothing"  'wsChem
        .InsertLines x + 400, "  Unload Me"
        .InsertLines x + 500, "End Sub"  'end OKAY
    
    End With  'With TempForm.CodeModule : TempForm.CodeModule
'   Show the form
    VBA.UserForms.Add(TempForm.Name).Show
'   Delete the form/s
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
    
'Remove assignments, reduce memory
'Set TempForm = Nothing
'Set NewTextBox = Nothing
'Set NewCheckBox = Nothing
'Set NewCommandButton2 = Nothing
'Set NewCommandButton1 = Nothing
End Sub

 

[Andrew Poulsom]

Instead of creating a UserForm at runtime why don't you create one at design time containing the maximum number of controls you will need and showing only those that are necessary?

 

[Dan777]

I am having difficulties doing that; I can modify the form at runtime (not a problem).

Could you supply me with code to:
1) repaint a cell that is dynamically generated
2) make the commandbuttons work
3) erase the dynamically generated code (the user may activate the module again)

 

[NdNoviceHlp]

Not sure if this helps but you are using "End" followed by "Unload Me".... that doesn't seem right. Dave

 

[Dan777]

on the exit button I do use "End"

?should I not?