VBE and Not enough memory

[corentint]

Good day to all,

I have an Excel data base which is the basis for a rather complex and big userform VBA program (hereafter the "WB"); the Useforms enable the user to do everything on that data base. Normally, the user never has to touch, not even see, the data base.
I was fortunate to develop the whole thing using a numbered version history of the excel file, done automatically using VBA.

Since version 45, I have this untractable problem, yet classic and known (at least on the Excel/VBA websites, including this one). Nothing has helped yet.
The problem:

1. When closing the WB, the VBA project stays open in the VBE window.
2. Anything else I do with Excel causes, from time to time, the message "Not enough memory"; whether I open another Excel file, or the WB.
3. After closing the WB and reopening it and then closing again (without shutting down Excel), only one instance of the project remains open in the VBE window. No properties can be accessed for this project.

I have tried many suggestions, including one from this forum.
I can state:

• There are no problems from the EnableEvents in the code (only one instance of this coding used, and it works and does never hang; besides, even if this short snippet of code is not called at all by a userform, my problem is still there); I also used the execute window to force EnableEvents = True at various stage of execution of the code).
• I have tried to set to nothing all objects upon closing (a tedious job of coding); this did nothing to solve the problem.

SO I have set to compare the last numbered version (44) that works with the next version (45) that started having the problem. This is a rather tedious and long task.
My question, finally:

Can someone just list for me here the known causes of both problems (they maybe tied together) as per 1. and 2. above
AND
The remedies tried out.

Although I realised this is a tall order, anything at this point would help in reducing my hunting down this (...expletive deleted) problem!

A gratefull thank in advance.

PS: I do not think reinstalling my whole Office 365 suite is a solution, but then again...


Corentint

 

[ranman256]

Is there code that didnt release memory after creating objects?
set oMyObj = nothing 'to release the memory

 

[corentint]

Is there code that didnt release memory after creating objects?
set oMyObj = nothing 'to release the memory

Thank you for ansering.

As per my description of the problem and what I did:

"

• I have tried to set to nothing all objects upon closing (a tedious job of coding); this did nothing to solve the problem.

" end quote

To do this, I have a macro that runs before close.
I even ran the macro manually in the VBE before closing the Workbook.

When setting up an excel range as a variable, I counted that as creating an object too.
I have a variable that contains excel.application; I set that too to nothing.
My numerous sheets are set as variables = all set to nothing before closing.
Really, I do not think that is the problem, unless a forgot somehow THE variable that may be staying open... I did a project search on the word "Set". I do not use any of the ObjectCreate scrip or VBA.

Thanks, for you help.

 

[corentint]

Hello everybody,

OK, I have solved my problem. It was due to an error of structure.

My Excel Project calls some procedures that are in Addins. I had some difficulties with this, but eventually I solved them by referencing the Addins in my Excel Project. So far, so good.

However later I found out that it was simpler to call the procedures in the Addins by using Application.Run ("name of procedure in Addin").
I converted my code and removed the referencing. Or so I thought: not all referencing was removed. That was causing the problem. Why? I do not really know, but I suspect having an Addin referenced and one of its procedure called by application.run instead of the name of its procedure directly did cause a hangup somewhere after the execution - the procedures ran without problem except the VBE and memory. Anyways, this has solved my problem.

Thanks to all that may have looked into this.

Corentint