VBA 修正箇所について

質問

（※）の行にエラーがでているのですが、何を指摘されているのか、どう直せば良いのかが分かりません。下記の情報だけで何かわかりますか？エラー名はインデックスが有効範囲にありませんです。
'*******************************************************************************
'機能名　　：指定文字を含むシートを検索し、指定文字を含んだシート名を返す
'引数　　　：指定文字（String型）
'戻り値　　：指定文字を含んだシート名を返す（String型）
'備考　　　：指定文字を含んだシート名が存在しない場合は"NotFound"が返される
'*******************************************************************************
Public Function gFnc_strSelectSheet(ByVal strTargetSheetName As String) As String

Dim i As Integer, intChkFlg As Integer
Dim strResult As String

intChkFlg = 0
For i = 1 To Sheets.Count
（※）If InStr(Worksheets(i).Name, strTargetSheetName) > 0 Then
strResult = Worksheets(i).Name
intChkFlg = 1
End If
Next
If intChkFlg = 0 Then strResult = gcnstrNotFound

gFnc_strSelectSheet = strResult

End Function

回答No.1

asciiz · ベストアンサー

「インデックスが有効範囲にありません」

というエラーは例えば、配列名(1)～(10) までしか用意していないのに、配列名(11)にアクセスしたようなときに出ます。

で、(※)の行のどこに配列アクセスがあるかというと、「Worksheets(i)」の部分です。

この場合、i がどのような値を取っているかデバッガで調べ、i が異常値になった原因を探ったりします。

----

がしかし、今回の問題はそんな難しい話ではありません。

＞ For i = 1 To Sheets.Count

これで1ワークシート内の全シート情報を読んだりしたいなら、

「Worksheets(i).Name」でなく「Sheets(i).Name」としたかったんじゃないでしょうか。

Worksheets オブジェクトと、Sheets オブジェクトは、まったく別のものですが、どちらも配列オブジェクトであり、.Nameプロパティも存在するため、「Worksheets(i).Name」という書き方は通ってしまいました(文法エラーになりません)。

しかし1ファイルだけを開いていれば「Worksheets(i)」は1つしかないため、「Worksheets(2)」にアクセスしようとした時点で、インデックス外エラーになったんです。

プログラミングとしては、「オブジェクト名の間違いにより、無関係のものにアクセスしてしまった」ということがエラー原因となります。

お礼

簡潔に分かりやすい回答を有難うございます。
初心者ゆえに、かみ砕いた説明がとても助かります。

回答No.5

kkkkkm

No.2の補足です

簡単な説明です。
何を指摘されているのかですが

Worksheets(i)
この場合は、その番号のシートはありませんということです。

Worksheets.Count
は通常のシートの数で
Sheets.Count
は、グラフシートのように通常のシート以外も含めた数になります。

For i = 1 To Sheets.Count
で、通常のシート以外も含めた数までループしていて
（※）If InStr(Worksheets(i).Name, strTargetSheetName) > 0 Then
で通常のシートの番号を指定しています。
「i」が通常のシートの数を超えた場合
その番号のシートはありませんとなります。
通常のシートしかない場合は、元のままでもエラーにはならないと思います。

Worksheets.CountとSheets.Countのどちらを使うかは、通常シート以外も探すかどうかで決めて下さい。

お礼

細かく教えて下さり有難うございます。
初心者故に、詳細の説明がとても助かります。

回答No.4

kkkkkm

No.2の追加です。

For i = 1 To Worksheets.Count
とした方が一か所の訂正ですむのでいいかもしれません。

回答No.3

nda23

オブジェクトの階層を理解しておられるでしょうか？
Application→WorkBooks→Workbook→
WorkSheets→WorkSheet→Cells→Cell
のようにコレクション(複数形)とオブジェクト(単数)は
階層構造になっています。
1.WorkSheetsですが、「どのブックに属するか」が不明です。
　一度に複数のブックを開く可能性があるので、必ず親ブックを
　指定しなければなりません。デフォルトはActiveWorkbookですが、
　これが、どのブックを指すかという保証はありますか？
　そういう意味で、このプロシージャには潜在バグがあります。
　もし、自身のブックなら、ThisWorkbook.WorkSheetsとすべきです。
　他のブックでしたら、Workbook型の変数を引数か大域変数に
　指定したほうがよいでしょう。
2.変数の取り方
　私は機械語が専門で、VBAを見ても内部の動作が透けて見えます。
　偉そうな物言いで、恐縮ですが、以下のようにすべきと思います。

　Dim 索引 As Long, 個数 As Long
　Dim シート名 As String
　Dim シート集合 As WorkSheets
　Dim シート個別 As WorkSheet

　Set シート集合 = ThisWorkbook.WorkSheets
　個数 = シート集合.Count
　For 索引 = 1 To 個数
　　Set シート個別 = シート集合(索引)
　　シート名 = シート個別.Name
　　Set シート個別 = Nothing
　　'★名前を検出したらForループを抜ける
　　If Instr(シート名, strTargetSheetName) > 0 Then Exit For
　Next
　Set シート集合 = Nothing
　'★見つからないと、索引が個数を超える。
　If 索引 > 個数 Then
　　strResult = gcnstrNotFound
　Else
　'★見つかれば途中で抜けたので、索引≦個数になる
　　strResult = シート名
　End If

要点
A.変数型のIntegerは16ビット整数で、32/64ビットCPUで
　使用すると、効率が悪いので、整数値は理由がない限り、
　Long(32ビット)を使うべきです。
　自動変数に16ビット値を使ってもメモリの節約にはなりません。
B.オブジェクトは必ず、階層構造の修飾をする。
　既に述べたように、デフォルトに任せるのは「バグ」と言っても
　過言ではありません。
C.コレクションやオブジェクトは使用済みになったら、
　速やかに解放します。(Set xx = Nothing)
　オブジェクトは内部に参照カウンタを持っており、解放により
　デクリメントされる。参照カウンタが0になると、オブジェクトが
　使用していたメモリが解放されます。
　これを怠ってもガベジコレクタが、何とかしてくれるますが、
　その間、メモリを浪費し、システムパフォーマンスに悪影響が
　出ます。
D.プロパティの取得、メソッドの実行は極力回数を減らすべきです。
　機械語でオブジェクト操作すると分かりますが、これらの動作は
　相当な労力を必要とします。拝見すると、"WorkSheets(i)"を
　多用していますが、デフォルトのItemプロパティの参照になり、
　更に、目に見えないWorkSheetオブジェクトのNameプロパティを
　参照しています。Nameプロパティの取得の度に、新しい文字列が
　生成され、メモリ効率が悪化します。

まぁ、上記で言う悪影響とは1～2桁のマイクロ秒の話で、
神経質にならなくても、イイのですが、参考になれば幸いです。

回答No.2

kkkkkm

多分グラフシートのような通常のシートじゃないものとかがあるのではないでしょうか

InStr(Sheets(i).Name, strTargetSheetName) > 0
とすればエラーは無くなると思います。