Excel VBA 配列について(4)要素数が不定の配列

Excel マクロ、VBA

スポンサーリンク

要素数不定の配列に要素数をセットする

Excel VBA 配列について(3)2次元配列はExcelシートの値を扱うのに強力

2020年3月20日

Excel VBA 配列について(2)1次元配列の別類型

2020年3月20日

Excel VBA 配列について(1)1次元配列の基本

2020年3月20日

前回までの記事で扱った配列(1次元・2次元)の生成の方法をまとめると、下記のような感じだ。
これらは、配列の要素数を決めるものが、どこかしらにあった。

今回はこれら以外のケース、すなわち配列の要素数が最初では分からず不定(そういう配列を動的配列などという)で、マクロを動かしている内に成り行き次第で要素数を決めていく方法を取り上げる。

まず、シートが幾つあるのか分からないExcelブックの中のシート名を、(1次元)配列に格納してみる。

配列の要素数が最初では分からない時は、このサンプルのように

Dim arr As Variant

などと、要素数を書かない単なるVariant変数として宣言し、その後にReDim というやつを使って

ReDim arr(1 To Num)

という感じで要素数をセットする。

これにより、要素数が不定の配列に、変数をもって要素数をセットすることができる。

変数でなく定数のみで

ReDim arr(1 To 5)

といった宣言でもOKだ。そんなやり方はあまり使われないだろうが。

Redimは中身がクリアされる

では、このRedimを使ってシート名を1次元配列に格納した後、もう1個のExcelブックのシート名も配列に追加格納しなければならないとしよう。
実際、複数ブックのシート名を全部格納しないといけないケースは、実務でも考えられる。

そんなマクロをまともに書くと結構長くなるので、もう変数とかは使わずに簡単なサンプルを書く。
要は、Redimで要素数を決めた後、もう一回同じくRedimする例であれば良いので、次のような感じで書く。

ReDimで要素数を2と宣言した後でもう一回ReDimして、要素数を4に増やしたわけだが、この場合は最初の2つ目までの要素は消えてしまう。

中身を覗くとこのように、1番目・2番目の要素はEmptyとして消えている。

シート名を配列に追加格納するというような場合なら、こんな風に前のデータが消えては当然駄目だ。

前のデータが消えないようにして、なおかつ配列の要素数も増やしたい場合は、単にReDimではなく、ReDim Preserveとする。

そのReDim Preserveを使ったソースコードが↓で、

結果として↓のように、配列の中身は消えず保持される。

2次元配列のRedim Preserveは不便

この、配列の中身を保持したまま要素数を増やすReDim Preserveだが、私は最初は便利と思って積極的に使おうとしていた。

しかしReDim Preserveは、可能な限り使わないようにソースコードを組み立てていくべきだ。

理由は幾つかあるが、まずReDim Preserveは要素数が不定で推測もできない場合にやることが多いゆえ、1回か2回やって終わりということは少なく、何度も何度もやるものだ。
だからその繰り返し処理で、時間が掛かってしまうことに繋がる。

またReDim Preserveは、1次元配列ならともかく2次元(2次元以上の)配列においては、最後の要素の数しか変えられないという性質があって非常に不便だ。

↓の例では、2次元配列arrの2つ目の要素を2→5にReDim Preserveで増やすことはできるが、1つ目の要素を100→101に増やすことはできず「インデックスが有効範囲にありません。」というエラーになる。

arr(1 To 100, 1 To 2)という2次元配列arrなら、縦100行・横2列のExcelシートそのままのイメージだ。

Excelシートのデータは横ではなく縦に増やしていくものであり、シートのデータをダイレクトに格納するものである2次元配列においては当然、1つ目の要素数100の方を増やしていきたい。

しかしReDim Preserveではそれができず、2つ目の要素(横2列)の方しか変更できなくて、それではほとんど意味がない。

もしどうしても要素数100の方を101に増やしたいなら、縦横を入れ替えるTranspose関数を使って、↓のようにする方法が考えられる。

私が配列を覚えたての時は、このTranspose関数を使う方法を非常に多用していたが、その時はReDim Preserveを何千回と繰り返す処理になっており、非効率なマクロになっていたものだと思う。

今の私なら、ReDim Preserveを使わなくても済むように、最善を尽くして立ち回る。
具体的な方法はケースバイケースだが、予め要素数をかなり大きく確保しておいて、後で増やさなくて良くするなどか。

なおTranspose関数は、元々Excelの通常関数として用意されており、縦横を入れ替える機能がある関数だ。
ここでは詳説はしないが、簡単に図だけで↓に示しておく。

Transpose関数

なおReDim Preserveでなく、単にReDimで配列を定義し直すなら、↓のように要素数は自由に再定義が可能だ。

1番目の要素だろうと2番目の要素だろうと、自由に要素数を変更して良い。

スポンサーリンク