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

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

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

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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