VBAで文字列から配列を作る。配列の要素数取得関数作成

はじめに

VBAで文字列から特定の文字で区切って配列にできないか、またVBAには配列の要素数を取得する関数及びメソッドがないので、配列の要素数を取得する関数を作りました。

データ

以下のデータを「Sheet1」に作成しました。

ソースコード

ソースコードは以下の通りです。

Option Explicit
'配列テストマクロ
'新規作成  2024/6/4
Sub test1()
    '正規表現用インスタンスを呼び出す
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    
    Dim wb As Workbook
    Set wb = Workbooks(ThisWorkbook.Name)

    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")

    Dim test1, test2, test3, test4 As Variant
    test1 = ws.Cells(3, 2).Value
    test2 = ws.Cells(4, 2).Value
    test3 = ws.Cells(5, 2).Value
    test4 = ws.Cells(6, 2).Value
    Dim array1() As Variant
    array1 = Array(test1, test2, test3, test4)
    Dim start_num As Integer
    Dim end_num As Integer
    start_num = LBound(array1)
    end_num = UBound(array1)
    
    Dim i As Integer
    Dim answer As String
    Dim array_answer As Variant
    For i = start_num To end_num
        Dim workarray As Variant
        Dim result_num As Integer
        Dim start_count As Integer
        Dim end_count As Integer
        workarray = Split(array1(i), ",")
        start_count = LBound(workarray)
        end_count = UBound(workarray)
        Dim array_count As Integer
        array_count = elem_count(end_count, start_count)
        array_answer = array_answer & " " & array_count
        Dim j As Integer
        For j = start_count To end_count
           re.Pattern = "\("
            re.IgnoreCase = False
            re.Global = True
            If re.Test(workarray(j)) = True Then
                Dim w_array2 As Variant
                w_array2 = Split(workarray(j), "(")
                answer = answer + w_array2(0)
            Else
                answer = answer + workarray(j)
            End If
        Next j
    Next i
    ws.Cells(10, 3) = answer
    ws.Cells(11, 3) = array_answer
    
End Sub
'配列の要素数を計算する関数
Function elem_count(ByVal max_elem As Integer, min_elem As Integer)
    Dim elem_sum As Integer
    elem_sum = max_elem - min_elem + 1
    elem_count = elem_sum
End Function

実行結果

実行結果は下記のとおりです。

最後に

VBAでも他の言語のように文字列を配列に入れて処理できますので、ご活用くださると幸いです。