Functionプロシージャーの働きと使い方 ユーザー定義関数もここ知れOK!

VBAFunctioneyecatch

FunctionプロシージャーはExcel VBAの学習を進める中で、絶対使いたいと思っている人が殆どだと思います。実務利用のVBAでも非常に有用なものです。
働き、使い方、ユーザー定義関数について説明します。

 

こんにちは、じゅんぱ店長(@junpa33)です。

 

エクセルVBAを勉強していると、Functionで始まるコード文のプログラムがあることに気づきます。

これは何かというと、

Functionプロシージャー」というものです。

どの様に働くのかというと、

与えられたデータ値に対してルール決めされた方法で加工処理して、その処理後データを依頼元に返すという働きをします。

 

Functionプロシージャーの働き

 

「Function」とはGoogle翻訳によると、「機能」「働き」「関数」ということになります。

すでに内容を言い当てています。

全体の中の「機能の一部分」ということになります。

 

つまりFunctionプロシージャーとは?

 

例え話です。

  •   ある和風レストランがあります。
  •   そのレストランは、食材の部分加工を自社の専用工場で行っています。
  • 実行 今回は、お客様から、刺身盛り合わせのオーダーが入りました。
  •   そこで、店内厨房から専用工場に、マグロ・鯛・ヒラメ・カンパチ・サーモン・イカの切り身の発注をしました。
  •   工場では、この6種それぞれの製造ルール(厚み、大きさ、鮮度など)に従って製造します。
  •   店内厨房では、製造された6種それぞれの切り身を大皿にきれいに盛り付け、
  • 完了 オーダーを頂いたお客様のテーブルに運びました。

 

プログラムが長く大きくなればなるほど、実行コードの部品化が重要になってきます。

一つのプログラムの中で、同様な処理を毎回その都度コード化していては、長大なプログラムになってしまいます。

そこで、部分加工の専用工場を作り、同一ルールで処理できるように仕組むことで、

プログラムコード全体を簡潔に記述し、また読みやすいものにしてゆくということです。

①が親プロシージャーで主にSubプロシージャになり

②が子プロシージャーで主にFunctionプロシージャーです。

 

Functionプロシージャーの構文

 

Functionプロシージャーの構文としては次のようになります。

Functionプロシージャーの構文

[ Private / Public ( Static ) ] Function プロシージャー名 ( 引数リスト ) As データ型

   –VBAのコード–

    ・・・・・・・

    プロシージャー名 = 戻り値

End Function

頭に、「Private」「Public」さらに「Static」を付加することが出来ます。もちろん何もしなくてもいいです。

  •  「Private」を頭に付けると、同一モジュール内のプロシージャーからのみ呼び出すことが可能です。
  •  「Public」を頭に付けると、他のすべてのモジュールからも呼び出すことが可能です。
  •  「どちらも」つけない場合(こちらが多い)「Public」を指定したとみなされます。
  • 「Static」を間に付加すると、プロシージャー内で使われている変数の値が保存され、次にこのプロシージャーが呼び出されたときには、保存値が初期値として扱われます。

 

Functionプロシージャーの使い方の例

 

Functionプロシージャーの使い方としては、使用場面として、

があります。

 

VBAプログラムの中のFunctionプロシージャー

Functionプロシージャーは、

呼び出したSubプロシージャーに対して値を返すことが出来ます。

今回のサンプルは、定価に対して設定した値引率を商品毎に呼び出し、売値を計算します。

同じ結果になりますが、

の記述の違いの例になります。

VBAFunction001

VBAFunction002

 

引数を設定しない場合

( 引数リスト ) のところは ( ) 括弧のみになります。

値引率(MyPar)の計算部分をFunctionプロシージャーに投げています。

 

コード
Sub Function引数なし()
    Dim MyPrice, i As Long
        For i = 4 To 11
            Cells(i, 3).Select
            Cells(i, 4) = MyPar
            MyPrice = WorksheetFunction.Round(Cells(i, 3).Value * MyPar, 0)
            Cells(i, 5) = MyPrice
        Next i
End Sub
コード
Function MyPar() As Double
    Select Case ActiveCell.Value
        Case Is >= 1000
            MyPar = 0.9
        Case Is >= 900
            MyPar = 0.92
        Case Is >= 700
            MyPar = 0.95
        Case Is >= 500
            MyPar = 0.97
        Case Else
            MyPar = 1
    End Select
End Function
VBAコード参考記事
ここの説明で出てくるVBAコードの参考にしていただける記事です。

ワークシート関数Roundについてはこちらを参考にしてください。

vbaroundeyecatch Round関数の毒針。VBAで数値を四捨五入する時の注意点

Select Caseステートメントについてはこちらを参考にしてください。

vbaselectcaseeyecatch Select Case 条件分岐の使い方。ステートメントの基本はコレ。

 

引数を設定する場合

( 引数リスト ) のところを記述します。

値引率(MyParb)の計算部分と売値(MyPriceb)の計算部分を、

それぞれ別のFunctionプロシージャーに投げています。

親プロシージャー

コード
Sub Function引数あり()
Dim i As Long
        For i = 4 To 11
            Cells(i, 3).Select
            Cells(i, 4) = MyParb(Cells(i, 3).Value)
            Cells(i, 5) = MyPriceb(Cells(i, 3).Value, Cells(i, 4))
        Next i
End Sub

 

子プロシージャー(MyParb)

引数を1つ持つプロシージャーになります。
引数は「定価」の値

コード
Function MyParb(par As Double) As Double
    Select Case ActiveCell.Value
        Case Is >= 1000
            par = 0.9
        Case Is >= 900
            par = 0.92
        Case Is >= 700
            par = 0.95
        Case Is >= 500
            par = 0.97
        Case Else
            par = 1
    End Select
    MyParb = par
End Function

 

子プロシージャー(MyPriceb)

引数を2つ持つプロシージャーになります。
引数は「定価」と「値引率」の値

コード
Function MyPriceb(pri As Long, 率 As Double) As Long
    pri = WorksheetFunction.Round(pri * 率, 0)
    MyPriceb = pri
End Function

 

ユーザー定義関数を作る

先の例題をユーザー定義関数で対応してみます。

ユーザー定義関数を設定する方法は、

利用するWorkbook(エクセルブック)のVBE標準モジュールに、

Functionプロシージャーを記述するだけ、それだけです。

 

実行方法は、ユーザー定義関数ですので、コードの実行はワークシート上で行うことになります。

VBAFunction005

 

ユーザー定義関数名  [ =L値引率(定価)]

先の例示で言うと「子プロシージャー(MyParb)」に当たる部分になります。
関数で「定価」のセルを指定して「値引率」列のセルに答えを表示します。

VBAFunction003

コード
Function L値引率(定価)
    Dim par As Double
    Select Case 定価.Value
        Case Is >= 1000
            par = 0.9
        Case Is >= 900
            par = 0.92
        Case Is >= 700
            par = 0.95
        Case Is >= 500
            par = 0.97
        Case Else
            par = 1
    End Select
    L値引率 = par
End Function
VBAコード参考記事
ここの説明で出てくるVBAコードの参考にしていただける記事です。

Select Caseステートメントについてはこちらを参考にしてください。

vbaselectcaseeyecatch Select Case 条件分岐の使い方。ステートメントの基本はコレ。

 

ユーザー定義関数名 [ =L売値(定価)]

先の例示で言うと「子プロシージャー(MyPriceb)」に当たる部分になります。
関数で「定価」のセルを指定すると、その定価が「値引率」を引き出して、四捨五入計算を行い「売値」列のセルに答えを表示します。

VBAFunction004

コード
Function L売値(定価)
    L売値 = WorksheetFunction.Round(定価.Value * 定価.Offset(, 1).Value, 0)
End Function

(.valueは省略可能です。)

VBAコード参考記事
ここの説明で出てくるVBAコードの参考にしていただける記事です。

ワークシート関数のRoundについてはこちらの記事を参考にしてください。

vbaroundeyecatch Round関数の毒針。VBAで数値を四捨五入する時の注意点

Offsetプロパティについてはこちらの記事を参考にしてください。

vbaoffseteyecatch Offsetプロパティの使い方。セルや選択範囲を移動するVBAコード

 

Functionプロシージャーの働きのまとめ

 

Functionプロシージャーは奥が深いです。

全部を知ろうとしても、ぶっちゃけ、実務的にあまり意味がありません。

必要部分、実務での利用部分に合わせて勉強しましょう。

必要に応じて学習を深めていきましょう。

この記事で説明したことが、(自分的には)まずは必要部分かなと考えます。

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできます。

Udemy1eyecatch エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

今回の記事はここまでです。   最後までご覧いただき有難うございました。

vbaborder1eyecatchVBAでセルに罫線を設定する 位置・線種・太さ・色vbarowseyecatchVBA Rowsプロパティの範囲選択と関連のメソッドvbadeleteeyecatchVBA DeleteとClearの使い分け 削除とクリアの違いを最速理解vbacurrentregioneyecatchCurrentRegionは範囲取得プロパティ 連続データをまとめて掴むSpecialCellseyecatchSpecialCellsメソッドは特定のセルを選択する多機能ツールVBAFunctioneyecatchFunctionプロシージャーの働きと使い方 ユーザー定義関数もここ知れOK!vbacalleyecatchCallステートメントはプロシージャーの部品化に必須項目 エクセルVBA最速理解vbacharactereyecatchaCharactersプロパティで文字列中の一部の文字の書式設定や加工をするvbafontsyseyecatcha文字の書式の設定方法 Fontプロパティで装飾操作ができるVBAFormateyecatchFormat関数を書式設定のテッパン関数にする!実務での書式と重要5例vbaintfixeyecatch数値の整数部分を完全分離 Int・Fix・Abs・Sign関数で出来る使い方vbarndeyecatchRnd関数で乱数を取得。使えない戻り値を加工して利用度を上げるVBAコードvbaroundupdowneyecatchRoundUpとRoundDown関数の使い方。数値の切り方に注意が必要ですvbaroundeyecatchRound関数の毒針。VBAで数値を四捨五入する時の注意点vbamaxminaveeyecatchMax・Min・Averageワークシート関数で最大値・最小値・平均を求めるvbainsteeyecatchInstr関数・InstrRev関数は文字列を検索する。ファイルパス取得に威力vbareplaceeyecatchReplace関数は文字列を置換・削除する利用度の高い関数vbaselectcaseeyecatchSelect Case 条件分岐の使い方。ステートメントの基本はコレ。VBAValeyecatchVal関数の使い方はデータ型不一致の解決策。値を文字列型から数値型へVBAStringeyecatchString関数の使い方は文字を指定した数だけ並べることが出来る

 

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。