Mutable_Yunの業務改善ブログ

業務改善や生産性向上のブログです。自動化の手段として、VBAやRPAの勉強に役立つ解説をしています。

VBA 配列を使うことによってFunctionプロシジャで複数の戻り値を返す

VBAのFunctionプロシジャは一つの戻り値しか返せません。しかし、2つ以上の戻り値を受け取りたいときがあります。

例として何度もVLOOKUPする事を考えます。
ここに商品リストがあります。

f:id:mutable_yun:20190909190107p:plain
商品リスト


次に、売上実績データがあります。普段基幹システム内で取り扱っているものをエクセルに
ダウンロードしたイメージです。

f:id:mutable_yun:20190909195747p:plain
売上実績データ

今回は売上データを分析するために、売上データに商品リストの情報を反映していきたいとします。

まずは普通のVLOOKUPの方法です。データが少ないため、時間測定のために10,000回繰り返します。

Sub VLOOKUP_売上データに商品の情報を貼り付け()
    
    Debug.Print Time
    
    Dim 最終行rw As Long, 最終列clm As Long
    最終行rw = Cells(Rows.Count, 1).End(xlUp).Row
    最終列clm = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim 商品情報rng As Range
    Sheets("商品リスト").Activate
    Set 商品情報rng = Range(Cells(1, 1), Cells(6, 5))
    
    Sheets("販売実績").Activate
    
    Dim i As Long, j As Long
    For i = 1 To 10000
        For j = 1 To 最終行rw
            On Error Resume Next
            Cells(i, 4) = Application.WorksheetFunction.VLookup(Cells(i, 2), 商品情報rng, 2, False) '商品名
            Cells(i, 5) = Application.WorksheetFunction.VLookup(Cells(i, 2), 商品情報rng, 3, False) '仕入れ先
            Cells(i, 6) = Application.WorksheetFunction.VLookup(Cells(i, 2), 商品情報rng, 4, False) '仕入れ単価
            Cells(i, 7) = Application.WorksheetFunction.VLookup(Cells(i, 2), 商品情報rng, 5, False) '販売単価
            On Error GoTo 0
        Next j
    Next i
    
    Debug.Print Time

End Sub

私の実行環境で約5秒でした。
次に、VLOOKUPでは無く、配列で取得する方法を試します。

Sub 配列_売上データに商品の情報を貼り付け()
    
    Debug.Print Time
    
    Dim 最終行rw As Long, 最終列clm As Long
    最終行rw = Cells(Rows.Count, 1).End(xlUp).Row
    最終列clm = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Dim 商品情報arr As Variant
    Sheets("商品リスト").Activate
    商品情報arr = Range(Cells(1, 1), Cells(6, 5))
    
    Sheets("販売実績").Activate
    
    Dim temp_arr() As Variant
    
    Dim i As Long, j As Long
    For i = 1 To 10000
        
        ReDim temp_arr(3) '次の行に行くときに配列を初期化
        
        For j = 1 To 6
            If Cells(i, 2) = 商品情報arr(j, 1) Then
                temp_arr() = 商品情報取得arr(j, 商品情報arr)
                Cells(i, 4) = temp_arr(0)
                Cells(i, 5) = temp_arr(1)
                Cells(i, 6) = temp_arr(2)
                Cells(i, 7) = temp_arr(3)
                Exit for
            End If
        Next j
        
    Next i
    
    Debug.Print Time

End Sub

つぎにFunctionプロシジャの中身を作ります。

Private Function 商品情報取得arr(rw, item_arr) As Variant
    
    Dim v(3) As Variant

    v(0) = item_arr(rw, 2)
    v(1) = item_arr(rw, 3)
    v(2) = item_arr(rw, 4)
    v(3) = item_arr(rw, 5)
    
    商品情報取得arr = v
End Function

コードは長くなってしまったものの、後者はなんと0.1秒も掛からずに、終了しました。

f:id:mutable_yun:20190909223310p:plain
実行結果

VLOOKUPでは4回ともわざわざ新たに商品リストの中を確認していました。

配列を使った手順では、If分で商品コードを判定したら、そのまま商品コードのB列以降を
配列で取得しました。一度の検索で4つのデータを取得したことが高速化につながりました。

このようにVBAではFunctionは一つの値しか返すことができませんが、配列を使うことで複数の値を、
一つの値として返すことで代用できます。

今回は小さな例ですが、ワークシートがたくさんあって、それぞれのシートに今回のように一つの情報元から
加工したりVLOOK風に貼り付けたりする場合は、加工するシートの中身を一旦全て2次元配列に格納してしまってから、今回のようにFunctionプロシジャーに配列を格納して返すと、一層効率が上がります。


VBAも工夫次第で高速化が十分可能です。