今回は例題として取り上げた、「欲しい利息から、必要な元金を逆算して、答えを返す」というユーザー定義関数のプログラムについて、詳しく解説します。
【1】ダウンロードしたExcelのファイル「gankin.xls」を開きます。
＊マクロを有効にする
【2】VBEを起動します。
＊「Altキー」＋「F11キー」
VBE(Visual Basic Editor)とは、統合開発環境のことです。
【3】VBEを起動すると、既に標準モジュールが作成されていて、ユーザー定義関数のプログラムが書かれています。
・calcGankin()
このプログラムは、私が作成したものです。
【解説】
それでは、ユーザー定義関数のプログラムを詳しく見てみましょう。
まずVBAのプログラミングは、プロシージャという単位で行ないます。
プロシージャには、いくつか種類があります。詳しくは今後のステップで説明します。
今回のように、何らかの処理を行なった後に値を返したい時には、Functionプロシージャ(関数プロシージャ)を使います。
Public Function calcGankin(risoku As Long, kinri As Single) As Currency
Dim gankin As Currency
Const TAX As Single = 0.2
gankin = risoku / ((kinri / 100) * (1 - TAX))
calcGankin = gankin
End Function
(1)Functionプロシージャは、「Function」で始まり、「End Function」で終了します。
(2)Publicとは「公の」という意味を表すキーワードです。
Publicで宣言したユーザー定義関数は、どこからでも呼び出して使えます。
例えば、他のプロシージャ内から呼び出したり、Excelのワークシート上でも使えます。
(3)関数名は、自由に付けることができます。働きがわかりやすい名前が良いでしょう。
例 calcGankin
(4)Functionプロシージャは、引数を受け取ることが出来ます。
例 calcGankin(risoku As Long, kinri As Single)
・「,」(カンマ)で区切れば、複数の引数を受け取れます。
・risokuとkinriは、引数のデータを受け取る変数で、データ型を指定しています。
長整数型(Long) サイズ：4バイト 範囲：-2,147,483,648 ～ 2,147,483,647
単精度浮動小数点数型(Single) サイズ：4 バイト
範囲：1.401298E-45 ～ 3.402823E38 (正の値) -3.402823E38 ～ -1.401298E-45 (負の値)
(5)gankinという変数を宣言している部分です。
Dim gankin As Currency
変数とは、データを入れておく箱のようなものをイメージしてください。
変数を使う場合には、どんなデータを入れるのか、あらかじめデータ型を指定しておく必要があります。
今回は Currency という通貨型を指定しています。
通貨型(Currency) サイズ：8バイト 範囲：-922,337,203,685,477.5808 ～ 922,337,203,685,477.5807
また変数は、宣言した場所によって有効範囲があります。gankinは宣言したプロシージャ内だけで有効な変数です。
(6)TAXという定数を宣言している部分です。
Const TAX As Single = 0.2
預貯金の金利には、源泉徴収税が20％かかります。税金の割合はあまり変わることがないので、定数に代入しています。
定数も値を入れておくための箱のようなものです。ただし定数は、変数と違って、プログラム中で値が変化しません。だから定数(定められた数)と言います。
定数は大文字で書いていますが、これは変数と明確に区別するためです。プログラミングの慣習のようなものです。
実際には、定数を小文字で書いてもエラーにはなりません。
(7)変数や定数に値を代入する時には、「=」(イコール)を使います。
プログラミングでの「=」は、算数のように「等しい」という意味ではありません。
プログラミングでは、「右辺の値を左辺に代入する」という意味なので、注意してください。
プログラミング言語によっても違いますが、VBAの場合は、「等しい」という意味で「比較」に使うこともあります。
少し紛らわしいですが、VBAプログラミングをしていくうちに、代入なのか比較なのか、区別できるようになります。
ちなみに他のプログラミング言語では、代入「=」と比較「==」を明確に区別しているものもあります。このほうがわかりやすいですよね。
(8)右辺で元金を計算して、左辺の変数に代入している部分です。
gankin = risoku / ((kinri / 100) * (1 - TAX))
必要な元金は、欲しい利息と、金利(年利)、源泉徴収税から、上記の計算式で逆算することができます。
計算式の書き方には、いろいろあります。答えが正しく出るなら、同じでなくてもかまいません。
(9)関数を呼び出す時の引数と、受け取る関数の引数のデータ型は、一致させる必要があります。
例
calcGankin(1000,0.5)
calcGankin(risoku As Long, kinri As Single)
金利の値は、小数点以下を含むので、Singleにしている点に注意してください。
(10)Functionプロシージャは、戻り値を返すことができます。今回はCurrency型です。
例 calcGankin(risoku As Long, kinri As Single) As Currency
＊関数の戻り値のデータ型を指定している部分は重要です。
戻り値(return value)とは、関数が処理の後に返す計算結果のことです。
(11)戻り値を返すには、関数名と同じ名前に代入します。
例 calcGankin = gankin
＊忘れやすいので注意してください。
(12)Excelのワークシート上で、Functionプロシージャを呼び出すには、先頭に「=」を付けて、引数を指定します。
=calcGankin(1000,0.5)
以上がFunctionプロシージャ(関数プロシージャ)の基本です。
この部分のプログラムを変えることで、いろんな処理(計算)を行なって、結果を返すことができます。
【補足】
プログラムの先頭にある、「Option Explicit」は、変数の宣言を強制する時に指定します。
＊Explicit 明快なという意味
この1行があると、変数は宣言しないと使えなくなります。
VBAでは、基本的に変数は宣言しなくても使えます。でもその時は、バリアント型 (Variant)になります。
バリアント型は、どんな値でも代入できるので便利なのですが、必要なメモリー容量が大きくなります。
それにプログラミングでは、変数を宣言しないと、バグ(誤り)が入り込む原因になります。
そのため、変数はしっかり宣言してから使うようにしましょう。
「Option Explicit」を加えておくことで、変数の宣言を忘れた時には、VBEが教えてくれます。
