Excelシートの使用済みデータ範囲をあらゆる状況下で確実に2次元配列に格納できる汎用関数を作ってみたので紹介します。
きっかけ
(本業ではないとは言え)ExcelVBAを書き続けてこの道10年の私が、何をいまさら・・・という感じですが、実は
Excelシートのデータ範囲を2次元配列に格納する汎用関数
を作ったことがなかったのです。
なんででしょうね。
なんででしょうか。
まあ必要なかったんです。
普段は下記のマイルールがありますからね。
「Excel VBA 2次元配列」などと検索して出てくる各種サイトで紹介されている方法は、理解のしやすさを優先しており特定の条件下では正常に動作しないバグがあります。
そのため、単純に読み込んで副作用について一切説明をしないか、複数パターン紹介して「状況に応じて使い分けてください」といったサンプルばかりです。
今回作成した関数は「非表示セルが存在する環境下で、A1からデータの存在するセルまでのデータを2次元配列に格納する関数」です。
もちろん、状況に応じて調整が必要になる場面はあるでしょうが、とにかく最小限のメモリで全データを確実に2次元配列に取り込むことを主目的として作成しました。
そもそものきっかけは、修正中のVBAプログラムにパブリック変数使いまくりの危険なプロシージャがあったため、仕様を大きく変えずに置き換えられる汎用関数がど~~~しても欲しくなったので作ってみることにしました。
↓修正前の超危険な関数
御覧ください。
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年6月13日
こちらが基幹システムで多様されている爆弾関数の再現図でございます。
とりあえずこれを駆逐する。 pic.twitter.com/w40JqIIi7t
マイルール
さて、私が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 ActiveSheet
→ With ActiveSheet.UsedRange
とFor
の部分です。
前者では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次元配列に読み込む
上記の方法で最終行FinalRow
と最終列FinalCol
が求まりました。
これらの変数を使ってセルのデータを2次元配列に読み込むことになります。
よく初心者向けの解説で以下のような記法が紹介されますが、こういうイケてない書き方は止めましょう。
ブック・シートが省略されているため、99%の人はバグを身を持って経験します。
'最悪な例 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
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)