VBAでシート関数使用時の配列要素数制限
VBAでワークシート関数が使えるのはとても便利です。
WorksheetFunctionのシート関数を使う事は多いですが、配列を引数に指定した場合は要素数に制限があります。
Excel2007で行数が増えたことにより、それまでの制限が大きく緩和された経緯もあります。
確認した関数は以下になります。
SORT関数、SORTBY関数
UNIQUE関数
XLOOKUP関数、HLOOKUP関数、VLOOKUP関数
XMATCH関数、MATCH関数
TRANSPOSE関数
横配列(1次元配列)は制限がかなりきつい、縦配列(2次元配列)は制限がほとんどない。
少なくともこれについては、わかった上でテストを開始しています。
FILTER関数
横配列(1次元配列)をFILTER
Sub TestFilter1()
Dim i As Long, cnt As Long
Dim ary1, ary2, ary3
For cnt = 100 To 10000000 Step 100
ReDim ary1(1 To cnt)
ReDim ary2(1 To cnt)
For i = LBound(ary1) To UBound(ary1)
ary1(i) = i
ary2(i) = True
Next
'=FILTER(配列,含む,空の場合)
ary3 = WorksheetFunction.Filter(ary1, ary2)
If UBound(ary1) <> UBound(ary3) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(LBound(ary1)) <> ary3(LBound(ary3)) Then
Debug.Print ary1(LBound(ary1)) & " → " & ary3(LBound(ary3))
Stop
ElseIf ary1(UBound(ary1)) <> ary3(UBound(ary3)) Then
Debug.Print ary1(LBound(ary1)) & " → " & ary3(LBound(ary3))
Stop
End If
Next
MsgBox "OK"
End Sub
1,000万件まで確認しますが、いきなり1Stepではいつ終わるか分からないので、まずは100Stepで確認しました。
すると、

配列が正しく作成されていません。
そこで、次は65000開始1Step

65,536
これが限界値となりました。
縦配列(2次元配列)をFILTER
Sub TestFilter2()
Dim i As Long, cnt As Long
Dim ary1, ary2, ary3
For cnt = 100000 To 10000000 Step 100000
ReDim ary1(1 To cnt, 1 To 1)
ReDim ary2(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
ary2(i, 1) = True
Next
'=FILTER(配列,含む,空の場合)
ary3 = WorksheetFunction.Filter(ary1, ary2)
If UBound(ary1, 1) <> UBound(ary3, 1) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(LBound(ary1), 1) <> ary3(LBound(ary3), 1) Then
Debug.Print ary1(LBound(ary1), 1) & " → " & ary3(LBound(ary3), 1)
Stop
ElseIf ary1(UBound(ary1), 1) <> ary3(UBound(ary3), 1) Then
Debug.Print ary1(UBound(ary1), 1) & " → " & ary3(UBound(ary3), 1)
Stop
End If
Next
MsgBox "OK"
End Sub

さすがに、これ以上はエクセルで扱うのかも疑問ですので、ここまでにしました。
SORT関数、SORTBY関数
横配列(1次元配列)をSORT
Sub TestSort1()
Dim i As Long, cnt As Long
Dim ary1, ary3
For cnt = 100 To 10000000 Step 100
ReDim ary1(1 To cnt)
For i = LBound(ary1) To UBound(ary1)
ary1(i) = i
Next
'=SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])
ary3 = WorksheetFunction.Sort(ary1, 1, -1, True)
'=SORTBY(配列,基準配列,並べ替え順序,...) VBAでは並べ替え順序は必須
'ary3 = WorksheetFunction.SortBy(ary1, ary1, -1)
If UBound(ary1) <> UBound(ary3) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(LBound(ary1)) <> ary3(UBound(ary3)) Then
Debug.Print ary1(LBound(ary1)) & " → " & ary3(UBound(ary3))
Stop
ElseIf ary1(UBound(ary1)) <> ary3(LBound(ary3)) Then
Debug.Print ary1(UBound(ary1)) & " → " & ary3(LBound(ary3))
Stop
End If
Next
MsgBox "OK"
End Sub
Filter同様に、100Stepから狭めていった結果は、
65,536
予想通りではあります。
これを超えた時は、配列が正しく作成されません。
If UBound(ary1) <> UBound(ary3)
この条件に引っ掛かってしまいます。
作成される配列の件数は、正しく作成されないので気にしても仕方ありませんが、元配列の件数により変わって來るようです。
クリックした時点でVBAが停止してしまうという現象が起きます。
DoEventsを適宜入れることで多少は緩和できますが、完全には対応できません。
VBA完成後は問題ないとは思いますが、VBA作成中は十分に注意してください。
縦配列(2次元配列)をSORT
Sub TestSort2()
Dim i As Long, cnt As Long
Dim ary1, ary3
For cnt = 100000 To 10000000 Step 100000
ReDim ary1(1 To cnt, 1 To 1)
ReDim ary2(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
'=SORT(配列,並べ替えインデックス,並べ替え順序,並べ替え基準)
ary3 = WorksheetFunction.Sort(ary1, 1, -1, False)
'=SORTBY(配列,基準配列,並べ替え順序,...) VBAでは並べ替え順序は必須
'ary3 = WorksheetFunction.SortBy(ary1, ary1, -1)
If UBound(ary1, 1) <> UBound(ary3, 1) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(LBound(ary1), 1) <> ary3(UBound(ary3), 1) Then
Debug.Print ary1(LBound(ary1), 1) & " → " & ary3(UBound(ary3), 1)
Stop
ElseIf ary1(UBound(ary1), 1) <> ary3(LBound(ary3), 1) Then
Debug.Print ary1(UBound(ary1), 1) & " → " & ary3(LBound(ary3), 1)
Stop
End If
Next
MsgBox "OK"
End Sub
10万開始の10万Stepで実行したところ、
2100000で停止しました。
そこで、2000000から1000Stepで実行したところ、
2098000で停止しました。
そして、順に狭めていった結果は、
2,097,152
なんとも中途半端な数値になりました。
しかも停止位置が、正しく並べ替えられなかったというものです。

ary1の最大値が、ary3の先頭に来ていません。
クリックした時点でVBAが停止してしまうという現象が起きます。
DoEventsを適宜入れることで多少は緩和できますが、完全には対応できません。
VBA完成後は問題ないとは思いますが、VBA作成中は十分に注意してください。
それでも、1.5秒程度で完了しているので十分高速処理されていると思います。
これ以降は、予想通り同じ結果となりましたので、テストしたVBAコードと結果のみ掲載します。
横配列(1次元配列)は、65,536
縦配列(2次元配列)は、1,000万件OK
全体の結果については、最後にまとめています。
UNIQUE関数
横配列(1次元配列)をUNIQUE
Sub TestUnique1()
Dim i As Long, cnt As Long
Dim ary1, ary3
For cnt = 100 To 10000000 Step 100
ReDim ary1(1 To cnt)
For i = LBound(ary1) To UBound(ary1)
ary1(i) = i
Next
'=UNIQUE(配列,[列の比較],[回数指定])
ary3 = WorksheetFunction.Unique(ary1, True)
If UBound(ary1) <> UBound(ary3) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(UBound(ary1)) <> ary3(UBound(ary3)) Then
Debug.Print ary1(LBound(ary1)) & " → " & ary3(UBound(ary3))
Stop
ElseIf ary1(LBound(ary1)) <> ary3(LBound(ary3)) Then
Debug.Print ary1(UBound(ary1)) & " → " & ary3(LBound(ary3))
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
65,536
縦配列(2次元配列)をUNIQUE
Sub TestUnique2()
Dim i As Long, cnt As Long
Dim ary1, ary3
For cnt = 1000000 To 10000000 Step 1000000
ReDim ary1(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
'=UNIQUE(配列,[列の比較],[回数指定])
ary3 = WorksheetFunction.Unique(ary1, False)
If UBound(ary1, 1) <> UBound(ary3, 1) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(UBound(ary1), 1) <> ary3(UBound(ary3), 1) Then
Debug.Print ary1(LBound(ary1), 1) & " → " & ary3(UBound(ary3), 1)
Stop
ElseIf ary1(LBound(ary1), 1) <> ary3(LBound(ary3), 1) Then
Debug.Print ary1(UBound(ary1), 1) & " → " & ary3(LBound(ary3), 1)
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
1,000万件OK
XLOOKUP関数、HLOOKUP関数、VLOOKUP関数
横配列(1次元配列)をXLOOKUP
Sub TestXLookup1()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 65000 To 10000000 Step 1
ReDim ary1(1 To cnt)
For i = LBound(ary1) To UBound(ary1)
ary1(i) = i
Next
'=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード,検索モード])
rtn = WorksheetFunction.XLookup(ary1(UBound(ary1)), ary1, ary1)
If rtn <> ary1(UBound(ary1)) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
65,536
縦配列(2次元配列)をXLOOKUP
Sub UniqueXLookup2()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 1000000 To 10000000 Step 1000000
ReDim ary1(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
'=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード,検索モード])
rtn = WorksheetFunction.XLookup(ary1(UBound(ary1, 1), 1), ary1, ary1)
If rtn <> ary1(UBound(ary1, 1), 1) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
1,000万件OK
横配列(1次元配列)をHLOOKUP
Sub TestHLookup1()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 65000 To 10000000 Step 1
ReDim ary1(1 To cnt)
For i = LBound(ary1) To UBound(ary1)
ary1(i) = i
Next
'=HLOOKUP(検索値,範囲,行番号,検索方法)
rtn = WorksheetFunction.HLookup(ary1(UBound(ary1)), ary1, 1, 0)
If rtn <> ary1(UBound(ary1)) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
65,536
縦配列(2次元配列)をVLOOKUP
Sub UniqueVLookup2()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 1000000 To 10000000 Step 1000000
ReDim ary1(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
DoEvents
'=VLOOKUP(検索値,範囲,列番号,検索方法)
rtn = WorksheetFunction.VLookup(ary1(UBound(ary1, 1), 1), ary1, 1, 0)
DoEvents
If rtn <> ary1(UBound(ary1, 1), 1) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
1,000万件OK
XMATCH関数、MATCH関数
横配列(1次元配列)をXMATCH
Sub TestXMatch1()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 100 To 10000000 Step 100
ReDim ary1(1 To cnt)
For i = LBound(ary1) To UBound(ary1)
ary1(i) = i
Next
'=XMATCH(検索値,検索範囲,一致モード,[検索モード]) VBAでは一致モードは必須
rtn = WorksheetFunction.XMatch(ary1(UBound(ary1)), ary1, 0)
If rtn <> ary1(UBound(ary1)) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
65,536
縦配列(2次元配列)をXMATCH
Sub UniqueXMatch2()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 1000000 To 10000000 Step 1000000
ReDim ary1(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
'=XMATCH(検索値,検索範囲,一致モード,[検索モード]) VBAでは一致モードは必須
rtn = WorksheetFunction.XMatch(ary1(UBound(ary1, 1), 1), ary1, 0)
If rtn <> ary1(UBound(ary1, 1), 1) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
1,000万件OK
横配列(1次元配列)をMATCH
Sub TestMatch1()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 100 To 10000000 Step 100
ReDim ary1(1 To cnt)
For i = LBound(ary1) To UBound(ary1)
ary1(i) = i
Next
'=MATCH(検査値,検査範囲,照合の種類)
rtn = WorksheetFunction.Match(ary1(UBound(ary1)), ary1, 0)
If rtn <> ary1(UBound(ary1)) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
65,536
縦配列(2次元配列)をMATCH
Sub UniqueMatch2()
Dim i As Long, cnt As Long
Dim ary1, rtn
For cnt = 1000000 To 10000000 Step 1000000
ReDim ary1(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
'=MATCH(検査値,検査範囲,照合の種類)
rtn = WorksheetFunction.XMatch(ary1(UBound(ary1, 1), 1), ary1, 0)
If rtn <> ary1(UBound(ary1, 1), 1) Then
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
1,000万件OK
TRANSPOSE関数
横配列(1次元配列)をTRANSPOSE
Sub TestTranspose2()
Dim i As Long, cnt As Long
Dim ary1, ary3
For cnt = 60000 To 10000000 Step 1
ReDim ary1(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
ary3 = WorksheetFunction.Transpose(ary1)
If UBound(ary1, 1) <> UBound(ary3, 1) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(UBound(ary1), 1) <> ary3(UBound(ary3)) Then
Debug.Print ary1(LBound(ary1), 1) & " → " & ary3(UBound(ary3), 1)
Stop
ElseIf ary1(LBound(ary1), 1) <> ary3(LBound(ary3)) Then
Debug.Print ary1(UBound(ary1), 1) & " → " & ary3(LBound(ary3), 1)
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
65,536
縦配列(2次元配列)をTRANSPOSE
Sub TestTranspose2()
Dim i As Long, cnt As Long
Dim ary1, ary3
For cnt = 60000 To 10000000 Step 1
ReDim ary1(1 To cnt, 1 To 1)
For i = LBound(ary1) To UBound(ary1)
ary1(i, 1) = i
Next
ary3 = WorksheetFunction.Transpose(ary1)
If UBound(ary1, 1) <> UBound(ary3, 1) Then
Debug.Print UBound(ary1) & " → " & UBound(ary3)
Stop
ElseIf ary1(UBound(ary1), 1) <> ary3(UBound(ary3)) Then
Debug.Print ary1(LBound(ary1), 1) & " → " & ary3(UBound(ary3), 1)
Stop
ElseIf ary1(LBound(ary1), 1) <> ary3(LBound(ary3)) Then
Debug.Print ary1(UBound(ary1), 1) & " → " & ary3(LBound(ary3), 1)
Stop
End If
Next
MsgBox "OK"
End Sub
結果は、
65,536
TRANSPOSEは縦横入替なので、ある意味当然の結果だと思います。
VBAでシート関数使用時の配列要素数制限まとめ
確認したPC環境


VBAでシート関数使用時の配列要素数制限の結果
縦配列(2配列)であればSort以外は特に問題はないようです。
Sort,SortByに関しては、先に書いた通り、件数以外にVBA実行時にも注意してください。
また、一部の関数では引数の省略がシートと異なっている点にも注意してください。
同じテーマ「マクロVBA技術解説」の記事
フォルダー・ファイル・ブック・シートの文字制限
Excel2013におけるScreenUpdatingの問題点
Dir関数の制限について
よくあるVBA実行時エラーの解説と対応
Application.Goto使用時の注意
ScreenUpdating=False時にエラー停止後にシートが固まったら
標準スタイル違いの問題点:標準フォント複写、列幅をピクセルで合わせる
VBAでエラー行位置(行番号)を取得できるErl関数
WorksheetFunction.Matchで配列を指定した場合の制限について
VBAでシート関数使用時の配列要素数制限
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.メッセージボックス(MsgBox関数)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.ブック・シートの選択(Select,Activate)|VBA入門
10.条件分岐(Select Case)|VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- VBAでシート関数使用時の配列要素数制限
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。