Excel VBAでシートの一覧を作成しハイパーリンクを作成して目次を作る

Excelで作業をしていたら、いつの間にか大量のシートが・・・。

他の人からもらったExcelなんだけど、シート、多すぎ・・・。

シートごとのリストを作って可読性を上げたい！

そんなときは、シートの一覧を作って、目次となるシートを作ってあげることで、生産性をあげましょう！

Excel業務あるあるシリーズ

VBAでキーごとにシートに分ける簡単なサンプル - 適材適所

ExcelVBAで差し込み印刷もどきをやってみる - 適材適所

Excelの仕様

そもそもExcelは何枚くらいシートを追加できるのでしょうか。

MicrosoftのサイトにExcelの仕様が書かれています。

Excelの仕様では、シート数は「メモリに依存する」と書いてあります。

仕様⇒ Excel の仕様と制限

普通の使い方で限界までシートを作ることはまずないかと思いますが・・・。

どのくらい追加できるのか確認してみました。

ちなみに私の環境だと、7000枚追加しようとしたらフリーズしてしまいました・・・。

 

Sub AddSheet()
    Const ADD_NUM As Long = 7000
    Dim i As Long
    
    For i = 1 To ADD_NUM
        Worksheets.Add
    Next i
    
End Sub

何枚追加できるかどうかは、環境依存ということですね。

限界までシートを追加することはないと思いますが、そんなときはあきらめてブックを分割しましょう。

知っておくと、何かのときに役立つかも。

いきなり話が逸れてしまいました。

話を元に戻しましょう。

シートの一覧を作成する

大量のシートがあるときは、シートの一覧が記載されたシートを作って目次を作ってあげるととっても便利です。

VBAでシートの情報を取得するときはWorksheetsオブジェクトを参照することになります。

VBEのオブジェクトブラウザ―で確認してみます。

Worksheetsオブジェクトは、Worksheetオブジェクトの集まりになります。

Worksheetsオブジェクトの中の個々のWorksheetは、Itemプロパティに入っています。

そのため、シートひとつひとつにアクセスする際は、Itemプロパティにアクセスします。

といっても、WorksheetsオブジェクトのデフォルトプロパティがItemですので、明示的に呼び出すことはあまりないかと思いますが・・・。

'下記の２つは、同義。明示的にItemを書かなくても勝手にItemにアクセスしてくれる
Worksheets.Item(1)
Worksheets(1)

ちなみに、Worksheetsオブジェクトと似たもので、Sheetsオブジェクトというものもあります。

Sheets>Worksheetsといったイメージです。

SheetsオブジェクトはWorksheetオブジェクトを含む概念です。

つぎは、Worksheetオブジェクトについてもオブジェクトブラウザを見てみます。

Worksheetsオブジェクトと比較するとプロパティやメソッドの数がかなり多いですね。

シートという巨大なオブジェクトを扱っているので当然っちゃ当然ですね。

その中でもシート名を取得するときは、Nameプロパティを参照します。

似たようなプロパティにNamesプロパティがありますが、こちらは定義された名前になるのでお間違えなきよう。

Namesプロパティはこっち

Worksheetsオブジェクトの個々の要素であるWorksheetオブジェクトにアクセスするためには、For Each を使うと簡単です。

次のコードは、For Each を使って、Worksheetsオブジェクト内のWorksheetオブジェクトを参照し、

各シート名を新しい目次シートに書き出すものです。

 

Sub CreateSheetList()
    Const CONTENTS_SHEET As String = "Contents"
    Dim sheetObj As Worksheet
    Dim contentsSheet As Worksheet
    Dim curRow As Long: curRow = 1
    
    '目次となるシートを追加
    With Worksheets
        Set contentsSheet = .Add(after:=.Item(.Count))
    End With
    contentsSheet.Name = CONTENTS_SHEET
    
    'シートを追加
    For Each sheetObj In Worksheets
        '目次シートは除外する
        If sheetObj.Name <> CONTENTS_SHEET Then
            'シート名を転記する
            contentsSheet.Cells(curRow, 1) = sheetObj.Name
            '行を進める
            curRow = curRow + 1
        End If
    Next sheetObj
    
End Sub

新しく追加する目次となるシート名は「Contents」としました。

Contentsシートは目次に含めないよう、If文で分岐し、除外しています。

これでシートの一覧を作成することができました。

ハイパーリンクを作成する

目次のシート名をクリックしたら、該当のシートに飛ぶようにし、使い勝手を上げていきましょう。

次はこの一覧にハイパーリンクを張り、目次として仕上げていきます。

セル範囲にハイパーリンクを張るときは、RangeオブジェクトのHyperlinksオブジェクトのAddメソッドを実行します。

オブジェクトブラウザーのRangeオブジェクトを確認すると、Hyperlinkオブジェクトがプロパティに含まれていることが確認できます。

またHyperlinkオブジェクトは、このようになっています。

RangeにHyperlinkを追加する構文は少しわかりにくいので、公式リファレンスを確認してみます。

expression.Add(Anchor,Address,SubAddress,ScreenTip,TextToDisplay)

引数

名前	必須 / オプション	データ型	説明
Anchor	必須	Object	ハイパーリンクのアンカーを指定します。 Range オブジェクトまたは Shape オブジェクトを指定します。
Address	必須	String	ハイパーリンクのアドレスを指定します。
SubAddress	Optional	Variant	ハイパーリンクのサブアドレスを指定します。
ScreenTip	Optional	Variant	ハイパーリンク上をマウス ポインターで指した場合に表示されるヒントを指定します。
TextToDisplay	Optional	Variant	ハイパーリンクで表示されるテキストを指定します。

Anchorには、リンク先を指定します。

今回の場合は、シート名が書かれたセルが該当します。

AddressにはURLやファイルパスを指定しますが、今回のケースでは、サブアドレスを指定するため、空文字列を指定します。

シートをリンク先に指定する場合は、SubAddressを指定します。

ScreenTip、TextToDisplayは特に無指定で構いません。

百聞は一見に如かず。

ということで、上記を踏まえて、実際にコードに落とし込んでみます。

目次ページを追加するコード

 

Sub CreateSheetList()
    Const CONTENTS_SHEET As String = "Contents"
    Dim sheetObj As Worksheet
    Dim contentsSheet As Worksheet
    Dim curRow As Long: curRow = 1
    Dim curRange As Range
    
    '目次となるシートを追加
    With Worksheets
        Set contentsSheet = .Add(after:=.Item(.Count))
    End With
    contentsSheet.Name = CONTENTS_SHEET
    
    'シートを追加
    For Each sheetObj In Worksheets
        '目次シートは除外する
        If sheetObj.Name <> CONTENTS_SHEET Then
            'シート名を転記する
            Set curRange = contentsSheet.Cells(curRow, 1)
            curRange.Value = sheetObj.Name
            'シート名にハイパーリンクを張る
            curRange.Hyperlinks.Add curRange, "", sheetObj.Name & "!A1"
            '行を進める
            curRow = curRow + 1
        End If
    Next sheetObj
End Sub

Hyperlinks.Addの引数について繰り返しになりますが、指定するのはAddressではなく、SubAddressです。

これで、シート名をクリックすることで、該当のシートに飛べる、便利な目次が完成しました！

シート数が大量になったり、面倒なシートが渡されても、これで一安心！！

目次だけだと・・・

確かに目次シートがあると便利なんですが、目次シートから該当のページに飛んだあと、

目次シートに戻れるとなお便利な気がします。

そのハイパーリンクを各シートに張ることも上記のコードを応用すれば、簡単かと思います。

ケースバイケースで対応してみてください。

終わりに

Excel VBAでシートの目次を作る方法を紹介してみました。

簡単なコードではありますが、ハイパーリンクでリンクを貼りつつ、目次を作ることで、地味に生産性があがる場面もあるかと思います。

でもシートが多すぎる場合はブックを分割するなど根本的な対策をすることをお勧めします。

というわけで、ここまでお読みいただき、ありがとうございました。