作ったマクロが増えてきて、どう管理したらよいか

質問

Excel2007を会社で使っていて、業務に小さなマクロを作って作業に利用しています

今まで何も考えずにＰＥＲＳＯＮＡＬ.ＸＬＳＢ内にいくつもマクロを組んでいたのですが
最近どのマクロがいつ何のために作ったのかが把握できなくなってきました。

また、Publicで宣言した変数を関係ないSubプロシージャが読んでしまうという失敗もしてしまいました
PERSONAL.XLSB 内でPublic変数を宣言すること自体良くなかったのでしょうか。

他にも「エクセルでマクロを表示すると全て表示されてしまうのでSubプロシージャを"Personal Sub"にすると良い」
と聞いたのでやってみたところユーザーフォームから呼び出せなくなってしまったり……

もう少しマクロ同士を分けて保存したり、管理したりするという方面の知識が必要だと感じています。

マクロの管理方法の常識、みたいなものはあるのでしょうか、
自分はほとんど自己流でやってきたのでその辺りの知識が全くありません。
手持ちの教科書や参考サイトには作り方は載っていても、マクロをどう整理して管理したらよいかというのはあまり見当たりません。

基本にするべき考え方や、参考になる書籍などをどなたかご紹介いただけないでしょうか。

回答No.2

DexMachina · ベストアンサー

【要旨】

Function/Subの内容に応じてPersonal.xlsbとは別のファイルに分割して
保存した上で、

　a)Personal.xlsbと同様に、ワークシート関数としてや『マクロ』ダイアログから
　　の呼出を行うだけなら「XLStart」フォルダに保存
　　(→Function/Subを定義したのとは別のブックのVBAでは使用不可)
　b)別のブックのVBAからも呼び出す必要がある場合は、『参照設定』を
　　使用
　　(→フォルダはどこでも可)

といった使い分けをします。

なお、これらのファイルのワークシートが表示されていると、誤った編集の
元となり得るため、これらのファイルはウィンドウを「非表示」に設定します。

【詳細】

私も自己流なのですが・・・(汗)
Personal.xlsbへの記録で事足りているということですので、自分専用で
使われているマクロと推測します。

Excel2003以前のPersonal.xls、同2007以降のPersonal.xlsbは、
マクロの自動記録を行うなどして一旦作成すると、次回以降はExcel
を起動するたびに自動で参照可能になるため、特別なファイルと考えて
しまいがちかもしれませんが、実際に『特殊』なのはこのファイルではなく、
このファイルが保存されている『XLStart』フォルダになります。
(Personal.xlsbに限らず、このフォルダに保存されたExcelのファイルは、
　Excelの起動と同時に自動で起動されます：
　マクロウィルスにはこの機能を悪用したものもあるので、注意が必要)

<Personal.xlsbが既存の場合のXLStartフォルダの確認方法>
　1)Alt+F11キーの同時押しなどでVisual Basic Editorを開く
　2)【Personals.xlsbの適当なモジュール】を選択する
　3)Ctrl+Gキーの同時押しなどでイミディエイト ウィンドウを開く
　4)イミディエイト ウィンドウに以下の式を入力したら、Enterキーを押す
　　?Application.VBE.ActiveVBProject.FileName
　5)すぐ下の行にPersonal.xlsbファイルのフルパスが表示されるので、
　　フォルダを確認すればOk
　　※別のファイルのパスが表示された場合は、「2」の手順を再度確認

また、VBAには『参照設定』という方法もあって、これを使用すると、
別のブックで定義したSub/Functionを使用することも可能です。

<参照設定の手順>
1)VBAのメニューで「ツール(T)→参照設定(R)」を選択
2)『参照設定』ダイアログが開くので、右側にある『参照(R)』ボタンを
　クリック
3)『ファイルの参照』ダイアログが開くので、一番下にある『ファイルの
　種類(T)』欄で「Microsoft Office Excel Files (～)」を選択
4)必要なファイルを指定した後、『開く(O)』ボタンをクリック

上記のようにファイルを分割すれば、例えばリボンの『開発』タブの
『マクロ』ボタンで『マクロ』ダイアログを表示させた場合は、Subの前に
ブック名が表示されるので、(ブック名が適切であれば)それぞれのSub
の内容を推測可能になるかと思います。

また、VBA上では、『参照設定』をしない限り他のブックのFunctionや
Subを呼び出そうとするとコンパイルエラーとなるので、間違って使って
しまうことも回避できます。

なお、上記でも書きましたが、通常の新規ファイルにマクロを記述した
だけだと、上記のどちらの方法を使用しても、Sub/Functionを定義
したブック(のシート)が表示されてしまいます。
そのままですと、新規ファイルと間違ってセルに入力をしてしまったりする
元となりますので、リボンの『表示』タブの『ウィンドウ』グループ内にある
『ウィンドウを表示しない』ボタンを使用して、ファイルを非表示に設定
することをお勧めします。
(Personal.xlsbも、この設定が行われているため、通常、起動はして
　いてもシートが目に触れることがなくなっています)
http://pc.nikkeibp.co.jp/article/technique/20090521/1015308/?rt=nocnt

・・・長文、失礼致しました。

お礼

おっしゃる通り Personal.xlsb が毎回勝手に起動してくれて便利なのでそこばかり使っていました。
同じ業務につかうマクロは同じブックに…等とするのがよさそうですね。
良く呼び出すようなプロシージャは参照設定で登録しておけばよい感じでしょうか。

いろいろと詳しく解説していただいてありがとうございます。

お返事遅れて申し訳ありません。

回答No.1

hallo-2007

>今まで何も考えずにＰＥＲＳＯＮＡＬ.ＸＬＳＢ内にいくつもマクロを組んでいたのですが
作成したエクセルを自分以外の人が操作する、ほかの人にも配布して使ってもらう
他の人が引き継いで、新しくマクロを組みなおす事がある
などを念頭において作ることでしょうね。

基本は、それぞれのブックのモジュールに記述しておいて、ボタンなどのイベントにするのが
他の人から見てもわかりやすいです。

それと、マクロ記述時に
コメントを挿入しておくことも、自分にも他人にも親切です。

ＰＥＲＳＯＮＡＬ.ＸＬＳＢは、自分専用のマクロをとりあえず保存しておく
よく使うコード集を保存しておいてコピペ用にとか云った具合にしか使っていません。

お礼

ショートカットキーへの登録が主で、ボタンへの登録は習いはしたのですが面倒であまり使っていませんでした。
確かに他人に渡す時にはそちらの方がよいですね。

コメントについては自分でもわかっているのですが、どうしてもソースをいじる方にばかり頭がいってしまってよく忘れます。

一番のご回答ありがとうございました。