VBAのFunctionプロシジャは一つの戻り値しか返せませんが、配列に複数の値を格納することで、複数の戻り値を返すのと同じことができます。その方法を解説します。
目次
配列でFunctionプロシジャで複数の戻り値を返す方法
この記事はVBAでFunctionプロシジャとは何かを理解している人を読者に想定しています。もしFunctionプロシジャそのものを知らない場合は、【VBA】 Functionプロシジャの作り方と使い所をご参照下さい。
ざっくりとFunctionプロシジャをおさらいしておくと、自分で関数を作ると言う事です。例えばエクセル関数でVLOOKUPという関数は値を検索して、一番始めに一致した値の行の別の列の値を取得します。そうではなく、最後に一致した行の別の列の値を取得したい、というように自分のやりたい事を関数にできるのです。
今回は、その自分独自の関数の戻り値を複数にしたい場合、どうすれば良いかについて解説する、と言う事です。例えばVLOOKUPで検索値と一致したときに隣の行とさらにその隣の行の二つの値を利用したい、と言うような場合です。
複数の戻り値を得たい場合の具体例
解説サンプルの為に、何度もVLOOKUPする事を考えます。このようなエクセルの商品リストがあります。
次に、売上実績データがあります。普段基幹システム内で取り扱っているものをエクセルにダウンロードしたイメージです。
今回は売上データを分析するために、売上データに商品リストの情報を反映する作業をVBAで自動化するプログラムを実装することを考えます。作業としては、商品名、仕入先、仕入れ単価、販売単価のデータを商品リストから売上データに転記します。
普通にVLOOKUPを複数回充てる場合（比較用）
やりたいことをさらに分かりやすくするために、配列を使わずにVLOOKを使ってセルに検索結果をベタ打ちしていくプログラムを提示します。商品名、仕入先、仕入れ単価、販売単価の情報を反映させるために、４回VLOOKUPを繰り返します。
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 For i = 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 i Debug.Print Time End Sub
このプログラムは繰り返しのFor分を使って上から順位VLOOKを必要な回数だけ行っているというシンプルなプログラムです。プログラムの構造自体はシンプルですが、作業としては無駄が多いことになります。なぜなら同じ行で何度もVLOOKUPをしているからです。人間が手作業でやるなら、一致する値を見つけたらその右に目をやりますが、このプログラムは検索値を見つけたらその右、その右、と見るのではなく、列ごとに検索値を検索しています。
配列を使って一度に複数の値を取得する場合
次に、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 ReDim temp_arr(3) '次の行に行くときに配列を初期化 For i = 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 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
行数とitem_arrを引数として受け取っています。行数が分かっているので、その右の列の値、さらにその右の列の値、と言うように順に配列に格納していって、配列を戻り値として返しています。これでVLOOKUPの時のサンプルコードのように無駄な検索を行わずに最小限の検索回数で必要な値を取得できています。
配列で複数の戻り値を返すことのメリット
ここまででFunctionプロシジャで複数の戻り値を返す方法を見てきました。何も複数の値を一度に返さなくてもいいではないかと思うかも知れません。確かにその通りです。しかし、複数の値を返すことには下記の二つのメリットがあります。
- プログラムがすっきりする
- 無駄な作業を抑えるため、処理時間を短くする高速化が実現する
プログラムがすっきりすると言うのは、Functionプロシジャをいくつも作らなくて済むという意味です。構造的にプログラムをきれいにする事ができます。そして、上のサンプルプログラムではVLOOKUPを列ごとに当てる例と、行数を引数として与えて、同じ行の別の列の値を一度に返す例を挙げました。この2つのプログラムをそれぞれ10,000回ずつ繰り返したところ、VLOOKUPの方は5秒程度、後者は瞬時に作業を終えました。
このように検索する回数を抑えることでプログラムの高速化を図ることができます。
配列でFunctionプロシジャで複数の戻り値を返す方法のまとめ
VLOOKUPでは4回ともわざわざ新たに商品リストの中を確認していました。
配列を使った手順では、If分で商品コードを判定したら、そのまま商品コードのB列以降を配列で取得しました。一度の検索で4つのデータを取得したことが高速化につながりました。
このようにVBAではFunctionは一つの値しか返すことができませんが、配列を使うことで複数の値を、一つの値として返すことで代用できます。
今回は小さな例ですが、ワークシートがたくさんあって、それぞれのシートに今回のように一つの情報元から加工したりVLOOK風に貼り付けたりする場合は、加工するシートの中身を一旦全て2次元配列に格納してしまってから、今回のようにFunctionプロシジャーに配列を格納して返すと、一層効率が上がります。
VBAも工夫次第で高速化が十分可能です。
