9

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 "/".

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.

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.

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.

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.

Improve this question
9
  • Do you really need to use RegEx for this? Would CDate not fit the bill? Commented Jan 23, 2015 at 9:50
  • First, Split is not a method of Regex. Second, the reference you linked is VB.Net, not VBA. There are lots of answers in SO on Regex in VBA, and also on the Split function. Commented Jan 23, 2015 at 9:59
  • @paul I'm trying not to depend on the locale settings on the computer due to some complicated reasons though. But how would CDate help to split it up? Commented Jan 23, 2015 at 10:02
  • @chrisneilsen oh, it's not the same thing? Pardon for my own confusion then. I've been trying to find it in SO for Regex Split in VBA to no avail though, but thank you, I will try to continue searching Commented Jan 23, 2015 at 10:04
  • 2
    datVar = CDate("10/11/12") then Year(datVar), Month(datVar), Day(datVar) Commented Jan 23, 2015 at 10:08

3 Answers 3

Reset to default
19

To split a string with a regular expression in 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", "[,;/]")
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

nice - but bad results if the string already contains vbNullChar characters.
how to return MsgBox ( v)? I get Run-time 13 error. Type mismatch.
What is String(). Is this a function that returns another function of type string?
Please explain more about how this works, like what is ChrW() for?
@chikitin That's because the result of function is an array of strings, not a regular sting. You have to display them in a loop.
1

Splitting by a regex is definitely nontrivial to implement compared to other regex operations, so I don't blame you for being stumped!

If you wanted to implement it yourself, it helps to know that RegExp objects from Microsoft VBScript Regular Expressions 5.5 have a FirstIndex property and a Length property, such that you can loop through the matches and pick out all the substrings between the end of one match (or the start of the string) and the start of the next match (or the end of the string).

If you don't want to implement it yourself, I've also implemented a RegexSplit UDF using those same RegExp objects on my GitHub.

Improve this answer

Comments

-1

Quoting an example from the documentation of 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:

"(\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).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.