VBA再入門
エクセルの機能を上手に使う

以下の表で考えてみましょう。

A列の単価にB列の数量を掛けてC列に金額を出力します。
ただし、金額の計算結果をセルに出力するのではなく、計算式をセルに設定したい場合です。
事前に説明しておきますが、
話をわかり易くするために、あくまで簡単な例にしています。
従って、以下のVBAサンプルは、どれが良いという事ではありません。
お伝えしたいのは、エクセルの機能を使う事で、色々な方法が考えられるという事です。
選択肢が増えれば、複雑な問題、難しい問題に直面した時に解決する手段が増えるという事です。

マクロの記録を使ったりして、
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
この部分を参考に、マクロVBAを書くと上記のようなコードになると思います。
.FormulaR1Cは省略できます。
もちろん、普通はこれで問題ありませんし、このように書く事が多いでしょう。
特に覚えなくても大丈夫ですが、説明の都合上簡単に書いておきます。
※R1C1参照形式

手操作で色々な方法を試してみましょう。
・C2セルに計算式を入れ、フィルハンドルをダブルクリックでオートフィル
・C2セルに計算式を入れ、C2セルをコピー、C3～C11を選択し貼り付け
・C2～C11を選択し、計算式を入力しCtrl+Enterで一括入力
それぞれのマクロの記録のVBAコードをみていきましょう。

今回の例なら、なんとなく最後が簡単そうです。

最終行取得のコードが長いので、一旦変数に入れてますが、実質は1行のVBAコードとなっています。
そもそもセルに手入力する時には、
=から書き始めれば数式となる訳ですから、R1C1形式である必要が無く、
普通にシート上と同じようにセル参照で書けば良いのです。
さらに、相対参照で入力すれば、Ctrl+Enterで全てのセルに正しく計算式が入ります。
このコードが書けるのは、
Ctrl+Enterで選択範囲内に数式を一括で入れられることを知っている人だけになります。
前回の、
第15回.手作業で出来なければマクロは書けない
ここで説明しましたが、手作業で出来ることが必要なのは、まさにこういう事です。

以下の表になります。

A列～C列に、日付順・商品コード順に金額の入った表になります。
F2～G11にそれぞれの商品の最大金額とその日付を求めます。
どんな方法でやれば良いか、ちょっとだけ考えてみて下さい。
・・・
色々な方法が考えられます。
愚直に、E列の商品コード1件ずつに対して最大値を捜していく方法や、
DMAX関数を使う方法もありますね。
オートフィルターを使って絞り込んでから・・・やりようはあると思います。
ちょっと発想を変えてみると、こんな方法もあります。
・A～C列を、C列降順で並べ替え
・MATCHで先頭行を取得し、F～G列に出力
MATCH関数やVLOOKUP関数は（完全一致の場合）、検索値が一致した先頭を返しますので、これを利用します。
マクロVBAコードは

処理後にA～C列を元の順に戻す必要があれば、再度Sortするようにします。
また、Matchの検索値が無い場合はエラーになってしまいますが、エラー処理については無視しています。
最初に書きましたが、この方法が良いと言っているわけではありません。
実際のデータでは、もっと複雑なデータ・条件となり、都度ベストな方法を模索する必要があります。
この例題の最初に、「ちょっとだけ考えてみて下さい。」と書きましたが、
並べ替えてMATCHを思いついた方はいますでしょうか。
これは、マクロの発想ではありません。
シートでデータ処理を多数こなした経験からの発想になります。
前回と今回の2回を通して伝えたいことは、
マクロVBAを自在に書けるようになるには、その前にシートでエクセルの関数・機能を自在に使えるようになる事が必要です。
という事です。
「並べ替え」と「オートフィルター」はマクロVBAでは頻繁に利用します。
これらについては、手操作での指定とVBAコードを対比しつつ、しっかりと覚えて下さい。

先のsample3でのSortは、Excel2003までのSortメソッドを使っています。
Excel2007以降では、Sortオブジェクトが追加されましたので、マクロの記録ではSortオブジェクトでのVBAコードとなります。
実際にマクロの記録をしてVBAコードをご自身で確認してください。
マクロの記録と、整理して書き直したVBAコード

マクロの記録のSortオブジェクトは、少し難しいと感じるかもしれません。
sample3の2003までのSortメソッドをまずは覚えて下さい。
SortオブジェクトもSortメソッドも、
注意点としては、Headerの指定です。
Sortのダイアログ画面の右上にある、
「先頭行をデータの見出しとして使用する」のチェックに対応しています。

この画面とVBAコードを見比べて理解するようにして下さい。

マクロの記録でのVBAコードをそのまま使えますが、いくつかの注意点を記しておきます。
先の表で、B列の商品コードを"S001"で絞り込んだマクロの記録です。

注意点は、
Field:=で指定する数値は、オートフィルターの範囲内での左からの列位置になります。
従って、Field:=2は必ずしもB列とは限りません。
データがB列から始まっていればその範囲の2番目のC列の意味になります。
以下の画面を良く確認しておいてください。


VBAコードの
Criteria1:=
この1は、上の画面の上のテキストボックスに対応しているので1となっています。
下のテキストボックスがCriteria2になります。
マクロの記録で、ANDやORを使うような指定をしてみてVBAコードを確認してください。

エクセルには、まだまだ多くの機能があります。
しかし、それらのVBAコードを全て覚えようとは思わないでください。
必要になった時、都度マクロの記録で調べられれば良いです。
ただし、その為には、事前にマクロの記録を行って、
画面での指定と、VBAコードを対比しつつ見ておくようにして下さい。
決してVBAコードを暗記する必要はありません。
一度でも見ておけば実際に必要になった時に直ぐに使えるようになります。

第3回.マクロの記録

第68回.シートの保護、ブックの保護（Protect）
第70回.ウィンド枠の固定（FreezePanes）
第71回.印刷（PrintOut）
第73回.ページ設定（PageSetup）
第87回.WorksheetFunction（ワークシート関数を使う）
第88回.並べ替え（Sort）
第89回.オートフィルタ（AutoFilter）
第90回.フィルタオプションの設定（AdvancedFilter）
第91回.条件付き書式（FormatCondition）
第126回.入力規則（Validation）
第92回.名前定義（Names）
第93回.ピボットテーブル（PivotTable）
第94回.コメント（Comment）
第95回.ハイパーリンク（Hyperlink）

同じテーマの記事
新着記事
アクセスランク