Vba: ‘On error’ statement safety

Question

I made a macro that records, then changes the local settings (in my case the decimal separator). At the end of the macro it would restore the settings.

When there is an error I make the program also restore the local settings using the ‘on error’ statement. (A simplified example of the program is given below)

So far I got no issues; however, as I am now planning to transfer the program to my working colleagues, I really wish to not interfere with them and overwrite their own local settings.

Does the On error statement is here safe enough to use and make sure that the settings are restored?

Is there any case where the program could run into an error that the On error would fail to redirect to the error handler?

PS: I already know I can convert all my numbers using String = Replace(Number, ",", ".") but for some reasons I cannot afford to go through all the many variables of the macro.

Example Code:

Private Declare Function GetUserDefaultLCID% Lib "kernel32" ()
Private Declare Function GetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String, ByVal cchData As Long) As Long
Private Declare Function SetLocaleInfoA Lib "kernel32" (ByVal Locale As Long, ByVal LCType As Long, ByVal lpLCData As String) As Boolean

Sub test()
  ' what to do on error
  On Error GoTo ErrorHandler:
  ' define a number (Your local settings are set up to
  Dim MyNumber As Single
  MyNumber = 0.03
  MsgBox ("Number is 0,03 ->" & MyNumber)
  ' record the settings in the variable LocalSettingsDecimal
  Dim LocalSettingsDecimal As String
  Dim Buffer As String
  Buffer = String(256, 0)
  Dim le As Integer
  le = GetLocaleInfoA(GetUserDefaultLCID(), 14, Buffer, Len(Buffer))
  LocalSettingsDecimal = Left(Buffer, le - 1)

  ' force decimal settings to '.'
  Call SetLocaleInfoA(GetUserDefaultLCID(), 14, ".")

  ' Now is the program body
  MsgBox ("Number is now 0.03 ->" & MyNumber)
  ' an unfortunate error:
  MyNumber = "aa"

  Call SetLocaleInfoA(GetUserDefaultLCID(), 14, LocalSettingsDecimal)
  MsgBox ("Number should be back 0,03 ->" & MyNumber)
  Exit Sub
  ErrorHandler:
  Call SetLocaleInfoA(GetUserDefaultLCID(), 14, LocalSettingsDecimal)
  MsgBox ("There was an error but it's ok, it should be back to 0,03 ->" & MyNumber)
End Sub

Answer 1

There are many ways VBA code can get interrupted, some not even involving a clever user that would break on a MsgBox and hit the Stop button: If the host crashes for of something completely unrelated to your code (some KB updates come to mind), in the middle of a procedure's execution, then your error handlers won't be jumped into and there's nothing you can do to prevent that.

Don't tamper with user's local settings. Fix your code instead.

I already know I can convert all my numbers using String = Replace(Number, ",", ".")

Your code is treating numbers as strings, or vice-versa. If the number came from a cell in a worksheet, you can read it into a Double without having to think about what the user's decimal separator is.

Dim myNumber As Double
With ActiveSheet
    If IsNumeric(.Range("A1").Value) And Not IsError(.Range("A1").Value) Then myNumber = CDbl(.Range("A1").Value)
    'myNumber contains a valid numeric value
End With

If the number came from a textbox on a UserForm you crafted, and you allowed the user to enter a comma when your code requires that to be a dot, then you need to fix your data entry code and add some input validation to prevent that. One way to do this is to handle the KeyPress event of the TextBox control that's receiving the user's input - and "swallow" any invalid keys:

Private Sub txtInput_KeyPress(ByVal keyAscii As MSForms.ReturnInteger)
    If Not IsValidKeyAscii(keyAscii, txtInput.Value) Then keyAscii = 0
End Sub

Private Function IsValidKeyAscii(ByVal keyAscii As Integer, ByVal value As String) As Boolean
'returns true if specified keyAscii is a number, or if it's a dot and value doesn't already contain one
    IsValidKeyAscii = (keyAscii = vbKeyDot And InStr(1, value, Chr$(vbKeyDot)) = 0) Or (keyAscii >= vbKey0 And keyAscii <= vbKey9)
End Function

If the number came from an InputBox, you won't be able to prevent the user from entering whatever they want, but you can still validate the input and prevent bad input from being propagated further into your code.

Private Function PromptForNumber() As Double
    Dim isValid As Boolean
    Dim userInput As String
    Do
        userInput = InputBox("Enter a decimal number:")
        isValid = IsNumeric(userInput) And InStr(userInput, ",") = 0
        If Not isValid Then MsgBox "Value '" & userInput & "' is not numeric. Please make sure to use [en-US] decimal separator."
    While Not isValid
    PromptForNumber = CDbl(userInput)
End Function

There's simply no excuse for messing with the user's Control Panel settings.

Answer 2

If the End command is encountered anywhere in the code then this will foil your plan.

Take a look at the example below. If A calls B then no problem, errors are handled and when control returns to A the handler will still execute your locale reset. But uncomment the call to C and run the code again. Execution simply stops and control never returns to A so the reset is not performed.

So your scenario is that you send the workbook to your colleague and then they to add on some more logic which uses the End command for whatever reason.

Option Explicit

Sub A()
    On Error GoTo ErrHandler

    Dim foo As Long

    'call B
    B
    ' call C - uncomment to see impact
    'C

ErrHandler:
    Debug.Print "Error occurred in A"
    Debug.Print "Fixing locale stuff..."

End Sub

Sub B()
    On Error GoTo ErrHandler

    Dim foo As Long

    'cause an error
    foo = "bar"

ErrHandler:
    Debug.Print "Error occurred in B"
    Debug.Print Err.Description

End Sub

Sub C()
    On Error GoTo ErrHandler

    Dim foo As Long

    'cause an error
    foo = "bar"

ErrHandler:
    Debug.Print "Error occurred in C"
    Debug.Print Err.Description
    End

End Sub