VBA便利関数まとめ

個人的によく使うもの、ありそうでない関数をまとめていきます。

シート名取得

' アクティブブックの全シート名を取得し、配列で返します。
Public Function SheetNameCollect()

    Dim i As Long
    Dim SheetCnt As Long
    Dim SheetName() As String

    SheetCnt = ThisWorkbook.Sheets.Count

    ReDim SheetName(0 To SheetCnt - 1)

    For i = 0 To SheetCnt - 1
        SheetName(i) = Sheets(i + 1).Name
    Next i
    
    SheetNameCollect = SheetName

End Function

セルの座標(レンジ)を取得

特定シート内の特定の文字列を持つセルのレンジを取得する。

' 引数：key -> 検索文字列
' 引数：SheetName -> 検索するシート名
' 引数：range -> 行や列のレンジ(ex. 1:1)

Public Function SearchRange(key, SheetName, range) As String
    Dim c As Object
    Dim myKey As String, fAddress As String
    
    fAddress = ""
    myKey = key
    With Worksheets(SheetName).range(range)
        Set c = .Find(What:=myKey, LookIn:=xlValues, lookat:=xlPart, _
                          SearchOrder:=xlByColumns, MatchByte:=False)
        If Not c Is Nothing Then
            fAddress = c.Address
        End If
    End With
    
    SearchRange = fAddress
    
End Function

配列

配列内の特定要素のインデックス番号を取得する。

' 引数：TargetArray -> 添え字を確認したい配列
' 引数：element -> 要素(文字列等)
' 戻り値：インデックス番号

Public Function IndexOf(TargetArray, element)
    For i = 0 To UBound(TargetArray)
        If TargetArray(i) = element Then Exit For
    Next
    IndexOf = i
End Function

配列の要素を削除する。

' 引数：TargetArray -> 対象の配列
' 引数：deleteIndex -> 削除したい要素のインデックス番号

Public Sub ArrayRemove(ByRef TargetArray As Variant, ByVal deleteIndex As Integer)
    Dim i As Integer

    '削除したい要素以降の要素を前につめて上書きコピー
    For i = deleteIndex To UBound(TargetArray) - 1
        TargetArray(i) = TargetArray(i + 1)
    Next i

    '最後の要素を削除する（配列を再定義）
    ReDim Preserve TargetArray(UBound(TargetArray) - 1)
        
End Sub

アプリケーション起動

拡張子に紐付くアプリケーションを起動する。

' 引数：path -> 開きたいファイルのパス

Public Sub OpenFile(path)

    Dim WSH As Object
    
    Set WSH = CreateObject("Wscript.Shell")
    WSH.Run path, 3
    Set WSH = Nothing
    
End Sub

TODO

参考にしたページのURLを載せる。ADODB関連も関数にして載せる。