How to retrieve Error Trapping options in VBA?

[lemonphy]

Hello!

How can we "read" information from the VBE Error Trapping options (Break on All Errors/ Break in Class Module/ Break in Unhandled Errors) programmatically?

Background: I am developping a spreadsheet that needs to be sent around to distant users. Some of them might not have the default "Break on Unhandled Errors" setting), which would just make my programme fail or risk to fail...

This issue has been covered in the thread below, but the proposed solution doesn't work on my side (always returning the same output!):
http://www.excelforum.com/excel-prog...s-excel-2.html

Hint: it has been suggested to look into the registry, which the linked post does.

Ideally, I would love to be able to change the setting programatically, but that seem near to impossible in excel 2007 VBA (unless calling an external programme that closes and restarts excel after having changed the option?...)

Thanks in advance for your answers, ideas and, not the least, your time! :-)

Phil

[mikeTRON]

Can you make the change then force close excel with a message to the user to reopen file as a setting needs to be updated?

[lemonphy]

Thanks Mike,
That's a good idea, but do you know how to implement it?
I already have problems looking for the settings. It involves reading in the registry. If it is also possible to write in the registry from VBA, then your idea would be perfect. But how (I unfortunately have no clue on the way the registry works...)?...

[lemonphy]

The macro below is supposed to solve the problem, but doesn't work for me. Does somebody have an explanation? Thanks!


Private Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long

Private Declare Function RegOpenKey Lib "advapi32.dll" _
Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" _
Alias "RegSetValueExA" (ByVal hKey As Long, _
ByVal lpValueName As String, ByVal Reserved As Long, _
ByVal dwType As Long, lpData As Any, _
ByVal cbData As Long) As Long

Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
Alias "RegQueryValueExA" (ByVal hKey As Long, _
ByVal lpValueName As String, ByVal lpReserved As Long, _
ByRef lpType As Long, lpData As Any, _
ByRef lpcbData As Long) As Long
Private Declare Function RegCreateKey Lib "advapi32.dll" Alias "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Const REG_DWORD = 4
Const HKEY_CURRENT_USER = &H80000001

Public Enum ErrorHandlingStatus
AllErrors = 1
ClassErrors = 2
UnhandledErrors = 3
End Enum
Function GetErrorHandlingState() As ErrorHandlingStatus
Dim hKey As Long, lngReturn As Long, lngDataLen As Long, lngKeyType As Long, lngCurrent As Long, success As Long
Dim varValue, varReturn
Dim strReturn As String

Const strAllErrors As String = "BreakOnAllErrors"
Const strClassErrors As String = "BreakOnServerErrors"
Const HKEY_CURRENT_USER = &H80000001

lngKeyType = REG_DWORD

' default state
GetErrorHandlingState = UnhandledErrors

RegOpenKey HKEY_CURRENT_USER, _
"Software\Microsoft\VBA\6.0\Common", hKey

If hKey > 0 Then
' check for all errors
lngCurrent = RegQueryValueEx(hKey, strAllErrors, 0, lngKeyType, lngReturn, 4)
If lngCurrent = 0 Then
If lngReturn = 1 Then
GetErrorHandlingState = AllErrors
Exit Function
End If
End If
' check for class errors
lngCurrent = RegQueryValueEx(hKey, strClassErrors, 0, lngKeyType, lngReturn, 4)
If lngCurrent = 0 Then
If lngReturn = 1 Then
GetErrorHandlingState = ClassErrors
Exit Function
End If
End If
RegCloseKey hKey
End If

End Function

[Kyle123]

Wouldn't you just be better off handling the errors properly in your code to begin with?

[lemonphy]

Kyle,
The errors are handled! Just, if the users have modified the error trapping settings, the error handlers won't be called...

[Kyle123]

Yes they will, for the below it doesn't matter what the user has selected:

Please Login or Register  to view this content.

[lemonphy]

Agreed for the cases when "Break on Unhandled Errors" and "Break in Class Module" are selected.

But your code gives an error if the VBE setting is set to "Break on All Errors": "Run-time error '11': Division by zero"

You will tell me that users who have changed these settings should (normally) be able to change them back. But having a more generic solution that at least detects the settings and instruct the users to change them manually would be nice

[Kyle123]

My advice is don't. Any user who is sufficiently advanced enough to have changed that setting will not thank you for faffing with his dev set up and you will cause irritation Worry about the 99% of users who don't know what they are doing - not the 1% who will know what the issue is and how to resolve it.

[lemonphy]

Ok, wise point of view

Without modifying any setting, wouldn't it be nice though to just warn the (advanced) users that their settings need to be at least temporarily modified to allow smooth operation of the application?

[lemonphy]

@Kyle
Finally, I'll adopt your solution and will just warn the users in a separate "manual". Thanks!