えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

Excelシートのデータ範囲を2次元配列に格納するVBA汎用関数を作ってみた Part1

Excelシートの使用済みデータ範囲をあらゆる状況下で確実に2次元配列に格納できる汎用関数を作ってみたので紹介します。

f:id:Kotori-ChunChun:20190614230310p:plain


きっかけ

(本業ではないとは言え)ExcelVBAを書き続けてこの道10年の私が、何をいまさら・・・という感じですが、実は

Excelシートのデータ範囲を2次元配列に格納する汎用関数

を作ったことがなかったのです。


なんででしょうね。

なんででしょうか。

まあ必要なかったんです。

普段は下記のマイルールがありますからね。


Excel VBA 2次元配列」などと検索して出てくる各種サイトで紹介されている方法は、理解のしやすさを優先しており特定の条件下では正常に動作しないバグがあります。

そのため、単純に読み込んで副作用について一切説明をしないか、複数パターン紹介して「状況に応じて使い分けてください」といったサンプルばかりです。

今回作成した関数は「非表示セルが存在する環境下で、A1からデータの存在するセルまでのデータを2次元配列に格納する関数」です。

もちろん、状況に応じて調整が必要になる場面はあるでしょうが、とにかく最小限のメモリで全データを確実に2次元配列に取り込むことを主目的として作成しました。


そもそものきっかけは、修正中のVBAプログラムにグローバル変数使いまくりの危険なプロシージャがあったため、仕様を大きく変えずに置き換えられる汎用関数が、ど~~~しても欲しくなったので、ちょっと作ってみました。


↓修正前の超危険な関数


マイルール

さて、私がExcelシート上のデータ範囲を2次元配列に読み込む時のテンプレは

Data = WS.Range(WS.Cells(1, 1), WS.UsedRange)

と決めています。

IMEの辞書に、だた=と登録しており、無意識下で入力できるようにしています。

ある時は「複数シート」、またある時は「ActiveSheet」、ある時は「ThisWorkbook.Worksheets(1)」など、相当数のバリエーションが登録されています。

末尾に不要な要素が付属する問題を除けば、大半の場面でこの記法で対応できます。


また、2次元配列のデータ範囲をExcelシート上に書き出すときのテンプレは

WS.Cells(1, 1).Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data

と決めています。

もちろん、こちらもIMEの辞書に=だたと登録しています。

こちらも一定の条件がつくものの、大半の場面はこの記法で対応できます。

ただし、書き出しについては、今回はこれ以上言及しません。


UsedRange利用上の注意

長年の経験から、マイルールの記法がベストだと信じて止まない私ですが、どうにもTwitterやブログを見ている限り、この書き方は微妙な評価なように感じています。


それもそのはず、UsedRangeにはいくつかの気になる点が存在します。

  • UsedRangeプロパティの取得が遅い。
  • 開始位置がA1になるとは限らない。
  • 白紙のときはA1を返す。
  • 末尾の取得範囲が安定しない。
  • メモリ不足の危険性がある。

ちなみに、いつものことながら、Microsoft Docs - UsedRangeプロパティはなんの役にも立ちません。


UsedRangeプロパティの取得が遅い

知ってました? UsedRangeって結構遅いんですよ。

と言っても、体感できる場面は限られますけどね。

マイルールのように開始時に1回使う分には全く問題ありません。

最悪なのはこんな書き方をした時です。

Sub UsedRangeの悪い例()
    Dim tStart As Single, tStop As Single: tStart = Timer
    Dim i As Long, j As Long
    With ActiveSheet
        For i = 1 To .UsedRange.Rows.Count
            For j = 1 To .UsedRange.Columns.Count
                '.Cells(i, j) = 1
            Next
        Next
    End With
    tStop = Timer: Debug.Print Format$(tStop - tStart, "0.00") & " sec."
End Sub

私のPC(Core i7 4790K)では、3万行=つまり3万回の実行で1秒の時間がかかりました。

1秒って結構デカいですからね?


一方で、こうすると、メチャクチャ早くなります。

Sub UsedRangeの良い例()
    Dim tStart As Single, tStop As Single: tStart = Timer
    Dim i As Long, j As Long
    With ActiveSheet.UsedRange
        For i = 1 To .Rows.Count
            For j = 1 To .Columns.Count
                '.Cells(i, j) = 1
            Next
        Next
    End With
    tStop = Timer: Debug.Print Format$(tStop - tStart, "0.00") & " sec."
End Sub

私のPCでは、0.05秒~0.10秒になりました。

変更したのは、With ActiveSheetWith ActiveSheet.UsedRangeForの部分です。

前者ではUsedRangeを3万回ほど実行していますが、後者では1回しか実行していません。


本来の処理時間が10秒かかるものが11秒になるなら許容範囲内ですが、一瞬で終わるはずのマクロが1秒かかるとしたら、それはストレスの元になります。

あとは大量のファイルを処理する時も、1秒×100ファイルなら100秒ですから、無視できない時間になりますね。チリツモ~

もし「悪い例」のような書き方をしていたら、「良い例」に書き換えるとちょっと高速化されるかもしれませんよ。


開始位置がA1になるとは限らない

UsedRangeは使用済みのセル範囲を返すので、先頭の行や列を飛ばしてデータを入力されると、開始位置がA1になりません。

見た目のために先頭の行を開けるなー! という主張がありますが、その要因の一つがコレでしょうね。

Cells(行,列).Valueと同じインデックスでアクセスできる2次元配列にしたいのに、開始位置が安定しないようでは安易に置き換えすることができず、とてもじゃないけど怖くて使えません。

だからこそ、この対策として、マイルールでは、

WS.Range(WS.Cells(1, 1), WS.UsedRange)

という、ちょっと周りくどい書き方をしているわけです。


白紙のときはA1を返す

UsedRangeは新規作成されたばかりのシートだとA1を返す仕様です。

しかし、その後、B5にデータが記入されるとB5が返ります。

納得いきませんが、こればかりは仕方ありません。

これが地味~に厄介です。


メモリ不足の危険性がある

まあこれはUsedRangeに限った話じゃないのですが。

超広範囲のデータを2次元配列に読み込んでしまうと、メモリ不足で実行時エラーになります。

こればっかりは巨大なデータを扱う場面では、小分けにして処理するとかVBA以外を使うとか検討する他ないと思います。


データの入っていない領域も範囲に含めてしまう

UsedRangeは使用済みのセル範囲を返すので、書式設定だけしかされていないセルも対象に含めてしまいます。

データの記載された最終行を取り出したいという人にとっては、非常に扱いづらいものと思われます。

さらに、使用済みセル範囲の更新のタイミングも厄介で、行や列を削除しても削除前の行数・列数を基準に範囲を返してしまうことがあります。

この範囲は上書き保存で更新されるはずですが、バージョンによっては挙動が微妙に違ったりするような話も聞くので、それも使用をためらう要因の一つかもしれません。


私がマイルールでコーディングする時は、こういったリスクがあるのを承知で問題とならないように記述する癖がついているため、これまで気にすることはありませんでした。

今回は元の互換性を保つ必要があるため、ココを解消していきます。


データの存在する最終行・列を求める

ネットで検索すると良く出てくるのが、

'A列からCtrl+↓、1行目をCtrl+→した時の行き先を最終行列とする
'※かなり危険(空白が存在すると途切れる)
FinalRow = Range("A1").End(xlDown).Row
FinalCol = Range("A1").End(xlToRight).Column

とか

'末尾の行、列からCtrl+↑、Ctrl+←したときの行き先を最終行列とする
'※A列、1行目など特定の行列だけから判断できる
'※結構危険(非表示セルが無視されるし)
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column

とか

'Ctrl+Endした時の行き先「最後のセル」を最終行列とする
'※結構危険(非表示セルが無視される)
FinalRow = Range("A1").SpecialCells(xlLastCell).Row
FinalCol = Range("A1").SpecialCells(xlLastCell).Column

なんてのがありますが、どれも危険なので使わないことをオススメします。

これらは、非表示(フィルタ適用時)を考慮して計算したい時に適したコードで、純粋なデータ全体を取得したい場面ではトラブルの元となるので避けたほうが良いです。


というわけで、一番信用できるのはUsedRange+Findなのです。

追記:.Findはフィルタされたセルを無視するため、完全ではありませんでした。本記事の末尾に続編へのリンクを記載しました。2019/6/16

'※ちょっと危険(フィルタで非表示にされているセルは無視される)
With ActiveSheet.UsedRange
    FinalRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    FinalCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
End With

詳しい解説は省きますが、データ範囲の末尾から逆方向に数式またはデータの入ったセルを検索しています。


これらの違いは、下記のサイトがわかりやすく解説してくれてます。

http://www.niji.or.jp/home/toru/notes/8-2.html

http://www.niji.or.jp/home/toru/notes/8.html


この記事を書いてて、ふと、逆順探索するなら別にCellsでも良いんじゃ・・・?と思って検証した結果、UsedRangeを使ったほうが僅かながら高速という事が分かりました。

追記:次記事にてデータ量によってはCellsのほうが高速になる場面が見つかりました。2019/6/16

1万回 UsedRange Cells
100行5列 6.19 7.08
30000行10列 6.24 7.09


データを2次元配列に読み込む

2次元配列にセルのデータを読み込むときに、こういうイケてない書き方は止めましょう。

'最悪な例
Data = Range(Cells(1, 1), Cells(FinalRow, FinalCol)).Value


上記を、実務に適したシートを省略しない記法にするとこうなります。

'悪い例1
Data = WS.Range(WS.Cells(1, 1), WS.Cells(FinalRow, FinalCol)).Value

'悪い例2
With ActiveSheet
    Data = .Range(.Cells(1, 1), .Cells(FinalRow, FinalCol)).Value
End With

シートを3回指定しなければならないのが気持ち悪いですよね。


それならResizeプロパティを使って、このように記述した方が遥かにスマートです。

もちろん、このほうが負荷が減るため高速になります。

'良い例
Data = WS.Cells(1,1).Resize(FinalRow,FinalCol).Value


2次元配列読み込みの汎用関数

暫定版

ソースコード

というわけで、先の記述を関数にまとめるとこうなります。

'シート内のセルデータ読み込み二次元配列を返す
Public Function ReadCell_Test1(WS As Worksheet) As Variant

    Dim FinalRow As Long, FinalCol As Long
    
    '領域の末尾からデータの存在するセルを検索
    On Error Resume Next
    
    With WS.UsedRange
        FinalRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        FinalCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    End With
    
    'セルのデータを2次元配列に読み込む
    ReadCell_Test1 = WS.Cells(1, 1).Resize(FinalRow, FinalCol).Value
    
    On Error GoTo 0
    
End Function


しかし、このコードには致命的な問題があります。


単一セルの場合に2次元配列にならない

これはExcelVBAの三大難問?の一つだと思うのですが、Range.Valueは単一セルのときはVariant、複数セルのときはVariant(,)を返すという非常に厄介な性質があるんですよね。

通常は利用する側で分岐しますが、汎用関数とするからには総行数と総列数が1の時は、無理やり配列化して返すようにしないと仕様が複雑になります。


空白シートの場合にA1が返る

先に記載したとおりです。

空白のときはA1を返して欲しくないんですが、このままでは""が返されてしまいます。

存在しない配列の要素数は-1です。2次元配列なのでVariant(-1,-1)を返す処置が必要です。


また、実用性を考えると、もうちょっと機能を付け足したいところです。


ヘッダ部分を消したい

VBAには2次元配列をジョキジョキする関数が無いんですよね。

別途汎用関数を用意しても良いのですが、いかんせんメモリを食う処理なので逆にメモリ不足を誘発することがあります。

したがって、読み込み時点で先頭行列を指定出来るようにしました。

  • Cellsに準拠して省略時は1,1。

  • B2をスタートにしたい時は2,2と指定します。


フッタ部分を消したい

同上です。

ただしインデックスが不定なので、末尾からの差分としました。

  • Resizeのイメージに準拠して、省略時は0,0

  • 例えば最終行を除外したい時は1,0と指定します。


完成版

ソースコード

というわけで、完成版がこちら。

え?長いって?(´・ω・`)知らんがな。

Option Explicit

'API
#If VBA7 Then
    Private Declare PtrSafe Function SafeArrayAllocDescriptor Lib "oleaut32" ( _
            ByVal cDims As Long, _
            ByRef ppsaOut() As Any _
        ) As Long
#Else
    Private Declare Function SafeArrayAllocDescriptor Lib "oleaut32" ( _
            ByVal cDims As Long, _
            ByRef ppsaOut() As Any _
        ) As Long
#End If

'Variant型二次元配列
Property Get EmptyVariantArray2() As Variant()
    Dim res As Long
    res = SafeArrayAllocDescriptor(2, EmptyVariantArray2)
    If res <> 0 Then Err.Raise 9999
End Property

'シート内のセルデータ読み込み必ず二次元配列を返す
' StartRow : 読み込み開始行(1~)
' StartCol : 読み込み開始列(1~)
' ExceptRow   : 読み込み除外行(0~) 末端から任意の行数消す
' ExceptCol   : 読み込み除外列(0~) 末端から任意の列数消す
Public Function ReadCell(WS As Worksheet, _
                            Optional StartRow As Long = 1, _
                            Optional StartCol As Long = 1, _
                            Optional ExceptRow As Long = 0, _
                            Optional ExceptCol As Long = 0) As Variant

    '最終行列 = 領域の末尾からデータの存在するセルを検索
    Dim FinalRow As Long, FinalCol As Long
    On Error Resume Next
    With WS.UsedRange
        FinalRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
        FinalCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    End With
    On Error GoTo 0
    
    '出力行列 = 最終 - 開始 + 1 - 除外
    Dim OutRow As Long, OutCol As Long
    OutRow = FinalRow - StartRow + 1 - ExceptRow
    OutCol = FinalCol - StartCol + 1 - ExceptCol
    
    '該当セル無し:-1,-1を返す
    If OutRow <= 0 Or OutCol <= 0 Then
        
        ReadCell = EmptyVariantArray2()
        
    '該当セルが単一セル:2次元配列として返す
    ElseIf OutRow = 1 And OutCol = 1 Then
    
        ReDim RetVal(1 To 1, 1 To 1)
        RetVal(1, 1) = WS.Cells(StartRow, StartCol).Value
        ReadCell = RetVal
    
    '通常の範囲データ
    Else
    
        ReadCell = WS.Cells(StartRow, StartCol).Resize(OutRow, OutCol).Value
        
    End If
    
End Function


補足

VBAでは空の配列変数の要素数Ubound(配列変数, 次元番号)で求めると、結果は-1になります。

空の一次元配列はArray()Split(vbNullString)によって生成できます。

しかし空の2次元配列はAPIを使用しないと生成できません。

一次元配列に対して二次元目の要素数を取得しようとすると実行時エラーになってしまいます。

戻り値を2次元配列に統一するため、APIを使用して(-1,-1)を生成しました。

尚、呼び出し元のロジックを間違えなければ、空の一次元配列で十分に対応できるはずなので、APIを避けたい場合はArray()と記載してしまっても問題ありません。

APIに関しては Qiita - VBA標準機能で作成できる要素数0の配列 を御覧ください。


おわりに

今回はExcelVBAの基本中の基本!

Excelシートのデータ範囲を2次元配列に格納する汎用関数」

を書いてみました。


その割には一部レベルが高かったような・・。

でも偶には初心に帰るのも大事って言いますし?

<当たり前のように使ってきたものが、正しいことを証明する>というのは、自信に繋がるので悪くないと思います!(間違ってたら自信を失いますが・・)

それでは今日はこの辺で。


続編

Findのオートフィルタ実行時に正確に最終行・最終列を取得できない問題に対応するため、続編を執筆しました。2019/6/16

www.excel-chunchun.com


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

週1回の更新を目指して、頑張ってますので応援よろしくおねがいします!

それでは、また来週♪ ちゅんちゅん(・8・)