【ExcelVBA】シートの一覧を作成してリンクも貼り付けしよう【便利技】

みなさん、こんにちはケンケンです。

今回は、VBAを使ってシートの一覧を作成し、同時にその一覧にハイパーリンクを設定する方法をご紹介します。

シートの枚数が多くなったときに便利な技です。

では見ていきましょう。

シート一覧の作り方

まずはおなじみですが、ForNext構文を使って、シート一覧を作って行きましょう。

最初にやることは、１回目の処理です。

２番目のシート名をIndexシートに書き出してみましょう。

上記のようにコードを書き、実行しましょう。

するとA2セルに２枚目のシート名を書き出せましたね。

そこまでできたらForNext構文を使って繰り返し処理をします。以下のコードを実行してください。

これで一覧を書き出せましたね。

自信のない方は復習しておいてください。

■セルの値を消去する

続いてセルの値を消去するコードを書きましょう。

IndexシートのA2以降の値は、シートの枚数によって変動しますので、新しいリストを作成する際は常に値を消去するように設定しましょう。

まずは、変数を使わずにダイレクトに消していきます。

以下のようにします。

値を消去する場合は、ClearContentsメソッドを使います。

範囲をRange(“A2:A7”)として直接指定しています。

しかし、実務の世界では常に値が入っている範囲が一定ではないので変数で最終行を取得します。

具体的には、Range(“A2:A7”)の「７」の部分を変数に置き換えて最終行を指定すればできそうですね。

以下の図で確認してください。

A7の部分を分解して、変数と合わせてうまく使うことで応用を効かせています。

ちなみにこの状態でもう一度コードを実行すると、A1セルも消えてしまいます。

理由は、この状態だと最終行が１行目なので、A2～A1が範囲指定されてしまうからです。

そこで、追加で変数が２より少なかったらコードを実行しないようにしておきましょう。

If文を使って最終行が２より少なかった場合は、このプロシージャから抜けるように修正しました。

さぁこれで値の削除コードは完成したので、次はハイパーリンクを貼っていきましょう。

リンクの設定方法

Sub sheets_List()
    Dim sList
    Worksheets("Index").Range("A1").Name = "Index"
    
    For sList = 2 To Sheets.Count
        
        Worksheets(sList).Range("A1").Value = Worksheets(sList).Name
        Worksheets(sList).Hyperlinks.Add anchor:=Worksheets(sList).Range("A1"), Address:="", SubAddress:= _
          "Index", TextToDisplay:="ワークシート一覧に戻る"
         
        Worksheets(sList).Range("A1").Name = Worksheets(sList).Name
        Worksheets("Index").Hyperlinks.Add anchor:=Worksheets("Index").Range("A" & sList), Address:="", SubAddress:= _
          Worksheets(sList).Name, TextToDisplay:=Worksheets(sList).Name
        
    Next
End Sub

上記コードが完成形です。

シート一覧作成からだいぶ複雑になったように見えますが、ハイパーリンクを参照元と参照先に貼り付ける２つの処理が入っているだけです。

コードの実行結果がこちら

いきなり完成形だと分かりにくいと思いますので、以下に単発でリンクを設定する場合の参考コードと結果を貼り付けておきます。

どうしてもセンテンスが長くなってしまいますが、ひとつずつ解読していけばそれほど難しくありません。

とはいえ、ハイパーリンクのaddメソッドは引数が多くあるので、それぞれの引数の内容を明記しておきます。

※ExcelVBAを極める勝ちワザ700より

今回、引数にAddressではなくSubAddressを使った意味もお分かりいただけると思います。

シートやセルの指定にSubAddressが必要なんですね。

使い勝手の良いコードなのでコピペでとりあえず試してみて、あとでじっくりコードの解読をするのもありですね。

最後にボタンも作っておきましょう。

まとめ

いかがでしたか。

今回ご紹介したリンクの設定は、普段あまり馴染みがなかったり、設定するにも手動でしていたと思います。

しかし、個別にみるとそれほど重宝しないと思っているスキルでも、繰り返し構文と組み合わせると便利な機能に早変わりします。

みなさんもシートが多くて分かりづらくなったファイルを１度や２度見てきたと思います。

そんなときは、今回ご紹介したコードをコピペしてサクッと使いやすいファイルに変身させてください。

それでは、今回は以上です。

ご覧いただきありがとうございました。