VBA函数-从数组中删除回车和换行-代码审查

Question

我有一些代码可以很好地从包含分割字符串的数组中删除无效的换行符和回车。

我知道这不是写这段代码的最佳方式，所以我想把它传递给社区，以建议更好的实践。

的评论步骤1:该函数将接受一个字符串(strString)，并将每个字符加载到声明的数组(varStringArray)中。

步骤2:如果代码发现任何已知的换行符或回车符(Ascii码10,9和32)，则继续对数组进行重新排序，在此过程中删除这些字符。然后，它将布尔值(bolReloop)设置为True，启动一个重新循环。

步骤3:完成后，将新数组加载到函数string中。

社区有什么建议可以更有效地执行这段代码而不需要再循环吗?

注意:内置的excel函数不适合这个，因为它们往往会错过大多数回车和换行符

Function fnRemoveInvalidStrings(ByVal strString As String) As String
Dim I                       As Long
Dim J                       As Long
Dim bolReloop               As Boolean
Dim varStringArray()        As Variant

ReDim varStringArray(Len(strString) - 1)
'Step 1:

For I = 1 To Len(strString)
varStringArray(I - 1) = Mid$(strString, I, 1)
Next I

'Step 2:  
Reloop:
bolReloop = False

For I = LBound(varStringArray) To UBound(varStringArray)
If Asc(varStringArray(I)) = 10 Or Asc(varStringArray(I)) = 9 Or Asc(varStringArray(I)) = 32 Then
For J = I To UBound(varStringArray) - 1
varStringArray(J) = varStringArray(J + 1)
Next J
ReDim Preserve varStringArray(LBound(varStringArray) To UBound(varStringArray) - 1)
bolReloop = True
Exit For
End If
Next I

If bolReloop = True Then
GoTo Reloop
End If
'Step 3:
fnRemoveInvalidStrings = Join(varStringArray)
fnRemoveInvalidStrings = Replace(fnRemoveInvalidStrings, " ", "")

End Function

Answer 1

如前所述，Replace()做得很好。这里有一个可能的通用解决方案来消除字符串中不需要的字符/字符串:

Dim sTest As String, sResult As String
Dim asInvalid(1 To 4) As String
sTest = "G[[j:273lub4mxlS<g;mQgQohYd5V_tcHz3pg7N5lof6T=an01WFdTE=<CBnU;:m8bC?KTq?t2ItiTLB64QNT>NALC]VNfC7>mm8vJQ3R:68t:UM8ERe<np:<?8Zx["
' Remove all occurances of '[', ';', '='  and 'an01WFdTE' from the string
asInvalid(1) = "["
asInvalid(2) = ";"
asInvalid(3) = "="
asInvalid(4) = "an01WFdTE"
sResult = RemoveInvalidStrings(sTest, asInvalid())
Debug.Print "Source: "; sTest
Debug.Print "Len(Source): "; Len(sTest)
Debug.Print "Result: "; sResult
Debug.Print "Len(Result): "; Len(sResult)

' Removes characters considered to be 'invalid' from a string
' sSource - source string
' asInvalidStrings() - list of invalid characters/strings
Function RemoveInvalidStrings(ByVal sSource As String, asInvalidStrings() As String) As String

Dim i As Long

For i = LBound(asInvalidStrings) To UBound(asInvalidStrings)
Do While InStr(sSource, asInvalidStrings(i)) > 0
sSource = Replace(sSource, asInvalidStrings(i), vbNullString)
Loop
Next i
RemoveInvalidStrings = sSource

End Function

Answer 2

这似乎是一个清理输入字符串的函数。您可能想要考虑以下函数?

Function CleanTrim(ByVal Text As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
' https://excelfox.com/forum/showthread.php/155-Trim-all-Cells-in-a-Worksheet-VBA#post1092
' Code modified from that by Rick Rothstein
Dim i As Long, CodesToClean As Variant
CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
If ConvertNonBreakingSpace Then Text = Replace(Text, Chr(160), " ")
For i = LBound(CodesToClean) To UBound(CodesToClean)
If InStr(Text, Chr(CodesToClean(i))) Then Text = Replace(Text, Chr(CodesToClean(i)), "")
Next i
CleanTrim = Application.WorksheetFunction.Trim(Text)
End Function

Answer 3

感谢大家的意见，我从Tragamor, Scott Craner和Hel O'Ween这三个人的作品中选取了例子。

这使得下面的代码得到了很大的改进，感谢您的贡献和输入!

Function fnRemoveCarriageReturns(ByVal strString As String) As String
Dim varChrArray(2) As String
varChrArray(0) = Chr(9)
varChrArray(1) = Chr(10)
varChrArray(2) = Chr(13)
Dim I As Long
fnRemoveCarriageReturns = strString
For I = LBound(varChrArray) To UBound(varChrArray)
If InStr(1, strString, varChrArray(I)) Then
fnRemoveCarriageReturns = Replace(strString, varChrArray(I), "")
End If
Next I
End Function