今回は、開発環境を使ってデバッグしながら、関数を記述してみたいと思います。これまでは、一つのセルに入力された UDF に対して結果を返す関数を定義してきましたが、配列数式として入力された関数の場合はどのように記述すればいいのでしょう? そもそも・・・Excel と UDFsのインターフェースはどうなっているのでしょう?
- Excel はUDFsのオーバーロードに対応しているのか?
- Excel のワークシート関数から C API にはどんな引数が渡され、何が返るのか?
- 配列数式の場合、呼び出し元はどのように取得できるのか?
- 配列数式の場合、何を返せば良いのか?
このあたりの基本的な Excel の挙動も探ってみたいと思います。
ひな型を作る:(VSの操作に馴れている方は読みとばしてください)
まず、Excel-DNA に添付されている、CSharpAddIn.sln から、「ひな型」を作っておきたいと思います。
(Distribution>Samples>VisualStudio>CSharpAddIn フォルダにあります)
早速開いて・・・ いきなりビルドしてみます。(ここでは、VisualStudio2010 を使って説明していきます)
(こんな感じ↓)
ここで・・・私の環境では、エラーでビルドが停止しました。
(こんな感じ↓)
出力メッセージから、ビルド後のコマンドラインでエラーになっているので、この部分を変更しました。
ソリューションエクスプローラーの「プロジェクト」を右クリックし、「プロジェクトのプロパティー」を開いて・・・
(こんな感じ↓)
ビルドイベントにある「ビルド後に実行するコマンド ライン(O)」の記述を変更します。
(この部分↓)
このサンプルプロジェクトの 「ビルド後に実行するコマンド ライン(O)」 にある記述は、
「Distribution フォルダにある、ExcelDna.xll ファイルを出力ディレクトリに、SampleCS.xll と改名してコピーする」
という内容ですが、ここでエラーが発生しています。(原因不明は不明ですorz)
2011’07/8 追記: bleis-tift さんからこの問題の解決策を教えていただきました。コメント欄をご覧ください !
ここの記述をそっくり削除してしまえばビルド出来るのですが、ExcelDna.xll と SampleCS.dna の2つのファイルを手動でコピー&ペーストして改名する手間が必要になるので、自動実行してくれる「ひな型」を作っておくと便利ですね。ここでは、Distribution フォルダにある、ExcelDna.xll を プロジェクトフォルダ(CSharpAddIn フォルダ)にコピーして、 コマンドを以下のように変更しました。
(こんな感じ↓)
cd $(ProjectDir) echo F | xcopy ExcelDna.xll $(OutDir)$(TargetName).xll /C /Y echo F | xcopy SampleCS.dna $(OutDir)$(TargetName).dna /C /Y
これで、ExcelDna.xll と SampleCS.dna が、プロジェクトのプロパティーで設定されている「アセンブリ名」と同じ名前に改名されて出力ディレクトリにコピーされます。サンプルプロジェクトでは、SmapleCS.dna がプロジェクトに追加されており、ビルド時に出力ディレクトリに「コピーする」設定になっていますが、改名が面倒なのでこの設定を「コピーしない」にして、.xll ファイルと同じようにコマンドラインで処理するようにしました。ただし、.dna ファイルで参照するDLL の名前(2行目の “CSharpAddIn.dll”)を出力アセンブリ名に手動で置換しておく必要があります。
(こんな感じ↓)
<DnaLibrary Name="Sample Excel-DNA Library in C#" RuntimeVersion="v4.0"> <ExternalLibrary Path="(ここを出力アセンブリ名に変更).dll" LoadFromBytes="true" /> </DnaLibrary>
あわせて、相対参照している ExcelDnaIntegration.dll も一旦参照を解除し、プロジェクトフォルダにコピーしてから参照しなおしておきます。
(ファイル構成はこんな感じ↓)
Debug ビルドすると、bin>debug フォルダに「アセンブリ名」で生成された DLL と、同名に改名された .dna .xll ファイルが出来ます。
(こんな感じ↓)
これで、好きなところに複製できる実験用の「ひな型」が出来ました♪
DEBUG の方法について:
つぎに、DEBUG の方法についてですが、Excel-DNA のサンプルソリューションにある README.TXT には3通りの方法が書かれており、どれでも良いのですが私はプロセスにアタッチする方法をよく使います。
手順はこうなります↓
- プロジェクトをビルドする。
- XLL ファイルを Excel にドラグ&ドロップする。
- 「デバッグ」>「プロセスにアタッチ」 から Excel のプロセスを選択。
この方法の良いところは、ターゲットにするブックを自由に選べる事と、Excel を開いたまま、連続してDEBUG できるところです。ビルドし直したら、再び XLL をドラグ&ドロップしてやればアドインが最新の状態になりますので Excel を開き直す必要はありません。Excel を 一旦閉じてから、もう一度開くのを待ってる時間って・・・イライラしますからね! 特にデバッグのときは・・・(笑)
Excel の挙動を探ってみる:
準備が出来たところで、さっそく実験してみましょう。
UDFs のオーバーロードを試す、こんなコードを書いてみました↓
public static string Hello(string name) { return "Hello! " + name; }</code> public static string Hello(double num) { return "Hello! " + num.ToString(); }
サンプルプロジェクトのコードで引数に double をとるオーバーロード関数を定義してみました。結果は・・・
(こんな感じ↓)
あとから定義した double をとる関数だけが有効になり、さきに定義した文字列をとる関数が #VALUE エラーとなっています。異なる引数を処理する UDF を定義したい場合には、下記のようにヘルパ関数を用意し、引数の型に応じて分岐して呼び出す必要がありそうです。
(こんな感じ↓)
public static object Hello(object obj) { switch (obj.GetType().ToString()) { case "System.String": return str_Hello(obj.ToString()); case "System.Double": return int_Hello(Convert.ToDouble(obj)); default: break; } return obj; }</code> // Helper private static string str_Hello(string name) { return "Hello! " + name; } private static string int_Hello(double num) { return "Hello! " + num.ToString(); }
(public でUDFs定義すると関数がやたらと増えてしまいますので、ヘルパー関数はprivate 関数として隠蔽しています。)
与えたセルの内容が文字列の場合も数値の場合もちゃんと動作しました♪
(こんな感じ↓)
このように、Excel は UDFs のオーバーロードには対応していませんが、object型を引数にとることにより、あらゆる型のオブジェクトを引数としてとることが出来ます。
次に、これを使ってセル範囲を引数にした場合に Excel から渡される引数の型を調べてみましょう。XLL をEcxel にドロップし、デバッグ>アタッチ で Excel にアタッチします。switch文の行にカーソルを置いて「F9 キー」 を押すとブレークポイントが設定されますので、Excel の C5 に 「=Hello(A1:A3)」 と入力してみましょう。設定したブレークポイントで実行が停止しますので、引数の obj にカーソルを合わせると、obj の情報を見ることが出来ます。
(こんな感じ↓)
これを見ると、Excel から object の二次元配列が渡されていることが分かります。ところで、Excel を Ctrl + Alt + F9 で再計算させると、Hello関数は3回呼び出されました。C1、C3、C5 と3か所のセルにHello関数が書かれているからですね。でも、デバッグ時にはどのセルから呼ばれているのか分かりません。例えば、配列数式を記述する場合など、どのセルから呼ばれているのか?知る必要がありますね。
呼び出し元(配列数式が設定されているセル範囲)を取得して、アドレスを RC 形式で返す関数を書いてみました。
(こんな感じ↓)
public static object[,] ArrayTest() { ExcelReference caller = (ExcelReference)XlCall.Excel(XlCall.xlfCaller); int rows = caller.RowLast - caller.RowFirst + 1; int columns = caller.ColumnLast - caller.ColumnFirst + 1;</code> object[,] result = new object[rows, columns]; for (int r = 0; r < rows; r++) { for (int c = 0; c < columns; c++) { result[r, c] = "R" + (caller.RowFirst + r + 1).ToString() + "C" + (caller.ColumnFirst + c + 1).ToString(); } } return result; }
実行するとこのようになりますね^^
(こんな感じ↓)
まとめ:
- Excel UDFs は、オーバーロード出来ない。
- Excel からは UDFs で定義された引数が返されるが、object型を利用すればなんでもあり。
- 呼び出し元は、object型の2次元配列で渡され、ExcelReference によって取得できる。
- object型 の2次元配列を、配列数式の戻り値に利用可能。
って、ところでしょうか?皆さんも、いろいろ遊んでみてくださいね♪
次回はもう少しまともな配列数式のサンプルとしてYahoo知恵袋で見つけた質問に回答した、組合せを列挙する関数について取り上げてみたいと思います♪ 皆さんも宿題としてやってみてください。頭の体操になりますよ^^
(回答として貼ったダウンロードリンクのファイルは削除してあります)
では、また(笑)
この記事で利用したひな型プロジェクトを ダウンロード
余談ですが、ボタンを設置しました↓ じゃんじゃんポチしてください! 喜びます♪
ビルドでエラーになる原因ですが、パスに空白が含まれているとxcopyで落ちるようです。
なので、
echo F | xcopy "$(ProjectDir)..\..\..\ExcelDna.xll" "$(TargetDir)SampleCS.xll" /C /Y
のように引数をダブルクォートで囲んでおくと、エラーが出なくなります。後ろの方はいらないかも?
bleis-tift さん
有用情報ありがとうございます m(_ _)m
bleis-tift さんは、F# MVP でおられますよね。
リンクさせて頂きたいので、「Excel-DNA で F#」な記事を是非!