Excel error "Macro error at cell"
I have the following setup working in Excel 2003:
If I disable the addin the workbook closes without issue.
I have looked at various articles and some have suggested this is an issue with the Autosave addin or the solver addin - and yet neither are activated. As you cannot remove the 'Autosave addin' via the '[Tools - addins] dialog I cannot be sure it is not working however it is disabled in [Tools - Options - Save]. The artical refering to the autosave issue suggests that there is an undocumented bug where filenames which include spaces will generate this type of error - however the code has been working fine until recently and the filename (which does include a space) has not changed. Unfortunatly I am not aware of anything of significance I have changed in the code for either workbook or addin.
I have tried stepping through the code but when I do this there is no error - when I let the code run the error is always shown. I have tried adding [DoEvents ] in between key commands on the idea that it is a timing issue but have had no luck so far.
Unfortunately, although it would be helpful, I cannot paste code examples are they are confidential but I would appreciate any suggestions I can check.
If I choose the [Halt] button I get an out of memory error and if I choose the [Continue] button nothing happens and teh workbook file does not close. Trying to close the file again brings about the same error. I have even tried checking the code to ensure [Application.EnableEvents]
Thank you all for your time.
Macro-error---Macro-error-at-cel.PNG
An Addin which listens to events and when the [Deactivate] event is called, as a result of a workbook closure, it disables key command bar items
A workbook that when it opens creates custom command bars and instantiates a custom class which listens to events, so that when the file closes that class removes the custom toolbars and resets Excel's application properties (such as customisable toolbars etc..)
The issue I have is that when I close the workbook with the addin loaded I receive the attached error at the very end of the file closure. A manual execution of the command [ThisWorkbook.close] in the IDE's immediate window will return the same error.If I disable the addin the workbook closes without issue.
I have looked at various articles and some have suggested this is an issue with the Autosave addin or the solver addin - and yet neither are activated. As you cannot remove the 'Autosave addin' via the '[Tools - addins] dialog I cannot be sure it is not working however it is disabled in [Tools - Options - Save]. The artical refering to the autosave issue suggests that there is an undocumented bug where filenames which include spaces will generate this type of error - however the code has been working fine until recently and the filename (which does include a space) has not changed. Unfortunatly I am not aware of anything of significance I have changed in the code for either workbook or addin.
I have tried stepping through the code but when I do this there is no error - when I let the code run the error is always shown. I have tried adding [DoEvents ] in between key commands on the idea that it is a timing issue but have had no luck so far.
Unfortunately, although it would be helpful, I cannot paste code examples are they are confidential but I would appreciate any suggestions I can check.
If I choose the [Halt] button I get an out of memory error and if I choose the [Continue] button nothing happens and teh workbook file does not close. Trying to close the file again brings about the same error. I have even tried checking the code to ensure [Application.EnableEvents]
Thank you all for your time.
Macro-error---Macro-error-at-cel.PNG
Rory Archibald
Does it happen if you run Excel in Safe Mode, load your addin and then use the workbook?
ASKER
Yes I am afraid it does.
Then it's not a conflict with another add-in, anyway, so it must be something in your code. Hard to say what, without any code to go on, but are you using Auto_ code rather than workbook events? (eg Auto_Open routines?)
ASKER
No, I have not used 'Auto_' macros for some time. I agree with you that it cannot be addin conflig because a) running in 'safe mode' produces the same error b) neither of the 'listner classes' are performing anything special on the closing events except modifying toolbars - which do not amend cells or the workbooks.
What makes my job even harder is a) when I step through the code it works b) the error message generated refers to the range '[:]!I28' which tells me nothing - I don't even regard that reference ([:]) as valid.
What makes my job even harder is a) when I step through the code it works b) the error message generated refers to the range '[:]!I28' which tells me nothing - I don't even regard that reference ([:]) as valid.
I can't think of any way to debug that error remotely, I'm afraid - it would, as you say, be hard enough with the actual files!
As a matter of interest, if the workbook name does not have spaces, does the same error occur? Also, if you comment out the commandbar resets, does the error still occur?
As a matter of interest, if the workbook name does not have spaces, does the same error occur? Also, if you comment out the commandbar resets, does the error still occur?
ASKER
It almost seems to be a dialog from the Excel Solver addin, although I am not sure why.
I am afraid you will have to bear with me until I get time to comment out the commandbar code and check... thanks
I am afraid you will have to bear with me until I get time to comment out the commandbar code and check... thanks
ASKER
I am still checking this ...
ASKER
I still checking ...
ASKER
Sorry for the delay. I will need some more time to investigate this issue as i believe it is due to the interaction of two Excel addins - both of which intercept the application events when you close a workbook. If you run each separately there is no issue.
It will take some time to work though the issue by disabling events to track the cause.
I this question needs to be closed now, rather than wait for me to post more information or an inspired answer by another Expert, then I will accept this post as the solution (the closest to an actual resolution).
Thanks
It will take some time to work though the issue by disabling events to track the cause.
I this question needs to be closed now, rather than wait for me to post more information or an inspired answer by another Expert, then I will accept this post as the solution (the closest to an actual resolution).
Thanks
ASKER
Well there does not appear to be a resolution at the moment but if you delete it then there will be no record of this problem.
Can I ask then ; are there any unresolved threads on Experts Exchange or should I just accept the solution as my last comment and add any information if I have any more success ?
Thanks.
Can I ask then ; are there any unresolved threads on Experts Exchange or should I just accept the solution as my last comment and add any information if I have any more success ?
Thanks.
As far as I can see there have been comments in the last 21 days so this should not be deleted. :)
If you subsequently find you don't have time to dig into this and decide to pursue another avenue instead, then we can delete it later, but otherwise I agree it will add benefit as a PAQ if we can get to the bottom of it.
Rory
If you subsequently find you don't have time to dig into this and decide to pursue another avenue instead, then we can delete it later, but otherwise I agree it will add benefit as a PAQ if we can get to the bottom of it.
Rory
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.