記事の概要
ちょっとマクロを作りたいってなった時に
よく使うやつをメモ書きしたもの
ショートカットキー
キー | 機能 |
---|---|
[Alt]+[F11] | コードエディタを開く1 |
よく書くコード
おまじない系
画面描画の停止
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上でのショートカット ↩