【ExcelVBA】プロシージャを分割したときのシート指定について

Question

●ExcelVBAで、プロシージャを分割して処理するとき、どのようにオブジェクトを定義するのが一般的なのでしょうか？

実務で他の人が書いたコードを見たことがないので、読みやすく書き直しやすいコードのために参考にしたいです。

以下の3つを今まで使ってきたのですが、どうも自身が持てません。よろしくお願いします。

①プロシージャごとに変数を宣言する
(宣言がモジュール内で重複するのでコード全体が冗長になる)

Sub main ()
Call a
Call b
End Sub

Sub a ()
Dim ws As Worksheet
set ws=Workbooks("sample.xlsx").Sheets(1)
ws.Cells(1,1)="処理1"
End Sub

Sub b ()
Dim ws As Worksheet
set ws=Workbooks("sample.xlsx").Sheets(1)
ws.Cells(2,1)="処理2"
End Sub

②Functionを使う
(F8で1行ずつ実行したときに上下に移動しまくるのでデバッグのとき混乱する)

Function ws () As Worksheet
set ws=Workbooks("sample.xlsx").sheets(1)
End Function

Sub main ()
Call a
Call b
End Sub

Sub a ()
ws.Cells(1,1)="処理1"
End Sub

Sub b ()
ws.Cells(2,1)="処理2"

③プロシージャに引数を入れてプロシージャ間で変数を受け渡す
(メインのプロシージャでCallしないプロシージャについてはそのプロシージャで再度宣言しなければいけない。例:Sub c)

Sub main ()
Dim ws As Worksheet
set ws=Workbooks("sample.xlsx").Sheets(1)
Call a (ws)
Call b (ws)
End Sub

Sub a (ws As Worksheet)
ws.Cells(1,1)="処理1"
End Sub

Sub b (ws As Worksheet)
ws.Cells(2,1)="処理2"
End Sub

Sub c ()
Dim ws As Worksheet
set ws=Workbooks("sample".xlsx).Sheets(1)
ws.Cells(3,1)="処理3"
End Sub

Comments

[meg_]

③で良いと思います。関数cについても呼び出し元で引数を渡す形で良いとおもいますが？「そのプロシージャで再度宣言しなければいけない」がよく分かりませんでした。

Answer 1

特定のシートしか対象にしない（変数に最初にセットして、以降で再代入はあり得ないケース）なら、
④グローバル変数（モジュールレベル変数）を使って、メイン処理でセットして、以降そのまま
でしょうか。このケースだと、①②③はちょっとあり得ない印象です。

①③は、同じ物に複数の場所でそれぞれ名前を付けるので、ちょっといやです。
②はそのデメリットは無いですが、常に同じ値なら関数でなく変数にするのが自然でしょう。

異なるシートの再代入が発生するケースでは、不用意に使うとグローバル変数のデメリットが出るので、よく考えて設計する必要があります。

Comments

[BlackCacao]

ありがとうございます!
一度定数で指定できるか試してみて失敗したので、グローバル変数もダメだという思い込みがあって試してみていませんでした。勉強になりました。

Answer 2

個人的には、③です。
(メインのプロシージャでCallしないプロシージャについてはそのプロシージャで再度宣言しなければいけない。例:Sub c)
についてですが、sub cは、他のプロシージャ（例 sub x）から呼び出されるなら、メインはsub xを呼び出しているはずなので、sub xを呼び出すときに、wsを引数にして呼び出し、sub xは、そのwsを引数にしてsub cを呼び出せばよいかと。（メインがsub xを呼び出さない場合は、そもそもsub cはどこからも呼び出されないことになるので、プロシージャを作成した意味がなくなる。）

上記③が一般的かと思いますが、Sheets(1)以外に、他の共有すべき、オブジェクト（又は変数）がたくさん増えたとき、どうするかという問題があります。
例えば、Sheets(1),Sheets(2),Sheets(3),Sheets(4),Sheets(5),Sheets(6)をパラメータとして渡すような場合です。
メイン
call a(ws,ws,2,ws3,ws4,ws5,ws6)
サブプロシージャ
Sub a (ws As Worksheet,ws2 As Worksheet,...省略....,ws6 As Worksheet)
としても構いませんが、
非常にパラメータが多くなると、記述が大変になります。
その場合、上記のパラメータ渡しの変数を共有する方法があります。
下記が、その例です。
モジュールの先頭で変数を定義します。
Option Explicit
dim ws as worksheet
dim ws2 as worksheet
・・・省略・・・
dim ws6 as worksheet

Sub main ()
set ws=Workbooks("sample.xlsx").Sheets(1)
set ws2=Workbooks("sample.xlsx").Sheets(2)
・・・省略・・・
set ws6=Workbooks("sample.xlsx").Sheets(6)
Call a
Call b

sub a ,sub b, sub c
では、ws,ws2,....,ws6をそのまま使用可能。
ws,ws2,....,ws6は、当該モジュール内で、共有されており、このモジュールシート内の全てのプロシージャで参照／更新が可能です。

Comments

[BlackCacao]

ありがとうございます!
パラメータが少ないときは引数で指定して、パラメータが多くなるときはグローバル変数を使用する方法もあるのですね。勉強になりました。