学习日志 全民一起VBA提高篇 第十回 split拆解字符串，动态数组接受结果集

学习日志

全民一起VBA提高篇

第十回 split拆解字符串，动态数组接受结果集

相关知识点

1、split(待拆分字符,分隔符字符)
    
    将字符串按指定分隔符拆分为多个子串，
    需用数组接受结果集（动态数组）;
    再用for 循环将每个元素赋值给单元格
  
    例如：

    dim a() as string,i%
    a=split(s,",")
    for i = lbound(a) to upbound(a)
    next i
2、对数组a中每个元素的for循环，可用

    for each x in a 来避免使用数组上下标，x 为变体类型，免声明
    next x

3、定义数组时 可以先不声明下界，如


    dim a() as string,b() as string
    a()=split(trim(cells(3,2)))    a接受结果集，可以无需指明下界
    redim b()                      此处重定义数组b下界

动态数组接受split结果集，for循环取出各个元素

Option Explicit
Sub split1demo()
    Dim a() As String, i%, j%
    j = 3
    a = Split(trim(Cells(3, 2)), "，")
    For i = LBound(a) To UBound(a)
        If Trim(a(i)) <> "" Then
            Cells(j, 5) = a(i)
            j = j + 1
        End If
    Next i
End Sub

动态数组接受split结果集，for循环取出各个元素2

option explicit
Sub split2demo()
'改进 split1demo中的 for 循环
'为 for each x in a x必须为变体类型,故此处无需指定变量类型
    Dim a() As String, j%, x
    j = 3
    a = Split(Cells(4, 2), "，")
    For Each x In a
        If Trim(x) <> "" Then
            Cells(j, 3) = x
            j = j + 1
        End If
    Next x
End Sub

将数组a中非空元素，赋给数组b

option explicit
Sub split3demo()
    Dim a() As String, b() As String, x, i%    '此时不知道b中元素个数，可先不声明
    a = Split(Trim(Cells(4, 2)), "，")
'判断拆分出来的字符数组中非空的个数，赋给 i
    For Each x In a
        If Trim(x) <> "" Then: i = i + 1
        End If
    Next x
'给出数组b的下标界,必须                           在使用数组b前，必须指明b下界
    ReDim b(i - 1)
'由于i前面已用完，这里重新赋值，用于表示数组b的下标
    i = 0
    For Each x In a
        If Trim(x) <> "" Then
            b(i) = x
            i = i + 1
        End If
    Next x
End Sub

用instr确定分隔符位置，mid根据位置取字符

option explicit
Sub othersplit()
    Dim first As Long, last As Long, name As String
    Dim i&, k As String
    k = Trim(Cells(3, 2)): i = 3: first = 0
    Do While first < Len(k)
        last = InStr(first + 1, k, "，")
        If last > 0 Then
            name = Mid(k, first + 1, last - first - 1)
                If Trim(name) <> "" Then
                    Cells(i, 4) = name
                    i = i + 1
                End If
            first = last
        Else
            Cells(i, 4) = Mid(k, first + 1)
            Exit Do
        End If
    Loop
End Sub