自分で関数を作ってみる
｜
｜
ユーザー定義関数 ｜
ワークシート関数
ワークシート関数とはその名のとおり、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が指定された場合、その引数が不特定な数の要素を持つ配列であることを表す。あらかじめ渡される引数の数が特定できないようなケースで利用するとよい。
あとはワークシート上でこの関数を使えば良い。
関連記事
- 前の記事
- ファイルを開くダイアログ
- 次の記事
- 選択したセルと同じ値のセルがあれば目視で分かるように
Comment
はじめまして。
私はVBAは初期の頃から使っていますが、ユーザー定義関数は初心者です。
SubStringAfter(A1, "/")の場合で、http://www.yahoo.co.jp/test.html ではなく、A１という値を得たい場合は、どうすれば良いでしょうか？
まことにぶしつけな質問で申し訳ありませんが、ユーザー定義関数に関して全く知識がないもので、困っています。どうかよろしくお願いします。
ちょっと質問の意味が解りません。
SubStringAfterを使ってA1という値を得たいと言うのは？
A3に「=SubStringAfter(A1,"/")」と入力したらA3に「A1」と表示させたい？
ならばユーザー定義関数を使わずにCell関数を使ってみればどうでしょう。
[太字]「=Cell("address",A1)」[/太字]
それともVBAでセルの位置を把握したいのでしょうか？
まず何がやりたいのか教えて頂ければ返答もし易いです。
D列には、おおまかに昇順で数値が入っており、その値が-0.0005を超えた時のC列の値を返したいのです。
大まかにとは、以下のような感じで徐々に増えていきます。
-45.7E-6
-34.7E-6
-40.2E-6
-29.2E-6
-40.2E-6
-45.7E-6
-40.2E-6
-51.3E-6
-50.2E-6
セルの中に”Stress1(-0.0005,D12,C12)”と入力して、以下の処理をした結果を得たいと思っていました。
Function Stress1(Strain, C, S) 'Strain = -0.00005 'C = "D12" 'S = "C12" n = Mid(C, 2) m = Left(C, 1) S = Left(S, 1) Do While Cells(n, m) > Strain n = n + 1 Loop Stress1 = Cells(n, S) End Functionしかし、引数にD12と入れてしまうと、D12の値（-45.7E-6）が得られてしまい、"D12"という値は得られなかったのです。
結局、苦肉の策として、Stress1(-0.0005,"D12","C12")として誤魔化しました。他にもっとよい方法があるように思えるのですが・・・・。
なるほど、やりたいことはD12から下に検索していき-0.00005以上になった行のC列のデータを返すという処理ですね。
【コード】
【セル内計算式】
[色:FF0000]Stress1(-0.0005,D12,C12)[/色]
RowとColumnを使えばセルの行位置、列位置を取得出来ます。
これで"D12","C12"と文字として扱わなくても大丈夫です。
文字扱いでLeftやMidを使うとAA列のように２文字の列になった時不具合になりますしね。
なるほど、RowやColumnをそこで使えばよかったんですね。オシカッタ。実は最後の悪あがきに、引数の中で試しにC.RowとかS.Columnを使ってみてダメだったので、そこで白旗を上げちゃったのです。もうちょっと想像力沸かせば、管理人さんに苦労をかけずに済んだのに。いろいろご丁寧に返答して頂き、ありがとうございました。
いつもブログを見るのを楽しみにしています。これからも楽しく読ませて頂きますね！
ユーザー定義関数のことを調べていてこのブログにたどりつきました。
今回、ユーザー定義関数を使いたいのですが、Excel 2010で動いてくれません。関数として認識はしているようなのですが、計算の結果が表示されずに、「=function(A2)」 といったように表示される状態です。
いろいろあたってみると、開発者向けのサイトの解説で、「UDF（ユーザー定義関数のこと？）を有効にするにはMicrosoft SharePoint Server 2010がインストールされたコンピューターが必要」とあったのですが、これが原因でしょうか？ 通常のExcelでは動かないのでしょうか？
基本的なところで間違っているのかも知れませんが、よろしくお願いいたします。