VBA Editor doesn't warn about unsaved changes on exit

[JenniferMurphy]

I thought I had made some changes to a UDF, but when I opened up the VBA editor today, the changes were gone. So I ran a little test. I made a simple change to some code, then closed the editor and then Excel. I did not get any warn about unsaved changes or any offer to save them. When I restarted Excel and reopened the editor, the changes were gone.

Is this normal? Do I have some setting wrong?

 

[jmacleary]

Hi Jennifer
I'm on excel 2007 pro as well (but on windows 10) - I just tried it and the changes saved OK for me. I tried 4 combinations - closing VBA (with or without an explicit save) then close excel (with or without explicit save of the workbook). In all cases the changes saved OK. Is the workbook on a shared or networked drive? If so, theres a reference on another help site (stackoverflow) that suggests it may be a permissions issue. Maybe someone else on here has a better idea?

 

[JenniferMurphy]

Is the workbook on a shared or networked drive?

The workbook is on the C-drive on my laptop. We do have a home LAN that works both by cat5 cable and wifi, but the C-drive is not shared.

 

[Repush]

I have the same . . but only for addins (.xla/.xlam)
lost lots of work over the years

 

[JenniferMurphy]

I have the same . . but only for addins (.xla/.xlam)
lost lots of work over the years

Aha. The code I am working on is in my personal (private) add-in, "My Add-Ins.xlam".

Is there any cure?

 

[JenniferMurphy]

Hi Jennifer
I'm on excel 2007 pro as well (but on windows 10) - I just tried it and the changes saved OK for me. I tried 4 combinations - closing VBA (with or without an explicit save) then close excel (with or without explicit save of the workbook). In all cases the changes saved OK. Is the workbook on a shared or networked drive? If so, theres a reference on another help site (stackoverflow) that suggests it may be a permissions issue. Maybe someone else on here has a better idea?

Was this code in an add-in?

 

[JenniferMurphy]

Is this a well-known problem with add-in code? Is there a rational reason why it is this way?

 

[RoryA]

Yes, it is well-known. I also find that the save button in the VBE is not reliable with add-ins, so I always have a simple routine in any add-in that saves it and run that instead.

 

[JenniferMurphy]

Yes, it is well-known.

This has to have been like this for 20 years or more. What are the jerks at M$FT doing -- counting their stock options? Their products are so full of bugs it's ridiculous. This really makes me very angry. (sigh) Is there anything we can do to get them off their lazy butts? This should be a very simple fix.

I also find that the save button in the VBE is not reliable with add-ins,

What??? When you say save "button", do you mean File | Save?

I always use the keyboard shortcut Ctrl+s. I just checked. It appears that that shortcut only applies to the active book. If I am working in one of the modules in my XYZ add-in and I click on the File dropdown, it says "Save XYZ Add-in.xlam". But if I switch to a module in a different add-in or in one of the open workbooks, it changes to that one. Maybe the explicit save function works, but if you switch modules without saving, code would be lost.

I just ran a test:

• Add a comment in a code module in add-in A.
• Switch to a code module in add-in B.
• Save (Ctrl-s).
• Close the editor.
• Open the editor (Alt+F11).
• The comment is still in the module in add-in A.
• Close the editor.
• Close Excel.
• Open Excel.
• Open the editor (Alt+F11).
• The comment is gone. It was not saved.

So it looks like the Save button works as long as you are in the module with the changes. And Excel keeps the code "as is" as long as Excel itself is open even if the editor is not. But when Excel is closed, unsaved changes are lost. Utterly garbage programming by M$FT.

so I always have a simple routine in any add-in that saves it and run that instead.

You have a routine that will automatically save on exit? How does that work? Can you post the code and how to use it?

 

[RoryA]

No it’s a manual save routine that I run after making changes.

Microsoft don’t care about VBA. It’s clearly not part of their vision for the future.