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
一回だけ実行する場合は十分高速で問題ないでしょう。しかしループの中で使用すると繰り返し実行されるため、場合によってはかなり時間がかかります。 かつ結果"シート名"と一致するものがない場合はシート数Xループ回数実行され、無駄に時間を浪費します。
上記の例では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
これでエラーが回避でき、コードもすっきりしているかと思います。面白いことに、計測してみると単純ループの55~60%程度の処理速度となります。75~80%のスピードアッ!!とも言えます。内部的にはループと同等のことを行っていると思いますが、コンパイル済みかどうかの差が大きいのでしょう。
ループによる実行回数が数千回になるような場合は、数単位での差になるのでバカにできません。
このひとつ前のブログ『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の方がコード的にもすっきりしていますが、使用する場面では使い道があるかもしれせん。
---------------------
おそらくほとんどの場合は上記の方法で十分高速になると思いますが、100シート超えで千回超えの検索をするような場合にはDictionaryを使うとさらに高速になります。シート数が少ないとかえって逆効果になることもあるでしょう。
Dim SheetNameDict As Object
Rem シート名がnameのWorksheetを返す。
Function GetSheetByName(name As Variant)
Rem SheetNameDict初期化。SheetNameDict未設定の場合、
Rem SheetNameDictに全ワークシートのKey=name, Value = sheetをセット。
If SheetNameDict Is Nothing Then
Set SheetNameDict = CreateObject("Scripting.Dictionary")
For Each sheet In Worksheets
Call SheetNameDict.Add(sheet.name, sheet)
Next sheet
End If
If SheetNameDict.Exists(name) Then
Set GetSheetByName = SheetNameDict(name)
Else
On Error GoTo NotFound
Dim sheet As Worksheet
Set sheet = Sheets(name)
Set GetSheetByName = sheet
Call SheetNameDict.Add(name, sheet)
Exit Function
NotFound:
Set GetSheetByName = Nothing
End If
End Function
使用例
Dim sheet as Worksheet
Set sheet = GetSheetByName("someSheetName")
if Not sheet is Nothing then ....
なお、このサンプルの場合は途中でSheetが削除されるとその後存在しないSheetを返すことになるので、Sheetを削除すると同時にSheetNameDict の項目も削除します。
Call SheetNameDict.Remove(name)