エクセルVBA｜Functionプロシージャ(関数)で自作の計算式や処理を実行する

　エクセルVBAでは、ひとつの処理のかたまりをプロシージャと呼び、プロシージャにはいくつかの種類があります。sub プロシージャでコードを書くことが一般的なものと言えますが、プログラミングが上達するには、特定の処理のかたまりに分けておき使いたいタイミングで呼び出せるようにしておくことが大切です。

　VBAを含めた他のプログラミング言語や、エクセルには最初から用意されている関数もあり、それらを利用することで特定の機能をもった処理を効率よく実装することができます。

しかし、すべてのユーザーが求める処理や環境のすべてに対して関数が存在するわけではないのも事実です。

　例えば毎月エクセルで決まった処理を実行する業務がある場合などでは、決まった処理を関数として作成しておいて必要なタイミングで呼び出せれば同じ作業を効率よく進められるようになります。このような場面では、Functionプロシージャ(関数)を作っておく方法がおすすめです。

　こちらの記事では、Functionプロシージャ(関数)の使い方や注意点、サンプルコードなどを紹介します。
Functionプロシージャ(関数)で自分の作業に応じた適切な処理をしてくれる機能を呼び出せるようになり、作業の効率化だけでなくコードのメンテナンスやアップデートの負担も軽減するなど良い事づくしなので試してみてください。

Functionプロシージャとは

　Functionプロシージャ(関数)とは、VBAで自分で関数を作ることができるプロシージャの一種です。Subプロシージャと同じようにモジュールに記載します。

Functionプロシージャ(関数)は、以下のように定義します。

構文

[Public] | Private | Friend][ Static ] Function 関数名 [ ( arglist ) ] [ Astype ]
　　処理
End 関数

適用範囲の指定

※指定しない場合は、Publicとなります。

指定項目	必須／省略可	説明
Public	省略可	スコープの範囲を指定します。 publicは他のモジュールのプロシージャから呼び出せます。
Private		スコープの範囲を指定します。 Functionプロシージャが宣言されているモジュール内の他のプロシージャのみ呼び出せます。
Friend		クラスモジュールでのみ使用。 Function プロシージャは、プロジェクトを全体で表示されます。

適用範囲(スコープ)はプロシージャだけでなく、変数にも指定ができます。

複数のプロシージャやモジュールでも利用可能な変数の適用範囲(スコープ)の指定方法ついては以下のリンクの記事で解説しています。

変数の適用範囲(スコープ)についてコチラ

有効期限の指定

※指定しない場合は、変数は保持されません。

指定項目	必須／省略可	説明
Static	省略可	Functionプロシージャの変数を保持する指定をします。

引数の指定

指定項目	必須／省略可	説明
arglist	省略可	Functionプロシージャに渡す引数を指定します。 変数はそれぞれコンマで区切ります。

[ Optional ] [ ByVal | ByRef ] [ ParamArray ] 変数名 [ ( ) ] [ Astype ] [ =defaultvalue ]

指定項目	必須／省略可	説明
Optional	省略可	引数が必須でないことを指定します。 ※ParamArrayと並記はできません。
ByVal		値渡しで引数を渡します。
ByRef		参照渡しで引数を渡します。引数を省略した場合の既定値です。
ParamArray		arglistの最後の引数として指定が可能で、引数が必須ではない配列を指定します。 ※Optional・ByVal・ByRefと並記はできません。
変数名	必須	受け取る変数名を指定します。 変数名は通常の変数の名づけルールと同様で指定が可能です。
type	省略可	プロシージャに渡す引数のデータ型です。
defaultvalue		定数または定数式を指定し、プロシージャに渡す変数の値を指定します。

戻り値のデータ型の指定

指定項目	必須／省略可	説明
type	省略可	Functionプロシージャから返される値のデータ型を指定します。

• 関数名は任意の名前を付けますが、既存の関数名や予約語と重複しない。
• 引数では、関数に渡す値を指定します。引数はなくても実行が可能ですが、複数指定する場合、引数名や型は重複しないように注意が必要です。
• 戻り値の型は、関数の結果として返す値の型を指定します。
• 処理内容は、関数の中で行う計算や処理を記述します。処理内容の最後には、関数名に戻り値を代入することで、関数の呼び出し元に戻り値を返します。

Functionプロシージャを呼び出す

Functionプロシージャ(関数)を呼び出すには、以下のように記述します。

Dim 変数 As 型
変数 = 関数名(引数1, 引数2, …)

• 変数は、Functionプロシージャ(関数)の戻り値を受け取る変数です。変数の型は、Functionプロシージャ(関数)の戻り値の型と一致する必要があります。例えば、Functionプロシージャ(関数)の戻り値が整数であれば、受け取る変数も整数型で宣言する必要があります。
• 関数名は、呼び出したいFunctionプロシージャ(関数)の名前です。
• 引数は、Functionプロシージャ(関数)に渡す値です。引数の個数や型は、Functionプロシージャ(関数)の定義と同じにする必要があります。

　Functionプロシージャの宣言で引数として指定する変数名と、Functionプロシージャを呼び出すときの変数名は、必ず同じに名前にそろえる必要はありませんが、引数を渡す順番や、データ型の違いが発生しないように注意が必要です。

Functionプロシージャを呼び出す

　以下のサンプルコードでは、SubプロシージャのテストSからFunctionプロシージャであるテストFを呼び出します。呼び出す際にテストSで宣言した変数を引数として受け取り、Functionプロシージャ(関数)のなかで受け取った変数を結合する処理をしています。

サンプルマクロのVBAコード

  Copy
Option Explicit

Sub テストS()

    '変数Strを宣言して"はじめまして！"を代入する
    Dim Str As String: Str = "はじめまして！"
    
    '変数Str2を宣言して"今日は暑いですね。"を代入する
    Dim Str2 As String: Str2 = "今日は暑いですね。"

    'Functionプロシージャ(関数)の結果を受け取り用の変数Str3を宣言する
    Dim Str3 As String

    Debug.Print Str 'はじめまして！
    Debug.Print Str2 '今日は暑いですね。
    
    '変数Str, Str2を引数として、Functionプロシージャ(関数)名テストFを呼び出す。
    Str3 = テストF(Str, Str2)
    
    Debug.Print Str3 'はじめまして！今日は暑いですね。ここはMeowのオフィスノートです。


End Sub

'Functionプロシージャで変数名SをStrとして、S2をStr2として受け取る。
Private Function テストF(S As String, S2 As String) As String
    
    '変数Str4を宣言して"ここはMeowのオフィスノートです。"を代入する
    Dim Str4 As String: Str4 = "ここはMeowのオフィスノートです。"
    Debug.Print Str4 'ここはMeowのオフィスノートです。
    
    Str4 = S & S2 & Str4 '変数Str4に文字列をつなげたものを代入する
    テストF = Str4 'Functionプロシージャの結果を代入して呼び出し元に戻す
        
End Function

03行目

SubプロシージャのテストSを宣言しています。

06行目と09行目

文字列型の変数としてStrとStr2を宣言して、それぞれに文字列を代入しています。
Strには”はじめまして！“とStr2には”今日は暑いですね。“が代入されています。

12行目

Functionプロシージャ(関数)で処理されて戻ってきた値を受け取るために、文字列型の変数としてStr3を宣言します。

18行目

Functionプロシージャ(関数)のテストFを呼び出します。サンプルマクロ内ではテストFで処理をする素材(引数)として、StrとStr2を渡すように指定しており、テストFの処理の結果をStr3で受け取るコードになっています。

20行目

テストFの処理の結果で受け取ったStr3の中身をイミディエイトウィンドウに表示します。
※この行のコードが実行されるのは、Functionプロシージャ(関数)のテストFの処理が終了してからとなります。

26行目

Functionプロシージャ(関数)のテストF宣言します。呼び出されたときの引数として文字列型の変数SとS2を指定します。
末尾のAs Stringは戻り値(関数で処理した結果)のデータ型を指定します。

引数の変数名は必ず一致してなくても良い

SubプロシージャのテストSで、Functionプロシージャ(関数)のテストFを呼び出すときに指定した変数名はStrとStr2ですが、テストF宣言時に指定した引数はSとS2となります。変数名は一致していなくても問題ないですが、引数の指定順やデータ型は呼び出し側と呼び出される側を合わせるように書きます。

29行目

文字列型の変数Str4を宣言し、”ここはMeowのオフィスノートです。“を代入します。

32行目

文字列型の変数Str4に代入されている文字列”ここはMeowのオフィスノートです。“とSubプロシージャのテストSから受けとった変数S(Str)、S2(Str2)に代入されている文字列を結合して、変数Str4の値を上書きします。

33行目

Functionプロシージャ(関数)のテストFの戻り値として、Str4を代入します。

35行目

Functionプロシージャ(関数)のテストFの処理が完了しましたので、呼び出し元のSubプロシージャのテストSに戻り、20行目が実行されます。

Functionプロシージャ(関数)をつかったマクロの処理のながれについては以下の画像を参考にしてください。

実行結果

Functionプロシージャをつかったサンプルマクロ

それでは、Functionプロシージャ(関数)を使っていくつかの処理をして、結果を出力するまでのマクロを作ります。このサンプルコードでは、Functionプロシージャ(関数)を3つ作成し、それぞれが特定の処理をする関数です。

Functionプロシージャをつかったマクロで処理したいこと

Functionプロシージャ(関数)の使い方を具体的に見てみましょう。ここでのサンプルコードでは、エクセルのワークシートにある表のD列からF列に結果の出力をしていきます。

それぞれの結果の求め方は下記のとおりです。結果を求める処理を、Functionプロシージャ(関数)で実行し、結果を戻り値で取得します。

• D列に対する処理
  
  Ａ列・Ｂ列・Ｃ列の値を要素として、Functionプロシージャ(関数)で台形の面積を求め、結果を出力します。
• E列に対する処理
  
  セルの背景色が赤色となっているセルの値の数値を足し算して、結果を出力します。
• F列に対する処理
  
  E列に入力された値(数値)に入力された行数を掛け算した値を結果として出力します。

Functionプロシージャをつかったマクロのサンプルコード

サンプルコードは以下のとおりです。
なお、ここでのプロシージャは、Subプロシージャ・Functionプロシージャに関わらず同じモジュール内にコードを記載しています。まずはSubプロシージャが処理を開始して、それぞれの役割を持つ３つのFunctionプロシージャ(関数)を呼び出します。

  Copy
Option Explicit

Sub 結果を出力する()
    
    Dim i As Long '処理する行数を代入する変数を宣言する
    Dim ans As Double 'Functionプロシージャの結果を受け取る変数を宣言する
    Dim Rans As Long 'Functionプロシージャの結果を受け取る変数を宣言する
    Dim Rdouble As Long 'Functionプロシージャの結果を受け取る変数を宣言する
    
    With ThisWorkbook.Sheets(1)
        i = .Cells(Rows.Count, 1).End(xlUp).row

'ワークシートの2行目から11行目まで繰り返す処理
        For i = 2 To i

'引数 i を指定して、Functionプロシージャ(台形面積)を呼び出し、戻り値は変数ansに代入する
            ans = 台形面積(i)
            .Cells(i, 4).Value = ans
            
'引数 i を指定して、Functionプロシージャ(台形面積)を呼び出し、戻り値は変数Ransに代入する
            Rans = 背景色が赤色の合計を出力する(i)
            .Cells(i, 5).Value = Rans

'引数 i を指定して、Functionプロシージャ(台形面積)を呼び出し、戻り値は変数Rdoubleに代入する
            Rdouble = 赤セル合計を行数倍にする(i)
            .Cells(i, 6).Value = Rdouble
            
        Next i
    End With
End Sub

'----------

Function 台形面積(i As Long) As Double '引数 i を指定して、戻り値は小数点型を指定
    
    With ThisWorkbook.Sheets(1)
    
            '上底列の値を変数xを宣言と同時に値を代入する
            Dim x As Long: x = .Cells(i, 1).Value
            
            '下底列の値を変数yを宣言と同時に値を代入する
            Dim y As Long: y = .Cells(i, 2).Value
            
            '高さの値を変数zを宣言と同時に値を代入する
            Dim z As Long: z = .Cells(i, 3).Value
       
    End With

    '台形の面積を求める公式は上底＋下底×高さ÷2を実行し、結果を戻り値に指定する
    台形面積 = ((x + y) * z) / 2
End Function

'----------

Function 背景色が赤色の合計を出力する(i As Long) As Long '引数 i を指定して、戻り値は整数型を指定
    '赤セルの合計値を代入する変数を宣言と同時に0を代入する
    Dim Rsum As Long: Rsum = 0

    With ThisWorkbook.Sheets(1)
        
        'A列が赤セルなら合計値に足す
        If .Cells(i, 1).Interior.ColorIndex = 3 Then
            Rsum = Rsum + .Cells(i, 1).Value
        End If
        
        'B列が赤セルなら合計値に足す
        If .Cells(i, 2).Interior.ColorIndex = 3 Then
            Rsum = Rsum + .Cells(i, 2).Value
        End If
        
        'C列が赤セルなら合計値に足す
        If .Cells(i, 3).Interior.ColorIndex = 3 Then
            Rsum = Rsum + .Cells(i, 3).Value
        End If
    
    End With
    背景色が赤色の合計を出力する = Rsum '合計した結果の数値を戻り値に指定する
End Function

'----------

Function 赤セル合計を行数倍にする(i As Long) As Long '引数 i を指定して、戻り値は小数点型を指定
    Dim r As Long
    With ThisWorkbook.Sheets(1)
        '変数rにはそれぞれの行数を代入する
        r = .Cells(i, 6).row
        赤セル合計を行数倍にする = .Cells(i, 5) * r '赤セル合計値に行数を掛けた数値を戻り値に指定する
    End With
End Function

subプロシージャでFunctionプロシージャ(関数)を呼び出しているコードは以下です。

ans = 台形面積(i)

Functionプロシージャ(関数)である“台形面積”を呼び出すコードです。引数に変数 i を指定するため、(i) を書いています。これで台形面積を処理に変数 i を持ち込むことを宣言しています。

Function 台形面積(i As Long) As Double

Functionプロシージャ(関数)にて変数 i を受け取る指定をしています。末尾の「 As Double 」は、このFunctionプロシージャ(関数)の戻り値が小数点型であることを指定しています。

Rans = 背景色が赤色の合計を出力する(i)

Functionプロシージャ(関数)である“背景色が赤色の合計を出力する“を呼び出すコードです。引数に変数 i を指定するため、(i) を書いています。これで台形面積を処理に変数 i を持ち込むことを宣言しています。

Function 背景色が赤色の合計を出力する(i As Long) As Long

Functionプロシージャ(関数)にて変数 i を受け取る指定をしています。末尾の「 As Long」は、このFunctionプロシージャ(関数)の戻り値が整数型であることを指定しています。

Rdouble = 赤セル合計を行数倍にする(i)

Functionプロシージャ(関数)である“赤セル合計を行数倍にする“を呼び出すコードです。引数に変数 i を指定するため、(i) を書いています。これで台形面積を処理に変数 i を持ち込むことを宣言しています。

Function 赤セル合計を行数倍にする(i As Long) As Long

Functionプロシージャ(関数)にて変数 i を受け取る指定をしています。末尾の「 As Long」は、このFunctionプロシージャ(関数)の戻り値が整数型であることを指定しています。

SubプロシージャとFunctionプロシージャの処理動作の処理のながれ

サンプルコードを実行した場合の処理のながれとイメージ図は以下のとおりです。

　Sub 結果を出力するプロシージャの実行が開始され、台形面積を呼び出すコードまで到達すると処理が移行します。
Functionプロシージャ(関数)の処理が完了すると、戻り値とともにSub 結果を出力するプロシージャに処理が戻ります。この時にFuctionプロシージャで得た結果を変数ansに代入しています。変数ansに代入された値はセル(D列)に出力しています。

　次に背景色が赤色の合計を出力するを呼び出すコードに到達しますので、今度はこちらのFunctionプロシージャ(関数)の処理が始まります。最初の呼び出しと戻りと同じように、戻り値とともにSub 結果を出力するプロシージャに処理が戻り、得た結果を変数Ransに代入しています。
変数Ransに代入された値はセル(E列)に出力しています。

　さいごに赤セル合計を行数倍にするを呼び出して、Functionプロシージャ(関数)で結果を得て戻り値を変数Rdouble“に代入しています。
変数Rdoubleに代入された値はセル(F列)に出力しています。

繰り返し文について詳しく知りたい人はこちらの記事がおすすめです。

Functionプロシージャをつかったマクロのサンプルコードの実行結果

サンプルコードを実行したあとのワークシートにある表は以下のとおりです。

D列の2行前から11行目までは、台形の面積がセルに入力されました。また、E列とF列には赤色のセルの合計値と、赤色のセルの合計値に各行数を掛け算した値が入力されました。つまり、それぞれのFunctionで規定の関数にはない、自作の関数で求めた結果を求めることができました。

まとめ

この記事では、VBAで自分で関数を作ることができるFunctionプロシージャ(関数)の使い方や注意点、サンプルコードなどを紹介しました。Functionプロシージャ(関数)は、独自の計算や処理を定義して使うことができ、コードの可読性や再利用性を高められますが、引数や戻り値などの扱いには注意が必要です。

Functionプロシージャ(関数)の使い方や注意点まとめ

• Functionプロシージャを使用するには定義と呼び出しのコードが必要
• Functionプロシージャは任意の関数名が指定できるが、重複や予約語との重複に注意
• Functionプロシージャの戻り値の型は、関数の結果として返す値の型を指定する
• Functionプロシージャは処理の最後に関数名に戻り値を代入する
• 引数は、Functionプロシージャ(関数)に渡す値であり、引数の個数や型は、Functionプロシージャの定義と同じにする

Functionプロシージャ(関数)が扱えるようになれば、それぞの処理を部品化できます。自分だけの計算式や処理を定義しておくことで、再利用ができる便利なマクロをつくれます。