EXCEL VBAでの安全なプログラミング

VBAの解説で本やネットに載っているコード、あるいは職場で見かけるコードでは、以下のようにCellsやRangeから始まっていたり、よくてもWorksheetsから始まっていることが多い。

Cells(5, 4).Select
Range("D5").Select
Worksheets(1).Range("D5").Select

もしCellsやRangeから始まるコードがシートモジュールに記載されている、あるいはWorksheetsから始まるコードがブックモジュールに記載されているのであれば問題ないが、標準モジュールに記載れていると、安全なコードとは言えない。

EXCEL VBAでの安全なプログラミングをするには、以下を守る必要がある。

• ActiveWorkbook / ActiveSheet / ActiveCell はできるだけ使わない
• Select / Selection はできるだけ使わない
• 標準モジュールでは、ThisWorkbook.Worksheets("Sheet1")をつける

そもそもActive系やSelect系をVBAの中で使う必要がある箇所はかなり限られると思う。
プログラムの基本である、INPUT→処理→OUTPUTの流れの中で、セルやシートをセレクトする必要がでてくることはあまりない。ユーザの入力をリアルタイムで検知して処理するようなVBAであれば必要になるが、シートモジュールやブックモジュールに書くことになるので幾分か安全。

ThisWorkbook.Worksheets("Sheet1").Cells(1, 1)のように毎回書くのは大変なので、Withを使うといい。

With ThisWorkbook.Worksheets("Sheet1")
    MsgBox .Cells(1, 1).Value
    MsgBox .Cells(2, 1).Value
End With

もっといい方法はSetを使ってシート自体をもっと短い名前かつわかりやすい名前の変数に代入して使うこと。

Dim ある業務のシート As Worksheet
Set ある業務のシート = ThisWorkbook.Worksheets("Sheet1")
With ある業務のシート
    MsgBox .Cells(1, 1).Value
    MsgBox .Cells(2, 1).Value
End With
Set ある業務のシート = Nothing

変数名がもっと短くできるならWith自体いらないし、複数のシートを扱うのであればWithは無い方がわかりやすい。

'商品マスタシート
Dim ProductSh As Worksheet
'注文シート
Dim OrderSh As Worksheet

Set ProductSh = ThisWorkbook.Worksheets("Sheet1")
Set OrderSh = ThisWorkbook.Worksheets("Sheet2")

ProductSh.Cells(1, 1).Value = OrderSh.Cells(1, 1).Value