VBA基本(番地だけ書いて郵便は届くか)

ヘンなタイトルですが．．．

マクロの記録で作ったマクロは、番地だけで郵便を届けようとするものです。

例えば、B1セルにA1セルの値をそのまま表示させる式を入れる動作をマクロに記録してみます。


    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"

こんなコードになると思います。
これを整理して、


    Range("B1").FormulaR1C1 = "=RC[-1]"

とすれば、セルの選択動作なく計算式がセットできるのですが．．．

ここで説明する問題は、そういうことではありません。

「1丁目2番地」はどこの町？

この質問の意味が分かりますか？普通、「市」や「町」を書かずに「番地」だけ書いて郵便を出しても届きません。
同様に、


    Range("B1")

では、B1セルを明示していますが、そのブックにシートが3つあればB1セルも3つあることになります。
ワークブックが複数開いていれば、さらに多くのB1セルがあることになります。つまり、このような記述では「市」や「町」を書かずに「番地」だけ書いて郵便を出すようなことと同じなのです。

実際は、ブックやシートを指定せずにセルを参照／更新する記述をした時にどのように動作するかは規則があるのですが、ここではその規則を説明するのではなく、ブックやシートを明示することを知ってほしいと思うのです。
シートの切り替えを伴うマクロを記録した場合は、


    Sheets("Sheet1").Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"

となりますから、この時点でのB1セルはSheet1のB1セルだということになりますが、 Sheet1が最初から表示されている状態で記録したマクロは最初のコードのようになってしまいます。
そのブックにシートが1つしかなかったり、複数のシート間のやりとりを一切しないマクロならほとんど誤動作はないかも知れませんが、マクロを作成する時はどのような環境で実行されても間違いなく動くように考えておく必要があります。

ブックやシートを指定するということは？

どのような環境で実行されても間違いなく動くということは、マクロの実行時点で他のシートが選択されていたり、他のブックが選択された状態でも所望した結果が得られるということです。
マクロの作用先が、自ブックのSheet1に決まっているのなら、少なくとも、


    ThisWorkbook.Activate
    Sheets("Sheet1").Select

これを記録したマクロコードの先頭に追加すれば、間違って他のブックにマクロが更新を行なってしまうことは避けられます。「ThisWorkbook」とは、動作中のマクロが記述してある「自分」のブックのことです。マクロの記録などで標準モジュールに記載されているマクロは、ブックやシートを明示していない記述に対しては、その時点でアクティブなブックやシートをターゲットとして動作することに留意して下さい。
但し、このような記述を前に付けるということは、東京都の人が鹿児島県の町に郵便を出すのに、わざわざ鹿児島県の町まで行ってそこのポストに投函するようなものではないでしょうか。

シート間の転記などは？

例えば、Sheet1のA1セルをSheet2のA1セルという単純な動作をマクロに記録してみます。
このようなマクロの記録を行なう場合は、最初は目的箇所が選択されていない状態から始めるのがコツです。なぜなら、最初からA1セルが選択されている状態で記録を行なうと、「A1セルの選択」は記録から抜けてしまうため、後で動作させると思わぬ動作になってしまうことがあるからです。


    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste

実際はこのようなコードになって、この前に先ほど説明した、


    ThisWorkbook.Activate

を追加すれば、とりあえず間違いなく動作するようになります。
ですが、転記するセルが連続していない領域でたくさんあったらどうしますか？少なくとも、1回の転記は1行で記述できないものでしょうか。
(先の「鹿児島県」の問題はちょっと置いておきます。)

転記は、[転記先]=[転記元]の式で動作します。

VBAというか、Basicの初歩となります。変数が「A」と「B」があるとして、


    B = A

と書くだけで、変数「A」の値は変数「B」に転記されます。
これをセル間の値の転記に応用すると、


    Range("A1").Value = Range("A1").Value

となるのですが、気持ちとしては左辺はSheet2、右辺はSheet1であって欲しいわけです。であれば、住所と同じように「番地」の前に「市」や「町」を指定してやります。


    Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("A1").Value

先の「Copy」「Paste」では書式も転記されますが、この方法では値だけの転記になります。 Copyメソッドでも1行に書けますから、書式ごとの転記なら、


    Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("A1")

と書いて下さい。(この場合は、値の転記の説明と左右逆です。)
この2つの書き方は、マクロの記録では手に入らないコードで、クリップボードも経由しない速度の速い方法です。「Destination:=」は元々第1引数なので省略しても構いません。このように、シートを明示し、さらにはこの前にブックも明示すると、先ほどの「鹿児島県」の問題は解決し、他のブックがアクティブな状態からマクロを起動させても不正な動作はなくなります。

これで、1件を1行で記述できますが．．．

1件の転記なら良いですが、これが何ヶ所もあったら醜いですね。
1つの矩形区画のセル範囲なら1行で記述できますが、下記のような場合は1つずつになってしまいます。


    Sheets("Sheet1").Range("A1").Copy Destination:=Sheets("Sheet2").Range("A1")
    Sheets("Sheet1").Range("B2").Copy Destination:=Sheets("Sheet2").Range("B1")
    Sheets("Sheet1").Range("C3").Copy Destination:=Sheets("Sheet2").Range("C1")
    Sheets("Sheet1").Range("D4").Copy Destination:=Sheets("Sheet2").Range("D1")
    Sheets("Sheet1").Range("E5").Copy Destination:=Sheets("Sheet2").Range("E1")
                      ・
                      ・
                      ・

規則だっていない転記であれば、必要行数書かなければならないと思いますが、このようにシート参照を毎行同じ記述をするのはもう少し整理できそうです。
例えば、「Withステートメント」を使って、一方のシート参照をまとめてしまいます。


    With Sheets("Sheet1")
        .Range("A1").Copy Destination:=Sheets("Sheet2").Range("A1")
        .Range("B2").Copy Destination:=Sheets("Sheet2").Range("B1")
        .Range("C3").Copy Destination:=Sheets("Sheet2").Range("C1")
        .Range("D4").Copy Destination:=Sheets("Sheet2").Range("D1")
        .Range("E5").Copy Destination:=Sheets("Sheet2").Range("E1")
                      ・
                      ・
                      ・
    End With

「Withステートメント」でSheet1を指定しているので、「End With」までの間は「.」で始まる記述はSheet1に属したものとして扱われます。
これを、さらに進めてみます。シートについての参照をオブジェクト変数に確保させてから転記させる記述です。


    Dim SH1 As Worksheet, SH2 As Worksheet

    Set SH1 = ThisWorkbook.Worksheets("Sheet1")
    Set SH2 = ThisWorkbook.Worksheets("Sheet2")
    With SH1
        .Range("A1").Copy Destination:=SH2.Range("A1")
        .Range("B2").Copy Destination:=SH2.Range("B1")
        .Range("C3").Copy Destination:=SH2.Range("C1")
        .Range("D4").Copy Destination:=SH2.Range("D1")
        .Range("E5").Copy Destination:=SH2.Range("E1")
                      ・
                      ・
                      ・
    End With

いかがでしょう。動作以外でも1行の記述が短くなったので視認性が向上します。このページではここで初めて「変数」が登場し、それがいきなりオブジェクト型変数で「Setステートメント」が登場してしまうので、混乱されるかも知れませんが、オブジェクト型変数には「Setステートメント」が要るのだと単純に覚えてもらって構わないと思います。(For Eachのループなどの例外はあります。) 一説によると、毎回「Sheets("Sheet1")」と記述するのは、内部的に毎回オブジェクトを参照し直す動作になるので、一旦変数に確保させてからの方が動作が速いということもありますが、 VBAなどではコンパイラが最適化させてしまうのかわかりませんが、テストケースレベルでは違いは分かりません。
この他、この最後の記述では、変数にシートオブジェクトを確保する時に「ThisWorkbook」を明示してあります。このため、他ブックがアクティブになっている状態でマクロが実行されても、自ブックに対して動作するということになります。
この件は、他のページにも同様のことを書いていますが、別の視点だったので新たにページを起こして見ました。

番地だけ書いて郵便は届くか(セル指定の話)

ヘンなタイトルですが．．．