Excel VBA ユーザフォームの検索について

質問

添付の画像のようなユーザフォームを作っています。
TextBox1に検索ワードを入力して、CommandButton1をクリックすると、下のComboBox1に一覧が出るようにしたいと思い、ほかのサイトから下記のコードを見つけて、作ってみました。参照先のsheet2を表示しているときは大丈夫なのですが、別のシートを選んでいるとエラーになります。
sheetは３つあり、それぞれ違うリストが入力されています。今回はsheet2のリストを参照したいのですが、最初はsheet1が表示されている状態で実行したいです。
エラーの内容は
実行時エラー9　インデックスが有効範囲にありません。

コチラがコードです。
Private Sub UserForm_Initialize()
Dim i As Long, imax As Long
Dim tbl() As Variant
imax = ThisWorkbook.Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
ReDim tbl(imax)
For i = 1 To imax
tbl(i) = Range("A" & i).Value
Next i
With ComboBox1
.List() = tbl()
End With
End Sub

Private Sub CommandButton1_Click()
Dim i As Long, imax As Long
Dim tbl() As Variant
Dim cnt As Long, j As Long
j = -1
With ThisWorkbook.Worksheets("sheet2")
imax = .Cells(Rows.Count, "A").End(xlUp).Row
cnt = Application.CountIf(Range("A1:A" & imax), "*" & TextBox1.Text & "*")
ReDim tbl(cnt)
For i = 1 To imax
If InStr(.Range("A" & i), TextBox1.Text) > 0 Then
j = j + 1
tbl(j) = Range("A" & i).Value　←この部分がエラーになる
End If
Next i
End With
With ComboBox1
.List() = tbl()
End With
End Sub
どこを直せば良いか、教えてください。
よろしくお願いします。

回答No.3

chie65536（@chie65535） · ベストアンサー

もう１つ。

宣言文で

Option Base 1

と書いてある所はありませんか？

これがかいてある場合、配列の添え字は「1から」になります。

Private Sub UserForm_Initialize()
の方では
ReDim tbl(imax)
For i = 1 To imax
tbl(i) = Range("A" & i).Value
Next i
のように、配列の添え字は「1からimaxまで、imax個」になっています。

一方

Private Sub CommandButton1_Click()
の方では
j = -1
とやってから
j = j + 1
tbl(j) = Range("A" & i).Value
とやってますから、配列の添え字は「0からcntまで、cnt+1個」のツモリで動い
ています。

Option Base 1

が宣言されている場合は、配列の添え字は「1から」ですから「jが0の状態で、tbl(j)に値を入れようとすると、添え字の値がエラーになる」のです。

実は、コレがエラーが出ている「直接の原因」です。

プログラム全体を見直しして「配列を、1番から使うか、0番から使うか、統一しないといけない」です。

現在は、UserForm_Initialize()の方は「1番から使うつもり」になっていて、CommandButton1_Click()の方は「0番から使うつもり」になっていて、整合性が取れていません。

とりあえずは「j = -1」と書いてあるのを「j = 0」に直して、プログラムの先頭行に「Option Base 1」と言う行を追加しましょう。これで「とりあえずはエラーが消える筈」です。

なお「色々なサイトから参考にした」という場合、これ以外にも「整合性がとれてない部分」が多く存在する筈なので、プログラム全体を見直しする必要があるでしょう。隠れたバグは、ここだけでは無い筈です。

回答No.4

imogasi

>Excel VBA ユーザフォームの検索について
は表現が不適切。
「Excel VBA ユーザフォームを使っての検索について」
ぐらいならおかしくはないと思うが。
ーー
どういう道具立てで、検索しようとしているのか、文章ではっきり説明せよ。
読者に、質問者の、十分でないコードをコピぺして読み解いてくれ、修正して回答してくれと言うのは、質問者として手抜きすぎる。
ーー
もう一つの要求は、Ｓｈｅｅｔ１－Ｓｈｅｅｔ３の簡単な模擬実例でも挙げて説明すべきだ。
Ｓｈｅｅｔ１、Ｓｈｅｅｔ２、Ｓｈｅｅｔ３があるらしいが、Ｓｈｅｅｔ２から、Ｃｏｍｂｏｂｏｘ１にリストするのはどういうものか。
Ｓｈｅｅｔ２の役割は何か？
検索語はＴｅｘｔＢｏｘ１の語とどういう関係にあるのか。
ＴｅｘｔＢｏｘとＣｏｍｂｏｂｘとの役割はどうなっているのか？
ーー
シートを検索するのは、普通はＦｉｎｄメソッドを使うが、ＩＮＳＴＲ関数を使っているのは知らないからか（Ｆｉｎｄ、ＦｉｎｄＮｅｘｔは確かにむつかしいが）
ーー
そういうやりたいこの「仕組み（検索語）と対象」の説明が不足しているとおもう。.
ーー
すでにご指摘があるが、シートが複数の場合は、神経質ぐらい、繰り返してシート部分から、記述したほうが良いと思う。ＡｖｔｉｖｅＳｈｅｅｔに当たる考えは封印すべきと思う。
プロシの初めに
Set Sh1 = Worksheets("Sheet1")
Set Sh2 = Worksheets("Sheet2")
Set Sh3 = Worksheets("Sheet3")
と定義してＳｈ１、Ｓｈ２，Ｓｈ３のどれかをＲａｎｇｅ表現の前につけて限定するとか。
ーー
Ｉｍａｘの値は、別モジュールに変数結果を渡すのは高等テクニックなので、注すること。質問のコードではその工夫が見られないようだが。
ーー
質問掲示コードを修正して上げるつもりだったが、やりたいことの仕組みが理解できず諦めた。上記で何かの参考になれば。

回答No.2

chie65536（@chie65535）

なお、UserForm_Initialize()の方も「呼び出した瞬間（ユーザーフォームを初期化した瞬間」のアクティブシートがsheet2じゃなかったら

tbl(i) = Range("A" & i).Value

の「Range()」は「Sheet2じゃないシート」を参照するので、やはり、誤動作します。

こっちも同様に

Private Sub UserForm_Initialize()
Dim i As Long, imax As Long
Dim tbl() As Variant
imax = ThisWorkbook.Worksheets("sheet2").Cells(ThisWorkbook.Worksheets("sheet2").Rows.Count, "A").End(xlUp).Row
ReDim tbl(imax)
For i = 1 To imax
tbl(i) = ThisWorkbook.Worksheets("sheet2").Range("A" & i).Value
Next i
ComboBox1.List() = tbl()
End Sub

のようにしないといけません。

あと

imax = ThisWorkbook.Worksheets("sheet2").Cells(ThisWorkbook.Worksheets("sheet2").Rows.Count, "A").End(xlUp).Row

ではなく

imax = ThisWorkbook.Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row

としてしまった場合、１つのブックしか開いてなければ問題ありませんが、旧バージョン(2007より古いバージョン)のEXCELのブックを開いている場合、問題が出る可能性があります。

Excel 2007以前のバージョンで作成したブックを開いていて、そのブックがアクティブになっていると「Rows.Count」の値は「65536」になってしまいます。

しかし、Excel 2007以降のブックは、行数の最大値は「1048576」ですから「入力されている最後の行」を求めるのに失敗してしまいます。

「Range」や「Cells」や「Rows.Count」など「現在アクティブになっているブックの、現在アクティブになっているシートに関連するプロパティやメソッド」を使用する場合は「今、どのブックの、どのシートがアクティブなのか？」を、きちんと把握していなければなりません。

さもないと「シート２をいじってるツモリで、全然ちがうシートをいじってた」なんて事が起きます（それでエラーが起きれば不具合に気付けますが、エラーが起きなければ「実行してる筈なのに、なにも起きない」とか「変な値がセットされる」とか、どうして良いか判らなくなります）

回答No.1

chie65536（@chie65535）

With ThisWorkbook.Worksheets("sheet2")
から
End With
までの間は「Range()やCells()」と「.Range()や.Cells()」は、意味が異なります。

「.Range()」は「ThisWorkbook.Worksheets("sheet2").Range()」を意味します。

一方「Rnage()」は「ActiveSheet.Range()」を意味します。

「Cells()」や「Rows」など、他の物も同様です。

ですので「アクティブシートがsheet2じゃない状態」の場合「Range」や「Cells」は「sheet2じゃないシート」を参照してしまいます。

つまり「Rangeと.Range、Cellsと.Cellsなど、ピリオドがある物とない物が混在してしまっているので、アクティブシートがsheet2じゃないと正常に動作しない状態」になってしまっています。

「アクティブなシートがどこであっても、ちゃんと動くようにしたい」のであれば「With文を使わず、シートの指定をきちんと書く」ようにしましょう。

とりあえず

Private Sub CommandButton1_Click()
Dim i As Long, imax As Long
Dim tbl() As Variant
Dim cnt As Long, j As Long
j = -1
imax = ThisWorkbook.Worksheets("sheet2").Cells(ThisWorkbook.Worksheets("sheet2").Rows.Count, "A").End(xlUp).Row
cnt = Application.CountIf(ThisWorkbook.Worksheets("sheet2").Range("A1:A" & imax), "*" & TextBox1.Text & "*")
ReDim tbl(cnt)
For i = 1 To imax
If InStr(ThisWorkbook.Worksheets("sheet2").Range("A" & i), TextBox1.Text) > 0 Then
j = j + 1
tbl(j) = ThisWorkbook.Worksheets("sheet2").Range("A" & i).Value
End If
Next i
ComboBox1.List() = tbl()
End Sub

のように、With文を一切使わないで、１つ１つ、こまめにシートを指定してみて下さい。