0

Without getting into the details of the code I have a VBA Excel program that shows a Userform at the user at some point in the code. This VBA Excel program is used for more then 10 years now without any problem. Suddenly from one day to another we get an automation error where Excel Finally Crashes. Using the Debug button is not possible because Excel Crashes.

enter image description here

It seems this problem is caused by a certain Office 365 update because my co-workers who uses Office 365 versions 2208 (last update 10 January/14 February 2023) gets the error from one day to the next. Co-workers who have Office 365 version 2302 last update (28 February 2023) don’t get the error.

First I could not troubleshoot the problem because it was not clear what did cause this error. Finally I could link this error to the UserForm that was shown to the user.

The strange thing was that when I set a breakpoint (F9) just at the line of code where the UserForm was shown and then continued running the code (F5) everything worked without problem!?

A the end by putting a “Timeout of a few seconds with a DoEvents” command in the code just before showing the Userform I could fix the problem. So it seems that Excel, for some reason, “loses the connection” with the Userform a some point. By putting a DoEvent in the code Excel “gets back his breath” an doesn’t lose the connection with the Usefrom?

Although I “fixed” the problem with a workaround I want to understand what’s happening here? Why doesn’t Excel manage this kind of disconnections itself and just Crashes? Why is it that when I put a breakpoint (F9) in the code or “Timeout of few seconds with a DoEvents commend” we don’t get to see the error? Any idea why we get this problem in Office version 2208 and not in Office version 2302?

From my point of view it looks like a Bug/Problem in Excel?

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Maxcit
  • 1
  • 2

3 Answers3

0

This is a widely spread issue when dealing with Office applications. For example, you could use an Excel object, release it (or disconnect), and use it again. Release objects (set them to Nothing) only after you're finished with it, or when calling Form_Closing.

You may find a similar thread helpful, see Excel VBA Automation Error: The object invoked has disconnected from its clients.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
0

Thanks for your feedback Eugene! I understand what you are saying. When you release an object the object is disconnected and when you use the released object again you get the automation error. In this case, in my opinion, it is a mistake in the logic of the VBA code.

I also have the same experience with this automation error in the past when using references to a worksheet. Code working fine for several years where I directly reference (non-full path) to a worksheet suddenly stops working. Referencing with a full path fixes the problem. For example I had to change “Sheets("Run").Select” to “Workbooks(“Name”). Sheets("Run").Select”.

In my case, from a programmatic point of view, it has nothing to do with released (or disconnected) objects. Nothing has changed in the code but suddenly after years the code stops working. Excel version 2302 works fine but version 2208 gives an error. I find the fact that putting a break via DoEvents in the code or setting a breakpoint (F9) fixes the problem very strange. I would expect that Excel catches/deals with this kinds of problems.

Maxcit
  • 1
  • 2
-1

Although i fixed the problem with a workaround by adding a timeout (via Timer) from a few seconds in combination with the DoEvents command i found a more Cleaner solution to my problem.

This was the workaround with the timeout (via Timer) en DoEvents commands:

Timeout and DoEvents solution

Start = Timer
Do While Timer < Start + PauseTime
    DoEvents
Loop

By adding the Thisworkbook.Activate in the Class of the Userform just before calling the command that shows the Userform the problem is also fixed.

Thisworkbook.Activate solution

Function Toon_Formulier()

ThisWorkbook.Activate
formulier.Show

End Function
Maxcit
  • 1
  • 2