Excel VBA 第4回 ActiveA1作成（すべてのシートのカーサーをA1セルに）

はじめに

私がよく実施する作業のひとつに、Excelのファイルの各シートのカーサーをA1セルにして、スクロールバーも左上にするという作業があります。
また、同時にExcelの表示倍率も100%にしたりします。
Excelでファイルを保存する場合、保存時のシートで、カーサー位置もそのまま保存されます。
いまどきのスマホやWindows 10でさえ、前回の作業していたアプリや場所を開いて起動してきますが、きっちりしたドキュメントのひとつとして作成する場合、開いたら表紙ページで開かれてほしいですし、各シートもトップにあってほしいですから、すべてのシートはA1セルにし、表示倍率は100%にしたりしたいのです。
頻出の作業をマクロにしているということを前に記載しましたので、そのひとつとして説明します。

今回実施する内容

実行すると、各シートのカーサーはA1にスクロールバーは、左上になるようなマクロActiveA1を作成します。
また、それに加えて表示倍率を100$にするActiveA1wZoom100を作成します。
これは、リボン上で扱えるようにします。

ソースコード (Git Hub)

VBA_04_RIbbon_ActiveA1

環境

OS:Windows 10 JP
Excel: Excel 2019 (32bit)

参考

Excel VBA 第1回 例外処理の対応（どのモジュールの、どの関数で例外は発生したのか？）
今回作成するマクロでは、例外処理も追加しますので、ここで作成したファイルを使います。

Excel VBA 第2回 リボンとアドイン作成
今回作成するマクロは、リボンで作成しますので、参考です。

Excel VBA 第3回 リボンにsplitButtonでボタンをまとめる
今回作成するマクロは、splitButtonを使いますので、これももとにします。

用語

作成

以下の手順で行います。

1. 「customUI.xml」の作成
2. マクロの作成

今回はマクロのほうがメインかもしれません。
「.rels」の編集などは、第2回を参照ください。

1. 「customUI.xml」の作成

以下の通りです。

customUI.xml

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false">
      <tabs>
        <tab id="Tab" label="OrgMacro">
            <group id="orgin" label="Origin">
				<splitButton id="orgSplitBtn" size="large">
					<button id="activeA1wZoom100" label="ActiveA1wZoom100" imageMso="A" onAction="R_ActiveA1wZoom100"/>
					<menu id="splitMenu" itemSize="large">
					<button id="activeA1wZoom100_2" label="ActiveA1wZoom100" imageMso="A" onAction="R_ActiveA1wZoom100"/>
					<button id="activeA1" label="ActiveA1" imageMso="A" onAction="R_ActiveA1"/>
					</menu>
				</splitButton>
            </group>
        </tab>
      </tabs>
    </ribbon>
</customUI>

splitButtonを使って、似たような機能についてはボタンにまとめています。
それ以外は特筆することはありません。
メニューで２つでてくるところはitemSize="large"にして表示を大きくしています。

2. マクロの作成

マクロは、以下のソースを作成します。

• ActiveA1.bas
• CreateObject.bas
• Constant.bas
• Ribbon.bas
• Zoom.bas
• Cls_VisibleSheets.cls

そのほか、第1回などで作成した

• Cls_Error.cls

も使います。

ActiveA1.basの作成

まずは、ソースです。

ActiveA1.bas

Option Explicit

'-----Function-----

'すべてのワークシートのカーサーをA1セルにし、先頭のシートに移動する。
'グラフシートなどワークシートでないものは対象外。
'非表示ワークシートもA1セルに設定する。
Public Sub M_ActiveA1AllSheets()
    Dim ClsVisibleSheets As Cls_VisibleSheets

    Set G_clsErr = CreateErrClass(PROJ_NAME, VER)   'エラークラス(Cls_Error)のインスタンス生成
    
    On Error GoTo AnyErr
    
    Application.ScreenUpdating = False              '画面更新の停止
    
    Set ClsVisibleSheets = CreateVisibleSheetsClass(ActiveWorkbook, G_clsErr)   'シート表示クラス(Cls_VisibleSheets)のインスタンス生成
    Call ClsVisibleSheets.VisibleWorkseets          '非表示シートの表示
    
    Call ActiveA1AllSheets(ActiveWorkbook)          'すべてのシートのA1セルへの設定
    
    Call ClsVisibleSheets.TryHiddenWorksheets       '非表示シートの再非表示
    Application.ScreenUpdating = True               '画面更新の再開
    
    Exit Sub
AnyErr:
    Call G_clsErr.SetError("ActiveA1", "M_ActiveA1AllSheets")
    Call G_clsErr.ShowErrMsg
End Sub

'すべてのワークシートのカーサーをA1セル、表示倍率100%にし、先頭のシートに移動する。
'グラフシートなどワークシートでないものは対象外。
'非表示ワークシートもA1セルに設定する。
Public Sub M_ActiveA1wZoom100AllSheets()
    Dim ClsVisibleSheets As Cls_VisibleSheets

    Set G_clsErr = CreateErrClass(PROJ_NAME, VER)   'エラークラス(Cls_Error)のインスタンス生成
    
    On Error GoTo AnyErr
    
    Application.ScreenUpdating = False              '画面更新の停止
    
    Set ClsVisibleSheets = CreateVisibleSheetsClass(ActiveWorkbook, G_clsErr)   'シート表示クラス(Cls_VisibleSheets)のインスタンス生成
    Call ClsVisibleSheets.VisibleWorkseets          '非表示シートの表示
    
    Call Zoom100AllSheets(ActiveWorkbook)           'すべてのシートの表示倍率を100%に設定
    Call ActiveA1AllSheets(ActiveWorkbook)          'すべてのシートのA1セルへの設定
    
    Call ClsVisibleSheets.TryHiddenWorksheets       '非表示シートの再非表示
    Application.ScreenUpdating = True               '画面更新の再開
    
    Exit Sub
AnyErr:
    Call G_clsErr.SetError("ActiveA1", "M_ActiveA1wZoom100AllSheets")
    Call G_clsErr.ShowErrMsg
End Sub


'すべてのワークシートのカーサーをA1セルにし、先頭のシートに移動する。
'グラフシートなどワークシートでないものは対象外。
'非表示ワークシートはない想定。
'
'@Wb Workbook 対象のワークブック。
Private Function ActiveA1AllSheets(Wb As Workbook)
    Dim wsSheet As Worksheet
    
    On Error GoTo AnyErr
    
    For Each wsSheet In Wb.Worksheets
        Application.GoTo Reference:=wsSheet.Range("A1"), Scroll:=True
    Next

    Application.GoTo Reference:=Wb.Worksheets(1).Range("A1"), Scroll:=True
    
    Exit Function
AnyErr:
    Call G_clsErr.SetError("ActiveA1", "ActiveA1AllSheets")
    Call Err.Raise(Err.Number, , Err.Description)
End Function

説明です。

• M_ActivateA1AllSheets

  本関数は、リボンから実施するActiveA1の処理の本体です。
  これを実行すると、各ワークシートのカーサーをA1セルにし、スクロールバーは左上にし、最終的に先頭シートに移動します。
  非表示シートについても実施します。
  上記の処理をひとつひとつ関数にして、それらをシーケンシャルに流しているだけです。
  

しいていえば、
エラークラスのインスタンス生成などは第1回の説明参照。
それから、非表示シートの表示、非表示関連はあらたにCls_VisiblSsheetsを作成し処理する（具体的には本クラスの説明でします）。
あとは、各シートをA1セルにするという処理を行っています。

• M_ActiveA1wZoom100AllSheets

  本関数は、リボンから実施するActiveA1wZoom100の処理の本体です。
  M_ActivateA1AllSheetsに加え、表示倍率をすべてのシートで100%にします。
  
  関数にZoom100AllSheetsが追加されただけです。
  

• ActiveA1AllSheets

  本関数は、各ワークシートのカーサーをA1セル西、スクロールバーを左上西、最終的に先頭シートに移動する処理をします。
  非表示シートなどの処理は含まれません。
  

CreateObject.basの作成

まずは、ソースです。

CreateObject.bas

Option Explicit

Public G_clsErr As Cls_Error

'Cls_Errorクラスのインスタンスを作成する関数。
'プロジェクト名とバージョンを初期化する。
'
'@ProjName String プロジェクト名。
'@Version String ツールバージョン。
'@return Cls_Error Cls_Errクラスのインスタンス。
Public Function CreateErrClass(ProjName As String, Version As String) As Cls_Error
    Set CreateErrClass = New Cls_Error
    Call CreateErrClass.SetProject(ProjName, Version)
End Function


'Cls_VisibleSheetsクラスのインスタンスを作成する関数。
'
'@Wb Workbook 対象のワークブック。
'ClsErr Cls_Error エラークラスのインスタンス。
'@return Cls_VisibleSheets Cls_VisibleSheetsクラスのインスタンス。
Public Function CreateVisibleSheetsClass(Wb As Workbook, ClsErr As Cls_Error) As Cls_VisibleSheets
    Set CreateVisibleSheetsClass = New Cls_VisibleSheets
    Call CreateVisibleSheetsClass.InitializeClass(Wb, ClsErr)
End Function

説明です。

• G_clsErr

  第1回でエラークラスを作りました。そのグローバル変数です。今回のActiveA1に直接的に関係するわけではありません。
  まあ、作ったクラスなので、実装しているというだけです。
  

• CreateErrClass

  第1回でエラークラスを作りました。そのインスタンスを生成するものです。
  

• CreateVisibleSheetsClass

  今回非表示シートの表示、非表示をするためのクラスを作りましt直生で、それのインスタンスを生成するものです。
  

Constant.basの作成

まずは、ソースです。

Constant.bas

Option Explicit

Public Const PROJ_NAME As String = "orgMacro"

定数を定義するためのソースです。ここでは、このプロジェクトの名前を定義しています。

PROJ_NAMEは、M_ActiveA1AllSheets関数でエラークラスのインスタンス作成時に、使用しています。

Ribbon.basの作成

まずは、ソースです。

Ribbon.bas

Option Explicit

Public Sub R_ActiveA1(control As IRibbonControl)
    Call M_ActiveA1AllSheets
End Sub

Public Sub R_ActiveA1wZoom100(control As IRibbonControl)
    Call M_ActiveA1wZoom100AllSheets
End Sub

リボンから呼び出している関数を二つ定義しているだけです。

Zoom.basの作成

まずは、ソースです。

Zoom.bas

Option Explicit

'各シートの表示倍率を100%に設定する。
'ワークシートが対象。
Public Function Zoom100AllSheets(Wb As Workbook)
    Dim wsSheet As Worksheet
    Dim lngZoomX As Long
    
    On Error GoTo AnyErr:
    
    lngZoomX = 100
    
    For Each wsSheet In Wb.Worksheets
        wsSheet.Activate
        ActiveWindow.Zoom = lngZoomX
    Next
    
    Exit Function
AnyErr:
    Call G_clsErr.SetError("Zoom", "Zoom100AllSheets")
    Call Err.Raise(Err.Number, , Err.Description)
End Function

各シートの表示倍率を100%に設定する関数を記載しています。
Zoomプロパティで100%にしますが、シートのプロパティではなく、ウィンドウのプロパティなので、wsSheet.Activateでいったんアクティブにしたうえで、ActiveWindow.Zoomを実行しています。

Cls_VisibleSheets.clsの作成

まずは、ソースです。

Cls_VisibleSheets.cls

Option Explicit

'-----Constant-----
Private Const ERR_MSG1 As String = "関数TryHiddenWorksheetsの直前に関数VisibleWorkseetsが実施されていません。"

'-----Global variable-----
Private G_wb As Workbook
Private G_visibleWorksheets() As Boolean
Private G_sheetNames() As String
Private G_obtained As Boolean
Private G_clsErr As Cls_Error

'-----Property-----

'ワークブックを設定するプロパティ。
'
'@Wb Workbook 対象のワークブック。
Public Property Set Wbook(Wb As Workbook)
    Set G_wb = Wb
End Property

'-----Class Initialize-----
Private Sub Class_Initialize()
    Set G_wb = Nothing
    Erase G_visibleWorksheets
    Erase G_sheetNames
    G_obtained = False
End Sub

'------Function-----

'クラスを初期化する。
'対象のワークブック設定と、エラークラスのインスタンスを設定する。
'
'@Wb Workbook 対象のワークブック。
'ClsErr Cls_Error エラークラスのインスタンス。
Public Function InitializeClass(Wb As Workbook, ClsErr As Cls_Error)
    Set G_wb = Wb
    Set G_clsErr = ClsErr
End Function

'対象のワークブックのワークシートのVisibleの値をG_visibleWorksheetsに保存しつつ、
'すべてのWorksheetを表示（True）に設定する。
'グラフシートなどワークシートでないものは対象外。
Public Function VisibleWorkseets()
    Dim wsSheet As Worksheet
    Dim lngNo As Long
    
    On Error GoTo AnyErr
    
    ReDim G_visibleWorksheets(G_wb.Worksheets.Count)
    ReDim G_sheetNames(G_wb.Worksheets.Count)
    
    For Each wsSheet In G_wb.Worksheets
        G_visibleWorksheets(lngNo) = wsSheet.Visible
        G_sheetNames(lngNo) = wsSheet.Name
        If wsSheet.Visible = False Then
            wsSheet.Visible = True
        End If
        lngNo = lngNo + 1
    Next
    
    G_obtained = True
    
    Exit Function
AnyErr:
    Call G_clsErr.SetError("Cls_VisibleSheets", "VisibleWorksheets")
    Call Err.Raise(Err.Number, , Err.Description)
End Function

'VisibleWorksheets関数で表示させたワークシートのうち、非表示だったWorksheetを再度非表示にする。
'本関数利用にあたり、以下の通り、VisibleWorksheets実施後、すぐにHiddenWorksheets関数を実施する想定であり、
'シートが増えたり、減ったりすることはそれほど想定されない。
' 1. VisibleWorksheets関数実行
' 2. 何か処理実行
' 3. HiddenWorksheets関数実行
Public Function TryHiddenWorksheets()
    Dim wsSheet As Worksheet
    Dim lngNo As Long

    On Error GoTo AnyErr
    
    If Not G_obtained Then  '事前にVisibleWorkseetsを実施済みでない場合にTrueとなり、エラー表示。
        Call G_clsErr.SetError("Cls_VisibleSheets", "TryHiddenWorksheets")
        Call Err.Raise(1, , ERR_MSG1)
    End If
    
    Call HiddenWorksheets
    
    Call Class_Initialize
    Exit Function
AnyErr:
    Call G_clsErr.SetError("Cls_VisibleSheets", "TryHiddenWorksheets")
    Call Err.Raise(Err.Number, , Err.Description)
End Function


'VisibleWorksheets関数で表示させたワークシートのうち、非表示だったWorksheetを再度非表示にする。
'本関数利用にあたり、以下の通り、VisibleWorksheets実施後、すぐにHiddenWorksheets関数を実施する想定であり、
'その間にシートが増えたり、減ったりすることはそれほど想定されない。
' 1. VisibleWorksheets関数実行
' 2. 何か処理実行
' 3. HiddenWorksheets関数実行
Public Function HiddenWorksheets()
    Dim wsSheet As Worksheet
    Dim lngNo As Long

    On Error GoTo AnyErr
    
    For Each wsSheet In G_wb.Worksheets
        For lngNo = 0 To UBound(G_sheetNames)
            If wsSheet.Name = G_sheetNames(lngNo) Then
                 wsSheet.Visible = G_visibleWorksheets(lngNo)
                 Exit For
            End If
        Next
    Next
    
    Exit Function
AnyErr:
    Call G_clsErr.SetError("Cls_VisibleSheets", "HiddenWorksheets")
    Call Err.Raise(Err.Number, , Err.Description)
End Function

説明です。

まずこのクラスは、対象のWorkbookの各シートが表示なのか非表示なのかを管理します。
この中の関数によって、非表示シートの表示、非表示を切り替えます。

• Constant

  エラー発生時のエラーを定義しており、ここでは、VisibleWorksheets関数を実施していない場合のエラーを定義しています。
  

• Global varialbe

  対象のワークブックや各シートの表示・非表示状態、シート名、VisibleWorksheetsを実施したかなどを保持するための変数を定義します。
  

• Property

  特に言うことがないです。今回は不要のプロパティです。
  

• Class Initialize

  各変数を初期化します。
  

• InitializeClass

  ワークブックとエラークラスのインスタンスをこのクラスに保持します。
  

• VisibleWorkseets

  各ワークシートの表示・非表示状態、シート名をグローバル変数に保持しつつ、すべてのシートを表示に切り替えます。
  

• TryHiddenWorksheets

  非表示シートを表示した後(VisibleWorksheets実施後)に実施する想定の関数で、再度非表示にします。
  いまのところ、事前に実施していないときだけエラーを出します。
  

• HiddenWorksheets

  非表示シートを表示した後(VisibleWorksheets実施後)に実施する想定の関数で、再度非表示にします。
  この関数は、エラーの判定は一切想定しない関数です。
  

以上で作成完了です。

おわりに

今回は、各シートのカーサーをA1セルにするのと、表示倍率を100%にするマクロを作成しました。

このソースコードは、正直あまり褒められたものではないと思ってまして、VBAの上級者からお叱りを受けてしまいそうなので、少しコメントします。

VBAを使ったマクロは、仕事上ではかなり作ってますが、作るソースコードは、どういった用途で、どういったシチュエーションで使うかによって、そのコードの実装方法はかなり変わります。
というのも、VBAで作るマクロっていうのは、だいたいが効率化と品質向上になってくるのですが、効率化が大きな比率を占めます。
今回のActiveA1も、各シートのカーサーを手動でA1セルにもってきたり、100%にしたりするのが面倒で、人がやると漏れがあるため作っています。
そういった場合に、あまりマクロ作成に時間をかけたくないというのがあります。
Excelに「マクロの記録」というのがありますが、あれも単に実施したことをどんどんVBAにしてくれるだけで、ソースコードは褒められたものではありません。それと同じでなるべく簡単に作りたいのです。時間をかけると効率化効果が薄れていくので。

汎用的に使うマクロは、「できる限り簡単に、短時間で作れて、かつ機能はシンプルなもの」をコンセプトに作っています。

チームでプログラム開発して、他の人もソースコードいじったりとか、Officialに関数をリリースするとなると、想定外のことがたくさん起きますので、もっといろいろ対策をいれないとダメです。
例えば、引数で受けた値の範囲チェックも必要ですし、関数の引数も増やして柔軟性を持たせることも必要になってきます。

Zoomの関数でも倍率を100%以外もできるようにするとか、ActiveA1とZoomを一緒に実施したほうが速いとか、色々直すところはいっぱいあります。
Cls_VisibleWorksheetsを作成したのは、最初は単なるモジュールで作っていたのですが、関数が一連の動作内で使用されず、違うWorkbookで実施されるとやっかいだと思ったため、あえてクラスにしました。単に自分が使うだけなら不要なのですが、ちょっぴりQiitaに載せることを意識したため、クラス化ました。クラスにしたほうが汎用性は上がりますから。

プログラムとして、より誤りがないものを作ったり、きれいなものを作ることはそれはいいことなのですが、それによるメリットがさほど得られないこと、それによるデメリットもさほどないことから、こういったシンプルなものは、多少ソースが美しくなくてもまあいいのかなと思っています。
もちろん、デメリットによって、多大な影響を受けるようならしっかりやるんでしょうが。ファイル保存もしていないし、さしたるものでもないので、こんなものかなと思っています。
いいわけがましいので、この辺で。

そういえば、人のソースコードってあまり見る機会がないなと思うのでした。
VBAマクロを作るときに、機能の調査でネットで調べたりしますけど、完全なソースコードってあまりみかけない。
というか、あまり自分が必要としていなくて気づいていないだけかも。
でも、実はソースの書き方は実は興味津々だったりするので、もう少しみるようにしたいと思います。

更新

日付	該当箇所	内容
2020/04/20	ソースコード	ソースコードを更新。
2020/04/20	ActiveA1の作成	et G_clsErr = CreateErrClass(PROJ_NAME, VER)がOn Error GoTo AnyErr以降に設定されていたが、以前に移動。