CODEWITHSUNDEEP
excelvbaout-of-memory
Anu
Anu

Reputation: 1129

Not enough memory resources are available to complete this operation

Scenario

I have two userforms whereby when I click a button in the first userform, a second one will show and then unload the first userform.

Problem

I am having memory error when I click on one listbox present in the second userform

My userform2 as below

enter image description here

And the error as below

enter image description here

Below are the whole codes in the userform2

Private Sub UserForm_Initialize()

        Dim reportWbi As Workbook
        Dim internal As Worksheet

        Set reportWbi = Workbooks.Add(reportFile)
        Set internal = reportWbi.Worksheets("Internal")
        internal.Select

        LastAddress = internal.Range("C" & Rows.Count).End(xlUp).Address
        ListBox2.RowSource = "C6:" & LastAddress

        reportWbi.Close savechanges:=False
        Set reportWbi = Nothing
        Set internal = Nothing
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    reportCreator.Show
End Sub

Actually there is not much variables or stuffs inside. After debug one by one, I noticed, without the following lines, there are no memory errors

    reportWbi.Close savechanges:=False
    Set reportWbi = Nothing
    Set internal = Nothing

The moment I gave the line reportWbi.Close savechanges:=False, it is throwing out memory error.

Just a side note that I am using office 365 and 8Gb RAM. I don't think this is causing any issue

Does anyone knows what is wrong?

Edit 1

I tried this whole code into a brand excel file with only one userform and having the same memory error. The moment I removed reportWbi.Close savechanges:=False, everything works fine without any error

Edit 2

I removed the ListBox2.RowSource = "C6:" & LastAddress code and this time also no error even with reportWbi.Close savechanges:=False present

Its very confusing why this happening. Please someone help if know anything about this

Upvotes: 1

Views: 3106

Answers (1)

Anu
Anu

Reputation: 1129

After some trials, found out that issue was due to ListBox2.RowSource = "C6:" & LastAddress this RowSource property. I don't know why it caused memory issue though. I removed this and populate Listbox2 using some other loop method as below

        Range("C6").Select
        Do While ActiveCell.Value <> ""
            With ListBox2
                .AddItem ActiveCell.Value
            End With
            ActiveCell.Offset(1, 0).Select
        Loop

Now there is no memory issues and everything works fine

Upvotes: 1

Related Questions