Vba: ‘On error’ 声明安全

Question

我制作了一个宏来记录，然后更改本地设置(在我的情况下是小数分隔符)。在宏结束时，它将恢复设置。

当出现错误时，我让程序也使用“on error”语句恢复本地设置。
(下面给出了一个简化的程序示例)

到目前为止，我没有遇到任何问题；但是，由于我现在计划将程序转移给我的工作同事，我真的希望不要干扰他们并覆盖他们自己的本地设置。
On error声明在这里足够安全使用并确保设置已恢复？

是否存在程序可能遇到 On error 的错误的情况？将无法重定向到错误处理程序？

PS:我已经知道我可以使用 String = Replace(Number, ",", ".") 转换我的所有数字。但由于某些原因，我无法承受宏的所有变量。

示例代码:

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

VBA 代码可以通过多种方式中断，有些甚至不涉及会在 MsgBox 上中断的聪明用户。并点击停止按钮:如果主机因与您的代码完全无关的事情而崩溃(想到一些 KB 更新)，在过程执行的中间，那么您的错误处理程序将不会被跳转到，您也无能为力做以防止这种情况。

不要篡改用户的本地设置。而是修复您的代码。

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

您的代码将数字视为字符串，反之亦然。如果数字来自工作表中的单元格，您可以将其读入 Double无需考虑用户的小数点分隔符是什么。

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

如果数字来自您制作的用户窗体上的文本框，并且您允许用户在代码要求逗号为点时输入逗号，那么您需要修复数据输入代码并添加一些输入验证以防止出现这种情况。一种方法是处理 KeyPress TextBox 的事件接收用户输入的控件 - 并“吞下”任何无效键:

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

如果号码来自 InputBox ，您将无法阻止用户输入他们想要的任何内容，但您仍然可以验证输入并防止错误输入进一步传播到您的代码中。

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

没有任何借口可以弄乱用户的控制面板设置。