パソコンスキルの教科書

パソコンスキルの教科書

東京大学大学院卒。博士課程に進学を志すも、担当教授と折が合わず、無職になる。医者を目指すも結局断念。田舎で派遣社員として働く。「スキルがなければ、仕事ももらえない」と悟り、ビジネススキルを学ぶ。プログラミング、英語を学び、一部上場企業へ転職。年間100時間以上の業務効率化を行い、社内講師に抜擢。海外の案件を担当し、数億円のプロジェクトに携わる。個人の事業でも、月売上100万を達成。現在は、自分の価値を高めるためのスキル向上支援を行う

マクロVBA配列の事例9|2次元配列,要素数,検索,格納,追加,初期化,引数

vba 2次元配列,要素数,検索,格納,追加,初期化,引数

エクセルマクロVBAの配列について事例を使って紹介します。

事例1|静的配列で値を格納し、Joinで結合する

Sub Sample01()
    
    '要素数が5の静的配列を宣言(静的配列の要素数は「0」から始まる)
    Dim myTanto(4) As Variant
    myTanto(0) = "A"
    myTanto(1) = "B"
    myTanto(2) = "C"
    myTanto(3) = "D"
    myTanto(4) = "E"
        
    MsgBox Join(myTanto, ", ")
End Sub

事例2|動的配列で値を追加し、Joinで結合する

Sub Sample02()
    Dim ws As Worksheet
    Dim cmax As Long, i As Long, j As Long
    Set ws = ThisWorkbook.Worksheets("設定")
    cmax = ws.Range("A65536").End(xlUp).Row

    'Variant型で変数を定義
    Dim myTanto() As Variant

    For i = 0 To cmax - 2
        'Variant型の変数tmpAryの要素数を再定義して値を代入(「Preserve」を指定することで、すでに配列内に格納されている値を保持)
        ReDim Preserve myTanto(i) As Variant
        
        'Variant型の変数tmpAryを配列に再定義して値代入
        myTanto(i) = ws.Range("A2").Offset(i, 0).Value
    Next
    
    MsgBox Join(myTanto, ", ")
End Sub

事例3|動的配列内の要素数を調べる

Sub Sample03()
    Dim ws As Worksheet
    Dim cmax As Long, i As Long, j As Long
    Set ws = ThisWorkbook.Worksheets("設定")
    cmax = ws.Range("A65536").End(xlUp).Row

    'Variant型で変数を定義
    Dim myTanto() As Variant
    
    For i = 0 To cmax - 2
        'Variant型の変数tmpAryの要素数を再定義して値を代入(「Preserve」を指定することで、すでに配列内に格納されている値を保持)
        ReDim Preserve myTanto(i) As Variant
        
        'Variant型の変数tmpAryを配列に再定義して値代入
        myTanto(i) = ws.Range("A2").Offset(i, 0).Value
    Next
    
    '「UBound(配列)」で要素数の最大値、「LBound(配列)」で要素数の最小値を取得
    '配列の要素数は「0」以外にも始まることがあり、一定ではない可能性があるため、最大値、最小値から計算で求めるのがおすすめ
    MsgBox "要素数の最大値 : " & UBound(myTanto)
    MsgBox "要素数の最小値 : " & LBound(myTanto)
    MsgBox "配列の要素数 : " & UBound(myTanto) - LBound(myTanto) + 1
End Sub

事例4|データを2次元配列で値を格納し、エクセルに書き込む

Sub Sample04()
    Dim ws As Worksheet
    Dim cmax As Long, i As Long, kingaku As Long
    Set ws = ThisWorkbook.Worksheets("サンプル")
    cmax = ws.Range("A65536").End(xlUp).Row
    
    '配列を定義してセル範囲の値を代入(セル範囲を配列に代入した場合、要素数は「0」ではなく「1」からはじまる)
    Dim myArray As Variant
    myArray = ws.Range("A1:E" & cmax).Value
    
    'セル範囲の値で作成した二次元配列を別のセル範囲の値に代入
    ws.Range("I11").Resize(UBound(myArray), UBound(myArray, 2)).Value = myArray    
End Sub

事例5|データを2次元配列で値を格納し、配列内検索し累積計算する

Sub Sample05()
    Dim ws As Worksheet
    Dim cmax As Long, i As Long, kingaku As Double
    Set ws = ThisWorkbook.Worksheets("サンプル")
    cmax = ws.Range("A65536").End(xlUp).Row
    
    '配列を定義してセル範囲の値を代入(セル範囲を配列に代入した場合、要素数は「0」ではなく「1」からはじまる)
    Dim myArray As Variant
    myArray = ws.Range("A1:E" & cmax).Value
    
    kingaku = 0
    
    For i = LBound(myArray) To UBound(myArray)
        If myArray(i, 3) = "A" Then
            kingaku = kingaku + myArray(i, 5)
        End If
    Next
    
    MsgBox kingaku
End Sub

事例6|2つの配列を比較し累積計算する

Sub Sample06()
    Dim ws As Worksheet
    Dim cmax As Long, i As Long, j As Long
    Dim str As String
    Dim kingaku As Double
    
    Set ws = ThisWorkbook.Worksheets("サンプル")
    cmax = ws.Range("A65536").End(xlUp).Row
    
    '配列を定義してセル範囲の値を代入(セル範囲を配列に代入した場合、要素数は「0」ではなく「1」からはじまる)
    Dim myArray As Variant
    myArray = ws.Range("A1:E" & cmax).Value
    
    Dim myTanto(4) As Variant
    myTanto(0) = "A"
    myTanto(1) = "B"
    myTanto(2) = "C"
    myTanto(3) = "D"
    myTanto(4) = "E"
    
    For j = LBound(myTanto) To UBound(myTanto)
    
        kingaku = 0
        
        For i = LBound(myArray) To UBound(myArray)
            If myArray(i, 3) = myTanto(j) Then
                kingaku = kingaku + myArray(i, 5)
            End If
        Next
        
        str = str & vbCrLf & myTanto(j) & ":" & kingaku
    
    Next
    
    MsgBox str    
End Sub

事例7|配列初期化にはEraseを使う

Sub Sample07()
    
    '文字列型の配列を定義して値を代入
    Dim myTanto(4) As Variant
    myTanto(0) = "A"
    myTanto(1) = "B"
    myTanto(2) = "C"
    myTanto(3) = "D"
    myTanto(4) = "E"
    
    MsgBox Join(myTanto, ", ")
    
    '配列を初期化(値をクリア)
    Erase myTanto
    MsgBox Join(myTanto, ", ")
    
End Sub

事例8|配列を引数にとる

Sub Sample08()
    
    '調整数型の配列を定義して値を代入
    Dim myTanto(4) As Variant
    myTanto(0) = "A"
    myTanto(1) = "B"
    myTanto(2) = "C"
    myTanto(3) = "D"
    myTanto(4) = "E"
    
    Call SampleFunction(myTanto)
    
End Sub

Function SampleFunction(ByRef myTanto() As Variant)
    '配列を引数にとる場合は、参照渡し(ByRef)でないとエラーとなる

    Dim i As Long
    Dim str As String
    
    For i = LBound(myTanto) To UBound(myTanto)
        str = str & vbCrLf & myTanto(i)
    Next

    '引数にとった配列の各値を表示
    MsgBox str    
End Function

事例9|Splitの結合

Sub Sample09()
    Dim i As Long
    Dim str As String
    Dim myArray As Variant
    myArray = Split("2019/04/30", "/")
    
    For i = LBound(myArray) To UBound(myArray)
        str = str & vbCrLf & myArray(i)
    Next
    
    MsgBox str    
End Sub