Excel2003でマクロを組みました。

質問

Excel2003でマクロを組みました。
それなりにコードを書き込んだので、ファイルとしての容量が3MBくらいあります。
ためしにソース（Module1～35）を全て削除してみたら、2.2MBまで減りました。
（思ったより減らなかったですが）

ファイル容量を減らしたいし、マクロを実行する側としてはソースはいらないので、
C言語のソースをコンパイルして実行ファイルだけを取り出して使うように、
（＝プログラムの実行にはソースは必要無いように）
Excelファイルからマクロの実行部分だけを抜き出す、
なんてことは可能でしょうか。
マクロとプログラムは違うから不可能でしょうか。

回答No.2

Wendy02 · ベストアンサー

>ためしにソース（Module1～35）を全て削除してみたら、2.2MBまで減りました。

おそらく、コードの設計の問題だと思います。そんなようにはふくれあがらないはずです。
いくら、プロ級の力をもってしても、VBAでは、モジュールが1～35など考えられません。全部で何行ぐらいになるでしょうか？数千行とかで、似たような部分があるのではないでしょうか？記録マクロで記録したものを、そのまま使っている方が、そのような巨大なコードを置いていることかあります。

>Excelファイルからマクロの実行部分だけを抜き出す、なんてことは可能でしょうか。
ソース部分をCOM にするか、アドインにすればよいですが、VBAは、VBAのメモリの割り振りがありますから、同じことです。

なお、VBAは、基本的にインタプリタ型の要素を持っていますが、結果的には、コンパイルされて中間言語(Psedo-Code または、Pコード)として、保存されてしまいます。コンパイル型言語は、コンパイル+ビルトですが、VBA には、ビルトができません。また、VBEditor のオプションの設定の[バックグラウンド コンパイル]のままにしておくと、どんどん中間言語がたまり、ファイル自体が肥大化しますので、ファイルの肥大化が気になるようでしたら、[順次コンパイル]にしてあるか確認してください。

MS サポート VBAは、コンパイル型とインタプリタ型、両方である。
という文面があります。興味があれば英語版で翻訳してお読みください。

参考URL：

http://support.microsoft.com/kb/209176/ja

補足

回答ありがとうございます。

１つのModuleにだいたい150～200行くらい書いており、それが30個くらいあります（＋20～30行の日本語のコメントだけ書いてあるのが5つ）。
改行は多目で、コメントアウトした日本語で何行も説明入れてたりします。
記録マクロはコードの確認に使いますが、基本的に自分で書き込んでいます。
難しいことはやっていないのですが、
確かに同じような処理を別モジュールでやっていたりしますので無駄な部分も多いとは思いますが、
行数だけで言うと、多くても200行×30Module＝6000行くらいです。

6000行くらいだとそのくらいのファイル容量になっても仕方ない、ということでしょうか。
減らしたいならコードを最適化すべし、という状態でしょうか。

> VBEditor のオプションの設定の[バックグラウンド コンパイル]のままにしておくと、どんどん中間言語がたまり、ファイル自体が肥大化します

設定を見ましたところ、
　・順次コンパイル
　　・バックグラウンドコンパイル
のように、後者が下位に位置しているように書かれており、
両方にチェックが入っていたのでバックグラウンドのみチェックを外しました。
外しましたが、
具体的にどう変化が現れるのでしょうか。
チェックを外すと今まで溜まっていた中間言語？が消えて容量が軽くなったり、はしないようですが。
チェックを外した後、
「VBAProjectのコンパイル」を実行し、保存してみたらむしろ容量が増えました。
（私、何か変なことしてますでしょうか）

回答No.5

Wendy02

>> 確か、以前の話では、UserForm も多用しているような気がしましたが、
>という一文が気になるのですが、

失礼しました。私は、tktk1228様には、今回を含めて、8つの質問に回答しています。ハンドルを替えない限りは、その人の過去の質問の一覧を出せるようになっています。そうして、前の回答から、現在の回答をしやすくしています。

>回答者様のその12MBのファイルだと、保存時にそれ以上の時間がかかるのでしょうか。

時間は測ったことがありませんが、そのようなファイルを3つも使っていても、ストレスを感じません。最近、重複チェックのために、条件書式を1列増やしたら多少遅くなりましたので、条件付き書式をなくしました。そういうのは、マクロで可能だからです。ふだん使うものではありませんから、マクロに置き換えてしまいました。12Mにもなると、5万行を越えていますが、何十秒も掛かることはありません。

関数はほとんどありません。それだけの大きさでを関数を使っていたら、保存の前に計算イベントが走るので、相当時間が掛かってしまいます。

こちらの場合は、あまり時間が掛かる時は、ハングしていると思い、強制終了させてしまいます。しかし、マクロでバックアップを取るようにしてありますから、最低でも、起動時に戻るようになっています。トラブルの原因は、Excel を起動中に、インターネット・アクセスが原因らしいのですが、最近は、トラブルが前よりも減ったような気がします。はっきりした理由は分かりませんが、マクロの時間短縮化を図ったことが功を奏しているようです。かなり、関数はマクロに置き換えることが出来ます。特に、FREQUENCY のような配列を引数に使う関数などは、まともに使える大きさは限られていますので、こういうものは、マクロに置き換える必要があります。

なお、「最適化」については、一度、まとめたことがありますが、あまり、素人受けしないような話です。Office Tanaka の田中亨氏の「VBA高速化テクニック」ぐらいが一般には適当かもしれません。

「最適化」の元は、『Microsoft Office プログラマーズ・ガイド』というテキストに出ているものです。MSのWebサイトでは、その断片しか残っていません。

・MSDN --VBA コードを最適化する
http://msdn.microsoft.com/ja-jp/library/Aa189065

今回の話のモジュールをまとめるというのは、「VBA開発標準」という題名でまとめている人もいるようですが、プロの間でいわれている話です。

・VBAお作法指南
http://www.vba-manners.info/

なお、ある程度のVBAの技術を持っていれば、人のコードをみれば人のスキルの凡そのことは分かります。ただ、VBAは、ほとんどの人は、自己流で、きちんと学習していない人が多いです。Microsoftは、すでに、Office 97 時代に、きちんとした書法を提示したのにも関わらず、そうしたマニュアルの内容がデベロッパー版のみで一般化することがなかったので、自己流を生む結果になったのかもしれません。

お礼

> 前の回答から、現在の回答をしやすくしています。

なるほど、都度確認してらっしゃるのですね。
ありがとうございます。

関数に関しては多いのか少ないのかわかりませんが、
関数を使わないようにしようと思ったことは無いので、そこが保存時間に影響を与えていそうです。
余談ですが、過去回答いただきましたUserFormについてはこれとは別件でして一切つかっておりません。

「VBAお作法指南」を少し読みました。
身に覚えのある話が多いです。
読み進めようと思います。
最適化については、まだまだレベルが高そうです。

私も思いっきり自己流で、ここでは散々お世話になっております。
VBAは「記録する」でやりたいことをするにはどのようなコードを書けばいいのかを知ることができるため、
つまりは動かすだけなら誰でもある程度使えるため自己流が多いんだろうと思っています。
（似たようなことがご紹介頂いたサイトの冒頭にありましたね）
初めてVBAを教えてもらった時も、「『記録する』で調べられるから」と教わりました。

正直最初の質問から離れた疑問がいくつか沸きましたが、
きりが無いので別の機会に質問しようと思います。
色々ありがとうございました。

回答No.4

Wendy02

#2の回答者です。

>チェックを外すと今まで溜まっていた中間言語？が消えて容量が軽くなったり、はしないようですが。
>チェックを外した後、
>「VBAProjectのコンパイル」を実行し、保存してみたらむしろ容量が増えました

そのままにしたら、中間言語(P-Code)は、累積していくので、どんどん肥大化していくことがあるということです。設定だけでは、今までのものに変わらないはずですが、増えたといってもどの程度の問題になったのか、また、そのような必要性があるのかとおっしゃっているのか、その意図は分かりませんが、その設定で肥大化は防げるということです。一旦、モジュールのコードをエクスポートして、中身を全部、空にした後に、再インポートしなければスリムにはなりません。

>減らしたいならコードを最適化すべし、という状態でしょうか。

「最適化」という言葉での切り返しは、現状のご質問の中で、どの程度の理解の上でおっしゃっているのか意味が分かりかねますが、もしもVBAの最適化(Optimization)のテクをご存知なら、最適化は最終的には開発レベルの問題であって、単にコードのスリム化とは違う話ですから、当たり前のようで当たり前ではないことも多く、ここの掲示板で私たちがどうこういう話でもないと思います。

ご質問者さんは、今、どのぐらいの実力があるのかは分かりませんが、ご質問のご様子からしたら、コード全体のスリム化やまとめる必要性はあるだろうと思います。

ちなみに、私がふだん使っているマクロの行数を調べてみましたら、25,000行にもなっていましたし、ファイルサイズも、12Mにもなっています。最初作ってから、かれこれ6～7年にもなりますが、何の支障も出ていません。昨年、コードのメインの部分を全部書き換え、前のコードをそのまま残しているから、そのような行数になっています。

マクロにもExcel.Applicationのメモリの割り振りがありますから、モジュール分散化と、オプションの設定の[バックグラウンド コンパイル]をオフにしていれば、切り抜けられるはずです。

確か、以前の話では、UserForm も多用しているような気がしましたが、アドイン化するということは、単に、モジュールを移行して動かせるかどうかは、なんとも言えない、というか、それなりにアドインを意識して書かないとうまくいかないはずですが、いずれにしても、モジュールが、35 個まで作ったら、管理しずらいだろうと思います。

私としては、オプションが、指摘した設定をしているなら、放っておいても大丈夫だと思います。

お礼

> >「VBAProjectのコンパイル」を実行し、保存してみたらむしろ容量が増えました

[バックグラウンド コンパイル]をオフにした状態で保存してみましたが、容量に変化がありませんでした。
勝手に「今まで溜まっていた「中間言語による肥大部分」がカットされるかと思ってました。
再度コンパイルしないと効果が無いのかと思い、
（一度もやったことのない操作ですが）「VBAProjectのコンパイル」という項目があったので実行し、
その後保存してみたところ、
3MBだったこのExcelファイルが3.2MBになりました。

> その設定で肥大化は防げるということです。

了解しました。
今後の肥大は防げるということですね。
これ以上そうそうソースは増えそうにありませんが、一応外しておこうと思います。

> 「最適化」という言葉での切り返しは、現状のご質問の中で、
> どの程度の理解の上でおっしゃっているのか意味が分かりかねますが、

申し訳ありません、全く理解しておらずに使いました。
意味があるとは思いませんでした。
「似たような作業を別モジュールでやらずに１つにまとめる」とかそんなレベルの話をしていたつもりでした。
（よく考えるとそれを最適化と表現するのはおかしいですね）

私もファイルが3MBになったところで特に問題は出ていないのですが、
このマクロが書き込まれたファイルを保存する際に10秒くらいかかる点は少々気になります。
この件で続けてここに質問するのは最初の質問からズレますが、
回答者様のその12MBのファイルだと、保存時にそれ以上の時間がかかるのでしょうか。
それとも私のマクロが書かれたファイルの保存に10秒もかかるのはまったく別の原因でしょうか。
（試しに容量の多いファイルを作ってみようと全セルに色塗ってみたりしたのですが、そんなんじゃ増えませんでした）

あの、これこそここでする質問ではありませんが、

> 確か、以前の話では、UserForm も多用しているような気がしましたが、

という一文が気になるのですが、
これは、私が「教えて！goo」に投稿した過去の質問のことを言っているのでしょうか。
確かに過去UserFormに関する件で質問し、Wendy02様に回答を頂いたことがあるのですが。
もしそうなら、数々の回答お世話になっております、ありがとうございます。
深読みしすぎでしたら、申し訳ありません、忘れてください。

回答No.3

keithin

＞これは具体的にはどのようにやるのでしょうか。

ん～～～と。。つまり「他のブックを開いて操作する」マクロの書き方が判らない，というご質問という事で良いのでしょうか？
当初ご質問とのギャップに，ちょっと戸惑いを憶えますが。

ごくシンプルな作成例：
sub macro1()
　dim wb as workbook
　set wb = workbooks.open("c:\folder\book2.xls")
　thisworkbook.worksheets(1).range("A1") = wb.worksheets("Sheet1").range("A1")
　wb.close false
end sub

screenupdatingを抑制したり，getobject関数の類を利用して，開いているところを見せない細工をすることもあります。

また勿論逆に2.2MBブックを主に開くこととして，マクロブックを呼び出す小さなプログラムだけを持たせておいて，必要なモジュールを持ったブックを必要なときに開いて必要なマクロを動かすといったやりくりもあります。これを延長していくと，メインのマクロをアドイン化して実装しておくような方向になります。

お礼

申し訳ありません。
「具体的にはどうなる/どういう体制になるのでしょうか」という意図でした（勿論マクロの書き方も判らなかったですけど）。
あたかも軽いファイルだけ存在させておいて、
必要な時にコードが沢山入ったファイルを開いて操作する、
という仕組みなのは理解できました。
ありがとうございました。

「アドイン化」についても、なにやらファイル化するようで、
ぼんやり想定していたことと全く違うことがわかりました。
Excel2008でマクロを開くとメニューに「アドイン」と出て、直接Moduleを呼び出せる作り方があるのでアレのことかと思ってました。
呼び出す先がファイルか内部Moduleかの違いなのかもしれませんが。

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

回答No.1

keithin

VBAはインタプリタなので，基本そういう事は出来ません。

＞マクロを実行する側としてはソースはいらない

データブックにマクロは要らないと言いたかったのなら，別ブックにマクロを持たせてそちらから実行しても出来ます。
たとえば「個人用マクロブック」，たとえば「アドイン」などに。
勿論，「仕事の開始ブック.xls」と「2メガのデータブック.xls」の２冊でも構いません。

補足

回答ありがとうございます。
なるほど、確かにエラーも実行時に発見されますね。

> たとえば「個人用マクロブック」，たとえば「アドイン」などに。
> 勿論，「仕事の開始ブック.xls」と「2メガのデータブック.xls」の２冊でも構いません。

これは具体的にはどのようにやるのでしょうか。
これを行うと、
　・仕事の開始ブック.xls　-----> 16KB（仮）
　・2メガのデータブック.xls　--> 2MB
となり、
見える位置、たとえばデスクトップには「仕事の開始ブック.xls」のみを置いておく、
といったことができるのですか？