Excel VBA

【Excel VBA】【マクロ】【Tips】ワークシートをWithに設定する際に気を付けること

こんにちは!Lenocoです。本日も見てくださりありがとうございます。

今回はWithについてのTips回です!!!

Withとオブジェクト変数を使ってコードを見やすくシンプルに!」の記事でご紹介した通り、Withってとっても便利なんです。
コードもすっきりシンプルになるし、非常によく使うのですが、
慣れてきたころにWithの罠にはまってしまうことがあるんです。。TT

ワークシートをWithに設定する際に気を付けること

簡単な例でご説明します。

前提

各シートのA1~C1セルには何かしらの数値が入っている

やりたいこと

ブックのシートをループします。各シートごとに以下の計算をします。
A1セルに、A1セルの値+10した値を入れます。
B1セルに、B1セルの値×A1セルの値を入れます。
C1セルに、C1セルの値+B1セルの値を入れます。

とってもシンプルですね!なんとなくイメージできるかと思います。

コード

Sub Sample()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        With ws
            .Range("A1") = .Range("A1") + 10
            .Range("B1") = .Range("B1") * Range("A1")
            .Range("C1") = .Range("C1") + Range("B1")
        End With
    Next ws
End Sub

うまく動くでしょうか

上記のコードを書いてみて実行してみると、どうでしょうか、
想定通りの動きをしてくれるでしょうか。

実際は・・・
正しく動きません!!!!

実行してみると、一見ちゃんと動いたように見えるかもしれませんが、
コードが間違えているため、正しく計算できていません。

コードをよく見てみましょう

With~End Withの中、よーーく見てみてください。

B1セル、C1セルの命令、何かおかしくないですか?

なんと、Rangeの前に「.」(ドット)がありません!!!!!

どんな結果になってしまったのか

With内で、「.」(ドット)から始まるセルの場合、Withで指定したオブジェクトを省略した、という意味になります。
つまり、「.Range(“A1”) = .Range(“A1”) + 10」であれば、「wsのA1セルにwsのA1セル+10の値を入れる」の意味になります。
「.」(ドット)で始まらずにセルを指定した場合、wsに入ったシートではなく、
アクティブシート(一番前面に表示されているワークシート)が対象となります。
つまりB1セルの値はどうなるでしょうか。
B1セルは正しく「wsのB1セル」と指定できていますが、
A1セルには「.」(ドット)がついていないため、
「wsのB1セルに、wsのB1セル×アクティブシートのA1セルの値を入れる」の意味になってしまいました。
C1セルも同様で、
「wsのC1セルに、wsのC1セル+アクティブシートのB1セルの値を入れる」の意味になってしまっています。

さらなる罠

Rangeの前に「.」(ドット)がついていないことにすぐ気がつければ良いのですが、
ここで気づかなかった場合、おそらくデバッグに移るかと思います。
ブレークポイントを設定し、F8で1行ずつ実行し、どこで計算がおかしくなっているか見ていきますよね。
その際、各シートを1つずつ選択しながら見ていくと思うのですが、
Rangeの前に「.」(ドット)がついていないことが原因の場合、この方法で見ていくと、
なんと、計算が正しくできてしまうのです。
理由はなんとなくわかるでしょうか。
各シートを手動で選択したため、偶然にもwsとアクティブシートが一致してしまったからです。

なんてややこしい!

「なんだ、正しくできてるじゃん」と思ってまた普通に実行したらまたおかしな結果になり、
「普通に実行したら正しく計算できないのに、デバッグしたら正しく計算できる。なぜ・・・?TT」
と疑問のループにハマってしまいます。

さいごに

「.」(ドット)が一つないだけですが、全く違う結果になってしまうので、マクロとしてはかなり致命的なミスとなります。
エラーも出てくれないので、自力で気づくしかありません。

ここで一つ、新卒で入社した会社の先輩のお言葉をご紹介します。
「プログラムが正しく動かないときは、絶対自分が間違えてるよ。
プログラムは書かれた通りにしか動かないから。」

自分が書いたコードに自信を持つことも良いですが、
疑うことも、とっても大事だと思います。

プログラミングって奥が深い・・・

自分の凡ミスではあるのですが、
書いているコードが複雑になればなるほど原因に気づきづらいので、
Withを使っている場合は「Withが原因かも?」と考えるきっかけになればと思います。
数時間かけて見つけた原因が「.」(ドット)1つだったときの脱力感は本当に半端ないです 笑
みなさんもお気を付けて!!!

Lenoco

COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です