VBAのApplication.Runで変数を参照渡しする方法

今回はVBAのApplication.Runを使用した場合のByRef参照渡しの方法について説明する。

ただし、本記事で紹介する方法には不明瞭な点があるため、あくまで覚えとして書き残しておく。

※対象読者：一般的なプロシージャで使うByRefの仕組みを分かっているものとする。

※引数を余分なカッコで囲うなどすると、参照渡しが引き継がれないのも理解していると尚良い。

きっかけ
Applicationを変数に格納するとRunの挙動が変化する
CallByNameでApplication.Runを実行すると参照が途切れない
- 実行結果
Application._Run2は実行出来ない場合がある
「参照渡し」ではなくオブジェクトを経由する
まとめ

f:id:Kotori-ChunChun:20190703010004p:plain

きっかけ

突然だが（いわゆるコールバック関数的な）VBAで任意の名称のプロシージャを呼び出す方法には、以下のような種類がある。

Application.Run
Application.OnTime
CallByName
インターフェースとクラスメソッド（型対応）

それぞれの手法についての解説は行わないので、気になる人は他所の記事を参照してほしい。

たとえば、VBAでコールバック関数を使ういろんな方法　とか

今回作ろうとしたプログラムは、プロシージャの実行結果を引数に指定した変数で受け取るような操作である。

プロシージャの実行結果はできる限り戻り値にしたほうが可読性が高いのだが、データ量が大きかったり複数の値を返したいような場合には参照渡しを使って受け渡しをする場合がある。

とはいえ、一般的なプロシージャの呼び出し(Call)では、仮引数にByRefをつけることで参照渡しとなるため何も難しい話ではない。

しかし、このようなプロシージャを「Application.Runで実行する」と言った場面で、そう簡単にはうまくいかない事が分かったので再現方法と対策を説明する。

Applicationを変数に格納するとRunの挙動が変化する

参考資料

実際に違和感に気がついた時のコードが残っていないので省略するが、検索したらまさに同じ事を質問している記事が見つかったので、こちらを参照願いたい。

Application.runで参照渡しアクセス呼び出し側 Sub tes...

non********さん

2015/4/1118:42:10

_

また訂正です。何度も訂正して申し訳ありません。 Wordでは、引数(flg)がVariant型なら値渡し、Integer, Long, Double, Currencyでは参照渡しになるようです。他の型は試していません。 Applicationの型が関係ないという点では最初の回答と同じです。 Wordは2000と2013での結果です。

_

Excel2000でもやってみましたが、2010と同じでした。つまり、引数(flg)の型には影響を受けず、Applicationが直接記述かApplication型変数に入っているなら値渡し、ApplicationがObject型やVariant型に入っているなら参照渡しになります。

_

これまでの結果から想像するに、世代（といかバージョン）による違いはおそらくないと思われます。（が、これも検証が必要ですね）

_

しかしこんなにわけがわからない状態では、VBAを書く立場としては安全サイドで、つまりApplication.Runの引数は値を受け取れないが壊される、と考えたほうがいいかもしれません。

このときの私のツイートはこちら。

https://t.co/6Kgl8MSY8r で「ByRef」が働くときと働かない時があることが判明した。

おいおい、これは新発見じゃないか。
と思ったら既出でした。https://t.co/XBrbj7mVBP #VBA pic.twitter.com/AbKppd7Rta
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2019年7月2日

ソースコード(Application.Run)

先の知恵袋の回答を改変して、私の作成した検証用のコードがこれ。

Sub Test_Proc_Sub(ByRef Num As Variant)
    Num = "ByRef成功"
End Sub

'一般的なApplication.Runの場合
Sub Test_ApplicationRun()
    Dim Num As Variant
    
    Num = "未"
    Application.Run "Test_Proc_Sub", Num
    Debug.Print "Application", Num  '"未"
    
    Num = "未"
    Dim xlApp_App As Application:   Set xlApp_App = Application
    xlApp_App.Run "Test_Proc_Sub", Num
    Debug.Print "xlApp_App", Num '"未"
    
    Num = "未"
    Dim xlApp_Obj As Object:        Set xlApp_Obj = Application
    xlApp_Obj.Run "Test_Proc_Sub", Num
    Debug.Print "xlApp_Obj", Num '"ByRef成功"
    
    Num = "未"
    Dim xlApp_Var As Variant:       Set xlApp_Var = Application
    xlApp_Var.Run "Test_Proc_Sub", Num
    Debug.Print "xlApp_Var", Num '"ByRef成功"
End Sub

実行結果

上記プログラムを実行すると、イミディエイトウィンドウへ以下のように出力される。

Application    未
xlApp_App     未
xlApp_Obj     ByRef成功
xlApp_Var     ByRef成功

どうやら、「ApplicationをVariantやObject型変数に格納してからプロシージャを呼ぶと参照渡しができる」らしい。

理由はさっぱりわからない。

ちなみに、ローカルウィンドウでこれらの変数の型をチェックするとこんな状態であった。

使用方法	変数の宣言の型	結果	ローカルウィンドウで見た型
Application	-	参照失敗	Object/Application
xlApp_App	Excel.Application	参照失敗	Application/Application
xlApp_Obj	Object	参照渡し	Object/Application
xlApp_Var	Variant	参照渡し	Variant/Object/Application

CallByNameでApplication.Runを実行すると参照が途切れない

2020/7/19追記

指摘を受けて調査した結果、CallByNameなら必ずByRefが働く事が判明した。

'CallByNameでRunを呼んだ場合
Sub Test_ApplicationRun_CallByName()
    Dim Num As Variant
    
    Num = "未"
    Call CallByName(Application, "Run", VbMethod, "Test_Proc_Sub", Num)
    Debug.Print "Application", Num  '"ByRef成功"
    
    Num = "未"
    Dim xlApp_App As Application:   Set xlApp_App = Application
    Call CallByName(xlApp_App, "Run", VbMethod, "Test_Proc_Sub", Num)
    xlApp_App.Run "Test_Proc_Sub", Num
    Debug.Print "xlApp_App", Num '"ByRef成功"
    
    Num = "未"
    Dim xlApp_Obj As Object:        Set xlApp_Obj = Application
    Call CallByName(xlApp_Obj, "Run", VbMethod, "Test_Proc_Sub", Num)
    Debug.Print "xlApp_Obj", Num '"ByRef成功"
    
    Num = "未"
    Dim xlApp_Var As Variant:       Set xlApp_Var = Application
    Call CallByName(xlApp_Var, "Run", VbMethod, "Test_Proc_Sub", Num)
    Debug.Print "xlApp_Var", Num '"ByRef成功"
End Sub

Sub Test_Proc_Sub(ByRef Num As Variant)
    Num = "ByRef成功"
End Sub

実行結果

上記プログラムを実行すると、イミディエイトウィンドウへ以下のように出力される。

Application    ByRef成功
xlApp_App     ByRef成功
xlApp_Obj     ByRef成功
xlApp_Var     ByRef成功

心配な人はCallByNameを使っておけば良さそうだ。

Application._Run2は実行出来ない場合がある

今回の問題とは「別件だろう」という見解ではあるが、せっかく検証したのでこちらについても記載しておく。

F2でオブジェクトブラウザを開き、Excel.Applicationのメンバを閲覧すると、_Run2という見かけないものが存在する。

※標準では非表示メンバなので見当たらないが、適当な場所で右クリックして「非表示のメンバを表示」にチェックを入れると表示される。

パラメータを見る限りはApplication.RunとApplication._Run2に差異はなさそうだった。

早速、上記コードを置き換えると以下のようになる。

ソースコード(Application._Run2)

'非公開メンバApplication._Run2の場合
Sub Test_ApplicationRun2()
    Dim Num As Variant
    
    Num = "未"
    Application.[_Run2] "Test_Proc_Sub", Num
    Debug.Print "Application", Num '"未"
    
    Num = "未"
    Dim xlApp_App As Application:   Set xlApp_App = Application
    xlApp_App.[_Run2] "Test_Proc_Sub", Num
    Debug.Print "xlApp_App", Num '"未"
    
    Num = "未"
    Dim xlApp_Obj As Object:        Set xlApp_Obj = Application
    xlApp_Obj.[_Run2] "Test_Proc_Sub", Num
    Debug.Print "xlApp_Obj", Num '実行時エラーで到達不能
    
    Num = "未"
    Dim xlApp_Var As Variant:       Set xlApp_Var = Application
    xlApp_Var.[_Run2] "Test_Proc_Sub", Num
    Debug.Print "xlApp_Var", Num '実行時エラーで到達不能
End Sub

'CallByNameを使って[_Run2]を呼び出した場合
Sub Test_ApplicationRun2_CallByName()
    Dim Num As Variant
    
    Num = "未"
    Call CallByName(Application, "_Run2", VbMethod, "Test_Proc_Sub", Num)
    Debug.Print "Application", Num '"未"
    
    Num = "未"
    Dim xlApp_App As Application:   Set xlApp_App = Application
    Call CallByName(xlApp_App, "_Run2", VbMethod, "Test_Proc_Sub", Num)
    Debug.Print "xlApp_App", Num '"未"
    
    Num = "未"
    Dim xlApp_Obj As Object:        Set xlApp_Obj = Application
    Call CallByName(xlApp_Obj, "_Run2", VbMethod, "Test_Proc_Sub", Num)
    Debug.Print "xlApp_Obj", Num '"未"
    
    Num = "未"
    Dim xlApp_Var As Variant:       Set xlApp_Var = Application
    Call CallByName(xlApp_Var, "_Run2", VbMethod, "Test_Proc_Sub", Num)
    Debug.Print "xlApp_Var", Num '"未"
End Sub

Sub Test_Proc_Sub(ByRef num As Variant)
    Num = "ByRef成功"
End Sub

結果

Application    未
xlApp_App     未

> xlApp_Obj.[_Run2] "Test_Proc_Sub", num
実行時エラー'450'
引数の数が一致していません。または不正なプロパティを指定しています。

と、まあ、実行時エラーが出て最後までは実行できない。

CallByNameの方は、エラーは出なかったものの全て参照渡しに失敗している。

Application    未
xlApp_App     未
xlApp_Obj     未
xlApp_Var     未

解説

まず、非表示メンバとは、名称がアンダーバーから始まるプロシージャや変数などのことである。

Application._Run2を一般的な記法で呼び出そうとすると、コンパイルエラー「構文エラー」が出てプログラムが赤字になってしまう。

そこで、メンバの前後に角カッコつけてApplication.[_Run2]のようにすることでコンパイルできる状態になる。

「非表示メンバの表示」をONにしておけば、インテリセンスが効く様になる上に、自動的に角カッコもつけてくれるので安心である。

実行時エラーが出る原因は私には良くわからないが、Iさんによると、非表示メンバは角カッコを使っても呼ぶことが出来ないモノらしい。

「参照渡し」ではなくオブジェクトを経由する

ここまでの検証では、プリミティブなデータを使用したのでByRefによる参照渡しが必要となっていた。

しかし、そもそもオブジェクト型を経由すれば、ByValによる値渡しだろうとByRefの参照が途切れようと呼び出し先で値を書き換えることが可能である。

データ型はオブジェクト型なら何でも良いのだが、好きな名前のメンバを作れるということで自作クラスを使用したサンプルで紹介する。

ソースコード(clsTest使用例)

'クラスを使用する
Sub Test_ApplicationRun_Class()
    Dim cls As clsTest: Set cls = New clsTest
    
    cls.Num = "未"
    Dim xlApp_App As Application:   Set xlApp_App = Application
    Application.Run "Test_Proc_Sub_Class", cls
    Debug.Print "Application", cls.Num  '"未"
    
    cls.Num = "未"
    xlApp_App.Run "Test_Proc_Sub_Class", cls
    Debug.Print "xlApp_App", cls.Num '"未"
    
    cls.Num = "未"
    Dim xlApp_Obj As Object:        Set xlApp_Obj = Application
    xlApp_Obj.Run "Test_Proc_Sub_Class", cls
    Debug.Print "xlApp_Obj", cls.Num '"ByRef成功"
    
    cls.Num = "未"
    Dim xlApp_Var As Variant:       Set xlApp_Var = Application
    xlApp_Var.Run "Test_Proc_Sub_Class", cls
    Debug.Print "xlApp_Var", cls.Num '"ByRef成功"
End Sub

Sub Test_Proc_Sub_Class(ByVal cls As clsTest)
    cls.Num = "ByRef成功"
End Sub

ソースコード(clsTest)

Option Explicit

Private num_ As Variant

Property Get Num()
    Let Num = num_
End Property

Property Let Num(n As Variant)
    Let num_ = n
End Property

結果

Application    ByRef成功
xlApp_App     ByRef成功
xlApp_Obj     ByRef成功
xlApp_Var     ByRef成功

見事に全てのパターンにおいて、値の書き換えに成功した。

ただし、ここまでするなら引数よりも戻り値にしたほうが良いような気がする。

まとめ

Application.Runで参照渡しをするには、一旦ObjectかVariant変数に格納してからRunを実行するか、CallByNameで実行すれば良い事が判明した。

しかし、何故このような挙動をするのか分からなかったので、積極的に使うのは避けたほうが良いような気がする。

知恵袋によると、Officeの種類によっても挙動が異なるなんて話もあるので、さらなる検証が必要である。

永続的に動作が補償できるわけではないので、Runに頼らず通常の方法でプロシージャを実行する方法を考えるべきなのかもしれない。

どうしても必要ならば自己責任で使うか、オブジェクト型で受け渡しするのが無難ということになりそうだ。

以上

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)