ワークシート関数を設計する

Excelアドインツールで、一番悩んだのは関数の設計です。

例えば、有報キャッチャーウェブAPIから証券コードを指定して、EDINETのデータの一覧を引っ張ってくる機能が欲しい。（実際に『UCXEdinetXBRLInstance』という関数で実装）

この場合、有報キャッチャーウェブAPIに対して、http://resource.ufocatch.com/atom/edinetx/query/{証券コード４桁} にリクエストすれば、ATOMフィードを返してくれます。普通に考えると、関数の引数に証券コード（４桁文字列）として、戻り値にデータ（タイトルやIDなどからなる構造体）の配列を返せばよい、ということになります。

　public Function UCXEdinetXBRLInstance(ByVal 証券コード As String) As データ構造体()

問題は、この関数はワークシート上で使われる、ということです。セルに配列を返してどうするんだ？しかも構造体？そもそも配列を返すワークシート関数なんておかしいだろう、と思いました。だとすると、何番目のデータか、データの何の情報が欲しいのか、を引数に追加する必要があるのか、、、

　public Function UCXEdinetXBRLInstance(ByVal 証券コード As String, インデックス As Long, データ種別 As Integer) As Variant
※インデックスは、ｎ番目のデータをあらわす
※データ種別は、0ならタイトル、1ならID、2ならXBRLインスタンスのURI、など

こうなると、有効なインデックスの範囲を事前に知るためにデータの個数がわかる関数も必要だ。。。。

　public Function UCXCountOfEdinetXBRLInstance(ByVal 証券コード As String) As Long

とまぁこんな感じで関数を設計していました。

しかし、Excelには配列を返すワークシート関数が実はいっぱいあって、配列数式なるものを使えば配列として処理できるらしい、ということがわかりました。

Excelでは、配列数式として入力したワークシート関数が２次元配列（または、配列の配列）を返すと、それをワークシートの縦横に展開して表示してくれます。ですので、最終的に関数は以下のような実装となりました。

　public Function UCXEdinetXBRLInstance(ByVal 証券コード As String) As Variant
※戻り値は、配列の配列

配列数式は慣れるまで結構難しいのですが、実に便利です。
使い方は簡単で、例えば、セルA1からE20くらいまでガバッと選択して、F2キーを押して「=UCXEdinetXBRLInstance(7011)」と入力、Ctrl+Shift+Enterで確定すれば、配列数式の入力完了。ずらずらずらっと7011(三菱重工)のEDINET開示のデータが表示されます。

こんな感じで、Excelアドインツールで定義されているユーザ定義関数の多くは配列（または配列の配列）を返す設計になっています。使っていただける方はぜひ配列数式を活用してみてください。