Excel-VBA Error - Automation Error: Fatal Error

Question

Very dear team,
dear community,
I have a strange situation with Excel 2016.
We use various Excel files here in the office, all with the release for our network, all with VBA macros or VBA modules for shortcuts (such as CTRL+R to create invoices). The shortcuts also work perfectly.
However, the problem is that in 2 files I very often fail to get in and get an "Automation Error: Fatal Error" error. Then I can only close the excel file via task manager.
Because the release is active, I can't see exactly where the error is, because I can't get into the modules with the release and Excel doesn't show me the modules either.
If I remove the network share, I can get into the file without an error message (assuming I go into the file from another computer to remove the share because I can't open it with my two). If I then check the modules, no error is displayed.
This problem occurs on 2 out of 7 computers in the office.
Do you have an idea what could be the reason?
Previous attempts:
Repair Office - Without success.
Reinstall Office - success for 2 days
Removed macros or modules - success (but no solution because we need the macros!)
Have the "programmer" check the macros - everything is correct
Many thanks for your help.

Answer 1

Sorry again for the delay. It took me some time, but i found a solution.

None of your answers worked for my Problem, but i found the solution.

Somehow the file was "corrupted" and i had to extract all my data in to a new file with this method:

[https://support.microsoft.com/en-us/office/repair-a-corrupted-workbook-153a45f4-6cab-44b1-93ca-801ddcd4ea53#:~:text=Klicken%20Sie%20auf%20Datei%20%3E%20%C3%96ffnen,dann%20auf%20%C3%96ffnen%20und%20reparieren.

Now my Files work on all of our Systems just fine (as far as i can tell by now - time will tell).

But thanks to all of you for the support. This Question can be closed now.

Answer 2

In my case, the problem occurs with a digital signed Excel VBA file. The Excel-sheet contains several self-written VBA functions that are used as formulas in cells. If I remove the formulas with the self-written functions, then there is no automation crash. Interestingly, the problem does not arise when the file is opened on a PC that does not require signed macros. The problem also occurs in other Excel VBA files where I have own code in the VBA macro "open"-function that accesses "something" like worksheets or the Internet. The cause seems - according to my interpretation - to be that VBA functions are already being executed during the opening process and the verification of the security certificate or macro security settings is a parallel processing that has not yet been completed. The Excel macro file is from 2020 and has been running successfully for 2 years. The problem first appeared in the summer of 2022, with no code change at that time - presumably after Office updates.

Answer 3

An automation error could occur when you are referring to a workbook or worksheet via a variable, but the variable is no longer active. Make sure any object variables that you are referring to in your code are still valid when you call the property and methods that you are controlling them with.

Answer 4

The issue is with the macros and could be just about anything. Automation errors can occur at runtime for a variety of reasons. Without any knowledge of the macros then we would have no way of diagnosing this. These are runtime errors (network blips, odd system behavior, etc) and is unlikely to be noticeable just by looking at the code.

You're going to need to enable more verbose logging when an error occurs. Unfortunately it depends where the error is occurring as to how you do that. I might start by looking in the Event Viewer to see if it logged any useful error messages. That would be the ideal situation.

If not then I'd modify each macro to wrap the errors and display useful information (or log it somewhere) so you can review it. I'm assuming here the default of "break on unhandled error" is already set and that is why you're seeing the message box. But it doesn't tell you anything useful. Therefore you need to capture the error and display it instead. There is a lot involved in doing this so I'm going to link to this article on how you might do that. Given the choices I'd lean toward the on error goto label approach. Then use Err.Description to get the error but I suspect that it is that description that you're already seeing so it is not useful.

This is where you need to understand your macro. If your macro is making calls into Excel then that is likely where the error is occurring. You could wrap each separate call into Excel with a custom label but that seems like overkill. I might recommend that you create a simple step variable in the macro and update the step variable as your code executes. If an error occurs then include the step in the message so you can figure out where things are failing. A pseudo example (not valid VBA).

   Dim step As String  
     
   On Error GoTo OnError  
     
   Set step = "Getting range"  
   Get range from Excel...  
     
   Set step = "Calculating value"  
   Calculating value from Excel...  
     
   Set step = "Updating spreadsheet"  
   Updating spreadsheet...  
     
   OnError:  
   MsgBox.Err.Description & vbCrLf & "Step: " & step  

Answer 5

You can have problem with files with code then can be a new security rules.
Add new definition to control panel/internet interface like path to SharePoint or intranet servers:

Regards.