excel - Splitting String in VBA using RegEx

Question

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.

Answer 1

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", "[,;/]")