えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

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

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

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

不完全な記事で申し訳ないが許してほしい。

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の引数は値を受け取れないが壊される、と考えたほうがいいかもしれません。


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


ソースコード(Application.Run)

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

'https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13144183728
'一般的なApplication.Runの場合
Sub Test_ApplicationRun()
    Dim num As Variant
    
    Dim xlApp_App As Application:   Set xlApp_App = Application
    Dim xlApp_Obj As Object:        Set xlApp_Obj = Application
    Dim xlApp_Var As Variant:       Set xlApp_Var = Application
    
    num = 9999
    Application.Run "Test_Proc_Sub", num
    Debug.Print "Application", num '9999
    
    num = 9999
    xlApp_App.Run "Test_Proc_Sub", num
    Debug.Print "xlApp_App", num '9999
    
    num = 9999
    xlApp_Obj.Run "Test_Proc_Sub", num
    Debug.Print "xlApp_Obj", num '8888
    
    num = 9999
    xlApp_Var.Run "Test_Proc_Sub", num
    Debug.Print "xlApp_Var", num '8888
End Sub

Sub Test_Proc_Sub(ByRef num As Variant)
    num = 8888
End Sub


実行結果

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

Application     9999 
xlApp_App      9999 
xlApp_Obj      8888 
xlApp_Var      8888 

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

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

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

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


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

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

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

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

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

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


ソースコード(Application._Run2)

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

Sub Test_Proc_Sub(ByRef num As Variant)
    num = 8888
End Sub


結果

Application       9999 
xlApp_App      9999 

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

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


解説

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

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

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

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


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

CallByNameならば呼ぶことができるのだとか。

ならば・・・


ソースコード(CallByName)

'CallByNameを使ってみる
Sub Test_ApplicationRun2_2()
    Dim xlApp_Var As Variant:       Set xlApp_Var = Application
    Dim num As Variant
    
    num = 9999
    Call CallByName(xlApp_Var, "Run", VbMethod, num)
    Debug.Print "CallByName - Run", num '9999
    
    num = 9999
    Call CallByName(xlApp_Var, "_Run2", VbMethod, num)
    Debug.Print "CallByName - _Run2", num '9999
End Sub

結果

CallByName - Run    9999 
CallByName - _Run2   9999 

実行時エラーは出なくなったが、値渡しのようになってしまった。

これでは当初の目的を満たさないので使えない。


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

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

しかし、そもそもオブジェクト型を経由すれば、ByValによる値渡しでもプロシージャ側で値を書き換えることが可能である。

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


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

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

Sub Test_Proc_Sub_Class(ByVal cls As clsTest)
    cls.Num = 8888
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     8888 
xlApp_App      8888 
xlApp_Obj      8888 
xlApp_Var      8888 

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

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


まとめ

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

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

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

できるだけ「参照渡しをしたいなら、通常の方法でプロシージャを実行する」方法を考えるべきなのだと思う。

それでも「Application.Runを使いたいなら、参照渡しは諦める」か、オブジェクト型を値渡しするのが無難ということになりそうだ。

以上


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

週1回の更新を目指して、頑張ってますので応援よろしくおねがいします!

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