Disabling VBE Break on All Errors Programmatically with DAO SetOpt

[Jsadleir]

Hi All

I want to disable progammatically the VBE "Break on all errors" option when
launching a VBA Add-in that contains handled errors. I read the Microsoft
Knowledge Base Article (ACC2000: How to Turn Off "Break on All Errors" Option
in Code
View) on the process that should be used to do this with Access. That
approach appears to work with Microsoft Excel VBA Add-ins if a reference to
DAO is included.

The approach is broadly to include the following two subs in the add-in:

Public Sub SuspendBreaks()
SetOption "Error Trapping", 2
End Sub

Public Sub ResumeBreaks()
SetOption "Error Trapping",0
End Sub

These two subs are then incorporated into your procedures as follows:
Function MyCodeModule()
SuspendBreaks
On Error GoTo MyCodeModule_Err
MyCodeModule_Exit:
ResumeBreaks
Exit Function
MyCodeModule_Err:
Resume MyCodeModule_Exit
End Function

I have read often that there is no reliable method of disabling
Break-On-All-Errors. As an inexperienced programmer, I was wondering if
anyone had tried this approach and knew whether it is reliable when the VBA
add-in is widely distributed. What are its limitations? Does it work or am I
mistaken? What are all the other options for the first argument that in this
case contains a string "Error Trapping" (but is meant to be a long)?

Any thoughts would be much appreciated.

Regards

Jeremy

 

[Jim Rech]

I have read often that there is no reliable method of disabling
Not so. When a VB project is protected (Tools, VBAProject Properties) error
handling is automatically set to Break on Unhandled Errors. After assigning
a password save, close and re-open the workbook/add-in before testing this.

 

[Jsadleir]

Thanks Jim - Actually you have answered this question for me before
correctly, but somewhere along the line I came to the concluion password
protecting the add-in wasn't a 100% block. I have just tried using the
password protection and a user-setting of break on all errors again, and have
to admit you are right. The break on all errors is ignored. So the Access
approach can be safely ignored.

Thanks again for your help.
--
Jeremy Sadleir

Not so. When a VB project is protected (Tools, VBAProject Properties) error
handling is automatically set to Break on Unhandled Errors. After assigning
a password save, close and re-open the workbook/add-in before testing this.