【Excel VBA】シートの存在チェック③

前回に続き、シートの存在を確認する関数の話です。これまで、関数を2回修正してきて、機能としては向上しているものの、引数が増えたことで利便性が低下した話をしました。

今回は、その問題を解消していきます。以下にコードを示します。

【コード】

-------------------------------------------------------------------------------

Function GetShtIndex(ByVal key As String, Optional ByRef wb As Variant, Optional ByVal match As String = "完全") As Long

    Dim i As Long

    '====参照判定===='

    If IsMissing(wb) Then

        Set wb = ThisWorkbook

    End If

    '====エラー判定===='

    'キーワードが空白

    If key = "" Then

        GetShtIndex = -1

        Exit Function

    End If

    '引数がWorkbook型でない

    If TypeName(wb) <> "Workbook" Then

        GetShtIndex = -1

        Exit Function

    End If

    '====シート番号取得===='

    For i = 1 To wb.Sheets.Count Step 1

        Select Case match

            Case "完全"

                If wb.Sheets(i).Name = key Then

                    GetShtIndex = i

                    Exit Function

                End If

            Case "部分"

                If InStr(wb.Sheets(i).Name, key) <> 0 Then

                    GetShtIndex = i

                    Exit Function

                End If

            Case Else

                If wb.Sheets(i).Name = key Then

                    GetShtIndex = i

                    Exit Function

                End If

        End Select

    Next i

    GetShtIndex = 0

End Function

-------------------------------------------------------------------------------

まず初めに、関数名を変更しました。前回までは「ExistSheet 」でしたが、関数の役割が「シートが存在するか否か」ではなく、「シートがブックの何番目に存在するか」というものに代わっているため、「GetShtIndex」としています。

次に、2番目以降の引数にOptional を付けています。Optional を付けると、引数を省略できるようになります。このとき、値型の引数であれば、引数が省略された場合の初期値を設定できます。逆に、参照型の引数は初期値を設定できません。

例、Optional ByVal match As String = "完全"

　matchという引数が省略された場合は、「完全」を初期値として設定する。

ここで、例えば2番目の引数にOptional を付けた場合は、3番目以降の引数にもOptional を付ける必要があります。

参照型の引数に初期値を設定する場合は、IsMissing()関数を使用します。

今回のコードでは、引数が省略された場合の初期値としてThisWorkbook を設定しています。このとき、参照型の引数の型はWorkbook型ではなく、Variant型であることに注意してください。IsMissing()関数を使用するためには、Variant型を指定する必要があります。

そのためコード内で、引数で渡されたwbがWorkbook型であるかの判定を行っています。

今回、作成した関数の使用例を載せます。

１．現在のブックに、"あいう"というシートが存在するか

    If GetShtIndex("あいう") > 0 Then

        MsgBox "存在します"

    End If

２．家計簿というブックに、"10月"を含むシートが何番目に存在するか

    If GetShtIndex(key:="10月", wb:=家計簿, match:="部分") > 0 Then

        MsgBox "存在します"

    End If