Mutable_Yunの業務改善ブログ

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

VBA Functionプロシジャの使いどころ / 自分独自の関数を作る

 

 

エクセルにはSUMやVLOOKUP、SUBTOTALといった便利な関数が多数存在しています。
それらを有効活用するのも大事ですが、独自の関数を作りたくありませんか?

VBAには自分だけの独自の関数を作る機能があり、それをFunctionプロシジャと呼びます。

自分だけの独自の関数のイメージがわかないかもしれないので、以下のようなシチュエーションを考えます。

このような商品リストがあるとします。

f:id:mutable_yun:20190907150427p:plain
商品リストサンプル

データ部分は少ししかありませんが、実際は1000行くらい
あると思って下さい。

複数の仕入れ先がある場合は「/」で区切られています。


次に、このような仕入れ先一覧があります。

f:id:mutable_yun:20190907145906p:plain
仕入れ先一覧サンプル

今回の作業は、それぞれの商品に対して、仕入れ先の本社の所在地に、自社の支店があるかどうかを
調べたいというものです。

自社の支店は大阪と福岡に存在します。

ここで変則VLOOKUPのようなイメージの独自の関数を作ります。

支店ありなし判定()

この支店ありなし判定()という関数は、商品リスト一覧の「仕入れ先」の列の値を入れると、
仕入れ先一覧から探して本社所在地を確認し、大阪か福岡のいずれかを含む仕入れ先があれば、「支店あり」なければ「支店なし」という値を返すという、「自分で考えた」関数です。

まず、本体部分を作ります。

Sub 支店の有無を確認する()

    Dim i As Long 'カウントアップ用
    Dim rw As Long '最終行を取得する
    Dim 仕入れ先str As String 'E列の仕入れ先の文字列を入れる
    
    Const データの最初の行rw As Long = 3
    Const 仕入れ先の列clm As Long = 5
    Const 結果を書き込む列clm As Long = 6
    rw = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = データの最初の行rw To rw
        仕入れ先str = Cells(i, 仕入れ先の列clm)
        Cells(i, 結果を書き込む列clm) = 支店ありなし判定(仕入れ先str)
    Next i

End Sub


次に独自の関数を作ります。

「支店の有無を確認する」プロシジャのEnd Subの下に
Function 支店ありなし判定 と書いてエンターを押すと自動で()とEnd Functionが挿入されるので、その()の中に
引数として受け取る為の変数名を適当につけます。

Function 支店ありなし判定(仕入れ先コードstr)

End Function

サブプロシジャで「仕入れ先str」という名前をつけて渡した引数を、「仕入れ先コードstr」という変数で
受け取る準備ができました。

関数の戻り値も受け取る引数もString型なので、追記しておきます。

Function 支店ありなし判定(仕入れ先コードstr As String) As String

End Function

あとは、順に中身を作っていきます。

方針としては、仕入れ先コードstrは「/」で区切られた文字列なので、「/」で分けて配列に格納し、
配列の要素を順番に仕入れ先コードの検索にかけます。配列のループの中に仕入れ先コードを検索する
ループがある入れ子の形となります。

Function 支店ありなし判定(仕入れ先コードstr As String) As String
    
    Const 仕入れ先一覧のデータの最初の行rw As Long = 3 '作業を開始する行
    Const 最終行rw As Long = 7 '最終行
    
    Dim 仕入れ先arr As Variant '「/」で分けた仕入れ先コードを要素とする配列
    仕入れ先arr = Split(仕入れ先コードstr, "/")
    
    Dim i As Long, j As Long
    Const 仕入れ先コードの列clm As Long = 1
    Const 本社所在地の列clm As Long = 3
    
    Dim 判定結果 As Boolean

    For i = 0 To UBound(仕入れ先arr)
        For j = 仕入れ先一覧のデータの最初の行rw To 最終行rw
            If 仕入れ先arr(i) = Sheets("仕入れ先一覧").Cells(j, 仕入れ先コードの列clm) Then
                If Sheets("仕入れ先一覧").Cells(j, 本社所在地の列clm) = "大阪" Or _
                    Sheets("仕入れ先一覧").Cells(j, 本社所在地の列clm) = "福岡" Then
                    
                    判定結果 = True
                    Exit For
                    
                End If
            End If
        Next j
    Next i
    
    If 判定結果 Then
        支店ありなし判定 = "支店あり"
    Else
        支店ありなし判定 = "支店なし"
    End If
    Next i
    
    If 判定結果 Then
        支店ありなし判定 = "支店あり"
    Else
        支店ありなし判定 = "支店なし"
    End If

End Function

VBAではFunctionプロシジャ名に戻り値として返す値を代入する事によって、このFunctionの呼び出し元に戻り値を返します。

結構な長さのコードになりました。この長さのコードをSubプロシジャでは「支店ありなし判定」という関数たったの一行で呼び出せます。この中身をSubプロシジャに書いてしまうとさらにループの中に入れることになるので、分かりづらくなります。

また、一度作ってしまえば何度でも関数として使えるところがFunction プロシジャのメリットです。

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