VBA Editor doesn't warn about unsaved changes on exit

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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?
 
Upvote 0
I have the same . . but only for addins (.xla/.xlam)
lost lots of work over the years :-(
 
Last edited:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Is this a well-known problem with add-in code? Is there a rational reason why it is this way?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,962
Messages
6,175,654
Members
452,664
Latest member
alpserbetli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top