記事の概要

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

ショートカットキー

キー	機能
[Alt]+[F11]	コードエディタを開く¹

よく書くコード

おまじない系

画面描画の停止

Application.ScreenUpdating = False

だいたいの場合、とりあえず書いとけば実行速度が早くなる。

オートフィルタのクリア

With Sheet1
  If .FilterMode Then .ShowAllData
End With

とりあえず一番最初に書いとけば「上から順番にアクセスしてたのにデータがスキップされてた」とかそういうのはなくなる。

Excel系

最終行を取得

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]+[↑]を押した時の行番号

注意点

狙ったセルより下のセルにデータが入っているとうまく動作しない。
空欄も含む列だとうまく動作しない可能性が高い。

表の右端の列を取得

With Sheet1
    Dim lc As Long
    Const HEADER_ROW As Long = 2

    lc = .Cells(HEADER_ROW, .Columns.count).End(xlToLeft).Column
End With

最終行取得の列バージョン。

ブックを開く時に「リンクの更新～～」を表示しない

Dim wb As Workbook
Dim path As String
path = "C:\hogehoge\test.xlsx"
Set wb = Workbooks.Open(filename:=path, UpdateLinks:=False)

ブックを保存して閉じる時に「プライバシーの注意～～」を表示させない

Application.DisplayAlerts = False
wb.Close True
Application.DisplayAlerts = True

フォルダ選択ダイアログ

Dim path As String
With Application.FileDialog(msoFileDialogFolderPicker)
    .InitialFileName = "C:\hogehoge\"
    If .Show = True Then
        path = .SelectedItems(1)
    End If
End With

path に選択されたフォルダのパスが入る。

Excelファイル選択ダイアログ

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 に選択されたファイルのパスが入る。

罫線をひく

With Sheet1
    .Range(.Cells(1, 1), .Cells(5, 3)).Borders.LineStyle = True
End With

A1からC5までのセルに罫線をひく

ファイルへのリンクを張り付ける

With Sheet1
    .Cells(1, 1) = "testファイル"
    .Hyperlinks.Add anchor:=.Cells(1, 1), Address:="C:\hogehoge\test.xlsx"
End With

セルをクリックするとそのファイルを開く。

ちょっと工夫系

処理にかかった時間を表示

Dim t As Single
t = Timer

'何かの処理

MsgBox "　実行完了(" & Round(Timer - t, 2) & " sec)"

処理終了時、メッセージボックスを表示する方が個人的にわかりやすい。

計測用タイマー

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

ブックを開く。すでに開いている場合はそれを選ぶ。

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

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を呼び出さないとマクロの中断ができないため、処理時間が長くなる場合は必須。でもループごとに呼び出すと処理時間がとても長くなるのでたまに呼び出したい、的な時に。

例外処理

Sub MySub()
    On Error GoTo MySubError

    '通常処理

    GoTo MySubFinally
MySubError:
    'エラー処理
    
MySubFinally:
    '後片付け
End Sub

ブックを開く前にエラー処理に飛ぶ→開いていないブックを閉じる処理→例外発生というコンボをよくやらかす。

年月日の文字列を取得

Dim str As String
str = Replace(Date, "/", "")

重複しないファイル名とかをつける時に。
例：20200210

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

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

年月日_時分秒の文字列を取得

Dim str As String
str = Replace(Replace(Replace(Now, "/", ""), ":", ""), " ", "_")

重複しないファイル名とかをつける時に。
例：20200210_160228

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

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

エディタ上ではなくExcel上でのショートカット ↩