Compile error, Automation error - Global variable problem?

[Bill Hamilton]

I have some VBA code which has been running quite happily for the last two or three years. Starting a couple of days ago it now fails every time with runtime error "Compile error. Automation error". There are no error numbers or codes given. On clicking OK on the error box the processing continues normally, BUT the user is also shown, and is left looking at, the Visual Basic editor screen. Not good. Clicking Help in the error box brings up a web page saying 'Unable to service request'. Useless.

The way things work is that the workbook the users open contains minimal code just to open a second w/b that contains many macros that the users will run using buttons in sheets on the first w/b. This macros file is opened in an Autpen Sub (yeh, yeh, I know, deprecated and all that, but I'm old school and set in my ways). I don't want to have all the macros in the first w/b and wish to keep them separate.

The Autpen will run a macro to check if the macros file is already allocated and will exit the process if so, and lets the users get on with things. If not (usually the case), it will open it and Set its reference to wbMacs, which is Dim'ed 'as Workbook' and is a global variable defined right at the top. Thus it is available in any Sub in the Module and there are some other Subs besides the Autpen where it's used and it pretty-much has to be a global variable. It's a little more complex than that but that's the gist.

When the runtime error is given and the VBE is displayed the line where wbMacs is Dim'ed is highlighted but processng continues after hitting the OK.

I've googled the words in the error message dialog box (i.e. Compile error, Automation error) and nothing seems to fit with this scenario so I'm pretty stuck. And why should it suddenly start doing this with heretofore stable code? Nothing has changed in my system as far as I know. I'm running Win 10 and Office 2016.

Any clues?

 

[rlv01]

Hard to know without seeing your code. Are you using any 'on error resume next' statements that might be masking the true error?

Also, if these are big complex workbooks that you have been coding in for a long time, you might consider cleaning your code. Excel is notorious for not cleaning up well and "garbage" can build up. "Automation" errors are right at the top of my list for that. When I start getting errors that reason cannot explain, it's the first thing I do and has resolved a number of problems for me. There are a number of cleaners out there, but I like Rob Bovey's

Excel VBA Code Cleaner Add-in

The potential downside for you is that it only supports 32-bit excel.

 

[Bill Hamilton]

Thanks for the reply.

I've just discovered that I actually have 32-bit Excel. Gawd knows why - I have a 64-bit machine and I'd have thought the Office install (about a year ago) would have sussed that out and installed the equivalent version, but there you go. I don't remember the install asking me which one I wanted. So I downloaded the Cleaner you recommended and ran it on several of my projects including this one. No change I'm afraid. However, now that you've brought a potential issue to my attention (which I wasn't aware of before) I'll hang on to it and use it in future to try to pre-empt problems, so thanks for that.

I have discovered a fix though. All my files are in 'xls' format (don't ask). I made absolutely no changes to any code, saved the relevant files as 'xlsm' files and bingo! - everything worked perfectly. In the interests of completeness I re-saved thm back as 'xls' files and the errors returned. Go figure, as they say.

For further info, I have three versions of this project - one Production, one Test (for testing smaller changes to the Prod version) and one Development (for working on bigger updates). All three started playing up at the same time. None of my other VBA systems (and I have a good few) showed this symptom. I'm officially baffled.

So, I have a workaround at least, but no explanation. I shall introduce the xlsm fles into the system unless anyone knows of a magic bullet to fix the actual problem. Happy to post code if anyone is really that interested (don't all rush at once).

 

[rlv01]

It is mostly likely the all-powerful Excel God signaling to you that it's time to give up the .xls format and get on board with Microsoft's vision of the future . My unsupported guess would be that if you were still running Excel 2010 you wold be fine, but that you've just hit a "wrinkle" in 2016 compatibility with the older format.

 

[Bill Hamilton]

Yes, I totally agree that xls is outdated and your guess is probably as good as any explanation.

I took over this particular system a couple of years ago from the person who invented it in the early 2000s when xls was the only way there was. Unfortunately the end-product file has to be distributed to about a hundred recipients, some of whom still use Windows 95 and the Excel of that period, so I have to keep this file going as an xls. These are private citizens, not corporates so I can't exactly tell them to modernise their systems. I have to cater to the lowest common denominator.

However, this has given me a wake-up call to convert at least the processing files to the modern format and I've done as many as I can. There have been no recurrences of the problem (touch wood). The final xls file contains no macros so I think I'm done.

Thanks for your interest, but I think this case can now be closed.