Help us understand the problem. What is going on with this article?

VBA便利関数まとめ

More than 3 years have passed since last update.

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

シート名取得

' アクティブブックの全シート名を取得し、配列で返します。
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関連も関数にして載せる。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした