私がExcelVBAでよく使う便利なコード・スニペットまとめ

コードってその人の癖とかあると思うんですが、私が個人的によく使っているモノ、更によく使うんだけどアレどうやって書くんだっけ…！みたいなものもまとめてみました。

セル・シート・ブック操作

クリア

Range("A1").Clear '値・書式設定・罫線などすべてクリア
Range("A1").ClearContents '値だけクリア

色

Range("A1").Font.ColorIndex = xlAutomatic '文字色を自動に
Range("A1").Font.ColorIndex = 2 '文字色変更（インデックス表記）
Range("A1").Font.Color = RGB(0, 0, 0) '文字色変更（RGB表記）

Range("A1").Interior.ColorIndex = xlNone '背景色をなしに
Range("A1").Interior.ColorIndex = 2 '背景色変更（インデックス表記）
Range("A1").Interior.ColorIndex = RGB(0, 0, 0) '背景色変更（RGB表記）

連続したデータが入っている範囲の最終端を取得

n = Range("A1").End(xlDown).Row '縦方向
n = Range("A1").End(xlToRight).Column '横方向

シートで使われているセルの最終端を取得

n = ActiveSheet.UsedRange.Columns.count '最終行
n = ActiveSheet.UsedRange.Rows.count '最終列

変数を含んだ範囲指定

Range(Cells(a, b), Cells(c, d)).Select

選択されてる範囲の一部を取得

n = Selection.Cells(1).Row '最初のセルの行
n = Selection.Cells(Selection.Count).Row '最後のセルの行
n = Selection.Cells(1).Column '最初のセルの列
n = Selection.Cells(Selection.Count).Column '最後のセルの列

ファイルを開く

Workbooks.Open "ファイル名.xls"
Workbooks.Open Filename:="ファイル名.xls", ReadOnly:=True '読み取り専用で開く

ファイルを閉じる

Workbooks("ファイル名.xls").Close
Workbooks("ファイル名.xls").Close saveChanges:=True '保存して閉じる
Workbooks("ファイル名.xls").Close saveChanges:=False '保存しないで閉じる

保存

Workbooks("ファイル名.xls").Save '上書き保存
Workbooks("ファイル名.xls").SaveAs "新ファイル名" '別名保存

コピペ

Range("A1").Copy 'コピー
Range("A1").PasteSpecial 'ペースト
Range("A1").PasteSpecial Paste:=xlPasteValues '値だけペースト
Range("A1").PasteSpecial Paste:=xlPasteFormats '書式だけペースト
Range("A1").AutoFill Destination:=Range("A1:A5") 'オートフィル
Application.CutCopyMode = False 'コピーモード解除

ファイル・フォルダ操作

'ファイル名変更
Name 変更前のファイル名(フルパス) As 変更後のファイル名(フルパス)
'ファイルコピー
FileCopy コピー前のファイルのフルパス, コピー後のファイルのフルパス
'ファイル削除
Kill 対象ファイルのフルパス
'フォルダ作成
MkDir パス名

ファイル・フォルダの存在場所

str = ThisWorkbook.Path '現在操作しているブックのパス
str = ThisWorkbook.Name '現在操作しているブックのファイル名
str = ThisWorkbook.FullName '現在操作しているブックのフルパス

文字列操作

連結

str = "サンプルテキスト" & smp_txt & "sampletext" '変数が混ざっても大丈夫

数値を文字列に変換

str = CStr(n) '変数nは数値であること

総文字数を取得

n = Len(対象文字列)

文字の抜き出し

str = Left(対象文字列, n) '対象文字列の左からn文字抜き出す
str = Right(対象文字列, n) '対象文字列の右からn文字抜き出す
str = Mid(対象文字列, n, i) '対象文字列の左からn文字目からi文字抜き出す

置換

str = Replace(対象文字列, 置換前文字, 置換後文字)
'例
str = Replace(str, " ", "")
str = Replace(str, "　", "")
'↑よくこうやって半角スペース、全角スペースを取り除いています

含まれているか

n = InStr(対象文字列, 探す文字列)
'見つかればその最初の文字数を返し、見つからなければ0を返す

日付のあれこれ

日付のフォーマット変更

str = Format(対象物(Dateなどの日付), "yyyy/mm/dd")

PCの設定によってDateで取得した日付のフォーマットがバラバラだったりするので…

日付の計算

d = DateAdd(設定値, 計算数, 対象)
'設定値：年→"yyyy", 月→"m", 日→"d", 週→"ww", 時→"h", 分→"n", 秒→"s"

'例
d = DateAdd("d", 1, Date) '1日プラス
t = DateAdd("h", -1, Time) '1時間マイナス

ちょっとしたスニペット

「ファイルを開く」ウインドウを出す

CreateObject("WScript.Shell").CurrentDirectory = 任意のパス '開くフォルダを指定
str = Application.GetOpenFilename("ﾌｧｲﾙ,*.*")
'strには選択されたファイルのフルパス、キャンセル時にはFalseが返る
'strをStringで宣言しているときには
If str = "False" Then Exit Sub
'のように""で括ってエラー処理をする

フォルダ・ファイルの検索

str = Dir(対象物, 属性) '属性：0→ファイル,16→フォルダ
'存在する場合はその名前を、存在しなければ""を返す

'例
If Dir(fol, 16) = "" Then 'フォルダが存在しなければ
  If MsgBox("該当フォルダが存在しません。作成しますか？", vbOKCancel) = 1 Then
    MkDir fol 'フォルダ作成（変数folはフルパスであること）
  End If
End If

シート内検索

Dim fnd As Range, str As String
Dim row1 As Integer, col1 As Integer

str = "sample" '検索文字列

Set fnd = Range("A1:C100").Find(str) '検索
If Not fnd Is Nothing Then '見つかったとき
  fnd.Font.ColorIndex = 3 '該当セルのフォントを赤に
  row1 = fnd.Row '該当セルの行取得
  col1 = fnd.Column '該当セルの列取得
End If

省略

Sheets("sheet1").Range("A1").Interior.ColorIndex = 6 'セルの色
Sheets("sheet1").Range("A1").RowHeight = 20 'セルの高さ
Sheets("sheet1").Range("A1").ColumnWidth = 10 'セルの幅

こういう横に長くなってしまうコードを

With Sheets("sheet1").Range("A1")
  .Interior.ColorIndex = 6 'セルの色
  .RowHeight = 20 'セルの高さ
  .ColumnWidth = 10 'セルの幅
End With

withを使ってまとめるとスッキリ！

Dim obj As Object '変数をオブジェクトで宣言
Set obj = Sheets("sheet1").Range("A1") '変数にセット

obj.Interior.ColorIndex = 6 'セルの色
obj.RowHeight = 20 'セルの高さ
obj.ColumnWidth = 10 'セルの幅

こんなふうにもかけます。

メッセージボックス

MsgBox ("サンプルテキスト") 'OKのみ
n = MsgBox("サンプルテキスト", vbOKCancel) '戻り値(n)：OK→1, キャンセル→2
n = MsgBox("サンプルテキスト", vbYesNoCancel) '戻り値(n)：はい→6, いいえ→7, キャンセル→2
n = MsgBox("サンプルテキスト", vbYesNo) '戻り値(n)：はい→6, いいえ→7

'例
If MsgBox("○○です。続けますか？", vbOKCancel) <> 1 Then End
'↑yes以外が選択されたときは処理を終了します

高速化

Application.ScreenUpdating = False
'処理
Application.ScreenUpdating = True

かなり駆け足で紹介してみました！使いやすいように省いてある値もあったりするので、思うようにいかないときはググったりしてみてください！