Compile error in hidden module Automation

0

I have recently been issued with a mathematical model in an Excel spreadsheet. This involves copious numbers of sheets, with many macros and a boatload of VBA behind the scenes.

But the model is rather old and was last known to work on Windows XP, Excel 2003. I am currently using Windows 8.1 and Excel 2013, and it is producing the error message:-

Compile error in hidden module: Automation

twice on starting up and once on closing down; the model doesn't work either although no further error message is produced.

Does anyone know how to fix the problem causing this message to appear? (I suspect the model's failings are related to this.)

Brian Hooper

Posted 2015-12-22T15:40:54.553

Reputation: 1 755

We have no way of knowing the issue without seeing the VBA. Obviously something in the module Automation is no longer compatible. The failure is likely exiting the routine, meaning nothing else is running. There have been a lot of changes to Excel VBA since 2003. You are going to have to debug it to find where is is failing. – CharlieRB – 2015-12-22T15:52:35.230

@CharlieRB, so the "Automation" isn't some Microsoft thing, then? I feared as much. Thank you. – Brian Hooper – 2015-12-22T15:55:20.257

No. The message is telling you it is in a module (which is in VBA) that is hidden. You will need to unhide it and debug it. Sorry, no easy answer. – CharlieRB – 2015-12-22T15:56:42.983

Do you have any errors in formulas? It may be something deprecated between 2003 and 2013. – Mark Fitzgerald – 2015-12-23T11:42:00.353

@MarkFitzgerald, quite possibly I have; I will be checking it over. I posted the question in case it was some system or configuration issue, before delving into the innards. – Brian Hooper – 2015-12-23T11:47:29.410

Answers

0

Have you updated the VBA project references?

The minimum should include: Visual Basic For Applications, Microsoft Excel 15.0 Object Library, and Microsoft Office 15.0 Object Library.

To set these, 
Open the Visual Basic editor (Alt+F11)
Select Tools
Select References
Scroll through until you find the library and click on the box to add/remove a check.

Outdated references can cause problems when running older VBA scripts.

B540Glenn

Posted 2015-12-22T15:40:54.553

Reputation: 1 045

Asked: 2015-12-22T15:40:54.553

Viewed: 1 141 times

Active: 2015-12-28T19:18:27.133