我正在寻找在 Excel（2010，Visual Basic 7）中创建一个名为 CODEMATCH 的用户定义函数，它将
1. Take a range as an input
2. Preserve the structure of the range when outputting
3. On each value in the range:
a. If the value matches [matchPattern]:
i. regex match the value against [matchPattern] and store it as [var1]
ii. regex replace [var1] against [stripPattern] and store it as [var2]
iii. return [var2]
b. If the value does not match [matchPattern]:
i. return an empty value
Where
matchPattern = "^[^A-Z0-9:]*[A-Z0-9][^A-Z0-9:]*[A-Z0-9]?"
stripPattern = "[^A-Z0-9]*"
AndWhere
RegEx match is not global and respects case
RexEx replace is global and respects case
Such that
"Nobody Cares about Bob" returns "NC"
"1 Duck for Jody" returns "1D"
"Apples: I Don't Like Them" returns "A"
"foobar" returns ""
我的痛苦之一是我是 Visual Basic 的新手。我认为，我的部分痛苦来自于 Visual Basic 中存在多个版本的 RegEx，并且不知道哪个版本采用什么属性。
我尝试以复杂程度来构建该函数，这是我在遇到无法穿透的砖墙之前所能达到的极限：
Function CODEMATCH(ByVal valueIN As String) As String
Set matchRegEx = New RegExp
matchRegEx.Pattern = "(sdi \d+)" '<--what's giving me difficulty
matchRegEx.Global = False
matchRegEx.IgnoreCase = False
Set matches = matchRegEx.Execute(valueIN)
If matches.Count <> 0 Then
CODEMATCH = matches.Item(0).SubMatches.Item(0)
Else
CODEMATCH = ""
End If
End Function
代码是有效的，但它不允许我使用matchPattern我之前定义的。除此之外，我仍然需要采用它来执行正则表达式替换，并采用它来处理范围而不是单个单元格。
这怎么办？ :)
Function CODEMATCH(ByVal valueIN As String) As String
Dim strTemp As String
Set matchRegEx = New RegExp
With matchRegEx
.Pattern = "[^A-Z0-9:]*[A-Z0-9][^A-Z0-9:]*[A-Z0-9]?"
.Global = False
.IgnoreCase = False
If .Test(valueIN) Then
Set matches = .Execute(valueIN)
.Pattern = "[^A-Z0-9]*"
.Global = True
strTemp = matches(0)
CODEMATCH = .Replace(strTemp, vbNullString)
Else
CODEMATCH = vbNullString
End If
End With
End Function
test
Sub Test()
Debug.Print CODEMATCH("Nobody Cares about Bob")
Debug.Print CODEMATCH("1 Duck for Jody")
Debug.Print CODEMATCH("Apples: I Don't Like Them")
Debug.Print CODEMATCH("foobar")
End Sub
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
