Excel Automation Error

Question

I have an excel spreadsheet which uses VBA code. In Excel 2013 (365) it displays an error box "Microsoft VBA, Automation Error" as soon as the sheet is opened and the VBA editor window is then displayed in debug mode. Looking at the project explorer pane in the VBA editor, each worksheet is listed twice with different icons and some of the code has disappeared. It does the same on three different computers, one of which has Office 2010 still installed alongside 2013, the second of which I've uninstalled 2010 and reinstalled 2013, and the third of which has only previously had Office 2013 Home and Student installed prior to the full 365 installation.

The problem may be related to hidden columns because the sheet appears to work OK if the columns are not hidden or if the columns are dragged to be very narrow. The sheet works perfectly in Excel 2010. Any ideas please.

Windows 7 and 8.1

Answer 1

https://dl.dropboxusercontent.com/u/51684362/Workbook%20before%20corruption.xlsm

The workbook works fine in Excel 2010 but throws the error in Excel 2013

I can not agree, I was able to reproduce the issue in Excel 2010 too.

The exact reason is not clear to me, but the it is the call to Application.Volatilein Function Eval which is used in the hidden column C "PGM Time (Mins)".If you don't call Application.Volatilethe issue is gone.

BTW, why so complicated and use a mix of a long formula with calls to different UDF's? It is much easier if you call only one UDF:

=CalcMins([@[PGM Time]])

Function CalcMins(ByVal S As String) As Variant

  If InStr(S, "h") > 0 Then

    S = Replace(S, "h", ":")

    CalcMins = CDate(S) * 24 * 60

  Else

    CalcMins = Application.Evaluate("=" & S)

  End If

End Function

Andreas.

Answer 2

There are two links below. The first to the workbook without errors, and the second to the workbook with the automation error.

To replicate the problem, open the good workbook, click in M15 and hit TAB to create a new empty row 16. Insert the following:

A16 b

B16 23+45

C16 Fred

D16 George

E16 SP

then tab to M16 and save the workbook. Open the workbook and the Automation Error dialog should appear and the VBA editor window will open. Before saving, the project explorer showed Sheet1, Sheet2, Sheet8 and ThisWorkbook. Now it shows Sheet1, Sheet11, Sheet2, Sheet21, Sheet8, Sheet81, ThisWorkbook, ThisWorkbook1 and all of the code has disappeared.

https://dl.dropboxusercontent.com/u/51684362/Workbook%20before%20corruption.xlsm

https://dl.dropboxusercontent.com/u/51684362/Workbook%20after%20corruption.xlsm

The workbook works fine in Excel 2010 but throws the error in Excel 2013

Any help would be gratefully received.

Answer 3

Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

Andreas.

Answer 4

Thanks for the response. Is there any way I could attach the spreadsheet?

Answer 5

I doubt  that anyone can help you without seeing the code.