Excel VBA：儲存格棑除特定字元輸入

在Excel中由於 "資料驗證" 無法有效過濾特殊字元，如「(空隔)  \ / :*?<>∣」，並且造成文字輸入後判斷異常，所以改以Excel VBA搭配事件處理。
藉由「正規表示法」來比對特殊字元，完成此項功能。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "工作表1" Then
        If Target.Column = 4 Then
            執行 Target
        End If
    End If
End Sub

Sub 執行(Target As Range)
    Dim regex As Object, rangecell As Object
    Dim row As Integer, col As Integer
    col = Target.Column
    row = Sheets(1).Cells(65536, col).End(xlUp).row
    Set regex = CreateObject("VBSCRIPT.REGEXP")
    Set rangecell = Sheets(1).Cells(2, col).Resize(row - 1)
    With regex
        .Pattern = "[\x28\x29\x5C\x2F\x3A\x2A\x3F\x22\x3C\x3E\x7C|\s]+"
        .Global = True
        For Each s In rangecell
            If .test(s) Then
                MsgBox "儲存格 " & Target.Address(False, False) & " 輸入錯誤文字 " & "\/:*?""<>|"
                Set rangecell = Nothing
                Set regex = Nothing
                Exit For
            End If
        Next
    End With
    Set rangecell = Nothing
    Set regex = Nothing
End Sub

執行結果：