Range Object - Excel 97 - vba - 表計算゛ (Hyo-Kei-Zan)

はじめに

いよいよ Range Object の登場です。Excel は、セルの集合体ですから、セルに纏わる操作をする機会が最も多いはずです。セルは、Range Object として扱いますので、Range Object を制するものは Excel VBA を制する、と言っても過言ではありません。

効率についてのヒント

Range Object の扱いは、Excel VBA の効率(スピード)に大きく影響します。「 Object の数が多い。」「データの入出力を頻繁に行う。」の二点によるものです。Worksheet や Window などの Object は、せいぜいが 10 とか 100 のオーダーであるのに対して、 Range (セル) の総数は、あっという間に 1,000 や 10,000 のオーダーになります。また、それら一つ一つに対して、データの入出力を頻繁に行わなければなりません。 Range Object の入出力、特にセルへの入力は、実はかなり重たい(時間のかかる)操作です。そのため、上手な組み方を考えるのと、そうでないのとでは、マクロの実行時間に雲泥の差が出ます。

Excel の場合、VBA コード内部での計算速度は、Range Object に対する処理に関わる時間に比べて圧倒的に早いため、多少、複雑なコードを書いてでも、 Range Object に対する入出力を減らすことができるなら、全体としてのパフォーマンスが大きく向上する場合があります。一方で、Range Object を使い倒せば、非常に少ない行数のコードで開発が完了することもあります。開発コードを少なくすることを実行速度より優先したい場合もありますから、そのあたりの比重を、ケースバイケースで使い分けることが大切です。

セルはテキストボックスではない

Excel のセルは、一見して VB や VBA のテキストボックスのようにも見えます。実際、セルへの入出力は、テキストボックスへの入出力と同じように手軽です。しかし、セルへの入出力は、テキストボックスと比べ、非常に複雑な内部手続きを伴っています。そのため、同じようなコードを書いても、Range (セル) への出力は、テキストボックスへの出力よりも、多くの CPU 時間を必要とします。

セルへの入力時には、次のような手続きが発生しています。

入力対象が、どのような領域かの判断。複数セルへの同時入力をサポートするため。
入力される値が、どのようなものかの判断。不適切な値なら入力できない。適切な値の場合、さらに型に応じて書式制御等や値の変換を行う。
影響する範囲の再計算を行う。

これらはいずれも、人がセルへ直接入力するという前提で組みあがってきた、Excel Worksheet の便利な機能です。 VBA によって、手入力のスピードと量の限界を超えた入力を行ったときに、この機能が足かせになります。

再計算の暴走

セルへの入力で再計算が必要だと判断されれば、関連するセルの再計算が自動的に行われます。VBA でセルの数値を変更した場合でも、再計算はしっかり行われていきます。この機能は、ワークシートの数値を矛盾無く扱うための重要なポイントになります。しかし、再計算の及ぶ範囲があまりに広範囲だったり複雑だったりして、計算に時間のかかる場合には、この機能もまた、足かせになります。

シート上で複雑に絡み合うセル計算を行っている場合に、複数のセルの値を次々に変更していけば、一つのセルの再計算が完了する前に、前提条件が変化して新たな再計算が発生します。これが繰り返されていけば、再計算要求が大量に発生し、Excel の手にあまることがあります。最悪の場合には「不正な処理」のメッセージとともに Excel が落ちてしまいます。

効率良く VBA を使うには、再計算を上手に制御する必要があります。マクロの実行中は一時的に再計算を抑止するとか、VBA に頼るならシート上での計算を最低限に抑える、といったことでこれらは解決します。

Active の誘惑

Active 系のキーワードや Selection といったものは、ユーザーの想定している Range Object を暗黙に受け取るには、非常に便利です。いちいちダイアログを出して、「マクロの操作対象はどこ?」と聞くよりも、実行前に選んだセルを自動的に対象にした方がスマートです。

しかし、ユーザーの意見を聞くのは、最初の一度切りでいいはずです。その後、VBA の中で、たとえばループ処理の内部などで、何度も繰り返し、このような暗黙の指定場所を受け取る必要は無いはずです。

Active や Select で対象を判断する場合、その都度、「暗黙の指定場所を探す」という余計な手順が入っていることになる。
暗黙の指定場所に頼るコードは、マクロの移植性を下げ、また、全自動マクロ化への阻害になる。

上に示した二つの理由から、Active や Select の多用は、不必要にマクロの実行効率を下げ、また、コードの再利用を困難にします。特に、作ったものをユーザーに提供するような場合、最初のうちは、すべてをセル上だけで行っていたのを「場所を選んでマクロを実行すればいい」にしただけでも感激されたものが、人は慣れれば手抜きをしたがりますから、「ボタン一つだけで動くようにできないの?」、「タイマーで、朝来たら出来上がってる、っていうのが嬉しいんだけど。」というように要求は次々とエスカレートしていきます。その都度、作りなおさずに、古い資源を上手に利用して要求にこたえるためには、明示的に Range Object を使用するように意識する必要があります。

. と With

Range Object の指定は、どうしても長くなります。コードを書く場合の効率上でも、また、アドレス等を変更する場合の手間を最小限にする意味でも、With の活用は欠かせません。しかし、With も気をつけて使わなければならないキーワードの一つです。

Sheet1.Range("D6").Font.Name = "Arial"
Sheet1.Range("D6").Font.Size = 18

With Sheet1.Range("D6").Font
  .Name = "Arial"
  .Size = 18
End With

With Sheet1.Range("D6:F8").Range("A1").Font
  .Name = "Arial"
  .Size = 18
End With

Set x = Sheet1.Range("D6:F8").Range("A1").Font
x.Name = "Arial"
x.Size = 18

With は、コンパイル時に解釈されるため、最初の2つは、まったく同一のものとなります。つまり、With は、コード作成者にとっての便宜に過ぎなく、実行コードには影響しないのです。3つ目の場合、Range Object のネストが入っています。このコードの実行速度は最初の2つに比べて、やや劣るでしょう。その理由は Object を参照する回数が増えるからです。一般に . が増えるごとに、何らかの Object を参照するわけですから、スピードは遅くなります。ただし、最初に書いた理由から、その回数は With を展開した後のもので数えることなります。

たくさんの . を使った参照を見やすく、かつ効率良く使うテクニックの一つに、「 Set を使う」というものがあります。最後の例では、Set により x の参照先が確定した後は、効率良くアドレスを参照することができます。ただし、Set 文という余分な操作が入るため、上の例のように利用回数が少なければ、かえって効率が悪くなることもあります。

計算精度についてのヒント

セル上で計算式を書いて作成したロジックを、そのまま VBA に移植した場合に、計算結果がセル上のものと VBA によるものとで異なる場合があります。

型の違い

セルは Variant 型です。あらゆる型の入力を受け付けます。VBA の数値計算でセル計算並みの精度を出すためには、最低でも Double 型で変数を割り当てる必要があります。

計算方式の違い

Excel は金融系ユーザーの使用を、かなり意識して作られています。そのため、セルの Variant 型は、やや Currency 型っぽい動きをします。先ほど、「最低でも」と書いたのは、VBA で Double を使ってもまだ、Excel のセル計算と数値を完全に一致させることができないからです。内部仕様はわかりませんので、外から見た限りの想像ですが、「内部計算時に、数値や計算結果に応じて、Double 型と Currency 型を使い分けている。」か、「 CPU の浮動小数点の端数処理を、切り上げにスイッチしている。」のいずれかではないかと思います。浮動小数点の処理方式をスイッチする方式なら、まったくパフォーマンスを下げずに、金融計算向けの高精度演算が可能ですが、それには他の言語での開発が必要です。

実例

小噺

上位 Object

Range Object の上位 Object は、Worksheet Object です。

ActiveCell.Parent

ActiveCell.Range("A1").Parent

上の表現は、どちらも ActiveSheet と同じものを返します。Range をネストして深く指定した場合でも、その上位 Object は、Range ではなく Worksheet です。

History

2002/3/24: URL移転
2001/3/25: 実例「セルで数学(回帰分析)」のリンクを追加。
2001/3/25: 実例「セルで数学(行列計算)」のリンクを追加。
2001/3/11: 実例「Range とループ」のリンクを追加。
2001/3/11: 実例「Range Object の様々な形態」のリンクを追加。
2001/3/11: 実例「アドレスを指定する」のリンクを追加。
2001/3/11: 実例「セル上のカーソルを移動する」のリンクを追加。
2001/3/11: 実例「値を更新する」のリンクを追加。
2001/3/10: 初版