Excel VBA で仕事効率化 : リンク付き INDEX を自動作成

今回は、 Excel VBA のマクロ機能でリンク付き INDEX を自動作成し、Excel資料を読みやすくして仕事効率化する方法をシェアしますね！

こんにちは、ゆう(@ProgYuki)です。

おそらく多くの方が仕事でExcelを使われているのではないでしょうか？

Excel関数やピボット等は使えてもVBAマクロまで使い熟せている人はまだまだ少ないと思います。

ただ、やはりVBAを使えると仕事効率が圧倒的に高まりますし、できる人が少ない分、少しできるだけでかなり重宝されます。

今回、仕事効率化のVBAとしてご紹介するのは、INDEX自動作成機能です。

あなたも経験ありませんか？

たくさんのシートが付いたExcelファイルを読みやすくするために、一番最初のシートに各シート名が一覧になったINDEXを作成した経験が。

しかも、各シートに飛ぶように内部リンクまで付けますよね。

こういう作業は、Excel関数では難しいですし、かと言って手作業でやるのもシートが多い分かなり大変なんですよね。

ということで、VBAマクロで一瞬で終わるようにプログラムを書きましたのでシェアしますね！

「プログラミングには興味ないけど、Excelの仕事を効率化したい！」

「VBAに興味があって、人のプログラムを見て学習したい！」

という方にとって役に立つ内容かと思います。

VBAを扱った事のない方にもできるように、どこにコピペすれば良いのかも説明してるので、参考にしてもらえればと思います。

ただ、VBAの注意点として、一度実行したマクロはCtl＋ｚとかで元には戻せないっていう特徴があります。

紹介するコードは、私が使う上では今のところエラー等は得にないですが、使い慣れるまでは必ずマクロを実行する前にバックアップを取るようにしてくださいね。

実行して発生したエラー等に関して責任は負いかねますので、ご理解をお願いします。

Excel VBA で仕事効率化 : リンク付き INDEX を自動作成しExcel資料を読みやすくする！

Excel VBA で仕事効率化 : リンク付き INDEX を自動作成しExcel資料を読みやすくする！

INDEX を自動作成する

それでは、まずどんなイメージになるのかですが、下記の画像のように

複数シート名を一覧に表示する
各シートへの内部リンクをつける

というINDEXを自動作成してくれます。

※今の仕様だとINDEXを表示するシート自体は自分で追加する必要があります。この記事の下の方で使い方を説明しているので、参考にしてください。

INDEXに一覧に表示されたシート名をクリックすると、各シートに飛ぶリンクが付いてます！

手作業でやると結構大変な作業ですが、マクロを使えば一瞬なので、使う頻度はあまり多くないと思いますが、使う場面が起きた場合には時間を節約できますね！

ちなみにINDEXの文字の大きさや色などは、VBAコード内で私が勝手に決めているだけなので、好みに合わせてお好きに編集してくださいね。

VBA プログラムの表示

次にVBAのプログラムを下記に表示しますね。

Sub indexSheet()

'accelerate ON
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    
    Dim sheets As Worksheet
    Dim thisSheet As Worksheet
    Dim m As Integer
    Set thisSheet = ActiveSheet
    
    m = 3
    
    With Range("A1")
        .Value = "INDEX"
        .Font.Size = 30
        .Font.Bold = True
    End With
    
'show the name of all worksheets within this book as an index and put a hyperlink on it
    For Each sheets In ActiveWorkbook.Worksheets
        If thisSheet.Name <> sheets.Name Then
            thisSheet.Hyperlinks.Add anchor:=thisSheet.Cells(m, 2), Address:="", _
            SubAddress:=sheets.Name & "!A1", TextToDisplay:=sheets.Name
            m = m + 1
        End If
    Next sheets
    
'autoFit for column B
    thisSheet.Columns("B").AutoFit
   
'accelerate OFF
    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

Sub indexSheet()

'accelerate ON

With Application

.ScreenUpdating = False

.DisplayAlerts = False

.EnableEvents = False

.Calculation = xlCalculationManual

End With

Dim sheets As Worksheet

Dim thisSheet As Worksheet

Dim m As Integer

Set thisSheet = ActiveSheet

m = 3

With Range("A1")

.Value = "INDEX"

.Font.Size = 30

.Font.Bold = True

End With

'show the name of all worksheets within this book as an index and put a hyperlink on it

For Each sheets In ActiveWorkbook.Worksheets

If thisSheet.Name <> sheets.Name Then

thisSheet.Hyperlinks.Add anchor:=thisSheet.Cells(m, 2), Address:="", _

SubAddress:=sheets.Name & "!A1", TextToDisplay:=sheets.Name

m = m + 1

End If

Next sheets

'autoFit for column B

thisSheet.Columns("B").AutoFit

'accelerate OFF

With Application

.ScreenUpdating = True

.DisplayAlerts = True

.EnableEvents = True

.Calculation = xlCalculationAutomatic

End With

End Sub

上記のプログラムをコピペする場所ですが、初めてVBAマクロを使う方は、Excelのメニューバーに「開発」のタブがないと思うので、まずは「開発」タブを表示します。

「ファイル」→「オプション」→「リボンのユーザー設定」

と進むと下のような画面が表示されると思います。

右側の「メインタブ」と書かれた表の中の「開発」にチェックを入れて、「OK」を押すとメニューバーに「開発」タブが表示されるかと思います。

※上手く表示されていない場合は、一旦Excelファイルを全て閉じてからもう一度開き直してください。

そして、

「開発」→「Visual Basic」

と進むと下のような画面が表示されると思います。

※Alt＋F11 のコマンドでも表示されます。

上記画面の左側のプロジェクトウィンドウの中に編集したいExcelファイルの名前があると思うので、その中の「標準モジュール」をクリックし、「Module1」をクリックすると下のようが画面が表示されるので、ここにプログラムを貼り付けます！

※上記画像では、「Management.xlsm」や「PERSONAL.XLSB」とありますが、これらは私が使っているExcelファイルなので、見た目が異なると思いますが、ご自身のExcelファイル名を選択すればOKです。

これでプログラム自体のコピペはできましたね！使える状態です。

ちなみにVBAマクロ付きのExcelファイルを保存するときは、

「ファイル」→「名前を付けて保存」

で保存するときに、「ファイルの種類」で「Excel マクロ有効ブック(*.xlsm)」を選択してください。

マクロ入りExcelファイルは、「.xlsm」の拡張子じゃないとちゃんと保存されないので、注意してくださいね。

使い方、注意点等

次に今回のVBAマクロの使い方や注意点です。

使い方

使い方は単純で、

新しいシートを追加する
追加したシートを開いた状態でマクロを実行する

以上です。

表示されているシートがINDEXを記入するシートとして認識されるので、必ず何もデータが入っていないシートを表示（アクティブ）にしてからマクロを実行してください。

ちなみにマクロの実行の仕方ですが、いろいろと方法はありますが、一番わかりやすい方法としては、

「開発」→「マクロ」

と進むと実行できるマクロの一覧が表示されるので、そこで今回は「indexSheet」という名前にしているので、それを選択すれば実行されます。

いかがでしょうか？

うまくできましたかね？

もし、何かエラー等があれば、コメントかTweetでご連絡いただけると幸いです。

注意点

注意点としては、冒頭でもお話しましたが、「VBAマクロは一旦実行すると元に戻せない」という特徴があるので、使い慣れない間は必ずバックアップを取ってから、実行することをお勧めします。

バックアップについては、大丈夫だとは思いますが、処理したいExcelファイルを別の名前をつけて、いざという時のために保存しておくという意味です。

考えられるミスとしては、データが既に入っているシートを開いた状態でマクロを実行させてしまうと、そのシートにINDEXが作成されてしまって、元のデータを失ってしまうので、そこは気を付けてくださいね。

VBA プログラムのポイント

ここからは、VBAのプログラミングに興味がある方のみ見てもらえればと思います。

今回のプログラムの流れを簡単に説明しますね。

accelerate ON から続くプログラムで処理の高速化をオンにしている
Dim～のプログラムで各変数を定義している
with～End withのプログラムで、シートの左上にINDEXという文字を表示させている
For Each～Next のプログラムで各シートを一つづつ参照していき、INDEXを表示させたいシート名と異なる場合には、シート名とそのシートへのリンクを設定するというループ処理を行う
AutoFitから続くプログラムでシート名一覧が記入されているB列の幅を調整する
accelerate OFFから続くプログラムで処理の高速化をオフにしている

ってな感じです。

①、⑥の処理の高速化はだいたいどんなプログラムでも使うので、ご自身でプログラムを書く時もコピペしておけば良いと思います。

②のようにプログラムを書く場合は、先に使う変数をDim～で定義する必要があります。

For Each ～Nextの文法は、シートを一つづつ参照（ループ）させて、シートを処理するのに使えるので、覚えておくとよいかと思います。

ちなみに私はVBAは独学なのですが、学習の仕方として