スポンサードリンク

ExcelのVBAで作成したユーザー定義関数プログラムの解説。

ExcelVBAを使うと、ユーザー定義関数が作成できます。

今回は例題として取り上げた、「欲しい利息から、必要な元金を逆算して、答えを返す」というユーザー定義関数のプログラムについて、詳しく解説します。

【1】ダウンロードしたExcelのファイル「gankin.xls」を開きます。

*マクロを有効にする


【2】VBEを起動します。

*「Altキー」+「F11キー」

VBE(Visual Basic Editor)とは、統合開発環境のことです。


【3】VBEを起動すると、既に標準モジュールが作成されていて、ユーザー定義関数のプログラムが書かれています。

excel-vba-20.gif

・calcGankin()
excel-vba-21.gif

このプログラムは、私が作成したものです。


【解説】
それでは、ユーザー定義関数のプログラムを詳しく見てみましょう。

まず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が教えてくれます。

スポンサードリンク






Excel VBA 初心者入門講座 TOPへ