VB Excelのシートを名前で探す方法 高速版

VBAでExcelのシートを名前で探す方法を調べると、たいていループで名前が一致するものを探す方法が紹介されいます。

たとえばこんなコードです。

    Sub Sample1(name As String)
　     For i = 1 to Worksheets.Count
            if Worksheets(i).name = name Then
                Debug.Print Worksheets.Index
                Exit For
            End If
        Next
    End Sub

一回だけ実行する場合は十分高速で問題ないでしょう。しかしループの中でさらにこのループを実行しするため、場合によってはかなり時間がかかります。 かつ結果"シート名"と一致するものがない場合はシート数Ｘループ回数実行され、  無駄に時間を浪費します。

上記の例ではWorksheets(i)と引数にindexを使用しています。またiの範囲がCountの範囲内なので、Worksheet(i)がエラーを起こすこともありません。

Worksheetsは引数にシート名を使用することもできます。そこで次のようなコードを試してみます。

    Dim sheet As Worksheet
    Set sheet = Worksheets("シート名" )

"シート名"のWorksheetが存在する場合は問題ないのですが、存在しない場合は「インデックスが有効範囲にありません。」というエラーが発生します。Worksheet(i)でもiが有効範囲外であれば同じエラーが発生しますので、内部では同様のループ処理が行われていることが想像できます。

今度は、つぎのような方法を試してみます。

    Sub Sample2(name As String)
        On Error GoTo NotFound
        Debug.Print Worksheets(name).Index
    NotFound:
    End Sub

これでエラーが回避でき、コードもすっきりしているかと思います。面白いことに、計測してみると単純ループの５５～６０％程度の処理速度となります。７５～８０％のスピードアッ！！とも言えます。内部的にはループと同等のことを行っていると思いますが、コンパイル済みかどうかの差が大きいのでしょう。

ループによる実行回数が数千回になるような場合は、数単位での差になるのでバカにできません。

このひとつ前のブログ『InternetExplorer.getElementById 「オブジェクトが必要です」エラー対策』で使用した「On Error Resume Next」も試してみました。

    Sub Sample3name As String)
        Dim sheet As Worksheet
        On Error Resume Next
        Set sheet = Worksheets(name)
        If Not sheet Is Nothing Then
            Debug.Print Worksheets(name).Index
        End If
    End Sub

これは処理速度的にはSample2と同等でした。この例の場合ではSample2の方がコード的にもすっきりしていますが、使用する場面では使い道があるかもしれせん。