小粋空間  -  a cheap joke and a play on words site

Excel VBAで関数名に変数を使う方法

Excel VBAで関数名に変数を使う方法を紹介します。

１．問題点

仕事で、セルに設定した1から10の値を関数名の一部（call_1,call_2…）として起動させる必要が生じました。

具体的には、次のように関数Testの中でセルの値を変数nameに保持し、その値を使って関数call_1やcall_2を起動させなければなりません。

Sub Test()
    Dim name As String
    Dim i As Integer
    For i = 2 To 11
        name = Worksheets("Sheet1").Cells(i, 2).value
        '???
    Next i
End Sub
 
Sub call_1()
    '...
End Sub
 
Sub call_2()
    '...
End Sub
 
（中略）
 
Sub call_10()
    '...
End Sub

もう少し簡単なサンプルに修正して不明点を示します。

変数nameの値「1」をそのまま関数名として扱い、関数call_1を起動したいということです。

Sub Test()
    Dim name As Integer
    name = 1 '1を関数名の一部として起動させたい
    '???
End Sub
 
Sub call_1()
    '...
End Sub

他の言語で、変数をそのまま関数名として扱うことがありますが、VBAではどのように記述すれば実現できるのか不明です。

２．VBAで関数名に変数を使う

関数名に変数を使うには、Application.Runメソッドを利用します。

次のようにApplication.Runの直後に文字列や変数を設定すれば、その文字列を関数とみなして起動してくれます。

Sub Test()
    Dim name As String
    name = 1
    Application.Run "call_" & name
End Sub
 
Sub call_1()
    MsgBox "OK"
End Sub

Application.Runは他のブックのマクロを起動するときに、

Application.Run "test.xls!Test1"

というような指定をするのですが、指定するブック名やマクロ名は文字列として設定します。

したがってこの部分に変数を設定すれば、変数展開された値が適用される仕組みになっているようです。

３．関数に引数を追加する

関数に引数を追加する場合は、関数名の後方にカンマ区切りで追加します。複数設定する場合は複数のカンマで区切ります。

Sub Test()
    Dim name As String
    name = 1
    Dim id As Integer
    id = 100
    Dim value As String
    value = "bar"
    Application.Run "call_" & name, id, value
End Sub
 
Sub call_1(id As Integer, value As String)
    MsgBox id & value
End Sub

４．その他

冒頭のサンプルでは、そもそもセルの値を関数名ではなく引数にして同じ関数を起動する（その中で処理を振り分ける）という代替案もあります。

Sub Test()
    Dim name As String
    Dim i As Integer
    For i = 2 To 11
        name = Worksheets("Sheet1").Cells(i, 2).value
        call(name)
    Next i
End Sub
 
Sub call(name As Integer)
    '...
End Sub

が、要件によっては今回のような起動方法が必要となる場合もあるので、覚えていて損はないテクニックだと思います。