Out of memory : or Just quits

[Dan777]

I have a workbook, #13.
It has a module called “Mass”

I made some small modifications to other modules, renamed the workbook #14.
I test the module “Mass” the same way, in both.

It works fine in #13.
It gives memory error in #14 or just plain quits.

-I generate a tempform
-I show it and write to the worksheet
----then it crashes

the module:

Sub GetMASS(OpArray, StreamNum, NumberStreamElem, Title, MaxStrLen)
'knowing the individual Mass flow of the constituents (of the waste stream)

Dim TempForm 'As VBComponent Setting up Temporary Form
Dim NewTextBox As Msforms.TextBox
Dim NewLabelBox As Msforms.Label
Dim NewComboBox As Msforms.ComboBox

Dim NewCommandButton2 As Msforms.CommandButton 'button for "Okay" (write-to)
Dim NewCommandButton1 As Msforms.CommandButton 'button for "EXIT"

Dim TextLocation As Integer, j As Integer, dH As Integer, LeftPos As Integer
Dim x As Integer, i As Integer, TopPos As Long

Dim wsStIN As Worksheet
Dim wsG1 As Worksheet

Dim StreamUNIT As String
Dim TotalTextbox As String

Set wsG1 = ThisWorkbook.Sheets("Global Input1")
Set wsStIN = ThisWorkbook.Sheets("Input Waste Stream")

' Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False

' Create the UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
' TempForm.Properties("Width") = 300

' Add the OptionButtons
TopPos = 4 'initalize the top position
dH = 17 'the TopPos change in height

StreamUNIT = wsG1.Cells(18, 2).Value 'obtains the dimesions for the stream inputs (desired output dimension)

'TempForm Generation: Generates a temporary form with the necessary information
'LabelBox 1

< code>

' Show the form
VBA.UserForms.Add(TempForm.Name).Show

End ‘to end program for debug

' Delete the form/s
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

'Remove assignments, reduce memory
Set wsStIN = Nothing
Set wsG1 = Nothing

End Sub

...it doesnt get to the "End" ‘to end program for debug


HELP! Please

 

[Norie]

Dan

Where do you get the error?

Also, what are you trying to do?

 

[Dan777]

The error comes after "show.form..."

and before delete: ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm


There is no action between "show" and "delete" (other than what is set up in the tempform).

' Show the form
VBA.UserForms.Add(TempForm.Name).Show
' Delete the form/s
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

I am writing user variable input to the worksheet.

 

[Dan777]

?any ideas?

 

[Norie]

What happens if you comment out or remove that line and instead open the userform manually?

By the way, why do you want/need to create a form at runtime for variable user input?

Couldn't you create the form at design time?

 

[Dan777]

the form works fine; by itself

The user may have different inputs, requiring a completly new form; a designated form is not possible.

 

[Norie]

Why not add the controls at runtime?

 

[Dan777]

**for those who wish to optimise***
?can this be done effectivly in a fixed form?
?can the code be optimised?
Sub GetMASS(OpArray, StreamNum, NumberStreamElem, Title, MaxStrLen)
'knowing the individual Mass flow of the constituents (of the waste stream)
'Input the individual Mass-constituents
'---the "total" mass box is kept as a "dummy"
'Input Temperature
'set-up tempForms to input the known values
'Dan Widhalm 29-Feb-2012
Dim TempForm 'As VBComponent Setting up Temporary Form
Dim NewTextBox As Msforms.TextBox
Dim NewLabelBox As Msforms.Label
Dim NewComboBox As Msforms.ComboBox
Dim NewCommandButton2 As Msforms.CommandButton 'button for "Okay" (write-to)
Dim NewCommandButton1 As Msforms.CommandButton 'button for "EXIT"
Dim TextLocation As Integer, j As Integer, dH As Integer, LeftPos As Integer
Dim x As Integer, i As Integer, TopPos As Long

Dim wsGlobal1 As Worksheet
Dim wsStream As Worksheet

Dim StreamUNIT As String
Dim TotalTextbox As String

Set wsGlobal1 = ThisWorkbook.Sheets("Global Input1")
Set wsStream = ThisWorkbook.Sheets("Input Waste Stream")
' Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False
' Create the UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
' TempForm.Properties("Width") = 300

' Add the OptionButtons
TopPos = 4 'initalize the top position
dH = 17 'the TopPos change in height

StreamUNIT = wsGlobal1.Cells(18, 2).Value 'obtains the dimesions for the stream inputs (desired output dimension)

'TempForm Generation: Generates a temporary form with the necessary information
'LabelBox 1
Set NewLabelBox = TempForm.Designer.Controls.Add("forms.label.1")
With NewLabelBox
'Input the Title information of the new Form
.Caption = "The Dimensions of this stream are in : [" & StreamUNIT & "]"
.Height = 11
.Width = 200
.Left = 8
.Top = TopPos
.BackColor = RGB(255, 255, 255)
.AutoSize = False
TopPos = TopPos
End With 'With NewLabelBox
'chemical labels: the number of chemicals the 1 additional is for the units; the first label
'LabelBox 2-to-(NumberStreamElem+1)
For i = 1 To NumberStreamElem
Set NewLabelBox = TempForm.Designer.Controls.Add("forms.label.1")
TopPos = TopPos + dH 'reset TopPosition
With NewLabelBox
.TextAlign = fmTextAlignRight
.Width = MaxStrLen * 4 + 2
.Caption = OpArray(i)
.Height = 15
.Left = 8
.Top = TopPos + 2.2 'Add 2.2 to align text with TextBox
.AutoSize = False
End With 'With NewLabelBox
LeftPos = MaxStrLen * 4 + 15 'position of textbox (user input information)
'add textbox's for input of info : Side-by-Side with Label box
'TextBox 1-to-(NumberStreamElem + 0) : for INPUT
Set NewTextBox = TempForm.Designer.Controls.Add("forms.textbox.1")
With NewTextBox
.TextAlign = fmTextAlignCenter
.Width = 50
.Height = 15
.Left = LeftPos 'position of textbox (user input information)
.Top = TopPos
.AutoSize = False
End With 'With NewTextBox
Next i 'For i = 1 To NumberStreamElem

LeftPos = LeftPos + 50 + 10 '50 is the width of the individual textboxs
If LeftPos < 150 Then LeftPos = 150 'Assign LeftPosition for secondary textboxes
' NO-INPUT stream total mass --this is the question box for the TOTAL MASS flow
'LabelBox (NumberStreamElem + 2) : just a label
'"Dummy Box"
Set NewLabelBox = TempForm.Designer.Controls.Add("forms.label.1")
With NewLabelBox
.Caption = "The Total Mass flow [" & StreamUNIT & "] is" 'Oringally: "what is the total..."
.TextAlign = fmTextAlignRight
.Height = 11
.Width = LeftPos - 15
.Left = 8
.Top = TopPos + dH * 2 + 2.2 '2.2 is to align text with TextBox
.Font.size = 9
.BackColor = RGB(255, 255, 204) 'light yellow
.AutoSize = False
End With

' Add the box for the "total" mass input --this is the info box for the TOTAL amount
'TextBox 1-to-(NumberStreamElem + 1) : for NO User Input
'"Dummy Box"
Set NewTextBox = TempForm.Designer.Controls.Add("forms.textbox.1")
With NewTextBox
.TextAlign = fmTextAlignCenter
.Width = 50
.Height = 15
.Left = LeftPos
.Top = TopPos + dH * 2
.BackColor = RGB(255, 255, 204) 'light yellow
.Value = "See Total"
.AutoSize = False
End With
' INPUT stream total TEMP --this is the question Labelbox
'LabelBox (NumberStreamElem + 3) : just a label
Set NewLabelBox = TempForm.Designer.Controls.Add("forms.label.1")
With NewLabelBox
.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 = 9
.BackColor = RGB(255, 255, 255)
.AutoSize = False
End With
' Add the box for the "total" temperature input --this is the info box
'TextBox 1-to-(NumberStreamElem + 2) : for INPUT
Set NewTextBox = TempForm.Designer.Controls.Add("forms.textbox.1")
With NewTextBox
.TextAlign = fmTextAlignCenter
.Width = 50
.Height = 15
.Left = LeftPos
.Top = TopPos + dH * 3
.AutoSize = False
End With
' this is the combobox [C] or [F] for temperature
Set NewComboBox = TempForm.Designer.Controls.Add("forms.ComboBox.1")
With NewComboBox
.Width = 34
.Height = 15
.Left = LeftPos + 55
.Top = TopPos + dH * 3
.RowSource = "Dimensions!A2:A3"
.AutoSize = False
End With

'put in the Total's box (SUMMATION of the text boxes on the tempform) : This Sum's-up the percentages from the "TextBox's"
'LabelBox (NumberStreamElem + 4) : just a label
Set NewLabelBox = TempForm.Designer.Controls.Add("forms.label.1") 'message "Total"
With NewLabelBox
.Caption = "Total:"
.TextAlign = fmTextAlignCenter
.Height = 10
.Width = 25
.Left = LeftPos
.Top = TopPos - dH * 2 + 5
.Font.size = 8
.AutoSize = False
End With

' total text-box this displays the TOTAL VALUE of the stream info : This Sum's-up the Mass from the "TextBox's"
'TextBox 1-to-(NumberStreamElem + 3) : for TOTAL VALUE (auto-sum)
Set NewTextBox = TempForm.Designer.Controls.Add("forms.textbox.1") 'updating textbox as user inputs amount
With NewTextBox
.Width = 80
.Height = 15
.Left = LeftPos
.Top = TopPos - dH
.SpecialEffect = fmSpecialEffectSunken
.TextAlign = fmTextAlignCenter
.BackColor = RGB(255, 255, 204) 'light yellow
.AutoSize = False
End With

'Adjust Main Form
With TempForm 'Set-up main form
.Properties("Caption") = "Stream Information"
.Properties("Width") = LeftPos + 120
If (TopPos + 7 * dH) > 380 Then 'if greater than 15 boxes
.Properties("Height") = 380 '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("ScrollHeight") = TopPos + 8 * dH 'top-position of the scroll bar
Else
.Properties("Height") = TopPos + 8 * dH 'height from number of boxes wanted
End If
End With
' Add the EXIT button
Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton1
.Caption = "EXIT"
.Height = 18
.Width = 38
.Left = dH
.Top = TopPos + 4.5 * dH
End With

' Add the OKAY button
Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton2
.Caption = "OKAY"
.Height = 21
.Width = 61
.Left = LeftPos
.Top = TopPos + 4.5 * dH
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 + 10, "Sub CommandButton1_Click()"
.InsertLines x + 15, " MsgBox (""Exiting Program"")"
.InsertLines x + 20, " MsgBox (""Return to Step 2) to Begin Again"")"
.InsertLines x + 25, " Unload Me" 'unloads program
.InsertLines x + 30, " CALL ExitingONcancelTEMPformDelete" 'Exits program: Module = ExitFormDeleteUserForm1
.InsertLines x + 35, "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()" 'Okay is clicked
.InsertLines x + 110, " Dim RowCount As Long, ColumnCount as Long"
.InsertLines x + 115, " Dim i As Integer"

.InsertLines x + 120, " Dim wsStream As Worksheet"
.InsertLines x + 125, " Dim Temperature As Double"
.InsertLines x + 130, " Dim TotalMass As Double, Totalinfo As Double"
.InsertLines x + 135, " Dim TotalMassAddress As String, TotalinfoAddress As String"

.InsertLines x + 200, " Set wsStream = ThisWorkbook.Sheets(""Input Waste Stream"")"
.InsertLines x + 205, " RowCount = 18" 'begin to copy info into row 20 in wsOff (18 + 1 + i)

'column to write to
.InsertLines x + 210, " ColumnCount = 10 * " & StreamNum 'the "frist" column

'Error check: Check for EMPTY input
'Use TextBox as a Number input: Value set to zero if nothing was input
'Use ComboBox as a String input: Re-enter if nothing was input
.InsertLines x + 300, " For i = 1 To " & NumberStreamElem + 2
.InsertLines x + 305, " If Not IsNumeric(Me.Controls(""TextBox"" & i).Value) Then " 'checks if value is NOT-numeric
.InsertLines x + 310, " If Me.Controls(""TextBox"" & i).Value = """" Then" 'checks to see if the value is "empty"
.InsertLines x + 315, " Me.Controls(""TextBox"" & i).Value = 0" 'if "empty" set value to zero
.InsertLines x + 320, " ElseIf (i<>(" & NumberStreamElem + 1 & ")) Then" 'value is not "empty" : a non-numeric value : Not the Mass-flow "total" box
.InsertLines x + 325, " MsgBox (""Non-numeric data detected: Please re-enter"")"
.InsertLines x + 330, " Unload Me"
.InsertLines x + 335, " " & TempForm.Name & ".Show"
.InsertLines x + 340, " Exit Sub"
.InsertLines x + 345, " End If" 'If Me.Controls(""TextBox"" & i).Value = ""
.InsertLines x + 350, " End If" 'If Not IsNumeric(Me.Controls(""TextBox"" & i).Value)
.InsertLines x + 355, " Next i"

'ERROR check: Temperature only 1 comboBox (temp dimension) NumberStreamElem + 2=Temperature
.InsertLines x + 400, " If (Me.Controls(""ComboBox1"").Value = """") Then"
.InsertLines x + 405, " MsgBox (""No Temperature-Dimension was input: Please re-enter"")"
.InsertLines x + 410, " Unload Me"
.InsertLines x + 415, " " & TempForm.Name & ".Show"
.InsertLines x + 420, " Exit Sub"
.InsertLines x + 425, " End If"

'ERROR check for MASS if no total mass is input, re-enter
'NO error check for Mass-total: a dummy value
'ERROR check for Temperature if no temp is input, re-enter
.InsertLines x + 460, " If Me.Controls(""TextBox" & NumberStreamElem + 2 & """) = 0 Then"
.InsertLines x + 465, " MsgBox (""No Temperature was input: Please re-enter"")"
.InsertLines x + 470, " Unload Me"
.InsertLines x + 475, " " & TempForm.Name & ".Show"
.InsertLines x + 480, " Exit Sub"
.InsertLines x + 485, " End If"
'End Error Check
.InsertLines x + 490, " wsStream.Cells(RowCount - 4, ColumnCount - 1).Value = """ & Title & """"
'total Mass from input box (NumberStreamElem + 3) = Same as "Totals" box : (NumberStreamElem + 1) is a dummy
'Temperature from input box (NumberStreamElem + 2)
'totals box from input box (NumberStreamElem + 3)
' .InsertLines x + 600, " TotalMass = Me.TextBox" & NumberStreamElem + 3 & ".Value" 'This is the INPUT Mass-Flow
.InsertLines x + 605, " Totalinfo = Me.TextBox" & NumberStreamElem + 3 & ".Value" 'this is the TOTAL value of the info; the mass%
'Totalinfo is now the total mass-flow that is input

' .InsertLines x + 610, " If (Totalinfo < 99 Or Totalinfo > 101) Then" 'Check Total Input percentage +/- 1%
' .InsertLines x + 615, " wsStream.Cells(RowCount + 2 + " & i & ", ColumnCount + 1).Value = ""ORIGINAL PERCENTAGE SHOULD BE VERIFIED""" 'display WARNING
' .InsertLines x + 620, " End If"

'Mass from Total input box goes in (ColumnCount + 0)
'Mass from input box goes in (ColumnCount + 1)
.InsertLines x + 625, " wsStream.Cells(RowCount + 3 + " & i & ", ColumnCount + 0).Value = Totalinfo" 'original percentage input"
.InsertLines x + 630, " wsStream.Cells(RowCount + 3 + " & i & ", ColumnCount + 1).Value = "" was the Original Mass-Flow Input"" 'original mass-flow Total input"

.InsertLines x + 635, " With wsStream" 'write to worksheet Stream Information Worksheet
.InsertLines x + 640, " .Cells(18, ColumnCount).Value = ""=SUM("" & .Cells(20, ColumnCount).Address & "":"" & .Cells(19 + " & NumberStreamElem & ", ColumnCount).Address & "")""" 'summation of the User Inputs Mass-flow
' .InsertLines x + 645, " TotalMassAddress = .Cells(18, ColumnCount).Address" 'This is the INPUT Mass-Flow
' .InsertLines x + 650, " TotalinfoAddress = .Cells(RowCount + 3 + " & i & ", ColumnCount + 1).Address" 'original percentage input Address"
'put in TEMPERATURE as Kelvin (temperature box = NumberStreamElem + 3)
.InsertLines x + 655, " Temperature = val(Me.TextBox" & NumberStreamElem + 2 & ".Value)" 'temp of the stream
.InsertLines x + 660, " .Cells(13, ColumnCount).Value = Convert_Units_Code(""temperature"", Temperature, Me.ComboBox1.Value, ""K"")" 'convert temp to Kelvin
.InsertLines x + 665, " .Cells(13, ColumnCount + 1).Value = ""K"""
For i = 1 To NumberStreamElem
.InsertLines x + 700 + i, " .Cells(RowCount + 1 + " & i & ", ColumnCount).Value = ""="" & Me.TextBox" & i & ".Value "
'ColumnCount + 1 is the Mass column
' The "total" value is the TOTAL input for all chemicals
Next i 'NumberStreamElem

.InsertLines x + 700 + i + 10, " End With" 'wsStream

.InsertLines x + 900 + i + 10, " Unload Me"
.InsertLines x + 1000 + i + 10, "End Sub"

'RESET x values
x = .CountOfLines 'counts lines of code in Tempform
'input the total's textbox Repaint-cells & textbox to display the total amount (percentage) inputted
For i = 1 To NumberStreamElem
.InsertLines x + 100, "Private Sub TextBox" & i & "_Change()"
TotalTextbox = ""
For j = 1 To NumberStreamElem
TotalTextbox = "val(TextBox" & j & ".Value) + " & TotalTextbox
Next j
TotalTextbox = TotalTextbox & "0"
.InsertLines x + 110, "TextBox" & NumberStreamElem + 3 & ".Value = " & TotalTextbox 'NumberStreamElem + 3 = Total's text box
.InsertLines x + 120, "Me.Repaint"
.InsertLines x + 150, "End Sub"
'RESET x values
x = .CountOfLines 'counts lines of code in Tempform
Next i 'NumberStreamElem
End With

' Show the form
VBA.UserForms.Add(TempForm.Name).Show
' Delete the form/s
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm

'Remove assignments, reduce memory
Set wsStream = Nothing
Set wsGlobal1 = Nothing
End Sub

 

[Norie]

Not sure what you mean by a fixed form but you could have a basic form with the exit/okay buttons, labels, comboboxes etc., basically anything that is static on the form.

Then you could dynamically add the required no of textboxes/labels and use a class to control their events.

 

[Dan777]

Okay… here’s a toughie…

I have written code to do a search and create a form.
It works… now, past tense, it has worked.

I imported other, possibly not so structurally good modules.
Now when I click the same button (on the form) I get “out of memory” error or other run-time errors.


? any ideas ?????