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

エクセルVBAでは、ひとつの処理のかたまりをプロシージャと呼びますが、このプロシージャにはいくつかの種類があります。一般的な使用される sub プロシージャでコードを書くこともできますが、プログラミングの上達するには、特定の処理のかたまりをそれぞれに分けることで部品化をしていくことが大切です。

また、VBAを含め、エクセルには最初から用意されている関数は多数存在しており、どれもとても優秀です。しかし、すべてのユーザーが求める処理や環境に対して網羅的に存在するわけではないのも事実です。

そういった場合は、特定の処理をプロシージャとして、プログラムすることで関数として呼び出すことができます。そういった場面で便利に使えるものがFunctionプロシージャ(関数)です。Functionプロシージャ(関数)は、引数と戻り値を持つことができ、独自の計算や処理を定義して使うことができます。

この記事では、Functionプロシージャ(関数)の使い方や注意点、サンプルコードなどを紹介します。

Functionプロシージャとは

Functionプロシージャ(関数)とは、VBAで自分で関数を作ることができるプロシージャの一種です。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()

    '変数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 'はじめまして！今日は暑いですね。ここはコダマのもりブログです。


End Sub

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

呼び出す側であるSubプロシージャのテストSでFunctionプロシージャ(関数)を呼び出す時に指定している変数名はStrとStr2です。

呼び出される側のFunctionプロシージャ(関数)の テストFで受け取る変数名はSとS2と指定しています。引数で渡した値は、指定した順番で受け取るため、Strに代入された値は変数Sに、 Str2に代入された値は変数S2に代入されます。

また、Functionプロシージャの”テストF”からの戻り値は、変数Str4でデータ型はString(文字列)型であるため、Subプロシージャの”テストS”で戻り値を受け取る変数もString(文字列)型で宣言する必要があります。

引数として指定した変数のデータ型の関連性は以下の画像で確認できます。

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

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

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

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

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

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

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

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

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