COM Microsoft Excel object Library で、ＶＢＡやボタンの付いたシートの生成は可能ですか？

環境/言語:[Visual Basic 2010]
分類:[.NET]

Microsoft Visual Basic 2010 Express での質問です。

COMで Microsoft Excel object Libraryを追加してEXCELシートを開いたり、新規作成したりして、データの埋め込みまでは行っています。

最近、添付した表のように、ボタンと、そのボタンを押すと指定の作業をするVBAが付いたシートの作成が出来ないか？　という要望を受けています。

Excel object Libraryを使って、ボタンやVBA付きのシートを生成するサンプルなどがありましたら、教えて頂きたいです。


「こんなのExcelのフィルタを使えばすぐできる。終了！」
という声が聞こえてきそうですね…
はい。私もそう思います。

ですが、指示者は、Excelは開く閉じるが辛うじて出来るレベルで、シート中のデータを選択して並べ替えはとても出来ませんんし、やる気もありません。

ちなみに、データのみ上書きする方法は試しましたが、ご希望に添えませんでした。

今回のサンプルで言うと、リンゴか〜もものデータエリアのみ（B5〜H7のみになりますね）選ばれてソートされなくてはいけない。
かつここに仮に「いちご」が加わったとすると、それも選ばれなくてはならない（B5〜H8という事になります）
では、B列からH列で、予め行数を多めに範囲指定しておき、データのみVisual Basic で書き換えれば良いんじゃないか？　と思って作成したところ
「データのない行が並べ替え対象になっている。信用できないデータ。非常識」
と散々でした。

結局、要望に添った形にするには、Excel object Libraryで新規シートを作成し、データを埋め込んだ後、ボタンとそれに付随するVBAを投入するしか無いという結論になりました。

このボタンとVBAの部分を自動で挿入できれば、かなり助かります。

よろしくお願いいたします。

■No32856に返信(よしさんの記事)
> 最近、添付した表のように、ボタンと、そのボタンを押すと
なお、ボタンには 2 種類あります。
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_130_02.html



> 指定の作業をするVBAが付いたシートの作成が出来ないか？　という要望を受けています。
不可能ではありませんが、「マクロシートや VBA を動的に生成」する行為は、
ウィルスの動的生成すら可能になってしまうという理由から、Office XP 以降で、
VBE モデルへのアクセスが制限されています。

危険性を承知の上で、アクセス制限を解除すれば作れなくは無いですが…。

まずは、ユーザー様の Excel 環境にて Alt+F11 で VBA エディタを表示し、
イミディエイト ペイン(非表示の場合は Ctrl+G で表示)にて
『MsgBox Application.VBE.Version』
が実行可能かどうか確認してみてください。アクセス制限がかかっていなければ
バージョン番号が表示されますが、既定の設定のままだと実行時エラーとなるはずです。


> Excel object Libraryを使って、ボタンやVBA付きのシートを生成するサンプルなどがありましたら、教えて頂きたいです。

手元の PC に VB.NET を入れていないので、VBA コードで回答しますが、
Excel.Button 型のフォームボタンの場合は

　Set X = Sheet1.Buttons.Add(10, 20, 80, 30)
　'X.Caption = "ぼたん"
　'X.Text = "ぼたん"
　'X.Name = "ExcelButton1"
　X.OnAction = "Module1.Macro1"

のような感じで、MSForms.CommandButton 型の ActiveX ボタンの場合は

　Set X = Sheet1.OLEObjects.Add("Forms.CommandButton.1", "", False, False, "", 0, "", 10, 20, 80, 30)
　'x.Object.Caption = "ボタン"
　'x.Name = "CommandButton1"
　Set button1 = x.Object 'WithEvents 変数

などという感じで。


VBA コードの生成については、田中さんのところとか。
http://officetanaka.net/excel/vba/vbe/

Excel ユーザー向けの解説なので、Excel ではなく VB から利用する場合は、
幾許かの読み替えが必要ですが、VBE オブジェクトモデルの理解の足掛かりにはなるかと。

VB から操作するための具体的なコードなどはこのあたり。
http://www.hanatyan.sakura.ne.jp/patio/read.cgi?mode=view2&f=319&no=38



> ちなみに、データのみ上書きする方法は試しましたが、ご希望に添えませんでした。
ソートボタンなどを埋め込んだブックをテンプレートとして用意し、
そのデータ部だけを VB 側で書き換えるという手法でしょうか。
「ご希望に添えない」とありますが、具体的には何が問題だったのでしょうか?


細かい要件が分からないので、代案になるかどうかは分からないのですが、
VB.NET ではなく、VBA で実装するわけには行かないのでしょうか。

「VB.NET から、VBA 入り Excel シートを操作する」よりも、あらかじめ
ボタンなどを埋め込んだ Excel シート側を用意し、VBA 側がデータを取り込めば
既存のソートボタンに手を加える必要は無い気もしますが…。


> では、B列からH列で、予め行数を多めに範囲指定しておき、データのみVisual Basic で書き換えれば良いんじゃないか？　と思って作成したところ
> 「データのない行が並べ替え対象になっている。信用できないデータ。非常識」
> と散々でした。
それはまぁ、空データまで並び替えてしまったら、非常識と言われても仕方ないような…。
データが入っている部分のみを拾い出し、その範囲のみを操作するコードに変更してみては如何でしょう。


> ボタンとそれに付随するVBAを投入するしか無いという結論になりました。か
不可能ではありませんが、VBE のオブジェクトモデルは、バージョン依存の問題が絡むため、
セキュリティ面を抜きにしても、個人的にはお勧めしません。

# デザインモードかどうかで動作が変わったり、Add した直後だとオブジェクトの生成が
# 追いつかず、VBA の OnTime や VB の Tick/Idle 等で遅延実行しないといけないケースがあったり…。

魔界の仮面弁士 さま

ありがとうございました。

> 不可能ではありませんが、「マクロシートや VBA を動的に生成」する行為は、
> ウィルスの動的生成すら可能になってしまうという理由から、Office XP 以降


そうだったのですが、情報ありがとうございます。
おすすめできないという方法では致し方ありません。なにか別の方法を考えてみます。


データシートだけ作って、なんとか自分でソートしてもらえればいいのですが…