動画『表計算の福音～エクセルでここまでやれる～VBA解説編④』をYouTubeにアップしました。
今回のテーマは「複数のWorkbookを使って一つの統一的な一個のシステムを構築する」です。
動画はこちらから
『購買くん』のようなそれなりの規模の一個の業務システムを、単一のExcelファイルで作るのは困難ですよね。
複数の、役割の違うファイルを使って業務を処理するにどうしたらいいでしょうか？それが今回のテーマです。
今回はそのためのコツというか、要領をご紹介します。大きくいって三つあります。
- データベースは単一の、共通のものを使用する
- メインとなるファイル(以下Main Workbook)を定める
- ファミリーファイルの間で、ある程度UIデザインの統一性を持たせる
それではそれぞれについてみていきましょう。
要領① データベースは単一の、共通のものを使用する
データの規模がそれほど多くないとしても、Excel Workbookが複数に分かれるとしたらなおさらのこと、データは各Workbookに分散して持つより、単一のデータベースで持つ方がシステムの開発・保守の面でも、パフォーマンス（個々のファイルの反応速度）の面から言っても合理的です。
『購買くん』で使用しているデータベースアプリはSQLiteです。
SQLiteには次のようなメリットがあります。
- 『購買くん』のような、ここで想定している規模、つまりExcelを主体にしてシステムを組むようなケースでの想定される規模感のデータ量には適していることです。
- SQLiteはサーバーベースではなくファイルベースのデータベースですので、扱いが簡単であること
- Excelから直接操作できること
- 無料であること
SQLiteの入手方法、Excelによる操作方法については、別途のテーマとして動画を投稿しますのでここでは詳しくは触れませんが、いずれにせよ、どのようなデータベースを使用するにしても、複数のWorkbookにデータが分散している状態が好ましくないのは容易に想像できることと思います。
要領② Main Workbookを定める
ひとつの統一システムとしてのシステム全体を管理する役割を負うWorkbookを一つ定めます。(『購買くん』の例では、これがDashboard.xlsmというブックになります。)
このブック(以後メインブックと呼びます）をユーザーが最初に開き、最後に閉じるべきブックということをルール化します。言い換えれば、「このMainブックが開いていないのに、他の購買くんファミリーのブックが開いていることはありえない」状態をつねに守るということです。
そのために、ユーザーがメインブック以外の購買くんファミリーのブックを開こうとしたとき、Dahsboard.xlsmが既に開いているかを確認し、開いていなかったら、メインブックをまず開くようメッセージを表示し、自身のブックは有無を言わさず閉じてしまう、もしくは、メインブックを勝手に開いてあげるか、のどちらかの処理をそのブックのThisworkbook の Workbook_Openイベントプロシージャに設定しておきます。そのブックのThisWorkbookモジュールの、Workbook_Openイベントプロシージャの中に次のようなコードを含めます。
Private Sub Workbook_Open() ‘に以下の部分を含める
Dim MainBkNm As String, wb As Workbook
MainBkNm = XXXX ‘メインブックファイル名
Set wb = utility_wbOpen(MainBkNm)
Set wb = Nothing
ThisWorkbook.Activate
End Sub
以下のプロシージャを新設する
Function wbOpen(ByVal BkNm As String) As Workbook
Dim i As Integer
‘指定ブックが開いていればそのブックオブジェクトを返す
For i = 1 To Workbooks.Count
If Workbooks(i).Name = BkNm Then
Set wbOpen = Workbooks(i)
Exit Function
End If
Next i
‘指定ブックが開いていなければ開いてそのブックオブジェクトを返す
Set wbOpen = Workbooks.Open(ThisWorkbook.Path & “\” & BkNm)
End Function
さて、今度は逆に、メインブックがユーザーによって閉じられようとするとき、まだ他のファミリーブックが開いたままだったら、それを阻止しなければなりません。
そのコードは以下のようになります。
Private Sub Workbook_BeforeClose(Cancel As Boolean) ’に以下のコードを含める
Dim wb As Workbook
For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then
If wb.BuiltinDocumentProperties(“Subject”) = “ファミリーの名前” Then
wb.Activate
MsgBox “ファミリーのブックを全て閉じてからこのブックを閉じてください。”
Cancel = True ’閉じるのを中止
Set wb = Nothing ’オブジェクト変数を開放して終わる
Exit Sub
End If
End If
Next
End Sub
ここでBuiltInDocumentProperties(“Subject”)というのは下記の設定を指します。ファミリーのブックにはあらかじめすべてこのように登録しておきます。
さて、ここでMainブックを定めることのメリットを解説していきたいと思います。
Mainブックを定めることのメリット①：プロシージャの共通化
二つ以上のブックで共通化できるプロシージャはMainブックに記述し、各ブックからそれを呼びだせるようにすることでCodingを省力化することが出来ます。
例えば、シート保護のプロシージャがそれです。
Sub ProtectSh(ByRef Sh As Worksheet, ByVal ToProtect As Boolean)
If ToProtect Then
Sh.Protect Password:=”XXX”), userinterfaceonly:=True
Else
Sh.Unprotect Password:=”XXX”
End If
End Sub
すべてのブック・シートで、シート保護したり保護解除したりする処理を実行するとき、これを１か所に記述しておけばいいわけです。
他のブックからそれを呼び出すときには以下のコードにようにApplication.Runメソッドを使います。
Sub ProtectSh(ByRef Sh As Worksheet, ToProtect As Boolean)
Dim BkNm As String
BkNm = ”メインブックのファイル名”
Application.Run BkNm & “!ProtectSh”, Sh, ToProtect
End Sub
VBA関連のネット情報には、「Application.runを多用するのはコードの依存関係を追うのが困難になり、保守の面から推奨しない」旨の記述もありますが、私は、このような一定のルールのものにオーガナイズされた形で使用する分には管理面からむしろ有益だと思います。
Mainブックを定めることのメリット②：システム管理上の情報の集中化
例えば『購買くん』のファミリーファイルのファイル名、構成シート名、その表示・非表示の別、シート保護の有無等の情報はメインブックの一シートに集中的に記述・保管しておいた方がメンテナンスがしやすくなります。各プロシージャからは、そこにある情報を見に来てそれを設定に使うといったしくみにしておくと、何か設定を変更するときにはメインブックのなかの一か所にまとめて規定しておく方が、いろいろなブックに分散して存在しているよりはるかに効率的です。
ここで一つの例をご説明します。
メインブックのSystemというシート（ユーザーには見せない非表示のシート。オブジェクト名はSysShとします）です。購買くんファミリーのそれぞれのブック・シートで使うショートカットキーは共通なものも異なるものもありますが、それをこのSysSh上で集中管理します。一番左の列に上からの各行にキー（Ctrl＋aなら”^a” です)の一覧を持たせ、第２列以後にそれぞれ対象ブック＋シートでそのキーが起動すべきプロシージャ名をブック名＋”！”＋プロシージャ名の形式で記入しておきます。
今あるワークシートがActivateされた時下記のようなコードを実行します。
Sub shortcut_SetKeys(ByVal ToSet As Boolean)
Dim i As Integer, j As Integer, BkNm As String, BkShNm As String
BkNm = ActiveWorkbook.Name
BkShNm = BkNm & “_” & ActiveSheet.Name
If SysSh.Range(“E1”) = BkShNm Then Exit Sub
j = SysSh.Range(“G1”) ’G1は該当ブック・シートに対応するこの表の列番号
If j = 0 Then j = 8 ’ショートカットのないブック・シートは8＝H列（標準）を使用
’表のH列にはすべてのセルに”Standard”と入力しておくWith SysSh.Range(“shortcutkeys”)
For i = 1 To .Rows.Count
If .Cells(i, 1) = “” Then Exit For ’1列目＝キー指定自体がない場合は終了
If ToSet And j >= 2 Then ’ショートカットを設定する指示で、かつ対象のブック＆シートがある場合
If .Cells(i, j) = “Standard” Then
Application.OnKey .Cells(i, 1) ‘OnActionがブランクならショートカット解除（標準に戻す）
Else
Application.OnKey .Cells(i, 1), .Cells(i, j)
End If
Else
Application.OnKey .Cells(i, 1)
End If
Next i
End With
End Sub
Application.OnKeyの構文は、
Application.OnKey 第１引数はキー名 第２引数はプロシージャ名 （他のブックの場合はファイル名＋！＋プロシージャ名）
第１引数のキーが押されたとき何もしない場合は第２引数には空文字列””を渡します。
第１引数のキーが押されたときExcel標準のショートカットキーと同じ動作をさせるのは、第２引数を
省略します。このようにActiveなブック・シートが変化するたびにショートカットキーを設定しなおすというプロシージャを各ブックにそれぞれ持つ代わりにメインブックで集中して管理したほうが容易です。
要領③ UIデザインの統一性
最後にデザインの統一性についてお話ししたいと思います。ここで言うデザインとはグラフィックデザインや操作性・操作感覚などの総合的なユーザーエクスぺリエンスを指しています。
VBA解説編①の動画で解説しましたユーザー向けUIの採用にも関連しますが、一定の決められた業務システムのUIとしてのExcelは、そうではない一般のExcelとはデザインの面でもはっきりと隔絶されたものであることが望ましいと思います。
上記のデザイン例はいかがでしょうか？一般のエクセルとは明らかに異質で際立った印象を受けるのではないでしょうか？ 一方同じファミリー内のブック間では、微妙に違うデザインとなっていますが、これはユーザーが今何を見ているのかを誤解・混同しない程度の変化を付けています。
また操作性の面でいうと、例えば入力項目を右クリックしたらヘルプへ飛ぶオプションが出てくるとか、ボタンを押すときシフトキーを押していると、そのボタンにかんする説明が表示される、などの使い勝手がその画面（ブック・シート）でも同一である、などのようなこともユーザーに一つの統一体としてのシステムを使っているという意識を持たせるのに有利となります。
コメント