Automation Error Catastrophic Failure

[bnj1776]

Hello,

I'm getting this error when I open a workbook after making a change to a user form (adding a worksheet print preview)

Microsoft Visual Basic for Applications
Automation error
Catastrophic failure

Running Windows 10 (up to date) with MS Office Pro 2016 (up to date)

Looking at older posts I see that there may be a Reference library issue. I can not get to the References once the error occurs, but with a restored backup file I have the following enabled:
Visual Basic for Applications
Microsoft Excel 16.0 Object Library
OLE Automation
Microsoft Office 16.0 Object Library
Microsoft Forms 2.0 Object Library

The workbook started out as a 2013 xltm

I just don't get it, don't see how to fix this. Any ideas?

 

[dazyparker]

Read the following solutions:
https://social.technet.microsoft.co...ae7ad13/automation-error-catastrophic-failure

https://social.msdn.microsoft.com/F...mponent-built-with-vbnet-2003?forum=vbgeneral

 

[bnj1776]

Finally found that this was related to two User Defined Functions being used. Searched and searched, but no good solution out there other than a hint that you have to "hide" the UDFs.

Probably not the best solution, but here is how I solved it:

On closing the file, I scan every cell with a formula for a UDF (via a list of UDF names). If found, I first copy the formula to that cell's "comment" and then replace the formula with a bookmark. (I use a formula "bookmark" so I can scan for the bookmarks more quickly via 'ws.UsedRange.SpecialCells(xlCellTypeFormulas)'.)

If Not (cl.Comment Is Nothing) Then
     cl.Comment.Delete
End If
cl.AddComment
cl.Comment.Text cl.Formula
cl.Formula = "=Trim(""UDF saved in comment"")"

On opening the file, I scan every cell with a formula for the bookmark. If found, I the copy the formula in the "comment" to the cell's formula, replacing the bookmark formula.

cl.Formula = cl.Comment.Text

The error has not been back sense doing this, as crazy as it seems, it worked.

I am hoping that maybe I'm missing a better solution. If you know of one, please share.

Thank you,
Brian