错误类型与回车符和换行符不匹配

Question

我写了一个函数来接收一个字符串并返回它，并清除除空格以外的所有空格。当空格位于字符串的开头或结尾，或者在另一个空格之后或之前时，它们也会被清除。

例如 ，"_a_____a"将变为"a_a"。（"_"表示空格）

我正在运行以下查询：

UPDATE table1
SET field1 = whitespace(field1)

出于某种原因，当字符串以回车符或换行符开头时，我收到类型不匹配错误。

Public Function whiteSpace(ByVal field As String) As String
    Dim i As Integer
    If (IsNull(field)) Then
        field = ""
        GoTo catchNulls
    End If
    field = RegexReplace(field, "(?=s)[^ ]", " ")
    field = Trim(field)
    field = RegexReplace(field, "  +", " ")
catchNulls:
    whiteSpace = field
End Function 
Function RegexReplace(ByVal text As String, _
                      ByVal replaceWhat As String, _
                      ByVal replaceWith As String, _
                      Optional ByVal ignoreCase As Boolean = False) As String
    On Error GoTo catch
    Dim RE As Object
    Dim str As String
    str = Empty
    Set RE = CreateObject("vbscript.regexp")
    RE.ignoreCase = ignoreCase
    RE.pattern = replaceWhat
    RE.Global = True
    str = RE.Replace(text, replaceWith)
continue:
    RegexReplace = str
    Exit Function
catch:
    Call raiseError(Err.Number, Err.Source, Err.Description)
    GoTo continue
End Function

我尝试将Public Function whiteSpace(ByVal field As String) As String换成Public Function whiteSpace(ByVal field As Variant) As String，但没有收到错误，但带有回车和换行的字段保持不变。

Answer 1

您的正则表达式不正确。尝试使用此函数来规范化空格：

''
' Strips all the white-spaces from a string and replaces any sequence
' of white-space characters by a single space.
''
Function NormalizeSpace(text As String) As String
  Static re As Object
  If re Is Nothing Then
    Set re = CreateObject("VBScript.RegExp")
    re.Global = True
    re.Pattern = "s+"
  End If
  NormalizeSpace = VBA.Trim$(re.replace(text, " "))
End Function