VBA編其ノ９ Do…Loop激闘編 後編
本日も、前々回から続く「税８パーセント」マクロの作成です。
元リストは其ノ７にありますので。
で、ここまでの流れを思い出してほしいのですが…、
税８パーセントマクロはこれまで、３列目に税抜価格があることを前提としていました。
つまり、「３列目が税抜価格だから、そのとなりに列を挿入して作業する」という内容だったわけで、逆に言うと、３列目が「税抜価格」じゃないと意味がないわけです。
でも、税抜価格、いつも３列目にいるのかな？
４列目や５列目にいる場合もあるんじゃないかな？
そういうときも、「税抜価格」のところを探して作業してくれると便利だな…、というのが、今回のテーマです。
ちょっと、やってみますか。
目的のセルを探す
「税抜価格」列が何列目にあっても動くようにするために、まず、「税抜価格」列を検索させます。 検索にはfindというワードを使います。 ちょいとやってみましょう。
はじめて見るワードもいくつかありますが…、 とりあえず、動かしてみましょうか。 こんな感じになります※１。
１行目を範囲として「税抜価格」セルを検索し、その列番号をMsgBoxに表示します。
現状、税抜価格は３列目にあるから、「 3 」と表示されますね。
もちろん、列の移動に対応しているので、例えば２列挿入して「税抜価格」列が右に移動しちゃったとしても、対応します※２ ※３。
イイ感じ。
これを応用すれば、やりたいことはもうできちゃいそうな感じです、が、まずは、内容の理解の方から。
ちょっとコードの方を見ていきましょう。
このマクロ、変数「targetCell」には「税抜価格」の検索結果、つまり税抜価格セル自体を格納しており、「targetCellClmn」にはそうやって検索したセルの列番号を格納しています。
おおっと！
最初の変数の宣言のところ、Objectになってますね。
オブジェクト…、遂に出てきてしまいました…。
オブジェクト（と、あとプロパティとメソッド）
オブジェクト…、遂に登場してしまいましたね。
というのも、僕は其ノ４では「目的語＋動詞」という言い方をして、「オブジェクト」について語るのを避けていたからです。
けど、今回は、「オブジェクト」について解説しないわけにはいかないようですね。
たぶんみなさんの方も、其ノ４の頃よりはVBAに慣れ親しんできていると思いますので、まあ、あの、あまり肩ヒジ張らずにお付き合いいただければ、と。
…さて。
オブジェクトということばの一般的な意味が「対象」「目標となるもの」だということは其ノ４で触れました。
では、何の対象、何の目標なのでしょう？
それはもちろん、VBAの命令の、です。
ワタクシ流の言い方だと「目的語＋動詞」の「動詞」の部分、具体例を挙げると.Selectなんかがそうです。
Selectだけ単独で１行だけ書いたとしてもダメ、というのは其ノ４ですでに述べたとおりで、というのも、何をSelectすればいいのか、ワークシートなのか、それともセルなのか、がわからないからです。
だから、命令の対象は明言してやらなきゃいけないワケで、このときの命令をメソッドと言い、命令の対象をオブジェクトと言います。
ここまではオケイ？
でね、メソッドの具体例って.Selectとか.Insertとかなワケだけど、オブジェクトの方はと言うと…、その対象だから、セルとかワークシートとかがそれになります。
Selectするのは「セル」だったり、「ワークシート」だったりしますもんね。
あと、実はワークブックもオブジェクトのひとつです。
オブジェクトって、すごくおおざっぱな言い方をすると、Excelを開いたときに、Excel上に配置されてあるモノがオブジェクトだと思ってください（あまりに雑な言い方なので、怒られるかもしれませんが…）。
Excelを開くと、ワークブックがあって、その中にワークシートが３枚（設定で変えられるけど）あって、んでその中にセルがあるでしょ？※４
VBAって、結局のところこれらを操作しているんですよ。
…そうそう、それでね。 「セル」ってオブジェクトなんだけど、オブジェクト名としてはRangeオブジェクトになります。 Rangeってアレね、範囲を指定するのに使っていたアレのことね。 各々のセルもRangeのひとつで、Rangeの最小単位がたまたまセルだった、というふうに理解しておいてください※５。
ということで、オブジェクト、なんとなく理解できました？
…だけどね、さっきワタシ、「VBAは結局のところ、Workbook・Worksheet・Rangeを操作している」と言いました。
これに対してね、「ホントかよ」と思っている方もいらっしゃるかもしれません。
つまりね、今まで.Valueとか.Interor.ColorIndexとかいろいろやってきたじゃないか、そいつらのことはどうするんだ？って方もいらっしゃると思うんですよ。
でもね、こいつらは結局セルのValue、セルのInteror.ColorIndexなワケですよ。
つまり、オブジェクトの細目として含まれているワケであって、こういうオブジェクトの構成要素のこと、オブジェクトを構成している性質のことをプロパティと言います。
プロパティ…、意味は「属性」でしたね。
なので、オブジェクトは１コでも、それに含まれているプロパティはめっちゃたくさんあります。
例えば、あるひとつのセル（というオブジェクト）は、その行番号や列番号といった番地や、それがもってる値、行の高さ、列幅、内部の色、フォントの種類、フォントの色…、といったさまざまな要素から成り立っています。
ひとつのセル、というRangeオブジェクトがもつプロパティの主要なものをまとめてみましょう。
| プロパティ名 | 解説 |
|---|---|
| Column | 列番号 |
| ColumnWidth | 列幅 |
| Font.Color | フォントの色 |
| Font.Name | フォントの名前（"ＭＳ ゴシック"など） |
| Interior.ColorIndex | セル内部の色番号 |
| Row | 行番号 |
| RowHeight | 行の高さ |
| Value | 値 |
ね？
主要なものに絞ったけれど、けっこう数あるでしょう？
でね、この「オブジェクトの」プロパティについて、現状の値を取得したり、新しく値を設定し直したりできる（し、これまでもやってきた）のです。
…大丈夫？
ついてこれてます？
「現状の値を取得」っていうのは、例えば、セル(3, 3)がこんなふうになっているとして※６。
下↓のマクロを動かすと、現在のプロパティが次々と表示されます※７。
…それに対して、「新しくプロパティを設定する」っていうのは、コレ↓のことね※８。
サーティスファクショーン。 ということでここまでの話をまとめると、オブジェクトがVBA操作の基本、というか、中心的存在となります。 われわれはVBAを使って、オブジェクトを操作している、というワケです。 で、そのオブジェクトに対してわれわれは、プロパティを取得、ないし設定したり、あるいはメソッドを実行したりできる、ということです※９。
以上、オブジェクト、プロパティ、メソッドの説明終わり。 イマイチ漠然としているな…という方は、「オブジェクトはRange・Worksheet・Workbook！」と機械的に覚えちゃいましょう。 仕組みの理解なんて後から付いてきますから。
変数のデータ型
さて、話を戻して。
なぜ上でオブジェクトの解説をしたかと言うと、さっき紹介したプロシージャのなかに「Dim targetCell As Object」というのが出てきたからでしたね。
われわれはこれまで、「Dim ○○ As Integer」のかたち、すなわちInteger型の変数しか使ってこなかったのですが、今回Object型の変数が登場したので、急遽オブジェクトという概念について説明させていただきました。
でね、変数って情報を格納するものだ、ということはみなさんも何となく気づいてると思うんですが、格納されるデータの種類によって、変数のタイプも変わってくるんですよ。
主要なデータ型をちょっとまとめておきましょう。
| データ型 | 名称 | 格納できるデータの種類 |
|---|---|---|
| Boolean | ブール型 | TrueまたはFalse |
| Integer | 整数型 | -32,768～32,767の整数 |
| Long | 長整数型 | Integerの範囲を超える整数（32,768とか） |
| Double | 倍精度浮動小数点数型 | 小数点を含む数値。 似たようなのにSingle（単精度浮動小数点数型）ってのもあるが、小数はコレ１択で当面ＯＫ |
| Date | 日付型 | 日付、時刻 |
| String | 文字列型 | 文字列 |
| Object | オブジェクト型 | オブジェクトへの参照 |
| Variant | バリアント型 | なんでもＯＫ |
だから例えば、「この変数に入るのは整数だな…」というときには「Integer」を使います。
これまでもInteger、使ってきましたね。
例えば、ループ処理で使った「 i 」とか。
あの場合、Excelの行数として使うから当然整数だし、ループさせる表の行数が32767行以下だから「Integer」を使うんです。
もし50000行もある表にループさせなきゃいけないとなったら、「Dim i as Long」と宣言してやりましょう。
今回のケースで言うと、変数targetCellClmnの方はIntegerで良いんですよ。
検索した「税抜価格」セルの列番号を格納するための変数なのですから。
ですが、変数targetCellに格納するのは、セルです。
「税抜価格」セルというオブジェクトです。
だから、「Dim targetCell As Object」と、オブジェクト型で宣言してやらないといけないのです。
Findの使い方
マクロ「税8パーセントその8」に戻りましょう。
このマクロの３行目は「Set targetCell = Rows(1).Find("税抜価格", lookat:=xlWhole)」となっています。
これは解説すると、「１行目の範囲内で"税抜価格"を検索して、見つかったセルを変数targetCellに格納する」という意味なのですが、変数にオブジェクトを代入するときはアタマにSetをつけます。
これはもうルールなので、こういうものとして受け入れていただきたく。
恐れ入りますが。
次の行の「targetCellClmn = targetCell.Column」を見ると、コチラはSetがついていませんね。
変数targetCellClmnに格納するのは、セルの列番号という整数であって、オブジェクトじゃないからです。
…まあ、このあたりはおいおい、慣れていっていただくとして。
で、Findメソッドの使い方ですね。
下↓をご覧ください※10。
Findはメソッドなので、まずオブジェクトを指定してあげてください。
使用するのはRangeオブジェクトです。
今回は、「税抜価格」という名前のヘッダー（項目名）を探したいので、「Rows(1)」を指定しています。
もし「全セルの中から特定の値を探したい」という場合は、「Cells」を使います。
Cellsは、これまでCells(3, 3)みたいに使ってきましたが、()かっこで番地を指定しないと「すべてのセル」という意味になります。
「Lookat:」のところにxlWholeを指定すると、完全一致するものを検索します。
部分一致はxlPartです。
wholeは「全体」、partは「部分」という意味ですもんね。
だから例えば、下↓みたいなマクロを書くと…※11
「メ」という文字を含むセル、すなわち「メーカー名」セルの列番号が表示されます※12。
…ですが。
実は、引数xlWholeのままでも、ワイルドカードを使えば部分一致検索を実現できます※13 ※14。
ワイルドカードってアレね、「 *（アスタリスク）」とか「 ?（クエスチョンマーク）」のことね。
知らない文字、未確定の文字の代わりになってくれるんだけど、アスタリスクの方は「どんな文字にもなれるし、文字数も何文字でもよい」、それに対してクエスチョンマークの方は「どんな文字にもなれるけど、１文字分だけ」という違いがあります。
もし先頭が「メ」ということだけから「メーカー名」をヒットさせたいとすると、アスタリスクなら「メ*」で「メーカー名」がヒットするんだけど、「メ?」ではヒットしてくれません。
なぜなら「メーカー名」は５文字だからです。
「メ????」ならヒットします。
ワイルドカードの使い方としては、「前後をアスタリスクではさんじゃう」ってのがポピュラーかな。
＜この機会にちょっとお話＞
このマクロを使った後Ctrl+Fで検索すると、「アレ？」ってなるかもしれません。
なんだか、見つかるハズのものが見つからない…。
実は、「検索と置換」オプションを見ていただくとわかるのですが、「セル内容が完全に同一であるものを検索する」と「半角と全角を区別する」にチェックがついています。
これは、Findメソッドで指定した引数のせいです。
「セル内容が完全に同一であるものを検索する」にチェックが付いたのは、Lookat:=のところをxlWholeにしたからです。
xlPartだと付きません。
また、「半角と全角を区別する」は、（今回紹介していませんが）MatchByte:=Falseを指定するとチェックが付きません。
逆に、Trueを指定すると半角と全角を区別することになるのですが、実は省略してもTrueを指定したのと同じことになるんです。
だから今回、この２つにチェックが付いちゃったんですね。
とりあえず、VBAのFindメソッドを使った後、改めて手作業で検索をする場合には、「検索と置換」オプションの設定が変わっている可能性を考慮に入れておいてください。
Findの引数とココは連動しちゃうので。
…さて。 ここで注意点。 Findメソッドをつかうときに１コ考慮しなければならないのは、検索値が見つからないときの対策です。 例えば、「税抜価格」セルを「税抜き価格」に書き換えて※15、「税8パーセントその8」マクロを動かしてみてください。 「税抜価格」に完全一致するものはなくなっちゃいますね。 そうなると、エラーになってマクロが止まってしまいます※16。
「税抜き」の「き」を送っているかどうかだけで動かなくなるなんて、融通がきかない、と思われるかもしれませんが、この「税8パーセント」マクロシリーズの最初の頃、問答無用で３列目を作業していたことを思えばずいぶん柔軟になっていますので、その辺は評価してあげてください。
それに、こういうのは厳密な方が良いんですよ。
「人間側がルールに則って表を作っている、作業している」ということのチェックになりますから。
…あ、そうそう、プロシージャが止まってしまったので、リセット（上↑の赤丸部分のスイッチ）を押して復帰しておいてください。
と、いうことなので、検索値が見つからない場合に備えておかないといけません。
使うのはIfステートメントです。
「検索値が見つからない」ということを、「targetCell Is Nothing」と記述します。 これはもう、決まり文句なので覚えてください。 でね、「見つからない場合」というのをIf節に収めて、「もし検索しても見つからないときは、メッセージを出してプロシージャを抜ける」というふうにしてやります。 これで、検索値が見つからなくても、エラーにならなくなります※17。
なので、「Findメソッドを使うときは、必ずIf文を使って検索がうまくいかないときの対策をする」ということを肝に銘じておいてください。
「税抜価格」列を探して処理してくれるマクロの仕上げ
以上のヒントから、「税抜価格」列を自動的に探して税込計算してくれるマクロが書けそうです。
みなさんの方でもチャレンジしてみてください。
・
・
こんな感じ↓になります。
最初に変数を４つ宣言します。
「targetCell」のところ、「As Object」のままでも良かったんですが、今回はより限定して「As Range」としました。
あとは、これまで作ったマクロを流用します。
ただし、これまで３列目を指定していたところをすべて、変数targetCellClmnにしてやります※18。
あと、細かいところなんですが、最初の作業準備確認のところ、キャンセル時のメッセージを「No列などを作成して埋めてください。」に変えました。
というのは、新しく最左列に列を増やしても、今回のマクロは対応できるからです。
まあ、今回はNo列、すでにあるんですけどね。
でも、もし仮に最左列がところどころ空白のあるものだったとしても、No列を作成しちゃえばマクロを動かせるようになるワケで、そのようにユーザーを促してやるというのも、作成者の気配りのひとつです。
これで、意図していたことはできました※19。 すばらしい。 ちなみに、「空想科学読本」でおなじみの柳田理科雄さんのモットーは「自画自賛」だそうです。
と、いうことで、今回はここまで。 だいぶ柔軟なマクロになりました。 もちろんこのマクロ、１行目から表がはじまっているというのが大前提になってます。 項目名を１行目で探すとか、ループ処理が２行目から始まるとか、すべて１行目から表がはじまっているというのが前提です。 しかし、まあそこは、ルール運用でカバーしていきましょうよ。 いかなる表にもマクロで対応できるというのは、すごくむずかしい、というか、ほとんど不可能だと思うので、ある程度人間側が機械に寄り添っていく、という姿勢も必要みたいです。 その辺のバランス感覚も、おいおい身に着けていきたいですね。