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

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

Posted at March 28,2014 1:55 AM
Tag:[Excel, VBA]

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

1.問題点

仕事で、セルに設定した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ではどのように記述すれば実現できるのか不明です。

2.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"

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

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

3.関数に引数を追加する

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

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

4.その他

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

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

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

関連記事
zenback
人気エントリー
トラックバックURL


コメントする
greeting

*必須

*必須(非表示)


ご質問のコメントの回答については、内容あるいは多忙の場合、1週間以上かかる場合があります。また、すべてのご質問にはお答えできない可能性があります。予めご了承ください。

太字イタリックアンダーラインハイパーリンク引用
[サインインしない場合はここにCAPTCHAを表示します]

コメント投稿後にScript Errorや500エラーが表示された場合は、すぐに再送信せず、ブラウザの「戻る」ボタンで一旦エントリーのページに戻り(プレビュー画面で投稿した場合は、投稿内容をマウスコピーしてからエントリーのページに戻り)、ブラウザをリロードして投稿コメントが反映されていることを確認してください。

コメント欄に(X)HTMLタグやMTタグを記述される場合、「<」は「&lt;」、「>」は「&gt;」と入力してください。例えば「<$MTBlogURL$>」は「&lt;$MTBlogURL$&gt;」となります(全て半角文字)