excel - How to force vba compile of worksheet code on worksheet.activate

From my experience, errors sometimes occur in VBA on other machines when You use in Your code some commands specific to x86 architecture when other machine is x64 and due to the fact that You use references (in VBA Tools->references) that are not available on other machines. Last cause that I've met were different security settings, e. g. maybe Your code uses sth that requires access to vba object model (in Excel File->Options->Trust center->Trust center settings->Macro settings). So first of all I recommend to check the Windows architecture on the suspected machine. Then check the references that You use in a workbook. And at the end check the trust center settings. I think that it might be very probable that You will not get the same errors on Your machine as they might be specific to only one machine (e. g. different system architecture, some specific references or trust center settings).

If the error is caused by a specific line in the code I understand that, e. g. there are two users of woorkbook and one deleted a tab, to which Your code reference and that causes the error. So I think that maybe a good approach for You will be to manage error. You can use On Error statements in vba, e. g. You have code that fires at workbook open and it runs three procedures from Modules, so You can do sth like this:

On Error GoTo Procedure1Error

here run procedure one

On Error GoTo Procedure2Error

here run procedure two

On Error GoTo Procedure3Error

here run procedure three

Exit Sub

Procedure1Error:

MsgBox ("Procedure 1 caused fatal error. Code was not executed.)
Exit Sub

Procedure2Error:

MsgBox ("Procedure 2 caused fatal error. Code was not executed.)
Exit Sub

Procedure3Error:

MsgBox ("Procedure 3 caused fatal error. Code was not executed.)
Exit Sub

End Sub

If You do not notify user about problems then You could use the just On Error resume next (Your code will continue to execute) but I do not recommend it.