WorksheetFunctionに一次元配列を渡すと配列が欠損する

知らずに落ちると抜け出せなくなるVBAの落とし穴です。

• WorksheetFunction.Matchでなぜか値が見つからない
• WorksheetFunction.Uniqueの件数が少なくなる
• 今まで動いていたマクロがデータ量が6万を超えたら動かなくなった

あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。

WorksheetFunctionに渡せる一次元配列の上限は65,536

WorksheetFunction(以下WF)に渡せる一次元配列には上限があり、
65,537を超える件数の一次元配列を正しく受け取れません。

例えばA列に連番を振っておき、そこから生成した一次元配列に対して、
UNIQUE関数を実行して要素数を見てみます。

正しく動いた場合はセルの数をそのまま返すはずですが。。。

Sub UNIQUE関数に一次元配列を渡す()
    
    Dim Arr
    
    Arr = Rangeから1次元配列を生成(Range("A1:A50000"))
    Debug.Print UBound(WorksheetFunction.Unique(Arr)) '  50000 OK
    
    Arr = Rangeから1次元配列を生成(Range("A1:A65536"))
    Debug.Print UBound(WorksheetFunction.Unique(Arr)) '  65536 OK
    
    Arr = Rangeから1次元配列を生成(Range("A1:A65537"))
    Debug.Print UBound(WorksheetFunction.Unique(Arr)) ' 1 NG 急に要素数1
    
    Arr = Rangeから1次元配列を生成(Range("A1:A100000"))
    Debug.Print UBound(WorksheetFunction.Unique(Arr)) ' 34464 NG
    
End Sub

Function Rangeから1次元配列を生成(対象Range As Range) As Variant

    Dim Arr生成配列() As Variant
    ReDim Arr生成配列(対象Range.Cells.Count - 1) As Variant
    
    Dim i As Long: i = 0
    Dim cell As Range
    For Each cell In 対象Range.Cells
        Arr生成配列(i) = cell.Value
        i = i + 1
    Next

    Rangeから1次元配列を生成 = Arr生成配列
End Function

こんな不思議な結果になりました。

65537件目以降がカットされてしまうならまだわかるのですが、
要素数が65537を超えた途端急に1件になってしまっています。

100,000件で試すと34,464件となり、
これは100000 - 65536 の結果と一致します。

同じようにMATCH関数で試してみるとこんな感じになります。

Sub MATCH関数に一次元配列を渡す()
    
    Dim Arr
    Arr = Rangeから1次元配列を生成(Range("A1:A100000"))
    
    Debug.Print WorksheetFunction.Match(10000, Arr, 0) ' 10000
    Debug.Print WorksheetFunction.Match(20000, Arr, 0) ' 20000
    Debug.Print WorksheetFunction.Match(34464, Arr, 0) ' 34464
    Debug.Print WorksheetFunction.Match(34465, Arr, 0) ' エラー
    Debug.Print WorksheetFunction.Match(40000, Arr, 0) ' 以降エラー
    
End Sub

1件目から34,464件目までは無事なのですが、
それ以降が配列として欠損したことが分かりますね。

65535件を超えるたびに後半の65535件を削除することで、
配列の件数が65535を超えないように制御されているようです。

XLOOKUPでも同様でしたので、おそらくすべてのWFにおいて、
巨大な一次元配列は処理できないようになっているようです。

しかもエラーが出るわけではなく、無告知で静かにカットという仕様。

こういった無告知系トラップは「知っておく」以外に対策が難しいので、
この仕様はしっかり覚えておいてください。

二次元配列やRangeオブジェクトは大丈夫

さてこの対策なのですが割と簡単で、
WFは二次元配列やRangeオブジェクトであれば正しく動きます。

' 一次元配列
Arr = Rangeから1次元配列を生成(Range("A1:A100000"))
Debug.Print UBound(WorksheetFunction.Unique(Arr)) ' 34464 NG

' 二次元配列
Arr = Range("A1:A100000").Value
Debug.Print UBound(WorksheetFunction.Unique(Arr)) ' 100000 OK

' Rangeオブジェクト
Set rng = Range("A1:A100000")
Debug.Print UBound(WorksheetFunction.Unique(rng)) ' 100000 OK

もともとWorksheet上で動くものですからね。

二次元配列であれば1048576件まではしっかり動いてくれるのでご安心ください。

また、セル範囲.Valueでセル値を配列に変換して取得する際も、
セル範囲が1行・1列だったとしても必ず二次元配列として取得されます。

つまりセル値をWFに渡す場合はRangeでもRange.ValueでもOKということですね。

この現象が起きるのは「一次元配列」の時だけですので、
もしどうしてもWFを使いたい場合はn行1列の二次元配列に変換してください。

Transpose関数だけは二次元配列でもNG

Transpose関数も同じく「65,535件」上限問題がありますが、
こちらは二次元配列を渡しても発生してしまいます。

Transpose関数には「生成配列が1行n列の場合は一次元に変換」する仕様があるため、
この影響で二次元配列でも動かないのかもしれません。

二次元配列を使う場合でも、Transpose関数だけは注意しておきましょう。

詳しくはこちらの記事をどうぞ。
www.limecode.jp

この情報はExcelVBAブログ友達のいおりさんからご提供いただきました。

いおりさんありがとうございました(´∀｀)
iori016.hatenablog.com