Macros stop working an unknown reason.

VytautasM

New Member
Joined
Jan 31, 2020
Messages
30
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Good day All,

Have a work project with which I am at my wits end. In short. There 16 worksheets for 16 different users. Each week has a new workbook. Every workbook is uploaded to a Sharepoint page. The workbook is restricted it has locked cells, protected and hidden sheets. Also it contains workbook_open, worksheet_change and other macros.

The problem. At random all macros in a users workbook stop working for no apparent reason. For some this problem does not exist. For some this problem occurs on random weeks. Other weeks works fine. There is no error upon opening the workbook, but workbook_open and worksheet_change macros do not work. For some upon opening the workbook, workbook_open macros work, but as soon as they trigger a worksheet_change macro, the workbook goes into auto recovery mode, after which ALL excel files crash. After they inform me of the problem, I open the workbook from my PC with no problems, everything working as intended. On some cases re uploading the workbook and telling the user to restart their pc seems to solve the problem.

I have gone threw my code and have not found anything to cause the problem (of course my knowledge is limited so might be wrong there). If there was something wrong with the code the problem would be constant every week for every user and this is not the case. Searched the internet, but have not found anything to solve the problem. Do not really know how to handle the problem since a cannot replicate the problem.

Any help would be appreciated.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
Could you please give an example of an error handling code ?
Usually, you put a line like this at the beginning of a procedure:
VBA Code:
    On Error GoTo err_chk
which basically tells it if an error happens, go to the error handling code.

Then, at the very bottom of the procedure, before the "End Sub", you mught have a block something like this:
VBA Code:
    Application.EnableEvents = True
    Exit Sub
    
err_chk:
    MsgBox Err.Number & ": " & Err.Description
    Application.EnableEvents = True
The "Exit Sub" is there so if you get to that line naturally, without any errors, it will exit the sub and not run the error handling code.
Our error handling code in this example has two lines, one to return the details of the error to the screen in a Message Box, and one to re-enable events.

You can actually use this to capture specific error codes, if you want too. For example, if you did no care about "1004" errors, you could do something like this:
VBA Code:
err_chk:
    If err.number = 1004 Then
        err.Clear
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
        Application.EnableEvents = True
    End If
So if error 1004 is encountered, it tells the code to clear the error and continue on with the Macro.
That is more traditionally how error handling is often used, to "handle" specific errors. But in this case, we simply want to make sure that if any error is encountered where it is kicking you out of your code, that we make sure that events are re-enabled.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

VytautasM

New Member
Joined
Jan 31, 2020
Messages
30
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Usually, you put a line like this at the beginning of a procedure:
VBA Code:
    On Error GoTo err_chk
which basically tells it if an error happens, go to the error handling code.

Then, at the very bottom of the procedure, before the "End Sub", you mught have a block something like this:
VBA Code:
    Application.EnableEvents = True
    Exit Sub
  
err_chk:
    MsgBox Err.Number & ": " & Err.Description
    Application.EnableEvents = True
The "Exit Sub" is there so if you get to that line naturally, without any errors, it will exit the sub and not run the error handling code.
Our error handling code in this example has two lines, one to return the details of the error to the screen in a Message Box, and one to re-enable events.

You can actually use this to capture specific error codes, if you want too. For example, if you did no care about "1004" errors, you could do something like this:
VBA Code:
err_chk:
    If err.number = 1004 Then
        err.Clear
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Description
        Application.EnableEvents = True
    End If
So if error 1004 is encountered, it tells the code to clear the error and continue on with the Macro.
That is more traditionally how error handling is often used, to "handle" specific errors. But in this case, we simply want to make sure that if any error is encountered where it is kicking you out of your code, that we make sure that events are re-enabled.
Thank you, Joe4. Will try to implement error handling.

As I was thinking where the
VBA Code:
Application.EnableEvents = False
might be triggered. This occurred to me. I prepare each file with a separate macro, it opens a Master File, changes 1 cell in that file (week number) and then "saves as" the file with a certain name. And this cycle continues 16 times. Might the problem be that when changing that 1 cell the
VBA Code:
worksheet_change
macro is triggered it goes threw a certain amount of code triggering
VBA Code:
Application.EnableEvents = False
line and file is closed before it reaches the
VBA Code:
Application.EnableEvents = True
line. Thus upon a user opening the file the macros are off. And for some the
VBA Code:
worksheet_change
manages to run all the way threw thus the macros work fine. Does this sound even plausible ? Might adding
VBA Code:
Application.Wait (Now + TimeValue("0:00:05"))
after a cell is changed solve the problem ?
 

VytautasM

New Member
Joined
Jan 31, 2020
Messages
30
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Good day,

An update. Debugged my code found 3 errors, but no Application.EnableEvents missing. Also implemented Joe4 suggested error check. Furthermore checked my own theory and added Application.Wait code. This did help. The first time opening the file no users got an error. But when opening the file a second or third time the same problem occurred and the macros stopped work. But this time a error messaged indicated that a library was missing. To be more precise two libraries. Always the same ones:
Untitled.png


After unchecking the missing libraries, macros start working again. As I understand the missing libraries are due to users having different MS Office versions. But still does not answer the question why opening the first time all macros work perfectly. My question would be is there a solution for this apart from installing same MS Office versions for users ?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top