Rangeを2次配列として扱うことの難点と代案

概要

Rangeの編集はエクセル側で逐次再計算や描画の更新などがあるのかそのまま扱うと重くなってしまうため、以下のように配列にして編集するほうが早く、こちらのほうが推奨されている(らしい)。

Dim arr As Variant
arr = Range.Value
Debug.Print IsArray(arr) ' true

しかし、配列の状態で編集する際にいくつか困ったことがあったのでそれについてまとめてみる

初めに

自分のプログラミングのレベルはVBAを独学で学んでいる程度なので、初歩的なやり方や定石を見落としている可能性も高いかと思われます。
また、本記事の内容について調べていた時点ではQiitaに投稿するつもりがなく、後から思い返してまとめているため、うろ覚えなところがあるかもしれません。
突っ込みどころがありましたらやんわりと指摘していただけましたら幸いです。

経緯

最近になって、業務中学んだことについてQiitaに投稿してよいということになった。
ちょうど最近対応したばかりのVBAの改修でいろいろと反省点があったので、さっそくそれについて書いてみることにした。
割と変更箇所がおおく、せっかくだからわかりやすくコードを整理しようと意気込んでいたのに、結果的によみづらいコードになってしまったので、次はこうならないようにという自省も込めて…。

前提

• Range.Valueで入出力ができる＝要素がすべて1始まりの2次配列を通常の配列（要素が0から始まる配列）と区別するため以降Range互換配列と表記する。
• 以降のコード中で配列はVariant型で宣言している。逆に、配列でないVariant型はこの投稿では使ってない（はず）。
• 前提知識としてはVBAの配列とRangeオブジェクトについて、基本的なところがわかっている人向け。（上2点の意図がわかるなら問題ないと思います）

問題点1: Rangeでは行に当たる配列の1次元目の要素が増やせない。

例

たとえば表Aの該当する行のみ抽出した表Bを作るとする。
この場合、表Aを一行ずつ確認し、条件を満たせば表Bに追加するという処理をするとして、これを配列に置き換えてみる。

一般的な配列の追加

配列を追加するには、配列の要素数を中身を維持したまま変えるReDim Preserveと、配列の最大の要素を取得するUBound関数を組み合わせる。

具体的には例えば一次配列であれば以下のようにする

ReDim Preserve arr(UBound(arr, 1) + 1) 'arrは一次配列

UBound の二つ目の引数は配列の次元で、このように1とすれば対象の1次元目、2とすれば対象の2次元目の最大の要素になる。そもそも1次配列であれば省略可能なのだが今回は2次配列の扱いが本題なので基本省略しないことにする。

多次元配列の追加

Range互換配列のように二次配列を表として扱うのであれば、一行ごとに処理して増やしたりしたいので、以下のようにしたいところだが、これはエラーになってしまう。

ReDim Preserve arr(UBound(arr,1) + 1, UBound(arr,2))'1次元目を増やそうとするとエラー

というのも、ReDim Preserve は最後の次元の要素数しか変更ができないという制限がある。

ReDim Preserve arr(UBound(arr,1), UBound(arr,2) + 1)'2次元目を増やすのは問題ない
'通常の2次元配列の場合はこれでいいが、Range互換配列の場合これだけだと0列目、0行目ができてしまうという問題がある（詳細は後述）

ので、Rangeを基準に考えると列は増やせるが行は増やせないという、非常にやりづらい仕様となっている。

今回取った対応

WorksheetFunction.Transpose()を使うことで、Range互換配列であれば行と列を入れ替えることができる。そのため、今回改修した案件では処理の最初と最後にTransposeを行い、処理中は列、行となる配列に一時的になおすことで対処した。
とはいえ、行列を一時的にでも列行にかえる処理というのは感覚的に違和感が大きいし、どっちがどっちがわからなくなりそうで読みづらいと思う。
この処理は一つの関数に切り分けているので、行列の並びが異なる2次配列の混在は避けるようにしたが、それでもわかりづらさは否めない。

問題2: 配列のデフォルトの要素数が0なのに対し、Range互換配列の場合1始まり

例えばRange("A1:B2").value
は(1, 1), (1, 2), (2, 1), (2, 2) の4要素の配列になり、(0, 0), (0, 1)などの添え字が0の要素は存在しない。
しかし、このようにRangeとやり取りする場合を除いて（具体的な例はRange.ValueやWorksheetFunctionの引数）、VBAではデフォルトでは配列の最初の要素数は基本的に0である

1始まりの配列の宣言

'普通に宣言すると0始まりになるので、 [0, 1, 2] の3要素になる
Dim arr(2) As Variant 

'1始まりにするには以下のようにすれば、[1, 2]の2要素になる
Dim arr(1 To 2) As Variant 

'この場合、redim preserve で要素数を増やす場合も、都度最小値をちゃんと指定する必要がある
ReDim Preserve arr3(1 To UBound(arr3, 1))

Split, Arrayなど配列を生成する関数

たとえばCSVの読込で一行ずつ処理するとして、

arr = Split("a,b,c", ",")
'arr(0) = a

この場合、配列は0始まりになり、宣言のように1始まりにすることができないので、Range由来のArrayとやり取りするには要素を一つずらす必要がある。

要素数をずらす

要素数を変えずにずらすことは可能で、具体的には以下のようにすればずらせる。

ReDim Preserve arr(LBound(arr,1)+1 To UBound(arr,1))
'LBoundはUBoundの逆で、最小の要素を返す。LとUはそれぞれLowerとUpperの意味だとか

以下のように配列を中身を残したままずらすことは可能ではあるが、0始まりと1始まりの配列が混在するのは処理としてややこしい

問題3: 1行ずつ切り分けて1次配列にすることができない

具体的に言うと以下のように1次配列を1次配列でネストしたもので、一行ごとに切り出せるものだと勝手に思っていたのだが、実際はこれと2次配列は別物であり、2次元配列は1行ずつを1次配列として直接扱うことはできないらしい。

Dim arr1 As Variant
arr1 = Array(Array(0, 1), Array(2, 3)) '1次配列を1次配列でネストしたもの
Debug.Print IsArray(arr1(0)) 'True (一つ目の配列をとりだす)
Debug.Print arr1(0)(0) '0 (1つ目の配列の1つ目の値を取り出す)

Dim arr2(0, 1) As Variant '2次元配列
Debug.Print IsArray(arr2(0)) '次元が一致していないというエラーが出る

これの何が問題かというと、以下のように1行ずつの処理を別の関数に分けたいのだがそのままではできなかった。

'1行を切り出した1次配列を受け取って、処理後の配列を返す
Function processByRow( rowArr As Variant) as Variant' 
'行ごとの処理
End Function

Sub processEachRows()
  Dim arr2d As Variant'二次配列
  '代入略
　for i = LBound(arr2d, 1) To UBound(arr2d, 1)
    arr2d(i) = processByRow(arr2d(i))' 2次配列の1行を1次配列として別関数に戻し、処理後に帰ってきた１次関数を戻す
　next i
End Sub

今回の対処法

以下のように2次元配列から1次元配列を出し入れする関数を作った。

'2次元配列の1行を1次配列として取り出す
'実はIndex関数がそのまま同じ使い方できるのでいらなかった(後述)
Function get1dArrayFrom2dArray (arr2d as Variant, index as Long) as Variant
'略

'2次元配列に1次元配列を行のように追加する。
Function push1dArrayTo2dArray(arr2d as Variant, arr1d as Variant) as Variant
'略

わかりやすくするために1行ごとの処理を切り出そうとしたのに、そのためにもともといらなかった処理が増えるという本末転倒な状況。

Index関数について(2020/07/07追記)

1つ目の2次関数から1次配列を取り出す関数については、WorksheetFunction.Index()がそのまま使えるので、関数を作る必要がなかった。

その他の問題

配列の仕様がどうこうという話ではなく、単純にわかりづらかった点として、以下がある

Range互換配列の起点がわかりづらい

これは配列を使わずRangeをそのまま使う際もそうなのだが、対象のRangeがどこからなのかというのは地味にややこしかった。
よく迷いそうなのは見出しを含んでいるのか否かだろう、含んでいれば各行の処理は2行目からにする必要がある。
きっちりA1から始まっている表であればこの2通りくらいだが、そうでなければもっと複雑化してくる。

今回の対応

今回自分が対応した分には基本的に処理対象の表はテーブル(ListObject)化し、配列化する際はListObject.DatabodyRangeをもちいることで、ぱっと見て範囲がわかりやすくできたはず。
（本来であれば自分が書いたところだけ書き方が違うのはほかの人が読むときにかえってわかりずらくなるので避けたほうがいいのでここは賛否あるとは思う。個人的な言い訳としては、後述のように変数が多すぎた件もあって、大幅につくりなおさないとそもそもそのままじゃ解読が難しかったのでやむなしと思っている。）

列番号を管理するための（グローバルな）変数が増える

処理のたびに列を添え字で指定する関係上、列番号を変数か定数で管理することになると思うのだが、列の数だけ増える。フルネームをスペースで氏名に分けるなど、元の表と出力先の表が違う場合は票の数だけさらに増える。また、関数を切り分けるためには複数の関数で参照するためにグローバル化する必要も出てしまう

今回の対応

もともと列番号は変数で、列名を検索して都度代入するという形だった。
今回の改修で取り込み元のcsvの形式が2種類になり、列名での検索が複雑化しそうだったので、列番号は列挙型でハードコーディングした。
宣言が一か所になったのでちょっとした列の修正はやりやすくなったとは思うが、ハードコーティングはこれも賛否あるとは思う。

結論: 表を2次配列で扱うのは難しい

2次配列をそのまま表として扱うのは無理があるとおもう。

代案: ユーザー定義型

あくまでも自己流なので他にいい案があったら教えてほしい。

個人的に今ベターだと思っているやり方は、一行ごとにユーザー定義型に入れてそれを1次配列に入れる、もしくは自作クラスをコレクションに入れるというやり方。（ユーザー定義型はコレクション化できないらしい）
コードは増えるけどあとから見返す際に度の列を処理しているかわかりやすくていいと思うがどうだろうか。

大雑把な例

たとえば以下のような表があるとして、

行¥列	A	B
1	ID	名前
2	1	太郎
3	2	花子
4	3	john

2次配列だと
このようになっていたところを

Sub test()
'宣言文は省略
  arr = rng.Value
  for i = LBound(arr, 1) To UBound(arr, 1)
    '行ごとの処理
    for j = LBound(arr,2) To UBound(arr, 2)
      '列ごとごとの処理
    next j
  next i
  rng.Value = arr ' 配列をRangeに代入
End Sub

このように

Type Person  '各行のデータを入れるためのユーザー定義型
  UserId as Integer
  FirstName as String
End Type

'Rangeをarrayを経由してユーザー定義型(この例ではPeople型)を含む配列にする関数
Function range2People(rng as Range) as Variant
'略

'ユーザー定義型の配列をarrayを経由してRangeに入力する関数
Sub people2range (people as Variant, rng as Range) 
'略

'行ごとの処理をおこなう関数、引数として上記のユーザー定義型を受け取り、加工後のユーザー定義型をかえす。(ByRefで上書きするのもありかもしれない）
Function processByPerson(person as Person) as Person

Sub main()
  Dim people as Variant' 'Personの配列。
  Dim person as Person
  'その他宣言省略

  people = range2People(rng)'対象の表からPerson型のコレクションを取得する

  for i = LBound(people) To UBound(people) 
    people(i) = processByPerson (people(i))'行ごとの処理を別関数にperson型を渡し、person型を返してもらう。
　　people(i) = 'anotherProcessByPerson(people(i)) 各行ごとに複数の処理があっても、このように処理別に関数に分けやすいはず。

  next i

  rng.value = people ' 配列をRangeに代入

End Sub

こんな感じで切り分けたい

ここまでやれば列番号はRangeとやり取りする2つの関数内で完結でき(range2People(),people2Range()),1行ごとの処理も元のpersonを投げて加工後のpersonを返すわかりやすい形になり、後から見た時にどこで何をやっているのかがだいぶわかりやすくなるはず…。

ただそうすると次の問題は今回のように中途半端に1部だけ改修したところで、他の人から見たらかえって読みづらくなりそうということ。
きっちりまとまった範囲をやりきろうと思うと、今回のように改修のついでではなく、その前に別でリファクタリング（？）の時間をとらないと難しいか...。

余談

最終的にはとりあえず期限以内に目的の改修は済んだうえ、心残りはあるもののいろいろと勉強になったのでよかった。
今までは自分用のツールを作るくらいしかやったことがなかったので、チーム開発というほどではないものの、人との共有を前提にコードを読み書きするのは初めてで、難航しつつも新鮮だった。

改修範囲外のツールの全体像もある程度把握できたので、次からはもっと計画的に、読みやすくしたいところ。

参考

Office TANAKA - VBAのステートメント[Option Base]
VBAで配列のインデックス・添字の最小値を1にずらす:エクセルマクロ・Excel VBAの使い方/配列
Office TANAKA - Excel VBA Tips[配列をセルに代入する]