VBAで複数ウィンドウのシート移動を同期させるクラスを作ってみた

今回はVBAで複数ウィンドウのシート移動を同期させるクラスを作ってみたので紹介します。

なにこれ？
まずは下準備
シート変更を監視して切り替えを同期させるクラス
使い方
さらなる実用化を目指して
まとめ

なにこれ？

私は業務上、膨大な調書の入ったエクセルファイルを扱うことがよくあります。

f:id:Kotori-ChunChun:20190128012413p:plain

先日こんな感じの様式の調書がありました。

f:id:Kotori-ChunChun:20190128000931p:plain

最終的には印刷するのですが、画面上でチェックするに当たって、

文字が判読可能なサイズで3枚とも画面上に並べたい。
3つに画面を分けたとしてシートが連動して切り替わって欲しい。

と。そういう需要がありました。

A4をデカデカと３つも表示するディスプレイがあるんかい！って話は、無いと仕事が進まないので、あるものとお考え下さい。

イメージはこんな感じ。

f:id:Kotori-ChunChun:20190128001541p:plain

まずは下準備

本命の前にまずは一つのコードをご紹介。

Sub アクティブシートのスクロール位置を全シートに反映()

    Dim ActWin As Window
    Set ActWin = ActiveWindow
    
    Dim Win As Window
    For Each Win In ActWin.ActiveSheet.Parent.Windows
    
        Win.Activate
        
        Dim ActWs As Worksheet
        Dim ActRow As Long
        Dim ActCol As Long
        Dim ActZoom As Long
        
        Set ActWs = Win.ActiveSheet
        ActRow = Win.ScrollRow
        ActCol = Win.ScrollColumn
        ActZoom = Win.Zoom
    
        Dim ws As Worksheet
        For Each ws In ActWs.Parent.Worksheets
            ws.Activate
            Win.ScrollRow = ActRow
            Win.ScrollColumn = ActCol
            Win.Zoom = ActZoom
        Next
        
        ActWs.Activate
        
    Next
    
    ActWin.Activate
    
End Sub

詳しい解説はプロシージャ名の通りなので省略します。わかりやすさ大事。

強いて言うならAlt→W→Nで新しいウィンドウの表示を2回実行してウィンドウ3つ開いてから、それぞれのシートを目的の位置までスクロールしてからこのマクロを実行します。

これであらビックリ、数百シート全てのスクロールが整います♪

シート変更を監視して切り替えを同期させるクラス

一応完成品のクラスがこちらclsAppSheetViewです。

Debug.Printは後ほどの説明で使うために残しただけなので、いらなければ消しましょう。

Option Explicit

Private WithEvents App As Application
Private BindWBN As String

'インスタンス生成と時点のActiveWorkbookを同期対象として登録する。
Private Sub Class_Initialize()
    Debug.Print "clsAppSheetView Initialize"
    Set App = Application
    BindWBN = ActiveWorkbook.Name
End Sub

Private Sub App_SheetActivate(ByVal Sh As Object)

    If Sh.Parent.Name = BindWBN Then
        Application.EnableEvents = False
        
        Dim ActWin As Window
        Set ActWin = ActiveWindow
        
        Dim Win As Window
        For Each Win In Sh.Parent.Windows
            Win.Activate
            Sh.Activate
        Next
        
        ActWin.Activate
        
        Application.EnableEvents = True
    End If
    
End Sub

Private Sub Class_Terminate()
    Debug.Print "clsAppSheetView Terminate"
End Sub

解説も・・・するほどの事はしていないので良いでしょう。

とりあえず、これでSet cApp = New clsAppSheetViewみたいにインスタンスを生成した瞬間に同期が始まります。

超便利っ♪

使い方

さて、本来であれば話はこれで終わりなのですが、実際に使うとなると利便性も大事です。

なんと、このクラスの使い方次第で、

変数の宣言とNewを同時にしたほうが良い時としないほうが良い時
Staticステートメントの有効な使い方

が説明出来ることに気が付きました。

不慣れな人がやりそうな失敗作

まずはWithEvents App As Applicationの経験がない人のやりそうな失敗コードです。

Sub シート連動開始_sample1()
    Dim cApp As clsAppSheetView
    Set cApp = New clsAppSheetView
End Sub

実はこれ、全く意味がありません。

プロシージャレベルで宣言されたcAppはEnd Subを過ぎた瞬間にインスタンスが破棄されてしまいます。

クラスのインスタンスが開放されてしまってはWithEventsとあれども監視出来ません。

一般的な使用例

WithEventsのサンプルとして紹介されている方法では、モジュールレベル変数にインスタンスを格納するのが一般的かと思います。

Private cAppMod As clsAppSheetView
Sub シート連動開始_sample2()
    Set cAppMod = New clsAppSheetView
End Sub

特にこれでも問題ありません。

というか大抵の場合はこれが正解です。

しかし私としては、このプロシージャしか使わないような変数をモジュールレベル変数にしたくありません。

変数のスコープを極限まで小さくした例

あまり聞き慣れないかと思いますが、Staticステートメントというものがあります。

プロシージャ中の変数の宣言でDimの代わりにStaticを使うことで、プロシージャが終了しても値を保持し続けることの出来る変数（静的変数）として宣言することが可能です。

静的変数を使うことで次のように書くことが出来ます。

Sub シート連動開始_sample3()
    Static cApp As clsAppSheetView
    Set cApp = New clsAppSheetView
End Sub

静的変数はある条件さえ無視することが出来れば、変数がモジュールに散乱しないので非常に便利です。

また再帰関数なんかでも私は使うことがあります。

ある条件とは・・・他のプロシージャからcAppにアクセスできないため、インスタンスの破棄を明示的に行うことが不可能となります。

そして今回のクラスをよく読んで頂くと分かるのですが、clsAppSheetViewのインスタンスが破棄されなくても誤動作は起こらないように設計してあります。

（敢えてWorkbookを文字列で保持している点です）

このように破棄を意識する必要ないクラスにおいては静的変数は無類の力を発揮します。

ちなみに静的変数の生存期間はモジュールレベル変数と同じです。

VBA上の中断ボタンが押された時
例外が発生して「終了」ボタンが押された時
ENDステートメントによってマクロが強制終了された時

もう一つの失敗作

ここでもう一つの失敗事例を紹介しましょう。

Sub シート連動開始_sample4()
    Static cApp As New clsAppSheetView
End Sub

変数の宣言とNewを同時に行った場合です。

VBAでは宣言とNewを同時に行うと初めて変数を使用した時にコンストラクタが実行されます。

つまり、宣言文しかないこのコードは、Class_Initialize()に入ること無くプログラムが終了してしまうため、App_SheetActivateイベントは永久に起こりません。

必ず宣言文とインスタンス生成の式は分けて書きましょう。

さらなる実用化を目指して

ところが実際にお仕事していて、昔に作成したブックと、新たに作成したブックの2つを並べて見たくなりました。（んな馬鹿な）

f:id:Kotori-ChunChun:20190128010427p:plain

要望通り２つのブックのみ対応としても良いのですが、贅沢なことりちゅんは、この程度では満足しません。

何ブック比較したくなっても良いように、コレクションを使って無限に対応出来るようにします。

多ブック対応した時の失敗例

何も分かっていない人は、たぶん再び失敗します。

先ほどは変数の宣言とNewは同時に行ってはいけないと説明しました。

正確には同時に行うと初期化が行われないです。

そうして書くてあろうコードがこちらです。※エラー処理省略

Sub シート連動開始_sample5()
    Static cApps As Collection
    Set cApps = New Collection
    cApps.Add New clsAppSheetView
End Sub

こんなコードを書いてしまったら、プロシージャを実行するたびにcAppsが初期化されて、最後に実行したブックしか監視対象となりません。

多ブック対応の正しい例

だからこの場合は、同時に書くのが正解なのです。

Sub シート連動開始_sample6()
    Static cApps As New Collection
    cApps.Add New clsAppSheetView
End Sub

こうすることで、初回のプロシージャ実行時はcApps.Addの直前にNew Collectionが実行され、2回目以降はNew Collectionは実行されません。

完成形

上記のコードはエラー処理（同一のブックを二度登録出来てしまう危険性）を考慮していないため、それを考慮するならば次のようなコードが完成形となるかと思います。

Sub シート連動開始_sample7()
    Static cApps As New Collection
    On Error Resume Next
    cApps.Add New clsAppSheetView, ActiveWorkbook.Name
End Sub

まとめ

使い方の部分が妙に長くなりましたが、複数ウィンドウのシート移動を同期させるクラスの紹介でした。

これで先日のクイズの問14に対する具体的な説明ができたと思います。

実際にNewのタイミングで困る事は少ないのですが、稀にこんな事もあるよ。ということを知っておくと良いと思います。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ