自分で関数を作ってみる

isakat

18年前

ワークシート関数

ワークシート関数とはその名のとおり、Excelのワークシート上で利用できる関数のことです。

いまさら特筆するまでもなく、Excelはデフォルトで実に多くのワークシート関数を提供しており、基本的な数値演算から日付計算、文字列操作、財務計算、統計、果てはちょっとしたデータベース処理までを、簡単な記述で実現できる。

恐らく一般的な表計算の処理を行う限りでは、標準的なワークシート関数で十分に用が足りるはずだ。

しかしExcelを駆使してさまざまな表計算を行っていく中では、当然のことながら、なかなか標準関数では十分に賄いきれない局面も出てくるだろう。

そのような場合に、いままでならばどうしていただろうか。複数の関数を組み合わせて、複雑な関数式を毎回記述していたかもしれない。あるいは、一時的なワークシートやセルに計算過程を退避させた上で、あらためて目的の値を導出する、といったようなわずらわしい作業を日常的に行っていたかもしれない。

もちろん、このようなアプローチがだめだとはいわない。しかし、関数式が長くなれば、当然、使いまわす中で間違いが発生する可能性も高くなるし、ほかのユーザーと共有しようと思った場合にも困難がつきまとう。このような事情は、一時的なワークシートやセルを介して演算を行っている場合にはなおさらだ。

そこで本稿では、日常的によく使用する（しかし、Excel標準では用意されていない）ワークシート関数を自前で追加する方法について紹介する。最初の手続きこそ煩雑に思えるかもしれないが、これによって、ワークシート上での記述を簡素化できるだけでなく、同様の機能を複数人で共有したいと思った場合にも容易に実現できる。

自分で作る関数

　それでは具体的なユーザー定義関数を記述してみることにしよう。どんな内容でも特に構わないが、ここでは仮に以下のようなワークシート関数を定義してみる。

関数名	SubStringAfter（文字列 , 部分文字列 [,フラグ] ）
機能	文字列の後方から指定された部分文字列を検索し、出現位置以降の文字列を取得する。戻り値に検索文字列を含むことができるかどうかは、第3引数によって切り替え可能とする。「フラグ」がTrueならば検索文字列を含み、Falseならば検索文字列を除いたものとなる

どういう処理？
=SubStringAfter(“http://www.yahoo.co.jp/index.html”,”/”)
とセルに入れたとしよう。
すると計算結果は『index.html』となるのである。

操作方法

手順1―新規に標準モジュールを生成する

ユーザー定義関数の実体は、標準モジュールで定義されたFunctionプロシージャだ。標準モジュールの追加は、Visual Basic Editorから行うことができる。

Visual Basic Editorを起動したら、［挿入］－［標準モジュール］で新しいモジュールを追加しよう。プロジェクト・ウィンドウに［標準モジュール］－［Module1］というモジュールが追加されるはずだ。

手順2―ユーザー定義関数のプログラム・コードを記述する

下記のコードをモジュールに追加する。

Function SubStringAfter(文字列, 検索文字列, Optional フラグ = False)
  intSrch = InStrRev(文字列, 検索文字列)　　' 引数フラグがTRUEの場合には検索文字列を含む部分文字列を、FALSEの場合には検索文字列を除く部分文字列を抜き出す
  If フラグ Then
    strResult = Right(文字列, Len(文字列) - (intSrch - 1))
  Else
    strResult = Right(文字列, Len(文字列) - Len(検索文字列) - (intSrch - 1))
  End If
  SubStringAfter = strResult
End Function

仮引数の前に指定しているキーワード「Optional」は、引数が省略可能であることを示す。キーワードOptionalを指定した場合には、必ず省略時のデフォルト値を指定する必要がある。また、Optionalを指定した引数のさらに後方に、省略不可の引数を指定することはできないので、注意すること。

なお、仮引数に指定可能なキーワードとして、ほかにもParamArrayなどは覚えておくと便利なキーワードだ、ParamArrayが指定された場合、その引数が不特定な数の要素を持つ配列であることを表す。あらかじめ渡される引数の数が特定できないようなケースで利用するとよい。

あとはワークシート上でこの関数を使えば良い。