Excel VBA を作る時によく使うものメモ

#記事の概要
ちょっとマクロを作りたいってなった時に
よく使うやつをメモ書きしたもの

#ショートカットキー

| キー | 機能 |
|:--|:--|
|[Alt]+[F11]|コードエディタを開く[^1]|

[^1]: エディタ上ではなくExcel上でのショートカット





#よく書くコード

##おまじない系
###画面描画の停止
```vb
Application.ScreenUpdating = False
```
だいたいの場合、とりあえず書いとけば実行速度が早くなる。





###オートフィルタのクリア
```vb
With Sheet1
  If .FilterMode Then .ShowAllData
End With
```
とりあえず一番最初に書いとけば「上から順番にアクセスしてたのにデータがスキップされてた」とかそういうのはなくなる。





##Excel系



###最終行を取得
```vb
With Sheet1
    Dim lr As Long
    Const TARGET_COLUMN As Long = 3

    lr = .Cells(.Rows.count, TARGET_COLUMN).End(xlUp).Row
End With
```
表の最後の行を取得する際などによく使う。

####意味
例だとC列の一番下のセルから[Ctrl]+[↑]を押した時の行番号
####注意点
狙ったセルより下のセルにデータが入っているとうまく動作しない。
空欄も含む列だとうまく動作しない可能性が高い。





###表の右端の列を取得
```vb
With Sheet1
    Dim lc As Long
    Const HEADER_ROW As Long = 2

    lc = .Cells(HEADER_ROW, .Columns.count).End(xlToLeft).Column
End With
```
最終行取得の列バージョン。






###ブックを開く時に「リンクの更新 ～～」を表示しない
```vb
Dim wb As Workbook
Dim path As String
path = "C:\hogehoge\test.xlsx"
Set wb = Workbooks.Open(filename:=path, UpdateLinks:=False)
```





###ブックを保存して閉じる時に「プライバシーの注意 ～～」を表示させない
```vb
Application.DisplayAlerts = False
wb.Close True
Application.DisplayAlerts = True
```





###フォルダ選択ダイアログ
```vb
Dim path As String
With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = "C:\hogehoge\"
    If .Show = True Then
        path = .SelectedItems(1)
    End If
End With
```
path に選択されたフォルダのパスが入る。





###Excelファイル選択ダイアログ
```vb
Dim path As String
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excelファイル", "*.xlsx"
    .InitialFileName = ThisWorkbook.path
    If .Show = True Then
        path = .SelectedItems(1)
    End If
End With
```
path に選択されたファイルのパスが入る。




###罫線をひく
```vb
With Sheet1
    .Range(.Cells(1, 1), .Cells(5, 3)).Borders.LineStyle = True
End With
```
A1からC5までのセルに罫線をひく



###ファイルへのリンクを張り付ける
```vb
With Sheet1
    .Cells(1, 1) = "testファイル"
    .Hyperlinks.Add anchor:=.Cells(1, 1), Address:="C:\hogehoge\test.xlsx"
End With
```
セルをクリックするとそのファイルを開く。





##ちょっと工夫系



###処理にかかった時間を表示
```vb
Dim t As Single
t = Timer

'何かの処理

MsgBox "　実行完了(" & Round(Timer - t, 2) & " sec)"
```
処理終了時、メッセージボックスを表示する方が個人的にわかりやすい。





###計測用タイマー
```vb
Dim t As Single
t = Timer

'何らかの処理1

Debug.Print Round(Timer - t, 2) & "sec:処理1"
t = Timer

'何らかの処理2

Debug.Print Round(Timer - t, 2) & "sec:処理2"
t = Timer

'以下、同様
```
処理にかかった時間を計測できる。
`例：`
`0.11sec:処理1`
`0.52sec:処理2`





###ブックを開く。すでに開いている場合はそれを選ぶ。
```vb
Dim wb As Workbook
Dim temp As Workbook
Dim found As Boolean
Dim path As String

path = "C:\hogehoge\macro.xlsx"
found = False

For Each temp In Workbooks
    If temp.FullName = path Then
        Set wb = temp
        found = True
        Exit For
    End If
Next
If Not found Then
    Set wb = Workbooks.Open(filename:=path)
End If
```
数十MBとかあるファイルを閉じたり開いたりしたくない場合など。




###一定時間ごとにDoEvents
```vb
Dim t As Single
t = Timer
For i = 1 To 10000

    '何かの処理

    If (Timer - t) > 2 Then
        DoEvents
        t = Timer
    End If
Next
```
だいたい2秒ごとにDoEventsを呼び出す。
`※DoEventsを呼び出さないとマクロの中断ができないため、処理時間が長くなる場合は必須。でもループごとに呼び出すと処理時間がとても長くなるのでたまに呼び出したい、的な時に。`





###例外処理
```vb
Sub MySub()
    On Error GoTo MySubError

    '通常処理

    GoTo MySubFinally
MySubError:
    'エラー処理
    
MySubFinally:
    '後片付け
End Sub
```
ブックを開く前にエラー処理に飛ぶ→開いていないブックを閉じる処理→例外発生というコンボをよくやらかす。




###年月日の文字列を取得
```vb
Dim str As String
str = Replace(Date, "/", "")
```
重複しないファイル名とかをつける時に。
`例：20200210`

※コメントより（ありがとうございます）
こっちのほうがいいかも

```vb
'年月日の文字列を取得
str = Format(Now, "yyyymmdd")
```



###年月日_時分秒の文字列を取得
```vb
Dim str As String
str = Replace(Replace(Replace(Now, "/", ""), ":", ""), " ", "_")
```
重複しないファイル名とかをつける時に。
`例：20200210_160228`

※コメントより（ありがとうございます）
こっちのほうがいいかも

```vb
'年月日_時分秒の文字列を取得
str = Format(Now, "yyyymmdd_hhnnss")
```