超初心者が最短でVBAの達人風を目指す（第2回）－ トライ＆エラーでマクロを理解する。With～End Withって何？

こんにちは、.TAOです。パソコンを普段仕事で使っている人なら、誰でも使ったことがあるであろうExcel。 用意されたフォーマットに入力するだけなら、Windowsを使えるスキルがあれば問題ないですが、「毎日の定型作業をマクロで自動化して効率よく仕事が出来れば楽になるのに。」と思ったことはありませんか？ そう思ったら、是非マクロにチャレンジしてみて下さい。

0.はじめに

何となく緩い感じでVBAの達人風を目指すシリーズの第2回目です。今回は、Visual Basic エディタの説明とデバックの方法、マクロの記録でよく出てくるWith～End Withについて、それと自動記録では出てこない制御構造文For～Nextを使った繰り返しについてです。「Excelのマクロ習得には実践が一番。」VBAに興味があってパソコンにExcelがインストールされているなら、このシリーズを読みつつ是非実際に試してみて、自分で書いたマクロが狙い通りに動いた時の喜びを体験して下さい。

私の実体験から言わせてもらえば、マクロを習得するには、その積み重ねた実経験が何よりも大切だと思いますし、それ以外ないと考えています。私自身大して知識を持っている訳ではありませんが、「面白かった経験を共有したい。」といったニュアンス、思いからの記事ですので、お付き合いしていただけるとうれしいです。 では、第2回目始めましょう。

1.With～End Withって何？

では、今回もマクロの記録で作業を記録していきます。今回の例では、「セルB2に”VBA”を入力→セルの背景色をオレンジに変更」を記録しました。

Sub Macro1() 
'
' Macro1 Macro
'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "VBA"
    Range("B2").Select

    With Selection.Interior
        .Pattern = xlSolid                  '網掛けパターン
        .PatternColorIndex = xlAutomatic    '網掛けパターン色
        .Color = 49407                      '背景色
        .TintAndShade = 0                   '色の濃さ1～-1
        .PatternTintAndShade = 0            '網掛けの濃さ1～-1       
    End With

End Sub

コードの前半は、第1回の時のコードとほぼ同じですね。そして後半は、セルの背景色を設定した部分でWith～End Withの部分がそれにあたります。VBAのオブジェクトは、構造体という形になっていると言う事を前回お話ししましたが、それぞれの要素が結構深いツリー構造となっているので、いちいち書いていては面倒ですし、行が長くなりすぎては理解しにくくなってしまいます。

そこで、構造体の上部分が同じものをWith～なんちゃらでひとまとめに宣言してしまい、それ以下の行については、宣言した部分を省略して書きます。上記のコードの Selection.Interior が共通する部分で、そこから下の行の End Withまでは、省略形で書かれています。省略形の特徴は、先頭にドット’.‘がある事で省略されている事を示すお約束となっています。

この例の中の With で宣言されている Selection.Interior は、「セレクション・インテリア～選択されている所の内部構造は」といった意味合いでしょうか（英語苦手で間違ってたらごめんなさい）。以下の行には、網掛けパターン、網掛けパターン色、背景色、背景色の濃さ、網掛け色の濃さの設定値が指定されています。

選択したセルの背景色をオレンジに指定したので、「.Color = 49407」の部分が背景色の値なのですが数値を見ても色を想像できませんよね。 そこで実際には、RGB関数で色を指定するか、今回の様にマクロを記録して色の値を拾うとかになるとは思います。 Excelの色については奥が深いと言うか沼と言うか・・・多様な方法やプロパティが存在そていて、全て説明していたらそれだけで終わってしまうので省略します。

With 内のプロパティはで前部分省略されていましが、Withを使わずに書くことも可能ですし、Selection. を前回で説明したセルを選択する他のオブジェクト Range. や Cells. 、ActiveCell. に置き換える事も可能です。

Sub セル背景色()
'
'セルの背景色を指定する
    Range("B3").Interior.Color = RGB(256, 0, 0)　
    Cells(4, 2).Interior.Color = &HFF00FF　’頭に&Hを付けて16進数で入力
    Range("B5").Select
    ActiveCell.Interior.ColorIndex = 4
 
'セルの背景色のColorプロパティ値を取得する
    Range("B3").Value = Range("B3").Interior.Color
    Range("B4").Value = Range("B4").Interior.Color
    Range("B5").Value = Range("B5").Interior.Color
End Sub

このサンプルマクロは、セルの背景色を違う方法で指定して、その色のプロパティを取得してセルに入力しています。また、同じ色でも .TintAndShade プロパティを使って（1～-1）の範囲で色の濃さを変更するといった使い方もあるでしょう。

このようにセル一つとっても、その中にはたくさんのプロパティが含まれたオブジェクトの一つなのです。そのセルもシート内のオブジェクトの一つであり、シートはワークシートのオブジェクトの一つと・・・要するに構造体なんですよ。VBAを理解するには、この構造体が根本にある事を踏まえて置くことが重要です。

2.ショートカットを記録してみる

実際に現場でマクロを作るときに有効なものがショートカットです。例として、B2からCTRL+↓で末行にジャンプと、CTRL+→で末列にジャンプするショートカットキーをマクロに記録してみましょう。

Sub Macro2()
'
' Macro2 ショートカットキーの記録
'
    Range("B2").Select
    Selection.End(xlDown).Select    ’CTRL+↓
    Range("B2").Select
    Selection.End(xlToRight).Select ’CTRL+→
End Sub

「これだけのマクロを記録したところで何なの？」って言われそうですが、前回説明したActiveCellと組み合わせると、様々な場面で重宝するんですよ。
という事で、次の For～Next と組み合わせて使ってみましょう。

3.制御構造文For～Nextで繰り返す

さて、ここからはマクロらしいコードに挑戦してみましょう。下の例では、ショートカットキーで表の末尾のセル行を 変数 R に代入し、表の下に縦合計の計算式を追加しています。 ActiveCellのプロパティであるRowは、選択セルの行番号を取得でき、Columnは列番号を取得できます。 Excel関数にもRow()、Column()があるのでお馴染みかもしれませんね。

さらに、行の最終列にも横合計の計算式を追加しています。 セルに代入する計算式は、「マクロを相対参照で記録」で記録されたコードに若干手を加えて、表の大きさが変わっても合計する範囲に対応出来るようにしてあります。

Sub ループ()
'
' 表の下と右に合計（SUM)を追加する
'

'表の末尾を取得
    Range("B2").Select
    Selection.End(xlDown).Select
    R = ActiveCell.Row
    Cells(R + 1, 1).Value = "合計"
    Range("B2").Select
    Selection.End(xlToRight).Select
    C = ActiveCell.Column
    Cells(1, C + 1).Value = "合計"

'合計式を追加   
    For i = 2 To R Step 1
        Cells(i, C + 1).FormulaR1C1 = "=SUM(RC[-" & C - 1 & "]:RC[-1])"
    Next i
    
    For i = 2 To C Step 1
        Cells(R + 1, i).FormulaR1C1 = "=SUM(R[-" & R - 1 & "]C:R[-1]C)"
    Next i
    
End Sub

そして制御構造文の For～Next を使って表の行部分、列部分を繰り返して合計式を追加しています。この例だと、「変数 i が、２から変数 R の値になるまで Next i の間を変数 i に１を足しながら繰り返す。」という意味になります。仮にStep の後の値がマイナスなら逆順で変化します。 また、１づつ加算して繰り返す場合は、Step 1 は省略する事ができます。
For は、必ず Next とセットで使い、複数のループを組み合わせる場合も含め Next の後ろに対になる変数を明示する方が解りやすいです。（省略も可能）

DoEvents で無限ループの回避

マクロで大量のデータをループ処理させたりした場合や、ミスコードで無限ループに陥った場合、処理をキャンセルしようと ESC を押しても止まらないって事になります。そんな場合の対処としてループ処理の中に DoEvents を一行追加して置けば、ESCでの割り込みが可能となります。

若干処理時間が長くなりますが、デバッグ中の保険で一行追加しておきましょう。保存前に無限ループから抜け出せなくなったら悲しいですから・・・。

Sub 無限ループ()
'
' DoEventsでブレイク（ESCキーで中断）可能
'
For i = 1 To 10
    i = i - 1
    DoEvents
Next i

このサンプルは１～10 のFor～Nextですが、ループ内に変数 i を-1する事で i の値が変わらず無限ループしています。DoEvents が入っていないと、パソコンがフリーズしてしまいタスクマネージャーを呼び出して、Excelを強制終了することになります。

For～Next で無限ループと言うのはなかなかないですが、Do～Loop のような条件判定式で繰り返すループでは、結構あるあるなので覚えておくと便利です。

4.Visual Basic エディタ

前回そして今回も、記録したマクロを見るためにVBE(Visual Basic Editer)を使いましたが、その説明をほとんどしていなかったので、あらためて詳しく説明したいと思います。

まず、VBエディタのデフォルトのウィンドウ配置は、左にプロジェクトウィンドウとプロパティウィンドウ、右にはコードウィンドウとなっていると思います。プロジェクトウインドウは、ツリー構造となっていてオブジェクト（このワークブック）の構造体としての関係がわかるようになっています。コードウインドウの右上のリストボックスから、記録されたマクロを選べるようになっていますね。

標準モジュールの追加

Excelでファイルを新規作成で開き、マクロの記録を行わずに開発タブからVBエディタを起動した場合、左のプロジェクトウィンドウには標準モジュールがない状態ですので、ファイル名になっているVBAProjectを右クリックして、挿入→標準モジュールの順に選択、Module1を追加します。

マクロの記述はどこに？

マクロを作成するのは、通常この標準Moduleに書き込みますが、このプロジェクトウィンドウのSheet1をダブルクリックしてもコードウィンドウにマクロを書く事もできますが、一部制約があるので使わない方がよいでしょう。

コードウィンドウの左上にもリストボックスがあり、(general)が選択されています。基本この(general)でマクロを書きますし、マクロの自動記録もここに記録されます。

マクロに慣れてくると、「何かの操作をトリガーにマクロを動作させたい。」といった場面があると思います。そんな時はここ以外の場所に記述する事になりますが、それはまた別の機会に説明しますね。

ブレイクポイントを設定してF8で一行毎に確認

マクロを実行した時に、エラーであったり、想定外の動作であったりといった場面は必ず出てくるものです。そんな時には、デバッグ作業が必要になりますが、マクロコードが想定通りに動いていいるかを一行ずつステップ実行して確認することになります。コードウィンドウの左にグレーの帯がありますね。ここをクリックすると ● が付いて、そこのコードも同色でマーキングされます。逆に解除する場合は、同じようにコード左の●をクリックします。

この状態でマクロを実行すると、このマーキングがブレイクポイントとなり、マーキングされたコードが実行される直前で一時停止します。マクロを引き続き実行する場合は▶ボタンまたはF5で継続、■ボタンでマクロを中断終了させることができます。また、F8を押す度に一行ずつコードがステップ実行されます。

マウスポインタ―を重ねて値を確認

このブレイクポイントで停止した状態だと、コード内の変数などにマウスを合わせると値を確認することができるので、エラーの原因を変数やプロパティの値を見て探ります。ステップ実行で丁寧に確認しながらコードを実行していくと、見えてくるものがあると思うので、思うようにマクロが動かないときは、ブレイクポイントをうまく利用して切り抜けて下さい。

5.まとめ

これで第2回目は終了です。少し盛りだくさんの内容で、一読しただけでは消化不良気味だと思います。できたら自分でマクロを実際に書いて試してみて下さい。読むだけと実際にするのとでは、その理解度に雲泥の差が生まれますし、「ここを変えたらどうなるんだろう。」といった興味が沸いてきたらシメたものです。

マクロを書く → 実行 → エラー → デバック → 実行 → ・・・

そうやって繰り返し行ったこと全てが、自身の身になっていくのですから。

このシリーズの2回目までで説明した事を大体理解できると、すでに簡単なマクロは作成できるレベルになっていると思います。チャレンジと応用、そして楽をしたいという原動力があれば、VBA達人も夢ではありませんよ。是非この機会にチャレンジしてみて下さい。

次回は、マクロ（プログラム）では最も重要な条件と分岐、それと基本になる変数と宣言についてを取り上げたいと思います。拙い説明に最後までお付き合いいただきありがとうございました。

6.関連リンク

「Excel（エクセル）VBA入門」記事一覧