VBAでXMACHを使用すると、配列の34,465行以降同じ値は却ってくる。

Question

VBAでXMACHを使用すると、配列の34,465行以降同じ値は却ってくる。

信一中嶋 60

Excel2021のVBAで100,000行の配列(Slist() as Long)を作成し、WorksheetFunction.XMatch(x, Slist, -1, 2)で検索すると、34,465行以降が全て34,464行が返却される。

なお、ExcelのワークシートでXMATCH(C34464,$C$3:$C$34484,-1,2)を実行すると正常に値が返却されます。

VBAでは配列の行数に制限があるのでしょうか？

XMACH

<モデレーター注>
質問内容をもとにタグを「Microsoft 365 と Office | Excel | その他 | Windows」→「開発者テクノロジ」に変更しました。

信一中嶋 60 評価のポイント

2026-03-03T03:58:34.48+00:00

質問者です。

貼り付けたJPEGが誤っていました。

正しくは以下です。
sk.exe 165 評価のポイント

2026-03-03T05:01:56.7633333+00:00
VBAで100,000行の配列(Slist() as Long)を作成

配列変数 Slist の各要素の値をどのように代入/取得しているのか。

WorksheetFunction.XMatch(x, Slist, -1, 2)で検索

変数 x のデータ型は何なのか。

XMatch を含むステートメントをどのようにして呼び出しているのか。

以上の点が不明ですので、現時点ではお答えのしようがありません。

可能な限り、実際に記述されているプロシージャ全体のコードをそのまま転記されることをお奨めします。
motosan 3,570 評価のポイント

2026-03-03T06:44:01.5233333+00:00

ExcelのワークシートでXMATCH(C34464,$C$3:$C$34484,-1,2)を実行すると正常に値が返却されます。

C34464 は $C$3:$C$34484 の 34462 番目の値なので VBA でも正常に値が返却されるのではありませんか？
motosan 3,570 評価のポイント

2026-03-03T07:10:06.2666667+00:00
添付の画像の意味が良くわかりません。

XMATCH(C34464,$C$3:$C$34484,-1,2)は常に同じ値がかえるのではありませんか？

WorksheetFunction.XMatch(x, Slist, -1, 2) は x の値が何か不明です。

実際は何を比較しているのでしょうか？

また、$C$3:$C$34484とSlistは配列のサイズが同じで同じ値が格納されていますか？
信一中嶋 60 評価のポイント

2026-03-03T07:23:39.9033333+00:00
配列変数 Slist の各要素の値をどのように代入/取得しているの

Slist（素数）を作成するクラスを以下に示します。

Const SNmax = 100000 '作成する素数の数 Private Slist(1 To SNmax) As Long '素数リスト '初期設定で素数リストを作成 Private Sub Class_Initialize() Slist(1) = 2: Slist(2) = 3: cnt& = 2 For i& = 5 To &H7FFFFFFF Step 2 For k& = 2 To cnt&: sk& = Slist(k&) If (i& Mod sk&) = 0 Then Exit For If i& < sk& ^ 2 Then cnt& = cnt& + 1 Slist(cnt&) = i& Exit For End If Next k& 　　If SNmax <= cnt& Then Exit For Next i& End Sub

変数 x のデータ型は何なのか。

変数xは下記の通りDoubleです。

XMatch を含むステートメントをどのようにして呼び出しているのか。

'x以下の素数の数を求める Public Function Spi(x As Double) As Double If x < 2 Then Spi = 0: Exit Function Spi = WorksheetFunction.XMatch(x, Slist, -1, 2) End Function

ExcelのワークシートでXMATCH(C34464,$C$3:$C$34484,-1,2)を実行すると正常に値が返却されます。

C34464 は $C$3:$C$34484 の 34462 番目の値なので VBA でも正常に値が返却されるのではありませんか？

・ExcelのワークシートでXMATCHが正常にもかかわらず、VBAのWorksheetFunction.XMatchを使用するとある値(x=408173)を超えてから返却値が変わらなくなくなるためVBAの問題と思われます？

以上よろしくお願いいたします。

質問作成者が受け入れた回答

0 件の追加の回答

お客様の回答

信一中嶋 60 評価のポイント

2026-03-03T03:58:34.48+00:00

質問者です。

貼り付けたJPEGが誤っていました。

正しくは以下です。
sk.exe 165 評価のポイント

2026-03-03T05:01:56.7633333+00:00

VBAで100,000行の配列(Slist() as Long)を作成

配列変数 Slist の各要素の値をどのように代入/取得しているのか。

WorksheetFunction.XMatch(x, Slist, -1, 2)で検索

変数 x のデータ型は何なのか。

XMatch を含むステートメントをどのようにして呼び出しているのか。

以上の点が不明ですので、現時点ではお答えのしようがありません。

可能な限り、実際に記述されているプロシージャ全体のコードをそのまま転記されることをお奨めします。
motosan 3,570 評価のポイント

2026-03-03T06:44:01.5233333+00:00

ExcelのワークシートでXMATCH(C34464,$C$3:$C$34484,-1,2)を実行すると正常に値が返却されます。

C34464 は $C$3:$C$34484 の 34462 番目の値なので VBA でも正常に値が返却されるのではありませんか？
motosan 3,570 評価のポイント

2026-03-03T07:10:06.2666667+00:00

添付の画像の意味が良くわかりません。

XMATCH(C34464,$C$3:$C$34484,-1,2)は常に同じ値がかえるのではありませんか？

WorksheetFunction.XMatch(x, Slist, -1, 2) は x の値が何か不明です。

実際は何を比較しているのでしょうか？

また、$C$3:$C$34484とSlistは配列のサイズが同じで同じ値が格納されていますか？
信一中嶋 60 評価のポイント

2026-03-03T07:23:39.9033333+00:00

配列変数 Slist の各要素の値をどのように代入/取得しているの

Slist（素数）を作成するクラスを以下に示します。

Const SNmax = 100000 '作成する素数の数 Private Slist(1 To SNmax) As Long '素数リスト '初期設定で素数リストを作成 Private Sub Class_Initialize() Slist(1) = 2: Slist(2) = 3: cnt& = 2 For i& = 5 To &H7FFFFFFF Step 2 For k& = 2 To cnt&: sk& = Slist(k&) If (i& Mod sk&) = 0 Then Exit For If i& < sk& ^ 2 Then cnt& = cnt& + 1 Slist(cnt&) = i& Exit For End If Next k& 　　If SNmax <= cnt& Then Exit For Next i& End Sub

変数 x のデータ型は何なのか。

変数xは下記の通りDoubleです。

XMatch を含むステートメントをどのようにして呼び出しているのか。

'x以下の素数の数を求める Public Function Spi(x As Double) As Double If x < 2 Then Spi = 0: Exit Function Spi = WorksheetFunction.XMatch(x, Slist, -1, 2) End Function

ExcelのワークシートでXMATCH(C34464,$C$3:$C$34484,-1,2)を実行すると正常に値が返却されます。

C34464 は $C$3:$C$34484 の 34462 番目の値なので VBA でも正常に値が返却されるのではありませんか？

・ExcelのワークシートでXMATCHが正常にもかかわらず、VBAのWorksheetFunction.XMatchを使用するとある値(x=408173)を超えてから返却値が変わらなくなくなるためVBAの問題と思われます？

以上よろしくお願いいたします。

Answer 1

motosan 3,570

WorksheetFunction.XMatch の引数が間違っているようです。

検索範囲は Range.Value に対応するように下記のようにしてください、

Private Slist() As Variant

Private Sub Class_Initialize()
     ' Slist = Sheet1.Range("A1").Resize(SNmax).Value
      ReDim Slist(1 To SNmax, 1 To 1)
      ・・・
        Slist(cnt&, 1) = i&
End Sub

Slist　は2次元配列になるので注意してください。

今回、この配列は値が全て埋まるので問題ありませんが、埋まらない場合は残りをクリアーする必要があります。

修正）

ReDim でも問題ありません。

信一中嶋 60 評価のポイント

2026-03-03T10:50:02.87+00:00

上記変更で治りました。

なお、 As VariantをAs Longに変更してもOKでした。

ご回答ありがとうございました。