Excel 2010 addin (XLAM) does not save

Sometimes when Excel gives up I get the error message attached - but sometimes it just does nothing (in which case it only takes a few seconds).

NOTE this error happened before and after I had rebuild the addin file from reimporting the modules.
Excel-save-error.PNG

It certainly sounds like there is stuff in the add-in Excel doesn't appreciate.

No solution, but I have the habit of doing my changes in a normal xlsm file and once I'm done saving it as add-in.

Have you also tried setting EnableEvents to false before the save and disabled any application-wide events your add-in might have?

Have you run Code Cleaner on the file (which is more or less the same as importing everything into a new workbook)?

jkpieterse: Thanks for you comments

Unfortunately I cannot do my development in a XMSM as the project is too huge and has to be tested (along with the code's interaction with it's child workbooks) as each core feature is added. The system is not just one or two macro(s) running from an addin it is a director-style controller that oversees and manipulates multiple workbooks and has it's own custom ribbon and when functioning needs to be an addin. If it were not an XLAM it would not work. It would be very time consuming and laborious to add 40 lines of code, save as addin, test, re-save as XLSM, add another function and so on.

I have not tried setting EnableEvents before the save. It is definitely worth keeping in mind ... however ...

I have found that when on either of the client's two machines - I get the save problem. When I transferred the file to my machine - no issue at all (so far several dozen successful consecutive saves).

That would suggest a) it has something to do with the client's network (what ever that could be - maybe the GPO) rather than a specific computer / unstable system and b) it is not the Excel file or the contents.

(Note: Both the 2 computers from the client and my own laptop use Win 7 x64)

All Excel developers know how 9 times out of 10 Excel and VBA errors do not refer to the actual problem but the fallout from it.

I was sharing another user's login (who was logged in at the same time - albeit on a separate machine on the same network) and for that reason I am going to log in as another user and see if that makes a difference.

For over a week now I have been working on my own laptop and each save (I do it almost every 30 mins or more when significant code added) and each one has succeeded !

I have now have this (once so far) on my laptop !

If the file is corrupt (which I highly doubt since I recently rebuilt it by reimporting the modules) then why has it worked without error for two weeks on my laptop ? When it did not work on the client's machine so I copied it (without any correction or cleaning action) to my laptop and it worked first time and since.

Could there be some hidden temporary file that is building up slowly as I develop the code ? It would have to be something on-disk as on that would explain why transferring the file to another machine instantly corrected the issue.

On the other hand it could be a different cause by the same symptom - the zero byte file.

Any assistance would be greatly appreciated !

Lots of stuff might be wrong:
- Networking issue
- Virus software
- Temporary files
- Bad Office installation
- Some problem with the file after all
- ...
Hard to say which it is.

jkpieterse:

- Networking issue
  As moving it from he client's system to my laptop originally cured the problem I think we can rule this one out. Especially since my laptop is not on a network - total independent (except for a wireless connection to the internet)

- Bad Office installation
Two different copies of office installed at separate times. One in a corporate environment (and licensed using a central server) and the other installed for local use manually and independently activated

- Some problem with the file after all
How could a corruption 'sleep' for two weeks and hundreds of saves and all that VBA development and then suddenly appear one date whereas in the client environment it occurs every time. Very odd sort of corruption.

- Virus software
I checked the client's system (via Task Manager) to see if the on-access protection was active while I saved, it didn't seem to be. I have disabled my laptop's on-access protection and tested - I still get the error

About the office installations:
Not sure what you mean by two different copies?

Even so, whether or not the installation was done properly, it still sometimes happens that problems surface.
Sometimes they are profile related and can be overcome by removing the user from the system and putting him back in again.

Is your project using any references to external libraries? Such as ADO, common controls, refedit, ...?

Any external controls on userforms?

Any ActiveX controls on worksheets?

About the office installations:
Not sure what you mean by two different copies?

Sorry, I mean two different installs, done at two different times on different platforms using different activation systems and licenses.

Even so, whether or not the installation was done properly, it still sometimes happens that problems surface.
Sometimes they are profile related and can be overcome by removing the user from the system and putting him back in again.

As you say it happens, but two different users on two different systems and failing then working and then failing and working ... If a user profile is corrupt it stays corrupt and I have seen no other signs of it.

Is your project using any references to external libraries? Such as ADO, common controls, refedit, ...?

Yes it has a number of references. The main one of which is IBM Cognos TM1. I have done a great deal of development against that addin and have never had this issue before.

Any external controls on userforms?

No

Any ActiveX controls on worksheets?

No

I have just tried moving the addin to a new path - in case there was any issue with a hidden file on that path etc... No joy I am afraid.

If I run the following code to save a copy of the XLAM

Thisworkbook.saveas thisworkbook.path & "\Sixth.xlam",xlOpenXMLAddin

Select allOpen in new window

and a new file is saved without a problem

However when I delete the old file and rename the new, open it, add code and try to save I get the same error

Does it open well after running code cleaner? If so, code cleaner saves the file when done, so that might be a feasible work-around?

What is 'Code Cleaner' - who is it from ?

From what you have said it basically exports all the modules, cleans the file, reimports and saves as another file. Well I have now several times done the following:


Even after all that I still get the error. How can that be any different from this tool you are referring to ?

Note the problem is saving - not loading. Apart from the saving there is no issue.

I should add that I thought I had solved it - that it was a coding error.

Basically the addin is acting as a director-style app that has it's own app event watcher class.  One of the events 'Workbook_BeforeSave' had some legacy code where it tried to make one of the addin's worksheet active - which of course would be an error. I disabled the code and the error disappeared.

I have then been using it on my laptop and the client's network with no issue for several days and hundreds of saves.

I am sorry to say I have now got the error again.

I have a small sub that just does a 'Thisworkbook.save' and I have added 'EnableEvents = FALSE' and 'IgnoreRemoteRequests' but nothing seems to work.

Code cleaner does not create a new file, it does the process against the existing file.

www.appspro.com turns up as first hit if you google for VBA code cleaner :-)

Another hunch: Maybe one of the userforms is causing all of this somehow. Have you got any (very) complex ones in the project?

Yes that is the one I found. Is it an install or an addin ? As this would be on a client's system they may not allow it.

No particularly large userforms

The code is huge though - over 25,000 lines of code, 15 userforms, 40 modules. Basically a rewrite of a system I wrote several years ago of the same size and complexity.

None of the modules are over 64K in size (which used to be an issue saving Workbooks).

I have had a project that kept causing problems. I ended up pasting all the code in textfiles rather than exporting them. Perhaps something to try,

Well I am testing another change. There is another event procedure that seem to be called when the last crash happened (I have an external text file that records each procedure entered) and since I put 'Exit sub' at the top, to nullify that code, I have not had an issue. Touch wood !

I can't see anything in that procedure that would cause an issue though.

Unfortunately my amendment only seemed to work for a bit last week and this morning the error is back.

I will try rebuilding the file again.

Does your add-in perhaps have an application-wide before_save event?
If so you might try disabling that for the add-in itself?

I rebuilt the file (which now saves again) and the only difference I can see is that it is 200K smaller than the file that would not save. We all know that Excel gathers 'content' that is unneeded during development but in all my years developing Excel VBA I have never had this problem and an issue that is this persistent and cannot be diagnosed.

It will be impossible to develop if every other day I have to rebuild the XLAM from scratch.

All I am doing is adding lines of code - nothing that should cause repeated randomly timed corruption.

Does your add-in perhaps have an application-wide before_save event?

Yes and I disabled it as my first 'solution' - but that the error came back after a while (and I checked the code was still enabled.

If so you might try disabling that for the add-in itself?

In fact I a) set the event watcher class to nothing before i saved and b) set Application.EnableEvents to FALSE before I saved. Both either have no success or short-lived success.

I am going spare - I have tried every diagnostic technique I know and it still causes errors - even after a rebuild.

Have you ever tried re-doing the forms (i.e. not import them, but actually reconstruct)?

Sadly that would not be possible - way to complex. It could take me a day to rebuild the most complex form.

But the fact that it works sometimes and not others is what confused me. It feels like a 'cache buildup' type of issue.

I made sure AutoRecover was disabled before each save - no joy.

It is nothing like proof but it seems to me that if i open the addin, make some code changes (not matter how large) and save using by sub it works fine but if i make some changes over half an hour say and then save I get the error !

I mean that happens even if I actually input the same amount of code / changes. Say for example a save fails after I have amended some code. I will copy the amended code to notepad, close the file and reopen, paste the amended code in the VBA module, save and it now works, Very very strange.

Indeed.
Lets take a step back.

After getting the error message, does Excel tell you what errors it encountered?  (perhaps you already mentioned this)

I have a tool that writes all controls and their properties of all userforms onto an Excel sheet. It can subsequently read that sheet to rebuild the userforms (including event code behind them). It does not handle multipages and the like, but it might get you half-way there :-)

Since my last post it has saved every time (I have done nothing special). Just now it has started failing.

I have had it again this morning (after 3 successful saves in one session and the reopening) I tried to do a 'SaveAs' using the following code once I had the issue - Excel could not even do that !

ThisWorkbook.SaveAs ThisWorkbook.path & "\Test.xlam",xlOpenXMLAddin

Select allOpen in new window

You could try the attached tool to export your forms to a worksheet.
Then remove all userforms, save-as (copy!!) the workbook, close it, open it (disable macros) and run the tool again to rebuild the forms.
ControlLister.xls

I am still getting the error and still random.

Worked all day this monday.
Error at 10 am
Working ...
Error at 2 pm
Working ...
Error at 3.45pm
Working ...
Wont save at all know.

I have tried restarting the machine, shutting down the machine and rebuilding the book.

I may have found the cause, I am giving it until next week before I am sure.

Sorry to say that the hunt goes on ! I found that one of the forms was corrupt. I recreated it from scratch and after that for 4 days there were no issues. The problem is back and the same form appears fine.

Also if the corrupt form was the cause I would expect the issue to be constant - not random.

I am wondering if is a 'dual factor issue'. There appears to be an issue with the client's network in that sometimes (with a normal Excel file) other network users will not be able to save an Excel file that they have just opened (and is not shared) because it comes back and says that the file is locked. This occurs randomly. Sometimes just cancelling and dismissing the prompt allows the users to save as normal.

I am therefore wondering if the same issue sometimes occurs during a save of my VBA heavy addin and this corrupts the file somehow.

Anyone any comments, thoughts or experiences ?

I've requested that this question be closed as follows:

Accepted answer: 500 points for jkpieterse's comment #a39688255
Assisted answer: 0 points for AL_XResearch's comment #a39719787

for the following reason:

The question has not been resolved, I believe I had found the nature of the issue and there is no apparent solution but jkpieterse provided much needed help.

The issue is not resolved but jkpieterse has provided valuable assistance