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

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

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


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

VBA関数以外に、Excelワークシート関数をマクロVBAで使うことが出来ます、ワークシート関数は、VBA関数よりはるかに多くの関数があるので、ぜひ活用したいところです。。ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多いものです。
この制限があることは、ワークシート関数だという事を考えれば仕方ないのかもしれません。

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

スピルに合わせて配列を扱える便利な新関数が登場したのをきっかけに、
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。
改めて新関数を中心に主要な関数について、扱える配列の要素数の制限を確認してみました。
確認した関数は以下になります。

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 ・・・新着記事一覧を見る

ユーザーに絶対に停止させたくない場合のVBA設定|VBA技術解説(4月1日)
CharactersプロパティとCharactersオブジェクト|VBA技術解説(3月31日)
指数近似/対数近似/累乗近似(掲載順位とCTR)|エクセル関数超技(3月31日)
練習問題32(連続数値部分を取り出し記号で連結)|VBA練習問題(3月24日)
連続数値部分を取り出し記号で連結|エクセル関数超技(3月24日)
数式バーの高さを数式の行数で自動設定|VBAサンプル集(3月21日)
LET関数(数式で変数を使う)|エクセル入門(3月21日)
スピルに対応したXSPLITユーザー定義関数(文字区切り)|VBAサンプル集(3月15日)
XMATCH関数(範囲から値を検索し一致する相対位置)|エクセル入門(3月14日)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)|エクセル入門(3月14日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.繰り返し処理(For Next)|VBA入門
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.セルに文字を入れるとは(Range,Value)|VBA入門




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


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



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