VBAの覚え書き

自分のメモ用に、更新情報に上がって来ないよう過去のnoteをこっそり編集。

■セル操作

セルの指定

Cells(行番号, 列番号)　※単一セルの指定にのみ有効
Rows(行番号)
Columns(列番号)
Range("A1:A" & lastRow)
Range(Cells(行, 列), Cells(行, 列))　※範囲選択

【使用例】
Cells(1, 1)、Cells(1, "A")
Rows(5)、Rows("1:5")　※1行のみは数値そのまま、複数行は""で囲む
Columns(3)、Columns("C")、Columns("A:C")

※Rangeを使うのは、固定位置のセルの場合・セル範囲（複数セル）の場合・名前定義を使う時
Cellsを使うのは、単一（1つの）セルを指定する場合
（複数セルであるセル範囲や名前定義をCellsで指定することはできない）

セルの指定

With Worksheet()
　.Range(.Cells(), .Cells()).Value=xxxx
End With

With tenkiSaki.Worksheets(1)
　.Range(.Cells(lastRowBefore + 1, 6), .Cells(lastRowAfter, 6)).Value = name(1)
End With
※Cellsにも「.」が必要なことに注意！！

各シートのA1へのハイパーリンク作成

For i = 1 To Worksheets.Count
　targetSheet.Hyperlinks.Add anchor:=Cells(i, 1), _
　　Address:="", _
　　SubAddress:=Worksheets(i).Name & "!A1", _
　　TextToDisplay:=Worksheets(i).Name
Next

Excelで目次（シートへのリンク）をつくるマクロ | EX-IT 効率化で独立を楽しく

フォントの色

セル指定.Font.Color = vbRed

フォントのサイズ

セル指定.Font.Size = 14

セルの色

セル指定.Interior.Color = vbRed

セルの表示形式を数字にする

セル指定.NumberFormatLocal = "\#,##0"

セルの縦位置

セル指定.VerticalAlignment = 位置
※中央揃え：xlCenter、下揃え：xlBottom

罫線

セル指定.Borders>LineStyle = xlContinuous
※xlContinuousは実線

削除

セル指定.ClearContents
※Deleteキーを押す操作

全選択

セル指定.CurrentRegion
※Ctrl+Aと同じ範囲
※カレント リージョンと読む

タイトル行を残して値のクリア

Range("A1").CurrentRegion.Offset(1,0).ClearContents
※Offset(1,0)しておくと、CurrentRegionの範囲をひとつ下にずらせるので、タイトル行を残すことができる

コピペ(関数も書式もそのまま)

セル指定.Copy セル指定

Sheets("Sheet1").Range("A1:C5").Copy Sheets("Sheet2").Range("A1")

コピペ(値のみ)

セル指定.Copy
セル指定.PasteSpecial Paste:=xlPasteValues

Worksheets("Sheet1").Range("A1:C5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

コピペ(書式)

セル指定.Copy
セル指定.PasteSpecial Paste:=xlPasteFormats

Worksheets("Sheet1").Range("A1:C5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats

値貼付け

セル指定.PasteSpecial xlPasteValues
※略さずに書くと
セル指定.PasteSpecial Paste:=xlPasteValues

並べ替え

セル指定.Sort key1:=キー名, order1:=順番,header:=見出しとして使うか
※昇順：xlAscending／降順：xlDescending

Range("A1").Sort key1:="判定", order1:=xlAscending, Header:=xlYes

オートフィルタ

セル指定.AutoFilter field:=列番号, Criteria1:=抽出条件
※「クライテリア」って読む
※列番号はA列からの列番号ではなく、オートフィルタの範囲の列番号

【1列目が「削除対象」をフィルタして、タイトル行を残して削除する】
Range("A1").AutoFilter field:=4, Criteria1:="削除対象"
Range("A1").CurrentRegion.Offset(1,0).Delete
Range("A1").AutoFilter　※オートフィルタを解除

並べ替え

セル指定.Sort key:=セル指定, order1:=順序, Header:=xlYes

【B列で昇順、C列で降順(1行目はタイトル行)】
Range("A1").Sort key1:=Range("B1"), order1:=xlAscending, _
                            key2:=Range("C1"), order2:=xlDescending, Header:=xlYes

小数点以下の書式設定

セル指定.NumberFormatLocal = "0.00"

塗りつぶしなし

Interior.Pattern = xlNone

塗りつぶし

Interior.Color = 色

アクティブセル番地の取得

ActiveCell.Address

※単一セルのアドレス。そのままだと絶対参照になるが、
　Address(False, False)
　にすると相対参照になる

アクティブセル範囲番地の取得

Selection.Address

※単一セルかセル範囲のアドレス。そのままだと絶対参照になるが、Address(False, False)
にすると相対参照になる

セルの選択

Sheets("貼付用").Activate
Sheets("貼付用").Range("A1").Select
※セルの選択前に、そのシートをアクティブにしないとエラーになる

乱数の生成

Rnd

Int(Rnd*3)+1　※1~3の乱数生成

■行操作

行番号取得

Rangeオブジェクト.Row

最終行取得

Cells(Rows.Count, 1).End(xlUp).Row
Range("A" & Rows.Count).End(xlUp).Row
※どちらもA列最終行を取得

行の自動調整

行指定.AutoFit

Rows("1:10").AutoFit

行削除

行指定.Delete

一行おきに行削除

Dim i As Long
　For i = 44 To 6 Step -2
　Rows(i).Delete
Next i

■列操作

列の自動調整

Columns("A:F").AutoFit
Columns(1:6).AutoFit

列削除

列指定.Delete

一列目の値を削除

Columns(1).ClearContents

列を項目名で指定

Range("定義した名前").Column

Cells(3,Range("漢字名称").Column)
※項目名が入力されているセルに、名前の定義をしておく

列番号取得

Rangeオブジェクト.Column

■シート操作

全シート数

Sheets.Count

シート名指定

Sheets("シート名")

シートのオブジェクト名取得

Worksheetオブジェクト.CodeName
※オブジェクト名でシート指定したら、シート名の変更に影響されない

他のブックのシートのオブジェクト名を探す

Dim ws As Worksheet
For Each ws In Workbooks("他のブック.xlsm").Worksheets
　If ws.CodeName = "Sheet1" Then
　　Dim targetSheet As Worksheet
　　Set targetSheet = ws
　　Exit For
　End If
Next ws

全シート名取得

For i = 1 To Worksheets.Count
　Cells(i, 1) = Worksheets(i).Name
Next

シート追加

Sheets.Add after:=Sheets("Sheet1")
※実行後は追加されたシートがアクティブの状態
※afterを省略すると、アクティブシートの前に追加される

追加したシートの名前変更

※Sheets.Addした後は、追加したシートがアクティブの状態なので、
ActiveSheet.Name = "シート名"
ActiveSheet.Name = Format(Date, ("yyyymmdd"))

シート選択

With シート指定
　.Activate
　.Select
End With
※先に「Activate」しないと、エラーになってしまう

シート削除

Sheets("シート名").Delete
※シート削除の確認ウィンドウが表示されるのを防ぐために、プロシージャの先頭に
Application.DisplayAlerts = False
を書くのがお約束

目印を付けたシート以外を削除

Application.DisplayAlerts = False
Dim i As Long
　For i = Sheets.Count To 1 Step -1
　　If Sheets(i).Name Like "★*" Then
　　Else
　　　Sheets(i).Delete
　　End If
　Next

シートの値クリア

シート指定.Cells.Clear

シートの存在確認

Dim isExistSheet As Boolean
isExistSheet = False

'シートの存在確認
For Each Sht In ThisWorkbook.Sheets
　If Sht.Name = SHEET_TITLE Then
　　isExistSheet = True
　　Exit For
　End If
Next Sht

シート操作シートが存在しなければ生成

If Not (isExistSheet) Then
　Dim NewSht As Worksheet
　Set NewSht = ThisWorkbook.Worksheets.Add
　With NewSht
　　.Name = SHEET_TITLE
　　.Move before:=Worksheets(1)
　End With
End If

シートの名前を順番に取得

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
　Debug.Print ws.Name
Next ws

シートの名前を順番に取得（Forで書くなら）

Dim i As Long

With ThisWorkbook
　For i = 1 To .Sheets.Count
　　Debug.Print .Sheets(i).Name
　Next i
End With

■ブック操作

ブックの新規作成

Workbooks.Add

ブックを開く

Workbooks.Open Filename:=フルパス

ブックの保存

ブック指定.Save

ThisWorkBook.Save　※マクロ実行ブックの上書き保存
ActiveWorkBook.Save　※アクティブワークブックの上書き保存
Workbooks("ブック名").Save　※ブック名を指定した上書き保存

ブックを閉じる

ブック指定.Close

Dim filePath As String
Dim tenkiMoto As Workbook
Set tenkiMoto = Workbooks.Open(filePath)
tenkiMoto.Close

保存しないで閉じる

Workbooks("ブック名") SaveChanges:=False

保存して閉じる

Workbooks("ブック名") SaveChanges:=True

※Openはフルパス指定できるけど、Closeはブック名のみかWorkbookでないとだめ。
なので、OpenするときにWorkbookにSetしておく。

相対パス

ThisWorkbook.Path

ThisWorkbook.Path & "\" & "\操作対象フォルダ"

ブックのフォルダパス取得

ブック指定.Path

ブックのフルパス取得

ブック指定.FullName

ブックの名前を調べる

Dirbookname=Dir(ThisWorkbook.Path & "\操作対象フォルダ\*")

ファイルの保存をしたか

ActiveWorkbook.Saved

If ActiveWorkbook.Saved = False Then
　ActiveWorkbook.Save
End If
※保存してなかったら、自動で保存する

プロジェクトのThisWorkbookをダブルクリック
→Workbookを選択
→勝手にできたプロシージャは無視して、BeforeCloseをクリック
→できたやつに書く。勝手にできたOpenのは削除してOK

■分岐

If Else

Dim i As Long
　For i = 1 To 最大値
　　If 条件式1 Then
　　　処理
　　ElseIf 条件式2 Then
　　　処理
　　Else
　　　処理
　　End If
　Next i
​End Sub

Dim i As Long
　For i = 1 To 100
　　If i Mod 3 = 0 And i Mod 5 = 0 Then
　　　Debug.Print "FizzBuzz"
　　ElseIf i Mod 3 = 0 Then
　　　Debug.Print "Fizz"
　　ElseIf i Mod 5 = 0 Then
　　　Debug.Print "Buzz"
　　Else
　　　Debug.Print i
　　End If
　Next i
End Sub

Select Case

Select Case テスト式(trueとか変数名とか)
　Case 条件1
　　処理
　Case 条件2
　　処理
　Case 条件3
　　処理
　Case Else
　　処理
End Select

Dim rank As String: rank = "優"
　Select Case rank
　Case "優"
　　Debug.Print "すごいですね"
　Case "良"
　　Debug.Print "がんばりましたね"
　Case "可"
　　Debug.Print "ギリギリでしたね"
　Case "優"
　　Debug.Print "残念でしたね"
End Select

For Eachで「もしこの文字が含まれていたら」

For Each コレクション
　If InStr(調査対象, "検索文字") Then
　　処理
　Else
　　処理
　End If
Next cell

For Each cell In Sheet1.UsedRange
　If InStr(cell.Address, "$B") Then
　　Debug.Print cell.Value
　Else
　　Debug.Print cell.Value,
　End If
Next cell

■繰り返し

For Next

For 条件
　繰り返す処理
Next

For i = 3 To Range("B2").CurrentRegion.Rows.Count + 1
　If Cells(i, 2) = "" Or Cells(i, 3) = "" Then
　　Cells(i, 4) = ""
　Else
　　Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
　End If
Next i

Do While

DO While 条件
　繰り返す処理
Loop
※条件に一致している間、処理を繰り返す

Do While bookname <> ""
　i = i + 1
　.Cells(i, 1) = bookname
　bookname = Dir()
Loop

繰り返し(コレクション)

For each 変数名 In コレクション
　繰り返す処理
Next 変数名

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
　Debug.Print ws.Name
Next ws

開いたブックを変数に入れる

Set 変数名=Workbook.Open(ThisWorkbook.Path & "\data\" & wbName)

Dim wbName As String
Dim wb As Workbook
　wbName=Dir(ThisWorkbook.Path & "\data\*")
　Set wb=Workbook.Open(ThisWorkbook.Path & "\data\" & wbName)
　wb.Close savechanges:=False

■フォルダ操作

フォルダ作成

MkDir

For i = 2 To 5
　MkDir ThisWorkbook.Path & "\" & Cells(i, 1).Value
Next

フォルダ内のファイルを取得(FSO)

Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim folderPath As String
folderPath = "C:\Users\○○\○○～\"
Dim folder As Object
Set folder = FSO.GetFolder(folderPath)
Dim file As Object
For Each file In folder.Files
　Debug.Print file.DateLastModified
Next file
Set FSO = Nothing

フォルダ内のファイルを取得(FileDateTime関数)

Dim tenkiMotoBookName As String
tenkiMotoBookName = Dir(ThisWorkbook.Path & "\CSVファイル保存先\*")
Dim filePath As String
Do While tenkiMotoBookName <> ""
　filePath = ThisWorkbook.Path & _
　　"\CSVファイル保存先\" &tenkiMotoBookName
　Debug.Print FileDateTime(filePath)
　tenkiMotoBookName = Dir()
Loop

■プロシージャ操作

Subプロシージャを呼び出す

Sub プロシージャ名()
　Call 呼び出すSubプロシージャ名(渡す引数)
End Sub

Sub 呼び出されるプロシージャ名(引数の型の宣言)
　処理
End Sub

Sub MySub3_03()
Dim x As Long: x = 10
Call IncrementNumber(x)
Debug.Print x
End Sub

Sub IncrementNumber(ByRef y As Long) '参照渡し = xの値も変わる
'Sub IncrementNumber(ByVal y As Long) 値渡し = xの値は変わらない
y = y + 1
Debug.Print y
End Sub

Subプロシージャには、戻り値がない。
省略したときは、参照渡しのByRefになる。
基本は値渡しのByValを使うので、省略したのかあえてなのかわからなくなるため、ByRefも省略せずに書く。

Functionプロシージャを呼び出す

Sub プロシージャ名()
呼び出すFunctionプロシージャ名(渡す引数)
End Sub

Function 呼び出されるプロシージャ名(引数の型の宣言) 戻り値の型の宣言
処理
End Function

Sub MySub3_06()
Debug.Print Square2(5)
End Sub

Function Square2(ByVal x As Long) As Long
Square2 = x ^ 2
End Function

Functionプロシージャ名と戻り値の変数名は同じにしておく。

■メッセージボックス

メッセージボックス表示

MsgBox
戻り値のない MsgBox はカッコ不要。

MsgBox Sheets.Count
MsgBox "おはよう"

Dim text As String
text = InputBox("文字列を入力してください")
MsgBox text
Debug.Print MsgBox(text, vbYesNo)

MsgBox(マクロ実行確認)

If MsgBox("実行するよ？", vbOKCancel) = vbCancel Then
　Exit Sub
End If

■VBA関数

VBA関数の一覧｜VBAリファレンス

数値に変換する

CLng(Me.オブジェクト名.Text)

■ワークシート関数

Vlookup

WorksheetFunction.VLookup( _
.Cells(i, 2), Sheets("マスタ").Range("A:B"), 2, 0)

数値を四捨五入

WorksheetFunction.Round(セル指定)

WorksheetFunction.Round(Cells(5, 4) * 1.08, 0)

■印刷

印刷する

PrintOut

For i =3 To Sheets.Count
　Sheets(i).PrintOut
Next i

PDFにする

ExportAsFixedFormat Type:=xlTypePDF,Filename:=変数名
※変数名はString

■調査

データ型を調べる

TypeName(対象)

Debug.Print TypeName(i)

■変数

定数の宣言

Const 定数名 As データ型 = “定数に格納する値”

■フォーム操作

フォームの起動

フォームのオブジェクト名.Show

frmSample.Show

フォームを閉じる

Unload Me

コンボボックスの選択肢

Me.オブジェクト名.AddItem "選択肢"

Me.cmb_区分.AddItem "A"

■お約束の便利コード

画面更新の停止

Application.ScreenUpdating = False
※冒頭に書く

確認メッセージを表示しない

Application.DisplayAlerts = False

コピー中の点線を消す

Application.CutCopyMode = False

実行を止める(テスト時)

Stop

Stop だけでもOKだけど、
If i=120 Then Stopでiが120ときに止められる

ダブルクリックでセルの編集を無効化

Cancel = True

数値じゃなかったら「数値じゃない」と入力

If Not IsNumeric(Sheet1.Range("C3").value) Then Sheet1.Range("E3").value = "数値じゃない"

■日時取得

現在日付取得

DateFormat(Date, "yyyymmdd")

現在時刻取得

NowFormat(Now, "hhmmss")

■文字操作

文字列を区切り文字で区切る

Dim tmp As Variant
tmp = Split("123,テスト,test", ",")
MsgBox tmp(0)
MsgBox tmp(1)
MsgBox tmp(2)
※配列で返るので、データ型はVariantになる