why Excel VBA modules are not getting saved?

Question

Hello All,

I am working on development of a project specific excel tool and I am stuck on one issue.

 

I am facing an issue in saving the modules which I create in Visual Basics Window. The modules gets discarded every time I close the excel and reopen it. I have to enter the programmed module as many times as I reopen the excel. I need to provide this excel sheet to few colleagues and cannot provide it unless this issue is resolved.

 

I tried with following setting mentioned but it is not working.

1. Trust Center-->Macro settings-->Enable Excel 4.0 macros when VBA macros are enabled
2. Save as Excel Macro Enabled Workbook(.xlsm) extension

 

It would be a great help if you can throw some light on this issue and can tell if any setting is there to keep the modules as it is even when you close it.

 

Thanks and Regards,

Medha Goley

Answer 1

Hi,

It's likely related to the way you're saving the workbook or the specific settings in your Excel application.

Here are a few suggestions from my side-

1. Open the VBA editor by pressing Alt + F11, then go to the "Tools" menu and select "VBAProject Properties." In the "Protection" tab, ensure that the "Lock project for viewing" option is unchecked. This option can prevent changes from being saved in the VBA project.
2. If you have any code in the workbook's "ThisWorkbook" module that clears or modifies the modules, make sure it is not interfering with the module preservation. Review your code to ensure there are no lines deleting or modifying the modules.
3. Try repairing or reinstalling Excel: Try to repair or reinstall your Excel application. Sometimes, a corrupted installation can cause unexpected behavior.

Sometimes due to severe corruption as well, the modules may not work and even after these steps it may not work. At this point, you can try to repair Excel file using a tool.

Best Regards.