arrays - 如何使用vba在逗号之间分割字符串并将每个字符串存储在数组变量中

Question

我有一个问题: 如何在左括号和右括号内的逗号之间拆分字符串并将每个字符串存储在数组变量中？

示例:

strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c, s4 s4t_d, ...n )"

上面有一个输入字符串，我想将逗号之间的三个等子字符串存储在一个数组变量中，这些子字符串位于左括号和右括号内:

substr(0) = "u1 u1t_a"
substr(1) = "u2 u2t_b"
substr(2) = "s2 s2t_c"
substr(n) = "...n"

到目前为止，我在 VBA 中很难将循环与数组一起使用，所以我的代码就像一个蛮力，最多只能处理 3 个文本，因为代码会变得很长，所以我做了一个限制。

在此处查看我的代码:

strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c )"

substr1 = Right(strinput, Len(strinput) - Find("(", strinput))
    'Output: u1 u1t_a, u2 u2t_b, s2 s2t_c )
substr1f = Left(substr1, Find(",", substr1) - 1)
    'Output: u1 u1t_a

substr2 = Right(substr1, Len(substr1) - Find("(", substr1))
    'Output: u2 u2t_b, s2 s2t_c )
substr2f = Left(substr2, Find(",", substr2) - 1)
    'Output: u2 u2t_b

substr3 = Right(substr2, Len(substr2) - Find("(", substr2))
    'Output: s2 s2t_c )
substr3f = Left(substr3, Find(")", substr3) - 1)
    'Output: s2 s2t_c

如何制作这个循环？

Answer 1

这就是你想做的事吗？我已经对代码进行了注释，因此您在理解它时可能不会遇到问题...欢迎来到 stackoverflow :)

Option Explicit

Sub Sample()
    Dim Ar As Variant
    Dim strinput  As String, s As String
    Dim i As Long

    strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c, s4 s4t_d, ...n )"

    '~~> Replace ( and ) with ""
    s = Replace(Replace(strinput, ")", ""), "(", "")

    '~~> Split and store in an arry based on ","
    Ar = Split(s, ",")

    '~~> See what is there in the array
    For i = LBound(Ar) To UBound(Ar)
        Debug.Print Ar(i)
    Next i
End Sub

如果您想结合替换和拆分，那么您也可以使用它

Option Explicit

Sub Sample()
    Dim Ar As Variant
    Dim strinput  As String
    Dim i As Long

    strinput = "( u1 u1t_a, u2 u2t_b, s2 s2t_c, s4 s4t_d, ...n )"

    Ar = Split(Split(Split(strinput, "(")(1), ")")(0), ",")

    '~~> See what is there in the array
    For i = LBound(Ar) To UBound(Ar)
        Debug.Print Ar(i)
    Next i
End Sub