Excel macros disappeared

Question

My Excel file had a load of macros and now when I click Developer - Macros, they don't show up in the list, the list is empty. How do I fix?

Linstock

Answer 1

Hi this has 100% worked for me in all situations, if it is a macro file (in my case at least).

Open the file in protected mode (usually I save it on the share network so it prompts me to "Enable Content"). You must make sure you're in protected mode so no macros/libraries are activated.

DON'T ENABLE CONTENT

Go to Developer, Visual Basic, then click on "Debug" and click on "Compile VBA Project".

Save the file -- > Close - - > Reopen.

Flaggy

You god damn genius. I was just having a problem with my file's macros disappearing - they didn't appear in the list. Then you reply to this 6 month old unrelated question, I try it out for the hell of it, and it works. Gonna try and retitle the question now – Linstock

Haha this cracked me up, the best comment I've ever recieved. No problem at all. I've learned so much from this community so feels good to give back whenever I can. – Flaggy

Happened multiple times to me. Finally found your (2 year old) answer. Wish I knew earlier! – Sensitive

Answer 2

I have seen this happen multiple times, and eventually found a fairly easy solution. To first summarize the problem, we have an excel workbook with macros. After saving and latter reopening the workbook, the macros are no longer there. Even selecting alt F11 ,fails to show any macros, even though macros are enabled in trust center. The solution which has worked for me each time is to simply do the following. Copy the Excel file to a thumb drive, and then open on a different computer. When the file opens, you will see an option at the top of the worksheet to Enable Functions. Click yes. At this point you can click alt F11 to verify that your macros are there. Assuming they are (this has worked for me every time), simply save the file, and then save back to your original location. When you open the workbook yur macros will be visible. Good luck, and keep Excelling.

Venessavenetia

Answer 3

I had this same issue, but I was not able to open the modules in the VBA editor. I solved it by emailing a copy to myself, then opening it from outlook (with macros disabled). Then I compiled the VBA and saved it. The macros are back :)

Ozell

This solution worked for me. Easy as sending an email. – Saga

Answer 4

Tried to do the "Compile VBA" method. Got as far as opening in protected view but the option to "Compile VBA" was not available (grayed out).

What worked for me, was going to "Open" and instead of "Open in Protected View" I selected "Open and Repair". Was a bit worried this might break something and connections might not be reformed correctly, but have not seen any issues yet and Macro list is populated again.

As far as root cause, not sure if this is what caused the issue in the first place, but I noticed that this type of issue happens when multiple people are editing a shared server file at the same time. On one instance, all of my macros disappeared not only form the macro list but from VBA developer as well. I had to revert to a backup file.

Photogravure

Answer 5

An even easier solution that has also worked for me 100% of the time is to open the file normally, and instead of clicking “enable content,” use shortcut alt f11 to open the VBA window. You should see your macros, save the file and reopen and “enable content” should work fine.

Yb

Answer 6

Surprisingly easy way to get it back. Without the file open, right click on it. Click on RESTORE PREVIOUS VERSIONS It'll give you a list of previous days copies. Just try opening one of the ones from a previous day that the macro worked. The missing macro appeared in one of those for me when I opened it.

Salutary

Had the same issue. But none of the version from previous day had macros. I had to go back to the version from 2 days to restore. Obviously, I've lost like 1 module or so.. – Frederico

Answer 7

Permanent fix which worked for me where other methods didn't.

Credit to James Howard here -> here

"I think I perhaps found an answer in this MSDN thread:

Per that thread:

the cause is that Excel does not correctly save the compile state of the VBA code and 64 bit Excel cannot recover from that issue when opening the afflicted Excel file (32 bit usually can). A fix was released for only Excel 2016 and not for other versions

That would indeed confirm that it is a bug within Excel and explains why we only see the issue with 365 64-bit Excel.

That also explains why my manual compile fix works. Based on the article I found, there is a more sustainable fix. You can change Excel’s registry and force VBA to compile accurately.

To implement the permanent fix:

1. Open the start menu and type “reg” and select the “Registry Editor”
2. Navigate to: Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\options
3. On the Edit menu, point to New, and then click DWORD Value.
4. Type ForceVBALoadFromSource, and then press Enter.
5. In the Details pane, right-click ForceVBALoadFromSource, and then click Modify.
6. In the Value data box, type 1, and then click OK."

Trousseau