excel - 为什么我的 Visual Basic excel 代码在我的单元格中返回 #value

Question

为什么我的 Visual Basic excel 代码在我的单元格中返回 #value？我正在寻找从单元格 A1 中提取电子邮件地址。下面是我的代码

Function ExtractEmailAddress(s As String) As String
    Dim AtSignLocation As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-Z0-9._-]"

    'Get location of the @
    AtSignLocation = InStr(s, "@")
    If AtSignLocation = 0 Then
        ExtractEmailAddress = "" 'no email address is there
    Else
        TempStr = ""
        'Get 1st half of the email address
        For i = AtSignLocation - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                    TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        If TempStr = "" Then Exit Function
        'get 2nd half of the email address
        TempStr = TempStr & "@"
        For i = AtSignLocation + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    'remove trailing period if there is any
    If Right(TempStr, 1) = "." Then TempStr = _
        Left(TempStr, Len(TempStr) - 1)
    ExtractEmailAddress = TempStr
End Function

我还包括了 excel VBA 的屏幕截图。

截屏:



此外，这是它如何返回值的屏幕截图

返回值截图:

Answer 1

我在这里收到“无效的模式字符串”错误:

If Mid(s, i, 1) Like CharList Then

问题在于 CharList持续的:

Const CharList As String = "[A-Za-Z0-9._-]"

替换 A-Za-Z与 A-Za-z并且功能开始工作=)

Const CharList As String = "[A-Za-z0-9._-]"