Office TANAKA - Excel VBA Tips[プロシージャ名を[マクロ]ダイアログに表示させない]

下のようなマクロを作成したとします。

Dim SheetName As String
Sub Sample1()
    SheetName = InputBox("挿入するシート名を入力してください")
    If SheetName <> "" Then Call AddNewSheet
End Sub

Sub AddNewSheet()
    Dim ws
    For Each ws In Worksheets
        If ws.Name = SheetName Then
            MsgBox SheetName & "は存在します", vbExclamation
            Exit Sub
        End If
    Next ws
    Worksheets.Add
    ActiveSheet.Name = SheetName
End Sub

Sample1を実行すると、新しいシートの名前を入力するInputBoxが開き、[OK]ボタンをクリックすると、その名前の新しいワークシートを挿入します。新しいシート名を受け取る処理と、実際にワークシートを挿入する処理を、それぞれ独立したプロシージャとし、ユーザーが入力したシート名はSheetNameという広域変数でやり取りしています。

マクロの内容は、ここで本題ではありません。このように複数のプロシージャを記述していると、[ツール]-[マクロ]-[マクロ]で表示される[マクロ]ダイアログボックスに表示されてしまう…というのが、今回のテーマです。

新しいシートを挿入するために、正しく「Sample1」を実行してくれればいいのですが、事情のわからないユーザーは、うっかり「AddNewSheet」プロシージャを実行してしまうかもしれません。上記の例では、ただワークシートを挿入するだけですが、特定の条件で呼び出される前提のプロシージャが、直接実行されてしまうのは危険です。「AddNewSheet」プロシージャを[マクロ]ダイアログボックスに表示させないためには、どうしたらいいでしょう。

方法は2つあります。

まず正攻法から。[マクロ]ダイアログボックスに表示されるプロシージャは、Public Subプロシージャだけです。Private Subプロシージャは表示されませんので、横着せずにきちんと指定すればいいのです。モジュールシートで[Public/Private]を省略すると、自動的にPublicプロシージャとみなされます。

Dim SheetName As String
Sub Sample1()
    SheetName = InputBox("挿入するシート名を入力してください")
    If SheetName <> "" Then Call AddNewSheet
End Sub

Private Sub AddNewSheet()
    Dim ws
    For Each ws In Worksheets
        If ws.Name = SheetName Then
            MsgBox SheetName & "は存在します", vbExclamation
            Exit Sub
        End If
    Next ws
    Worksheets.Add
    ActiveSheet.Name = SheetName
End Sub

2つめは少々裏技的な方法です。[マクロ]ダイアログボックスにはPublicプロシージャがリストアップされますが、もうひとつ条件があります。それは、引数を受け取るプロシージャは[マクロ]ダイアログボックスに表示されないということです(一部例外を除く)。上記のマクロを次のようにすると、AddNewSheetプロシージャは表示されません。

Sub Sample1()
    Dim SheetName As String
    SheetName = InputBox("挿入するシート名を入力してください")
    If SheetName <> "" Then Call AddNewSheet(SheetName)
End Sub

Sub AddNewSheet(SheetName As String)
    Dim ws
    For Each ws In Worksheets
        If ws.Name = SheetName Then
            MsgBox SheetName & "は存在します", vbExclamation
            Exit Sub
        End If
    Next ws
    Worksheets.Add
    ActiveSheet.Name = SheetName
End Sub

しかし、このようにマクロを変更できるケースばかりではありません。中には、まったく何も引数を必要としないプロシージャもあるでしょう。そんなときは、ダミーの引数を受け取ることにすればいいのです。

Dim SheetName As String
Sub Sample1()
    SheetName = InputBox("挿入するシート名を入力してください")
    If SheetName <> "" Then Call AddNewSheet(1)
End Sub

Sub AddNewSheet(dummy)
    Dim ws
    For Each ws In Worksheets
        If ws.Name = SheetName Then
            MsgBox SheetName & "は存在します", vbExclamation
            Exit Sub
        End If
    Next ws
    Worksheets.Add
    ActiveSheet.Name = SheetName
End Sub

AddNewSheetプロシージャが受け取った引数dummyは、プロシージャ内で使われていない点に留意してください。また、ダミーの引数をParamArrayで宣言してやると、Call側で省略することも可能です。どちらの方法でも、AddNewSheetプロシージャは[マクロ]ダイアログボックスに表示されません。

Dim SheetName As String
Sub Sample1()
    SheetName = InputBox("挿入するシート名を入力してください")
    If SheetName <> "" Then Call AddNewSheet
End Sub

Sub AddNewSheet(ParamArray dummy())
    Dim ws
    For Each ws In Worksheets
        If ws.Name = SheetName Then
            MsgBox SheetName & "は存在します", vbExclamation
            Exit Sub
        End If
    Next ws
    Worksheets.Add
    ActiveSheet.Name = SheetName
End Sub

後半の方法は裏技的な発想です。まぁ、こんな対処もできるという知識として覚えておくといいでしょう。特に理由がない限り、Privateプロシージャにする正攻法をお勧めします。

関連項目
[関数の挿入]ダイアログに表示させない