VBA技術解説
VBAでシート関数使用時の配列要素数制限

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2020-05-05

VBAでシート関数使用時の配列要素数制限


VBAでワークシート関数が使えるのはとても便利です。
WorksheetFunctionのシート関数を使う事は多いですが、配列を引数に指定した場合は要素数に制限があります。

・ワークシート関数の使い方 ・WorksheetFunctionで使用できる関数 ・個別の関数の使い方 ・関数の結果(戻り値) ・WorksheetFunctionの使用例. ・検索系の関数での日付の扱い ・WorksheetFunctionのエラー対処 ・最後に
この制限があることは、ワークシート関数だという事を考えれば仕方ないのかもしれません。

エクセルのバージョンアップに伴い制限数が変化する場合があります。
Excel2007で行数が増えたことにより、それまでの制限が大きく緩和された経緯もあります。

スピルに合わせて配列を扱える便利な新関数が登場したのをきっかけに、
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事
改めて新関数を中心に主要な関数について、扱える配列の要素数の制限を確認してみました。
確認した関数は以下になります。

FILTER関数
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で確認しました。
すると、

VBA マクロ シート関数 配列制限

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

VBA マクロ シート関数 配列制限

つまり、
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

VBA マクロ シート関数 配列制限

1,000万件が無事処理されました。
さすがに、これ以上はエクセルで扱うのかも疑問ですので、ここまでにしました。

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)
この条件に引っ掛かってしまいます。
作成される配列の件数は、正しく作成されないので気にしても仕方ありませんが、元配列の件数により変わって來るようです。

SortBy関数でも同じ結果となっています。

VBA実行時の注意
VBA実行中に、VBE画面でクリックすると動作が不安定になります。
クリックした時点で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
なんとも中途半端な数値になりました。
しかも停止位置が、正しく並べ替えられなかったというものです。

VBA マクロ シート関数 配列制限

昇順に作成した数値を降順に並べ替えしているので、順序が逆転して作成されるはずですが、
ary1の最大値が、ary3の先頭に来ていません。

SortBy関数でも同じ結果となっています。

VBA実行時の注意
VBA実行中に、VBE画面でクリックすると不安定な動作となります。
クリックした時点でVBAが停止してしまうという現象が起きます。
DoEventsを適宜入れることで多少は緩和できますが、完全には対応できません。
VBA完成後は問題ないとは思いますが、VBA作成中は十分に注意してください。

また、200万件超となると処理時間もそれなりにかかります。
それでも、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 マクロ シート関数 配列制限

VBA マクロ シート関数 配列制限

VBAでシート関数使用時の配列要素数制限の結果

関数 横配列(1配列) 縦配列(2配列)
FILTER関数 65,536 制限なし
SORT関数 65,536 2,097,152
SORTBY関数 65,536 2,097,152
UNIQUE関数 65,536 制限なし
XLOOKUP関数 65,536 制限なし
HLOOKUP関数 65,536
VLOOKUP関数 制限なし
XMATCH関数 65,536 制限なし
MATCH関数 65,536 制限なし
TRANSPOSE関数 65,536 65,536
※「制限なし」は1,000万件までは動作確認できたという事です。


縦配列(2配列)であればSort以外は特に問題はないようです。
Sort,SortByに関しては、先に書いた通り、件数以外にVBA実行時にも注意してください。
また、一部の関数では引数の省略がシートと異なっている点にも注意してください。



同じテーマ「マクロVBA技術解説」の記事

フォルダー・ファイル・ブック・シートの文字制限
Excel2013におけるScreenUpdatingの問題点
Dir関数の制限について
よくあるVBA実行時エラーの解説と対応
Application.Goto使用時の注意
ScreenUpdating=False時にエラー停止後にシートが固まったら
標準スタイル違いの問題点:標準フォント複写、列幅をピクセルで合わせる
VBAでエラー行位置(行番号)を取得できるErl関数
WorksheetFunction.Matchで配列を指定した場合の制限について
VBAでシート関数使用時の配列要素数制限


新着記事NEW ・・・新着記事一覧を見る

TOROW関数(配列を横1行の配列にして返す)|エクセル入門(2022-10-31)
TOCOL関数(配列を縦1列の配列にして返す)|エクセル入門(2022-10-31)
CHOOSECOLS関数(配列から複数の指定された列を返す)|エクセル入門(2022-10-29)
CHOOSEROWS関数(配列から複数の指定された行を返す)|エクセル入門(2022-10-29)
WorksheetFunctionの効率的な使い方とスピル新関数の利用|VBA入門(2022-10-27)
VSTACK関数(配列を縦方向に順に追加・結合)|エクセル入門(2022-10-25)
HSTACK関数(配列を横方向に順に追加・結合)|エクセル入門(2022-10-25)
LAMBDA以降の新関数の問題と解説(配列操作関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)|エクセル入門(2022-10-24)
LAMBDA以降の新関数の問題集|エクセル入門(2022-10-24)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.エクセルVBAでのシート指定方法|VBA技術解説
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ