使用 RegEx 在 VBA 中拆分字符串 [英] Splitting String in VBA using RegEx

问题描述

我是 VBA 新手，想在使用 RegEx 方面寻求一些帮助，我希望能以某种方式启发我了解我做错了什么.我目前正在尝试将日期拆分为单独的日期、月份和年份，可能的分隔符包括、"、-"和/".

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

这就是我目前正在做的事情.但是，RegEx.Split 函数中似乎有问题，因为它似乎导致我的代码挂起而无法进一步处理.

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 弹出，但 "Bye" msgbox 永远不会弹出，更下面的代码似乎根本没有被执行，这导致我怀疑 RegEx.Split 导致它被卡住了.

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

我可以检查我是否真的以正确的方式使用 RegEx.Split 吗?根据 MSDN 此处，拆分(String, String) 也返回一个字符串数组.

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.

谢谢！

编辑:我试图不探索 CDate() 函数，因为我试图不依赖于用户计算机的区域设置.

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.

推荐答案

在 VBA 中使用正则表达式拆分字符串:

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

使用示例:

Dim v
v = SplitRe("a,b/c;d", "[,;/]")

这篇关于使用 RegEx 在 VBA 中拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！