今回は先日投稿したクイズの問6～問8のVBAの配列に関する問題について解説していきたいと思います。
前の記事
出題ページはこちらになります。
問1～問5の解説 Excel VBAのRange操作の注意事項
解説について
まず初めに申し上げて起きますが、ここで解説する内容は実際に実行した結果から中の動きを推測して説明しているものです。
全ての説明にMicrosoft公式の根拠があるわけではないため、正確性に欠けるかもしれない点にご注意ください。
また誤りや不明な点がありましたら、遠慮なく教えてください。
今回はRangeに関する問題の続きとして、問6～問8について説明していきます。
基礎知識
まずは本記事で使用しているプロパティのリンク集を貼っておきます。
- Range オブジェクト
- Range.Address プロパティ
- Range.Cells プロパティ
- Range.Count プロパティ
- Range.Cut メソッド
- Range.Insert メソッド
- Range.Itemプロパティ
- Range.Merge メソッド
- Range.Offset プロパティ
- Range.Resize プロパティ
- Range.Row プロパティ
- Range.Select メソッド
- Worksheet.Cells プロパティ
- WorksheetFunction.Transpose メソッド
- UBound 関数
- LBound 関数
- Array 関数
解説
問６ 二次元配列の活用に関する問題
Sub q_06() Dim data(3, 1) data(1, 1) = "A" data(2, 1) = "B" data(3, 1) = "C" Cells(1, 1).Resize(UBound(data, 1), UBound(data, 2)).Value = data Debug.Print Cells(1, 1).Value End Sub
VBAで高速化を目指すなら避けては通れない二次元配列のデータをセルに一括出力する場面における問題です。
二次元配列をコード上で作って、セルに出力なんてことは少ないんですけどね。
| 設問 | 正解 |
|---|---|
| 1. |
答えは実は空欄なのでした。
まずはこのプログラムの問題を実行すると何が表示されるのかお見せしましょう。
| ＼ | A | B | C |
|---|---|---|---|
| 1 | |||
| 2 | |||
| 3 | |||
| 4 |
はい、実は何も表示されません。
なんとABCが出力されてすらいません。
このプログラムには間違いと思われる原因が2つあり、どちらかを修正する必要があります。
その２つの間違いについて解説していきます。
1つ目は dataの宣言文が間違っている。 という点です。
そもそもDim data(3, 1)が不適切なんです。
VBAでは配列変数の宣言では要素番号の開始値と終了値を明示しない場合はOption Baseで指定した値が開始値になります。
そして今回のルールではOption設定は変更しない標準的な環境と決めているので、0が省略されているものと見なされ、コンパイラにはDim data(0 To 3, 0 To 1)と解釈されます。
Aと答えた人は、縦3、横1の配列変数だと思っていたかと思いますが、この時点で間違っているわけです。
（きっと間違えた人は、たまたま見落としただけだと思いますが。）
2つ目は UBound(data, 1)とUBound(data, 2) です。
もし1つ目の説明の通りズレていたとして、data全体をセルに出力したらこういうことになるはずです。
| ＼ | A | B | C |
|---|---|---|---|
| 1 | |||
| 2 | A | ||
| 3 | B | ||
| 4 | C |
が、実際にはなにも出力されていませんでした。
Cells(1, 1).Resize(UBound(data, 1), UBound(data, 2)).Value = dataという式を見慣れない方のために簡単に説明すると、「dataの内容をA1を起点として縦に3、横に1の範囲のセルにdataを出力する。」 という式になります。
この縦に3、横に1が曲者です。
UBoundは配列の要素番号の最大値を取得する関数であって、要素数を返す関数ではありません。
従って、
- dataは4行あるのにも関わらず、出力範囲が3行しかなかった。
- dataは2列あるのにも関わらず、出力範囲が1列しかなかった。
という理由から、2列目にあるA、B、Cは出力されなかったのです。
このようにセル範囲に二次元配列のデータを一気に流し込む時は、常に先頭の要素のデータからセル範囲に収まる部分だけが出力される という点に注意してください。
はみ出した要素はエラーが出ることもなく無視されます。
つまり、仮にDim data(3,1)がミスではなかったとして、A1:A3にABCを出力したいなら次のようにそこら中直さなければなりません。
Sub q_06_true() Dim data(3, 1) data(0, 0) = "A" data(1, 0) = "B" data(2, 0) = "C" Cells(1, 1).Resize(UBound(data, 1) - LBound(data, 1) + 1, UBound(data, 2) - LBound(data, 2) + 1).Value = data Debug.Print Cells(1, 1).Value End Sub
要素数を求める式がUBound(data) - LBound(data) + 1の部分ですね。無駄に長くて面倒ですが、そればかりは仕方ありません。
しかし実際に使うとなると、dataの要素番号が0～になって、Cellsと1ズレるので別のバグを誘発する元となります。
また更新の必要のない行と列が末尾に1つ増えるので美しくありません。
以上の理由から、私が配列変数を宣言する時は必ずDim data(1 To 3, 1 To 1)とするように心がけています。
これで目的どおりの動きになります。
| ＼ | A |
|---|---|
| 1 | A |
| 2 | B |
| 3 | C |
尚、絶対の安全性を保つならUBound(data) - LBound(data) + 1に直したほうが良いのですが、そもそも二次元配列の要素が0から始めていることが間違いの元だと私は思っているので、セル出力においてはLBoundは無視することにしています。
問７ 一次元配列の活用に関する問題
Sub q_07() Dim v As Variant v = Array("1", "2", "3", "4") Range("A1").Resize(UBound(v, 1), 1).Value = WorksheetFunction.Transpose(v) Debug.Print Range("A1").Value, Range("A2").Value, Range("A3").Value, Range("A4").Value End Sub
二次元のあとに一次元・・・というのも辺な話ですが、エクセルは二次元が基本ですから一次元配列の問題を敢えて後にしました。
1 2 3 4と答えた方は不正解！ ４番目は出力されていないので 空欄 です。
| 設問 | 正解 |
|---|---|
| 1. | 1 2 3 |
まずv = Array("1", "2", "3", "4")ですが、格納された結果はこのようになります。
| 変数 | 値 |
|---|---|
| v(0) | 1 |
| v(1) | 2 |
| v(2) | 3 |
| v(3) | 4 |
もし要素番号を1,2,3,4と振っていた場合はその時点で間違いなのでした。
次に右辺のWorksheetFunction.Transpose(v)ですが、二次元配列の行と列を反転させるワークシート関数です。
ところが今回指定しているvは 一次元配列 です。
そこで、イメージしづらいと思うので、仮にv = WorksheetFunction.Transpose(v)とした場合、変数の内容がどのように変化するのかを示す表を掲載します。
| 変数 | 値 |
|---|---|
| v(1,1) | 1 |
| v(2,1) | 2 |
| v(3,1) | 3 |
| v(4,1) | 4 |
なんと行方向にデータの続く二次元配列に変化しました！
これでセルに一括代入できますね！
※WorksheetFunctionには配列を返すことの出来る関数（RESIZEやINDEX等）がありますが、私の知る限り全てのワークシート関数の結果は要素番号が1からになるので、覚えておくと良いかも知れません。（例外があったら教えて下さい。）
次にRange("A1").Resize(UBound(v, 1), 1).Valueですが、先の通りvは0～3の配列でした。つまり「4」だけが出力されなかった原因はココにあります。
UBound(v, 1)は3が返ることになるので、出力セルはA1:A3ということになり、問6と同じで「セル範囲＜配列の大きさ」の場合は エラーが出ないまま無視される ため、「４」は出力されていなかった。というのが真相です。
よって、今回のプログラムを修正するなら、
Range("A1").Resize(UBound(v, 1) - LBound(v, 1) + 1, 1).Value とするか、
v = WorksheetFunction.Transpose(v)を追記します。
あまり巨大な配列だと後者はコピーロスが大きいので避けるべきですが、一次元配列なら後者でも気になるほどのロスは出ないのでまず大丈夫です。
前者の方が間違いないですけどね。
でも私はデバッグしやすい後者のほうが好きだったりします。
※先の通りワークシート関数は1から開始するという前提があるので。
問８ Range.Valueへの出力に関する問題
Sub q_08() Range("A1") = "-999-666" Range("A2") = "=999-666" Debug.Print "" & Range("A1") Debug.Print "" & Range("A2") End Sub
なんとなんと、ここへきて基本中の基本。Range.Valueへの単なる代入 に関する問題です。
しかし何名が正解を答えられたでしょうか・・・。
| 設問 | 正解 |
|---|---|
| 1. | -999-666 |
| 2. | 333 |
(1)は文字通りの出力なので問題なさそうですね。
しかし(2)は厄介です。
Valueに設定したはずなのに、まるでFormulaに設定したかのような挙動 です。
これも、エラーが出ないので中々気が付きません。
何も考えず「ぼーっと」生きてるとExcelちゃんに叱られちゃいますよ？
対処はとても簡単で、「セルの書式設定」を「標準」から「文字列」に変えておくだけです。
つまりVBAで書くならRange.NumberFormatLocal = "@"を代入前に記述しておけば万事OKです。
さらにこの現象は 二次元配列で一括出力する時ににも発生 します。
イコールの他にも"+5"が5になってしまったりと恐ろしい現象が発生する恐れがあります。
だから面倒ですがこんな感じで書くようにしましょう。
Sub q_08_array_better() Dim v As Variant v = Array("aaa", "=999-666", "+5", "bbbb") v = WorksheetFunction.Transpose(v) With Range("A1").Resize(UBound(v, 1), 1) .NumberFormat = "@" .Value = v End With End Sub
また、今回は問題にしませんでしたが、Range("A3") = "=====ここまで====="を実行すると、エラー1004でマクロが止まるという問題もあります。
むしろエラーが出る分だけ良心的なのかも知れませんが。
まとめ
今回は問6～問8について説明しました。 ←なかなか先に進みませんね・・。
二次元配列の活用はVBAの高速化で非常に重要な役割を持っているので、使いこなせるようになると心強いです。
これでようやくRangeに関する問題が終わりました。
次回はVBに共通する関する問題が続きそうです。
水曜日くらいには更新できたら良いなぁ
以上
次の解説
問9～問13の解説 VBAの文法の注意事項
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)
