VBAでシートの詳細情報を一覧出力する

シートの一覧の用途

Excelのシートの数が増えてくるとシートの一覧が必要になることがあります。
一覧を取得する際の表現方法や出力内容にはいくつか考えられます。

  • シート一覧のシートを新規作成する。
  • 非表示のシートは一覧に出力しない。
  • 各シートに番号を付ける。(A列)
  • 各シートの名前を列挙する。(B列)
  • 目次シートを作って各シートへのリンクを張る。(C列)
  • 各シート名のフルパスを出力する。(D列)
  • 各シートの指定したセルの値を取得する。(E列)
  • 各シート名で並べ替えをする。

これらを実現するマクロです。
いらない部分は削除してください。

ソースコード



ソースコードの説明

コードのコメントにほとんど書いているのでそれ以外の補足です。

シート名の設定

このソースでは強制的に一番左にシートを追加しています。シート名には現在日時を入れています。10行目の=から右側は適宜シート名を入れてください。

例えば「シート一覧」という名前のシートに出力したい場合は、2通りのやり方があります。

シートを事前に作っておく方法

1つは「シート一覧」シートを作っておく方法です。
7行目から12行目を以下に書き換えます。

シートを作り直す方法

もう1つは「シート一覧」シートを作り直す方法です。
この場合も7行目から12行目を以下に書き換えます。

Application.DisplayAlerts = Falseは、シート削除時の警告メッセージの表示をさせないための処理です。

Sheetsコレクション

上のソースコードの22行目で全シートの意味であるSheetsコレクションを利用しています。

Sheetsコレクションには全てのシートの情報が設定されており、ブックの一番左のシートから一番右のシートに向かって登録されています。

そのため、For Eachループで各ループで設定されるsht変数には一番左のシートから順に設定されていきます。

シート名で並べ替え

簡単にではありますが、45行目以降にシート名での並べ替え処理を入れています。

必要ないのであれば削除してください。

非表示シートがある場合

なんらかの理由でシートを非表示にしているブックがあります。

そのため、非表示のシートは一覧には出力したくない、という要件も出てくると思います。

24行目のif文は非表示のシートは除外する条件文です。

シートの表示形式には3通りあります。

  • 表示されている状態(xlSheetVisible)
  • Excelの操作で再表示可能な非表示の状態(xlSheetHidden)
  • Excelの操作で再表示できない非表示の状態(xlSheetVeryHidden)

24行目のif文に2つの条件があるのはそのためです。

SheetsコレクションとWorksheetsコレクションの違い

上のソースコードではSheetsコレクションを使っています。でもWorksheetsコレクションに書き換えてもおそらく同じ期待結果になると思います。

違いはWorksheetsコレクションはワークシートのみが対象となり、Sheetsコレクションはワークシートだけでなくシート挿入時の画面に表示されるグラフシートやExcel 4.0 マクロシートやMS Excel 5.0 ダイアログシートも対象になります。

ただ、グラフシートはあるかもしれませんがExcel 4.0 マクロシートやMS Excel 5.0 ダイアログシートを使うことはまずないでしょうから、SheetsもWorksheetsも気にしなくて大丈夫かなあ、と思います。ワークフシート以外を使うことがない私は常にSheetsと書いてます。

使用例

実際に私がこのマクロを繰り返し使った例です。見出しがないなあ、と動かしてみて思ったのですが、そこは適宜作ってみてください。15行目のA1をA2にすれば一覧は2行目以降に出力されますのであとは1行目に見出しを固定で出力して対応してみてください。

関連記事

サブコンテンツ

このページの先頭へ