【Excel】シートの非表示と作業グループで起きる事故

「シートの非表示と作業グループの事故」のタイトルと、VBAのコード3行を示したアイキャッチ画像 実務設計

引き継いだブックを開くと、シートが数枚しか見えない。入力したはずの数字が、別のシートにも入っていた。シートの「非表示」と「作業グループ」は、画面を見ただけでは状態に気づきにくい機能です。この記事では、関数やマクロに慣れたExcel中級者向けに、隠れたシートに何が起きるか、作業グループで入力やマクロがどこまで効くかを、手元のExcelで確かめた結果で整理します。引き継ぎで最初に見る場所の全体像は、【Excel】引き継ぎブックで先に見る外部リンク・名前・接続にまとめています。

先に結論

  • 見えないシートは「非表示」と「VeryHidden」の2種類。VeryHiddenは「再表示」の一覧に出ない
  • ブックの構成を保護していると、非表示の解除もできない
  • 作業グループ中に全シートへ書き込まれるのは、Selection・ActiveCellを使った書き込みと消去。シートを指定して書くコードは、そのシートだけ
  • グループを選んだまま保存すると、開き直してもグループのまま

見えないシートは2種類ある

Worksheet.Visibleには3つの値があります。xlSheetVisible(-1)、xlSheetHidden(0)、xlSheetVeryHidden(2)です。非表示(0)は、シート見出しの右クリックから「再表示」で戻せます。VeryHidden(2)は、「再表示」の一覧に出ません。下の画面は、S2を非表示、S3をVeryHiddenにしたブックで「再表示」を開いたものです。

「再表示」ダイアログの一覧に、非表示にしたS2だけが出ている。VeryHiddenのS3は一覧に出ない
VeryHiddenのシートは「再表示」の一覧に出ない

VeryHiddenのシートは、VBEのプロパティウィンドウか、VBAでVisible = xlSheetVisibleにすると戻せます(保護なしのブックで確認)。Microsoftのワークシートの表示と非表示を切り替えるにも、VBAで非表示にしたシートは再表示コマンドでは出ない、という記載があります。

隠れたシートは、Worksheets.Countに数えられ、VBAからセルの読み書きもできます。Selectはエラー(1004)になります。なお、最後の可視シートを隠そうとするとエラー(1004)で、非表示のシートがあるときのWorksheets.Select(全シートの選択)もエラー(1004)でした。

ブックの構成を保護していると戻せない

「ブックの保護」で構成を保護している(ProtectStructure = True)と、非表示の解除、VeryHiddenの解除、シートを隠す操作、シートの追加が、どれもエラー(1004)になりました。引き継いだブックでシートが戻せないときは、レビュータブで構成が保護されているかを先に見ます。パスワードの解除方法はここでは扱いません。

作業グループでは、書き方によって全シートに入る

シート見出しを複数選ぶと、作業グループになります。タイトルバーは、ブック名のあとに「グループ」と表示されます。

作業グループの状態のExcelのタイトルバー。ブック名のあとに「グループ」と表示されている
作業グループ中は、タイトルバーに「グループ」と出る

S1〜S3をグループにして、S1をアクティブにした状態でVBAの書き方を変えて、S1〜S4のA1に何が入るかを調べました。

書き方入ったシート
ActiveCell.Value2 = "X"S1・S2・S3
Selection.Value2 = "X"S1・S2・S3
Selection.Formula = "=1+1"S1・S2・S3
Selection.ClearContentsS1・S2・S3(すべて消える)
ActiveSheet.Range("A1").Value2 = "X"S1だけ
Worksheets("S1").Range("A1").Value2 = "X"S1だけ
ActiveWindow.RangeSelection.Value2 = "X"S1だけ
ActiveSheet.Cells.ClearContentsS1だけ
ActiveSheet.Rows(1).InsertS1だけ

グループに入れていないS4には、どの書き方でも入りませんでした。ActiveSheet.Deleteも、確認ダイアログを出さない設定では、S1の1枚だけが消えました。つまり、選択したセルにSelectionやActiveCellで書くコードは、グループが残っていると複数のシートを書き換えます。マクロの冒頭でActiveWindow.SelectedSheets.Countが1かを確かめるか、ws.Selectで1枚に戻してから書くと安全です。シートを変数で指定して書くコードは、グループの影響を受けません。

グループを解く条件も確かめました。グループの一員をActivateしてもグループは残ります。グループ外のシートをActivateするか、Select Replace:=Trueで1枚を選ぶと、1枚に戻りました。

グループのまま保存すると、開き直しても残る

S1とS2をグループにしたまま保存し、開き直すと、選択されたシートはS1とS2のままでした。開いた直後にActiveCell.Value2 = "Z"を実行すると、S1とS2の2枚に入りました。受け取ったブックを開いてすぐ入力すると、意図しないシートにも入ります。引き継ぎでブックを開いたら、まずタイトルバーに「グループ」と出ていないかを見ます。

引き継いだら、一覧で状態を調べる

隠れたシート、非表示の名前、クエリ・接続、グループの状態を一覧にするコードです。非表示の名前(Visible = False)も、For Eachで拾えます。リンクの有無は、名前を読む前にLinkSourcesを呼びます(理由は引き継ぎブックの記事に書いています)。

Function InventoryText(ByVal wb As Workbook) As String
    Dim sh As Object, nm As Name, s As String, v As String
    Dim hasLink As Boolean
    hasLink = Not IsEmpty(wb.LinkSources(xlExcelLinks))
    s = "ProtectStructure=" & wb.ProtectStructure & vbLf
    s = s & "SelectedSheets=" & wb.Windows(1).SelectedSheets.Count & vbLf
    For Each sh In wb.Sheets
        Select Case sh.Visible
        Case xlSheetVisible: v = "Visible"
        Case xlSheetHidden: v = "Hidden"
        Case xlSheetVeryHidden: v = "VeryHidden"
        End Select
        s = s & "Sheet|" & sh.Name & "|" & v & vbLf
    Next sh
    For Each nm In wb.Names
        s = s & "Name|" & nm.Name & "|Visible=" & nm.Visible & vbLf
    Next nm
    s = s & "Queries=" & wb.Queries.Count & " Connections=" & wb.Connections.Count
    s = s & " ExcelLinks=" & hasLink
    InventoryText = s
End Function

S2を非表示、S3をVeryHiddenにして、非表示の名前を1つ作ったブックで実行すると、Sheet|S2|Hidden、Sheet|S3|VeryHidden、Name|hidName|Visible=Falseが出力されました。シートを別ブックに渡す前の確認としても使えます。配布用ブックの作り方は、【VBA】シートを別ブックにコピーして配布用ブックを作るで扱っています。

まとめ

  • 見えないシートは「非表示」と「VeryHidden」。どちらもマクロは読み書きでき、構成を保護すると戻せない
  • 作業グループ中は、Selection・ActiveCellで書くコードが全シートに入る。シートを指定して書けば、そのシートだけ
  • グループのまま保存すると、開き直しても残る。タイトルバーの「グループ」を見る
  • 引き継いだら、一覧コードでシートの状態を最初に確認する

確認した環境は、Excel 16.0(Microsoft 365)・日本語版です。Excel 2021未満とMac版は未検証です。

コメント

タイトルとURLをコピーしました