使用正则表达式在 VBA 中拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28107005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Splitting String in VBA using RegEx
提问by lyk
I'm new to VBA and would like to seek some help with regards to using RegEx and I hope somehow can enlighten me on what I'm doing wrong. I'm currently trying to split a date into its individual date, month and year, and possible delimiters include "," , "-" and "/".
我是 VBA 新手，想在使用 RegEx 方面寻求一些帮助，我希望能以某种方式启发我了解我做错了什么。我目前正在尝试将日期拆分为单独的日期、月份和年份，可能的分隔符包括“,”、“-”和“/”。
Function formattedDate(inputDate As String) As String
Dim dateString As String
Dim dateStringArray() As String
Dim day As Integer
Dim month As String
Dim year As Integer
Dim assembledDate As String
Dim monthNum As Integer
Dim tempArray() As String
Dim pattern As String()
Dim RegEx As Object
dateString = inputDate
Set RegEx = CreateObject("VBScript.RegExp")
pattern = "(/)|(,)|(-)"
dateStringArray() = RegEx.Split(dateString, pattern)
' .... code continues
This is what I am currently doing. However, there seems to be something wrong during the RegEx.Split function, as it seems to cause my codes to hang and not process further.
这就是我目前正在做的事情。但是，在 RegEx.Split 函数中似乎有问题，因为它似乎导致我的代码挂起而无法进一步处理。
To just confirm, I did something simple:
为了确认，我做了一些简单的事情：
MsgBox("Hi")
pattern = "(/)|(,)|(-)"
dateStringArray() = RegEx.Split(dateString, pattern)
MsgBox("Bye")
"Hi" msgbox pops out, but the "Bye" msgbox never gets popped out, and the codes further down don't seem to get excuted at all, which led to my suspicion that the RegEx.Split is causing it to be stuck.
"Hi" msgbox 弹出，但 "Bye" msgbox 永远不会弹出，而且更下面的代码似乎根本没有被执行，这导致我怀疑 RegEx.Split 导致它被卡住。
Can I check if I'm actually using RegEx.Split the right way? According to MSDN here, Split(String, String) returns an array of strings as well.
我可以检查我是否真的以正确的方式使用 RegEx.Split 吗？根据 MSDN here， Split(String, String) 也返回一个字符串数组。
Thank you!
谢谢！
Edit: I'm trying not to explore the CDate() function as I am trying not to depend on the locale settings of the user's computer.
编辑：我试图不探索 CDate() 函数，因为我试图不依赖于用户计算机的区域设置。
回答by Florent B.
To split a string with a regular expression in VBA:
在 VBA 中使用正则表达式拆分字符串：
Public Function SplitRe(Text As String, Pattern As String, Optional IgnoreCase As Boolean) As String()
Static re As Object
If re Is Nothing Then
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.MultiLine = True
End If
re.IgnoreCase = IgnoreCase
re.Pattern = Pattern
SplitRe = Strings.Split(re.Replace(text, ChrW(-1)), ChrW(-1))
End Function
Usage example:
用法示例：
Dim v
v = SplitRe("a,b/c;d", "[,;/]")
回答by Oliver
Quoting an example from the documentation of VbScript Regexp: https://msdn.microsoft.com/en-us/library/y27d2s18%28v=vs.84%29.aspx
引用 VbScript Regexp 文档中的一个例子：https: //msdn.microsoft.com/en-us/library/y27d2s18%28v=vs.84%29.aspx
Function SubMatchTest(inpStr)
Dim retStr
Dim oRe, oMatch, oMatches
Set oRe = New RegExp
' Look for an e-mail address (not a perfect RegExp)
oRe.Pattern = "(\w+)@(\w+)\.(\w+)"
' Get the Matches collection
Set oMatches = oRe.Execute(inpStr)
' Get the first item in the Matches collection
Set oMatch = oMatches(0)
' Create the results string.
' The Match object is the entire match - [email protected]
retStr = "Email address is: " & oMatch & vbNewLine
' Get the sub-matched parts of the address.
retStr = retStr & "Email alias is: " & oMatch.SubMatches(0) ' dragon
retStr = retStr & vbNewLine
retStr = retStr & "Organization is: " & oMatch.SubMatches(1) ' xyzzy
SubMatchTest = retStr
End Function
To test, call:
要测试，请致电：
MsgBox(SubMatchTest("Please send mail to [email protected]. Thanks!"))
In short, you need your Pattern to match the various parts you want to extract, with the spearators in between, maybe something like:
简而言之，你需要你的 Pattern 来匹配你想要提取的各个部分，中间有 spearators，可能是这样的：
"(\d+)[/-,](\d+)[/-,](\d+)"
The whole thing will be in oMatch, while the numbers (\d) will end up in oMatch.SubMatches(0) to oMatch.SubMatches(2).
整个过程将在 oMatch 中，而数字 (\d) 将在 oMatch.SubMatches(0) 到 oMatch.SubMatches(2) 中结束。
