我正在尝试想出一个函数来从字符串中捕获数字。我自己想出的东西目前用于以下代码:
Function ArrayOfCleanedString(strIn As String) As Variant
Dim objRegex As Object, objAllMatches As Object, sAllMatches() As String, v As Variant, i As Long
Set objRegex = CreateObject("vbscript.regexp")
objRegex.Global = True
objRegex.IgnoreCase = False
'objRegex.Pattern = "^(?:(?i)(?:[+-]?)(?:(?=[0123456789]|[.])(?:[0123456789]*)(?:(?:[.])(?:[0123456789]{0,}))?)(?:(?:[E])(?:(?:[+-]?)(?:[0123456789]+))|))$"
'objRegex.Pattern = "^-?(?:(?:0|[1-9][0-9]*)(?:,[0-9]+)?|[1-9][0-9]{1,2}(?:,[0-9]{3})+)$"
objRegex.Pattern = "-?d+[" & Application.DecimalSeparator & ".]?d*"
Set objAllMatches = objRegex.Execute(strIn)
If objAllMatches.Count <> 0 Then
ReDim sAllMatches(1 To objAllMatches.Count)
For Each v In objAllMatches
i = i + 1
sAllMatches(i) = v
Next
ArrayOfCleanedString = sAllMatches
Else
ArrayOfCleanedString = Array()
End If
End Function
Sub test()
Dim v As Variant, v2 As Variant
v2 = ArrayOfCleanedString("1abc-10.1-1abx1,1o1.")
'v2 = ArrayOfCleanedString("")
If Not IsArrayEmpty(v2) Then
For Each v In v2
Debug.Print CStr(v)
Next
End If
End Sub
但是,此代码确实存在一个问题,因为它在数字末尾捕获标点符号/逗号,即使它后面没有数字。
我做了一些搜索,找到了我在这篇文章中尝试过的另外两种模式,但正如您可能猜到的那样,它们在 VBA :)中不起作用
我会尝试修改它们,但考虑到我并不真正理解它们,这有点棘手。
所以我要问的是; 如果标点符号/逗号后面没有数字,是否有任何简单的方法可以从匹配的末尾去除标点符号/逗号?
或者,有没有办法将其他两种模式中的任何一种转换为 VBA 的正则表达式品牌?
我的方法还有其他明显的缺陷吗?我对此很陌生,所以我做了很多反复试验:P
哦,如果你们中的任何人想知道我的代码中的isEmptyArray方法,它是从Chip Pearson的VBA数组函数页面复制的。
您可以使用已发布的正则表达式,只需在返回数组之前从数组的元素中删除尾随的".":
Function ArrayOfCleanedString(strIn As String) As Variant
Dim objRegex As Object, objAllMatches As Object, sAllMatches() As String, v As Variant, i As Long
Set objRegex = CreateObject("vbscript.regexp")
objRegex.Global = True
objRegex.IgnoreCase = False
'objRegex.Pattern = "^(?:(?i)(?:[+-]?)(?:(?=[0123456789]|[.])(?:[0123456789]*)(?:(?:[.])(?:[0123456789]{0,}))?)(?:(?:[E])(?:(?:[+-]?)(?:[0123456789]+))|))$"
'objRegex.Pattern = "^-?(?:(?:0|[1-9][0-9]*)(?:,[0-9]+)?|[1-9][0-9]{1,2}(?:,[0-9]{3})+)$"
objRegex.Pattern = "-?d+[" & Application.DecimalSeparator & ".]?d*"
Set objAllMatches = objRegex.Execute(strIn)
If objAllMatches.Count <> 0 Then
ReDim sAllMatches(1 To objAllMatches.Count)
For Each v In objAllMatches
i = i + 1
sAllMatches(i) = v
If Right(sAllMatches(i), 1) = "." Then
sAllMatches(i) = Left(sAllMatches(i), Len(sAllMatches(i)) - 1)
End If
Next
ArrayOfCleanedString = sAllMatches
Else
ArrayOfCleanedString = Array()
End If
End Function