53_VBAmemo
このページは、豆知識53の各ページの追加説明のリンクページです。それぞれの項目には関連がありません。
------------------------------------------------------------------------------------------
RangeとCells
VBAでセルを操作する時に使うRangeとCellsはとても奥が深くて、簡単に説明することなどはとても出来
ません。それに関するExcelのヘルプの一部が下図ですが、よく読むとなるほどと思うことが沢山書いて
あります。関連するヘルプ記事も他に沢山あります。
なので、私がそれについて何かを説明するなどおこがましいのですが、すごく最初の基本だけ。^^;
RangeはRange("A1")のように、一般操作で普通に使う「A1」セルのようなセルのアドレスをそのまま括
弧のなかの引数に使用出来ます。Range("A1:D10")とかRange(Cells(1, 1), Cells(5, 3))という風に
してセル範囲を表すこともできます。
CellsはCells(1,1)のように、Cells(行番号,列番号)のような書き方で１つのセルを取得します。一応
Cells(1,"A")のように書くことも出来ますが、通常は数字を引数に使用します。そして、行または列の
指定に変数を使用できます。
ということで、「印刷用」シートのB2セルを表すのに「Range("B2")」としたのに対して、「名簿」シー
トのA2セルを表すのに「Cells(2, 1)」とした理由は、印刷用シートの３つのセルはいつも固定のセル番
地なのでRangeを使っていて、名簿シートは２行目からデータの最終行まで行番号を変数に入れて繰り返
し処理をするためにCellsを使った、というものでした。
------------------------------------------------------------------------------------------
VBAでセルに数式を設定する時に
セルに数式を設定する場合、「A1参照形式」と「R1C1参照形式」のふたつの設定方法があります。
普段一般操作で数式を設定する場合に馴染んでいるのは「A1参照形式」だと思いますが、VBAで数式を設
定する時は「R1C1参照形式」が便利な気がしています。
下図はそれぞれのExcelのヘルプの記事です。
シートの表示を「R1C1参照形式」に変更して、ちょっと様子を見てみましょう。
「ファイル」-「オプション」で出る「Excelのオプション」の「数式」をクリックし、「数式の処理」
の「R1C1参照形式を使用する」にチェックを入れて「OK」としてみます。
R1C1参照形式にして、数式表示にしてみたのが下図です。
同じものを、A1参照形式に戻したのが下図です。
どちらもA1セルを選択しているので、名前ボックスにそれぞれ「R1C1」「A1」と表示されています。
R1C1参照形式では列番号の表示が数字になっています。
それぞれの数式を比較すると、特徴がわかると思います。R1C1参照形式では「金額」「消費税」「合計金
額」の各行のセルに入っている数式が皆同じです。自セルと同じ位置の場合には数字が省略され、相対位
置にあるものはそこまでの距離が角括弧付きの数字で表示され、絶対参照の場合は「R3C9」のように括弧
なしの数字がRとCの右に付きます。
R1C1参照形式の数式を自分で考えることには慣れていませんが、マクロの記録をするとこの参照形式の数
式を取得することができます。
そして、上図でわかるように、どの行にも同じ数式を設定すればよいので、VBAで数式を設定する時には
やりやすいような気がしています。
数式をマクロ記録するためには、数式の入っているセルを選択して、下図のように数式バーをクリックし
てセルを編集状態にしてからEnterキーを押します。
上図のE3、F3、G3セルについて記録操作をした結果が下図です。
これをもとにして、下図の水色部分にVBAで数式を設定してみたいと思います。
「E3セルを選択して、そのアクティブになったセルのR1C1形式の数式を・・」という記録のされ方ですの
で、「E3セルのR1C1形式の数式を・・」という風に省略できます。
上の方のコメント行は不要ですのでDeleteします。
これを実行すれば3行目に数式が入りますが、3行目から7行目まで一度に数式を設定したいと思います。
Rnageはセル範囲を指定することが出来ますので、Range("E3")を同じ数式を入れたいセル範囲の
Range("E3:E7")に変更します。Range("F3")もRange("G3")も同様に範囲に変更します。
このコードを実行すると、下図になります。
このページの上の方で変数を使う時はCellsを使うというようなことを書きましたが、私はこのセル範囲
に数式を設定する時だけはRangeの引数に変数を使ってしまいます。^^; ここにちょっと言い訳を。
職場で利用するVBAは出来るだけ正しい（と思われる）コードにしたいと思って、VBAのセミナーを受けたりしましたが、
Rangeに変数を使うのは「使えるけどお奨めしない」と大抵言われました。
でも、Cellsでは1つのセルしか表せません。なので、セル範囲はRange(Cells(3, 5), Cells(7, 5))という風にRangeの
引数にCellsで始点と終点のセルを設定します。（ Cells(3, 5).Resize(5, 1)など他にもいろいろ設定の仕方はありま
すが。）で、大抵項目行の次の行から最終行までの範囲に一気に数式を書き込みたい時、最終行を変数LastRowに取得し
たとすると、Range(Cells(3, 5), Cells(LastRow, 5))という書き方になります。列番号が一ケタの数字くらいならまだ
大丈夫なのですが、下図のようにBJ列あたりに設定しようと思うと、Range(Cells(2, 62), Cells(LastRow, 62))などと
いうのはBJと62がほんとに同じ列を表すのかどうかピンときません。
なので、それならRange(Cells(2, "BJ"), Cells(LastRow, "BJ"))と書いた方がまだわかりやすいと思います。
でも、それならRange("BJ2:BJ" & LastRow)と書いた方がずっとわかりやすい気がするのです。なので、お奨めしないと
言われても、このパターンの時だけはこれを使うことをやめたいとは思わない。というのが私の言い訳です。^^;
で、下図の数式部分は大抵その後でRange("BJ2:BW" & LastRow).Value = Range("BJ2:BW" & LastRow).Valueとして、数
式から値へ変換してしまいます。（その後でデータを訂正してそれを数式部分に反映させることがある場合には数式を
残しますが。）
豆知識53の始めに、「4.出力されたデータに対して、集計に必要な項目が不足していれば数式等を利用して項目を追加
する。」と書きましたが、上図のようなものをイメージしています。システムから出力されるデータだけではどうしても
必要な集計が出来ない場合に、こんな風に数式でいろいろ追加します。そうすればピボットテーブルで欲しい集計が得ら
れますので。^^
