配布の問題とは、実際は「配布の問題」です。継続的に利用を繰り返すExcelでの仕組みの場合、その「仕組み」の修正が必要となった時どのように対処するかがここでのテーマです。
作成した仕組み(定型業務で、それも数千行のマクロを記述するカッチリした仕組みのもの)を大勢に配布して運用してもらっている私にとっては重大な問題です。
同じ機能のブックを多数配布する場合の問題    勤務状況などの報告書を作成するツールなどを始め、社内で運用するものをExcelでかなり作成しています。
集計諸表ならExcelを利用することは相当昔から一般的だと思いますが、報告系の書式ではどうでしょうか。 私の所属する会社では私自身が率先して進めていることもありますが、報告書式にExcelを採用することで再利用を含めて合理性が進み、さらにはペーパーレス化によっておそらくコストダウンにもつながったことと思います。
こういったものは「原紙」あるいは「テンプレート」から個人毎や業務毎に「運用」ワークブックを作って長期に渡って繰り返し使用するために一度作成したものが種類によっては数百の別ワークブックになって運用されています。

そもそもExcelワークブックというのは、シートのデザイン、計算式、マクロなどの「仕組み」でありながら、ユーザーがワークシートに入力した「データ」も同一ファイル(ワークブック)の中に収容されていきます。

Excelはデータと仕組みが渾然一体になったもの

このような感じですね。この図では区別されているように見えますが、外部からは1つのExcelワークブック(*.xls)ですから、データと機能部分(プログラム)が渾然一体となっているのです。

会社や部署内で「皆で使えるものにしよう」とワークシートのデザインだけでなく、計算式やマクロを仕込んだワークブックを作成して、関係各所に配布します。
配布後はリネームされたり、あちこちバラバラに保存されたりしてしまう訳です。
テンプレートとして社内の標準的な場所に保存させて利用させることである程度統制を取ることは可能でしょうが、報告書式のようなものを例として考えると毎回テンプレートから新規作成してくれるのかは難しいと思います。 なぜなら、報告者の氏名や部署はもとより、報告に関する扱い商品、プロジェクトなどは以前に作成した報告書からのコピー利用の方がはるかに便利で合理的だからです。

このような状態で、後から「仕組み」部分だけを変更しなければならなくなった場合にどうするかが、ここでのテーマなのです。

あちこちに点在しているものの中の「仕組み」だけ入れ替えたい

1フォルダに集約してもらって、がんばって1つずつ開いて入れ替えるの関の山といったのが普通の状態だと思います。何十、何百もあったらどうするのでしょう!?

もちろん、このような話になる以前に「なぜExcel「仕組み」を作るのか」があります。
まず、業務報告等の書式として便利であることは皆さんご承知の通りですが、ここに「仕組み」が伴わなければワープロの文書と何も変わりません。現に入力した文書をファイル名を付けて多数のワークブックとして保存させての運用は数多く見られます。
ですが、このような方法だと一覧表を作るだけで大変な作業になる上、その維持も困難になります。また、テキストファイルなら数KBで済む所が、1つ1つがワークブックで保存されるとなると数十倍以上のリソース食いに膨れあがってしまう問題も発生します。
ここをうまく仕組めば、Excelの入力のしやすさ、データの一元化などを両立させる仕組みを構築することが可能なのです。また、Excel自体にそれをこなせるだけのパフォーマンスがあるのです。
逆に言うと、こなせるからExcelでそう言った仕組みを作るわけですね。
変更要件の発生は?
一回利用して捨ててしまうような仕組みであればこの問題は発生しません。ですが、長期に利用するような「仕組み」であったり、単発利用のつもりが結局、後まで継続利用されてしまうようなこともよくあります。

「仕組み」を後から変更する要件は、次のようなものがあります。
配布してから動作上の不具合が発生(BUGや仕様上の問題)したことによる対応。
配布したものに対して、機能追加・改変の要求が発生したことによる対応。
規程、規則、法律の改正によって仕様の変更が発生したことによる対応。
WindowsExcel自身の利用されるバージョンの対応で時代に合わせて変更対応する必要性。
社内のストレージやデータベースサーバの変更など記述実装している環境の変更による対応。
つまり、「充分テストしたからBUGの心配もなく多数配布されても大丈夫」ということでは済まないということです。この件に配慮しないで済むのは、作成した仕組みが使われる範囲や期間がはっきり限定されている場合のみです。
実際のところ、Excelはデザインや計算式などは簡単にできてしまうので、私の会社での取扱いでは「内製システム」という甘えもあって事前の要件設定が充分に行なわれないことから「プロトタイプ」的に運用開始してしまうことも多く、後から「仕組み」を変えることは実際問題として日常茶飯事です。

では、「後から仕組みだけを変更する」のはどのような方法があるのでしょうか。
「仕組み」=マクロ(VBA)と考えて下さい。マクロの記述の変更(差し替え)をどうするかという問題です。シートデザインや計算式もありますが、マクロの入れ替えができればその時点でマクロ側から書き換えが可能です。

変更のマクロ記述を公開して、ユーザーにVBEを開いて変更してもらう。
エンドユーザーがExcelVBAに詳しければ「あり」かも知れませんが、ここで説明するには身も蓋もない方法です。詳しく説明するまでもないでしょう。実態は開発者が運用中のワークブックをひとつひとつ開いて修正せざるを得ません。
何の配慮も行なわなければこのようになるのですが、申請諸表や報告書類などだと案件ごとにファイルが複製で作成されるので膨大な量のファイルとの格闘になってしまいます。 テンプレート化したところで、新規作成がかならずテンプレートから開いて作成されるとも限らず、テンプレート自身も所定の場所から別の場所にコピーされていることもあるでしょう。
また、バージョン管理の問題もあります。初心者が作成したマクロはこの配慮がされていないことがほとんどです。つまり、ワークブックを見てマクロの更新要否がどのように判断できるのかということです。

モジュール自体を入れ替えてもらう。
上記のマクロ記述の直接変更よりは多少「まし」ですが、エンドユーザーへの操作上の負担はあまり変わりません。方法は、
  • 1.現状のモジュールの「解放」
  • 2.新しいモジュールの「インポート」
です。
モジュールのインポート
方法が判れば簡単ですが、以下の問題があります。
  • 立ち上げマクロで動作中の状態であると、モジュールの解放時点でエラーが起きることがある。
  • VBプロジェクトがパスワード保護されていると実施できない。(パスワードを通知しては保護の意味がない)
  • 標準モジュールやクラスモジュールは解放→インポートで入れ替え可能であるが、シートやワークブックのイベント記述部分は不可能。(VBEで書き換えるしかない)

新しいバージョンのワークブックに元のワークブックから「データ」を貼り替えてもらう。
私は以前はこの方法を良く使っていました。元々「原紙」のファイル名を固定しておき、「原紙」を立ち上げた時にフォームを立ち上げて設定内容を登録してもらう方法を採っているため、そのフォーム上に「既存ブックの更新」のボタンを用意しておき、そのボタンをクリックした時には「ファイルを開く」のダイアログで元ワークブック指定させて、元ワークブックの内容を「原紙」側に全部写します。次に元ワークブックを閉じた上で、「原紙」を元ワークブックの名前で保存(上書き)させます。つまり、「仕組み」を入れ替えるのではなく、新しい「仕組み」の方に以前の「データ」を載せ替えるのです。
導入処理に既存ブック更新の機能を実装
この「データの入れ替え機能」は新しいバージョンの「原紙」に実装させておけば良いわけで、元々のワークブックにこのような仕組みの配慮がなくても後から実装させれば済みます。但し、「データ」の登録状態が新しい「原紙」側の入れ替えマクロから判別できなければならず、入力項目が限定されており、シートやブックが保護されているような仕組みでないと実装は困難です。

モジュール自体を入れ替えるマクロを実装する。
これはかなり「高度」なやり方になりますが、不可能ではありません。VBAProjectを操作するマクロを作成することになります。
コードのサンプルは提示しませんが、以下のような処理になります。
@
ワークブックには設定シートや隠しセルに「バージョン番号」を記録しておく。
A
交換するモジュールは先頭行にコメントで「バージョン番号」を記述しておく。
B
立ち上げマクロを用意し、その中でモジュール交換のプロシージャを呼ぶ。モジュール交換のプロシージャは、それだけを単独の標準モジュールにしておく。他のプロシージャと同じモジュールやワークブックイベントのページに記述してしまうと、「それ」自身の入れ替えができなくなってしまう上、ウィルスチェックプログラムに「新種のマクロウィルス」として捕まります。
C
モジュール交換のプロシージャでは、まず、ワークブックの所在フォルダにモジュール(*.bas)やフォーム(*.frm)、クラス(*.cls)のファイルがあるかをDir関数等で判断する。
D
交換モジュールが見つかった場合は、交換モジュールをテキストファイルとして開き、1レコード目は「Attribute VB_Name = "Module1"」なので読み飛ばし、2レコード目のコメントマークの右の「バージョン番号」を読み取り、自ブックに記録してある「バージョン番号」と比較して入れ替えが必要か判断する。
E
VBIDE」オブジェクトから「VBE」「VBProjects」「VBComponents」を取得して、「DeleteLines」で全行を削除してから、「AddFromFile」で上記のファイルからモジュールを取り込む。
F
ユーザーフォームの場合は、「Remove」→「Import」で処理する。
これが実現できれば、改変があったモジュールを入れ替えるワークブックと同じフォルダにコピーしておくだけで、次回開いた時に「入れ替え」が行なわれます。
モジュール自動更新の確認メッセージ
「サンプルは提示しない」理由は、安易にこれを公開した場合、悪意を持った人にとっては以前にはやった「マクロウィルス」のようなものが作れてしまうからです。私は悪意は持っていませんが、このサンプルになっている機能の作成段階で当初、起動イベント(ThisworkbookWorkbook_Open)にこの機能をの記述してテストしていたところ、常駐のウィルス検索ソフトに「新種のマクロウィルス」としてトラップされ、駆除動作に掛かったことがあります。
また、この方法は完全ではなく、VBプロジェクトがパスワード保護されていると実施できません。VBプロジェクトの保護はワークシートと違い、マクロからの解除できません。
さらに、上記が解決してもExcel2002以降では、VBEを操作するマクロを実行すると、
実行時エラー '1004': プログラミングによる Visual Basic プロジェクトへのアクセスは信頼性に欠けます
強化されたセキュリティチェックに掛かってしまいます。
(これはセキュリティの設定で回避できますが、デフォルトではこのようになります。)
モジュール入れ替え機能!? ※この説明に誤りはないと思いますが、実際のところVBAProjectを操作するマクロは今やネット上のいたるところで紹介されています。ここで拒否だけしていても意味はありませんので、「モジュール自動入れ替え機能」を紹介することにしました。

「仕組み」の部分を「アドイン」に分割しておく。
「アドイン」というキーワードが解らない方は「アドインとは!?」で説明します。
ここでの位置付けは、データが登録されるワークブックからマクロ記述部分を切り離すことです。
ワークブックからマクロの主要部分を切り離す。
これが一番お勧めする方法です。「アドイン」を使うのは、「アドイン」は開いてもワークブックとして認識されないからであって、この点に配慮しなければ単に別のワークブックでも構いません。
運用のワークブックには必要なイベント記述を実装しますが、どれも「アドイン」側のプロシージャを呼び出すだけにしておきます。実際の動作部分は全て「アドイン」側に実装させます。当然、「アドイン」は運用するワークブックの立ち上げ処理の中で開いておきます。運用者に意識させてはなりません。
プロジェクトエクスプローラの表示例

一般的な「アドイン」とは、Excel全体に対する追加機能の実装(普通は常駐させるもの)ですが、ここで説明する「アドイン」は特定のワークブックのために実装する機能であって、他のワークブック上から呼び出されては都合が悪い場合が多いものです。「アドイン」は拡張子が「xla、xlam」となっており、開いてもワークブックとしては認識されませんが、VBプロジェクト上ではワークブック同様に扱われます。
つまり、要件から言うと「アドイン」ではなくマクロ専用の「ワークブック」としてデータを登録する「ワークブック」として「仕組み」から分離するのでも良いのですが「ワークブック(*.xls、*.xlsm)」だとExcel側にウィンドウとして表示されてしまうため都合が悪いので「アドイン」にしているのだとご理解下さい。

この方式で難しいのは、以下のポイントの克服です。
  • アドインの呼び出し(開く)方法。
  • アドイン内のプロシージャの呼び出し方法。
  • アドインを呼び出したワークブックが閉じられた時のアドインの処置。
  • 複数のワークブックが開かれている時のアドイン側から処理するワークブックの特定。
  • 同じアドインを呼び出すワークブックを複数開いた時のアドインから見たコントロール。
Excelに常駐させておく「アドイン」とは異なるため、この「アドイン」を必要とするワークブックが開かれた時に同期的に「アドイン」を開いて利用し、不要になったら「アドイン」は閉じてしまいたいということを実現させようとしているということです。
これらについては、「アドインにVBAの主機能を収容する。」 で詳しく説明します。