Excel VBA サブルーチン関連

質問

複数のマクロが標準モジュールに書かれているとします。（例：Module1～Module10）
このモジュールを順番に実行するマクロを作成したいのですが、モジュールの名前を指定してサブルーチンコールは出来ないようです。
全部のモジュールを数珠つなぎにして１つのモジュールにまとめる方法は気が進みません。
モジュールの名前を指定してサブルーチンコールするようなスマートな方法はないでしょうか？

回答No.4

WindFaller · ベストアンサー

#3の回答者です。

各々のシート・オブジェクトに対するものは、ActiveX コントーロールのコマンドボタンを使うというのが通例です。標準モジュールをそのお使いのボタンは、フォームコントロールのボタンをお使いのようです。

フォームボタンの場合は、少し違った使い方をします。フォームボタンは、マクロを兼用で使えるという利点があります。ActiveX コントロールの場合は、同じように出来ますが、かなり難しいコードになってしまいます。

例えばこのようにします。

シート1 に、「ボタン 1」
シート2 に、「ボタン 2」

このボタンには、両方とも、Test1 というマクロを登録しておきます。
以下は簡単な例ですが、このようにして、押したボタンの違いを分けることが可能です。

'標準モジュール
Sub Test1()
　 Select Case Application.Caller()　'←Caller を利用する。
　　　Case "ボタン 1": MsgBox "シート1 からです"
　　　Case "ボタン 2": MsgBox "シート2 からです"
　 End Select
End Sub

もちろん、Sub ボタン1_Click ()となっているのは、単なるプロシージャですから、
連続して実行するなら、以下のように、並べるだけで結構です。それぞれのモジュールに入っていても、名前が同じでなければ、そのままプロシージャ名だけを書けばよいです。Callを使うのは、特に、引数（パラメータ）がある時に、間違いないように使うためです。できれば、そう習慣づけたほうが間違いが少ないです。

Sub Main()
　ボタン1_Click　'←　一意（同じものがない）の名前にする
　ボタン2_Click
End Sub

>自分としては Module n の名前を見て Sheet n で使用しているマクロがここに書いてあるくらいの感覚で分かり易いのですが。

やはり、標準モジュールは、そのようにして増やさないほうがよいかと思います。一般の方に、強制する理由などはないのですが、シートモジュールがあるのに、そのような使い方はしないのです。

それから、これは、VBAを扱う人ならほとんどの人は知っていることですが、Application.Run は、他のブックのマクロを実行する時に使います。

「他ブックのマクロを実行する」
http://officetanaka.net/excel/vba/tips/tips09.htm

マクロの詳しくない方には、ちょっと難しいかもしれませんが、Application.Run と Call ステートメントでは、以下のように、参照渡しを期待しても、それが出来ないことがあります。だから、ミスをなくすために、Call を使うのです。

'//
Sub Test1()
　Dim a As Long
　a = 10
　Application.Run "Macro1", a
　MsgBox a　'値渡しになってしまいました。
End Sub

Sub Test2()
　Dim a As Long
　a = 10
　Call Macro1(a)
　MsgBox a
End Sub

Sub Macro1(ByRef arg As Long) '参照渡し（明示的にByRefを入れました）
　arg = 10 * 10
End Sub

'//
今回のように単独で使う分には、特に問題は発生しませんが、なるべくコンパクトに分かりやすいように書くのが良いかと思います。

お礼

詳しく説明していただき感謝します。

回答を読みながら、まだVBAが理解出来てないなと感じています。
覚えなくてはならない項目も沢山ありますが、先ずは説明していただいたような基本的な所を勉強しようと思います。

すでに定年を過ぎたシニアの趣味なのでぼちぼちやります。

回答No.3

WindFaller

こんにちは。

最初に、標準モジュールに書かれているマクロなら、どこに置いてもよいのですが、
ある程度のレベルの人たちは、モジュールは、その作業内容によって分けます。それは、修正しやすいさからです。だから、Module10 とか、10個もモジュールになるということは、まずありえません。管理しにくくなります。あまりモジュールを増やすことはやめたほうがよいです。せいぜい、5個ぐらいです。これ全部で、数千行から、何万行というコードを入れていることもあります。

>全部のモジュールを数珠つなぎにして１つのモジュールにまとめる方法は気が進みません。

それはどちらでもよいのですが、サブルーチンなら、同じモジュールに書きますが、サブプローシージャなら、別々のモジュールに置くことあります。時々、モジュールに1つのプロシージャしか入れていない人がいますが、これはあまり良くない使い方です。

サブルーチンは、もともと、ひとつの流れの中で、編集しやすいように抜き出したコードのことですから、同じモジュールにないとややこしくてしょうがありません。サブプロシージャは、別のモジュールに置くことも多いです。

http://kabu-macro.com/vba_kiso/vba_subprocedure.html

#2さんのおっしゃるとおり、
>マクロ(サブルーチン)を実行するマクロの記述は、
>マクロ名を記述するだけでOKです。
サブルーチンなら、マクロ名しか書かないのが一般的です。

しかし、この質問は、コードがどのようなものか分かりませんが、おそらくは、以下のような書き方をします。通常は、同じプロジェクト内では、プロシージャ名は一意の名前を入れるというのがルールですが、この場合は、同名でも、プロシージャが違えば特定できます。（もちろん、この場合は、サブルーチンとは呼びませんし、サブルーチンが別のモジュールにあるというのは不自然です。）

一意の名前ですと、そのままプロシージャ名を書いて平気ですが、そうでない場合は、Mudule名から入れます。ちょうど、メソッドと同じ扱いになります。

Call を入れるのは、サブプロシージャのパラメータの括弧の有無で、参照渡しか、値渡しか混乱することがあるので、Call して、サブプロシージャの本来持っている仕様によって、間違いないようにするためです。

'Module1

Sub Main()
　Dim a As Long
　a = 20
　Call Module2.Macro1(a)
　Call Module3.Macro1(a)　'Call抜きと比較してみてください。
　MsgBox a　'そのままなら、40と出るはずです。
End Sub

'Module2
Sub Macro1(a As Long)
　 a = a * 10
End Sub

'Module3
Sub Macro1(a As Long)
　 a = a / 5
End Sub

補足

回答No.1のお礼欄に今回の質問に至ったいきさつを書いておりますのでご覧いただければ幸いです。

＞モジュールは、その作業内容によって分けます。それは、修正しやすいさからです。だから、Module10 とか、10個もモジュールになるということは、まずありえません。

この件についてもう少し教えてください。

当初は１～２枚のシートでスタートしたものが、段々欲が出て（仕様が膨らんで）徐々にシートの枚数が増えてしまうことはよくあることだと思います。
（1枚のシート上に多数のテーブルやグラフを配置すると画面スクロールが頻繁になるので、なるべく別シートを使用するような EXCEL の使い方をしています。）

例えばシート3までは問題なく使えるとして、新たにシート4を作成してこのシートで動作するマクロを作成するとき従来のモジュールに追記する形ではなく、標準モジュールを追加してここにマクロを書く方法を取ってきたためにシート枚数と同数以上の標準モジュールが出来てしまいました。
自分としては Module n の名前を見て Sheet n で使用しているマクロがここに書いてあるくらいの感覚で分かり易いのですが。
強いて言うなら、ほぼ同じ処理を行っている Sub プロシージャが各モジュールの中に重複して書かれているので無駄にプログラムが長くなっていることはあると思います。

ご意見をお聞かせください。

回答No.2

米沢 栄蔵（@YON56）

マクロ(サブルーチン)を実行するマクロの記述は、
マクロ名を記述するだけでOKです。
マクロ名に漢字等の文字を使用してもOK。
モジュール名を指定する必要はありません。

Sheets(1).Select
マクロ名
Sheets(2).Select

メインのデータ(A,B)をサブルーチンに送りたいときは、
マクロ名　A,B

但し、サブルーチン側のマクロ名の後の()の中にA,Bをもらい受ける記述が必要
マクロ名(A,B)
また、そのマクロ内で、A,BをDimしないことも必要。

お礼

回答ありがとうございました。

回答No.1

keithin

こんばんは。

＞複数のマクロが標準モジュールに書かれているとします。

１枚のModule1の上に、MAINとMacro1から5までの「プロシジャ」があるとします。

sub main()
　dim i
　for i = 1 to 5
　　application.run "Macro" & i
　next i
end sub

それとも？
＞モジュールの名前を指定してサブルーチンコール

誤記じゃないならModule1から5にいずれもMacro1があるとします。

sub main2()
　dim i
　for i = 1 to 5
　application.run "Module" & i & ".Macro1"
　next i
end sub

お礼

すばやい回答ありがとうございます。

当方はEXCELもVBAも本格的に使った経験がありません。
３０年以上前にアセンブラーを使用した簡単なプログラミング経験、その後に機械制御用PLCのプログラミング経験はあります。
以前よりEXCELというソフトは本当にすごいなと思っており、最近EXCELおよびVBAの勉強を始めたところです。

前置きが長くなりましたが、今回の質問に至ったいきさつについて説明します。
同一のブックに数枚のシートがあり、シート毎に作成した別々のマクロをmodule1、module2、－－と別のモジュールとして標準モジュールに格納しています。
各シートにはマクロ釦を配置してそのシートに対応するマクロを実行するようになっています。
現在は、初めにシート１を開いてマクロ釦を押して処理を実行後、その結果を持ってシート２に移動してマクロ釦を押して処理を実行、また次のシートに移動して処理　を順次繰り返して最終の結果が出る状況です。
この「シートを開いてマクロ釦を押す」という一連の処理を自動的に実行するマクロを作成する途中行き詰まりました。
自分の勝手なイメージでは、モジュール名を指定するとそのモジュール内の先頭のプロシージャから順番に処理されるものと思い込んでいたので、
エラーメッセージ：モジュールではなく、変数またはプロシージャを指定してください
を見て、何故モジュール名でサブルーチンコール出来ないの？　で思考停止に陥りました。

結論的には「application.run "Module" & i & ".Macro1"」というヒントを頂いてほぼ解決しております。
確認作業をしていたためお礼が遅くなりました。