VBA memory does not clear after closing workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-3194492%22%20slang%3D%22en-US%22%3EVBA%20memory%20does%20not%20clear%20after%20closing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3194492%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20issue%20with%20the%20memory%20in%20VBA%20not%20clearing%20whenever%20I%20close%20down%20a%20workbook.%20I%20am%20using%20Office%20365.%20This%20is%20a%20rather%20new%20issue%20that%20I%20only%20had%20for%20a%20few%20weeks.%20In%20other%20words%20this%20is%20happening%20to%20workbooks%20that%20previously%20didn't%20have%20the%20issue.%20To%20add%20to%20the%20enigma%20it%20only%20happens%20on%20some%20of%20my%20computers%20while%20others%20don't%20have%20the%20problem.%20I%20am%20using%20the%20excactly%20same%20workbooks%20on%20different%20computers%20(using%20OneDrive)%20and%20only%20seeing%20the%20problem%20on%20some%20of%20the%20computers%20while%20others%20are%20just%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20happens%20in%20detail%20is%20that%20I%20open%20an%20xlsm%20workbook%20containing%20macros.%20I%20run%20a%20macro%20and%20close%20the%20workbook%20again.%20Now%20I%20still%20see%20the%20VBA%20project%20of%20the%20closed%20workbook%20in%20the%20VBA%20Editor%20with%20all%20the%20modules%20and%20code%20even%20though%20the%20workbook%20is%20closed.%20Usually%20this%20would%20of%20course%20have%20been%20cleared%20when%20the%20workbook%20containing%20the%20VBA%20project%20was%20closed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%20resolve%20this%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3194492%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3198373%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20memory%20does%20not%20clear%20after%20closing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3198373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1313745%22%20target%3D%22_blank%22%3E%40SrednaNova%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EDo%20all%20PCs%20have%20the%20same%20macro%20settings%20in%20Excel%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EThis%20could%20be%20an%20issue%20when%20different%20Office%20versions%20access%20the%20file.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EAt%20the%20same%20time%2C%20please%20check%20whether%20clicking%20the%20%22Reset%22%20button%20in%20the%20%22Debug%22%20toolbar%20helps.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E%3CSPAN%3Ethis%20also%20deletes%20all%20static%20and%20project-wide%20variables.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E%3CSPAN%3ERunning%20the%20VBA%20END%20command%20in%20a%20macro%20should%20have%20a%20similar%20effect.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EHowever%2C%20the%20macros%20and%20user%20forms%20must%20then%20be%20restarted%2C%20just%20like%20after%20opening%20the%20file.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EWith%20that%20I%20would%20also%20know%20at%20the%20end%2C%20hope%20that%20this%20information%20could%20help%20you.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3199397%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20memory%20does%20not%20clear%20after%20closing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3199397%22%20slang%3D%22en-US%22%3EPls.%20try%20following.%20It%20would%20not%20solve%20the%20issue%20but%20may%20provide%20some%20info%20that%20may%20help%20further%20(a%20hit%20and%20trial%20thing)%20%3A-%3CBR%20%2F%3E%3CBR%20%2F%3EActivate%20any%20of%20the%20modules%20of%20closed%20workbook%20which%20is%20still%20appearing%20in%20VBA%20project%20explorer.%3CBR%20%2F%3EOpen%20immediate%20window%20(Ctrl%2BG).%3CBR%20%2F%3ECheck%20the%20address%20of%20the%20file%20containing%20this%20module%20by%20typing%20below%20in%20the%20immediate%20window%3A-%3CBR%20%2F%3E%3F%20ThisWorkbook.FullName%3CBR%20%2F%3EIs%20it%20a%20success%20or%20error%3F%3CBR%20%2F%3ETry%20the%20command%20again%20by%20moving%2Fdeleting%20the%20closed%20workbook.%3CBR%20%2F%3E%3CBR%20%2F%3E(You%20can%20also%20try%20running%20a%20ThisWorkbook.Close%20sub)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3219698%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20memory%20does%20not%20clear%20after%20closing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3219698%22%20slang%3D%22en-US%22%3EHi%20Again%2C%3CBR%20%2F%3EThank%20you%20for%20the%20help.%3CBR%20%2F%3EI%20figured%20out%20how%20to%20roll%20back%20to%20a%20previous%20version%20of%20Office.%20That%20solved%20the%20problem.%20For%20now%20at%20least.%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I have an issue with the memory in VBA not clearing whenever I close down a workbook. I am using Office 365. This is a rather new issue that I only had for a few weeks. In other words this is happening to workbooks that previously didn't have the issue. To add to the enigma it only happens on some of my computers while others don't have the problem. I am using the excactly same workbooks on different computers (using OneDrive) and only seeing the problem on some of the computers while others are just fine.

 

What happens in detail is that I open an xlsm workbook containing macros. I run a macro and close the workbook again. Now I still see the VBA project of the closed workbook in the VBA Editor with all the modules and code even though the workbook is closed. Usually this would of course have been cleared when the workbook containing the VBA project was closed.

 

Could you please help me resolve this issue?

 

Thank you in advance. 

4 Replies

@SrednaNova 

Do all PCs have the same macro settings in Excel?

This could be an issue when different Office versions access the file.

 

At the same time, please check whether clicking the "Reset" button in the "Debug" toolbar helps. this also deletes all static and project-wide variables. Running the VBA END command in a macro should have a similar effect. However, the macros and user forms must then be restarted, just like after opening the file.

 

With that I would also know at the end, hope that this information could help you.

 

NikolinoDE

I know I don't know anything (Socrates)

@NikolinoDE 

 

Thank you very much for your answer.

 

All the PC's have the same macro settings.

Your remark about different Office versions made me look into this anyway though. I turns out there is a difference here.

I have 5 computers, 4 is on Windows 10 and 1 on Windows 11. Just for the record. The 5 versions of Excel are as follows:

- Version 2201 (Build 14827.20198)

- Version 2201 (Build 14827.20198)

- Version 2201 (Build 14827.20192)

- Version 2201 (Build 14827.20192)

- Version 2201 (Build 14827.20158)

 

The last on that is only on build 14827.20158 is the one that is working. It is also telling me that is has an update available. I suspect that if I update it then it would have the same problem, but of course I don't know for sure untill I try.

 

If this is a general bug in the new builds I would expect many people to experience it, but I have not been able to find anything on the problem by searching the internet.

 

Clicking the "Reset" button in the "Debug" toolbar does not help whether it is done prior to closing the workbook or after. 

Pls. try following. It would not solve the issue but may provide some info that may help further (a hit and trial thing) :-

Activate any of the modules of closed workbook which is still appearing in VBA project explorer.
Open immediate window (Ctrl+G).
Check the address of the file containing this module by typing below in the immediate window:-
? ThisWorkbook.FullName
Is it a success or error?
Try the command again by moving/deleting the closed workbook.

(You can also try running a ThisWorkbook.Close sub)
Hi Again,
Thank you for the help.
I figured out how to roll back to a previous version of Office. That solved the problem. For now at least.