Excel VBAスタンダードの学習記事です

この記事は、変数やメソッドを日本語で記述しています。コーダーであれば「日本語で命名してはいけない」ことは常識だと思います。入力速度、可読性(エンジニアであれば誰が見てもわかりやすいこと)、日本語対応でないプログラムが使えないなど、デメリットがあるからです。しかし、この記事は、VBA初心者が、自分が分かる内容でプログラムが動くことを第一に作成しています。Excelファイルを作成していて、マクロを使ったことがない人が、文書作成を効率的に行うスキルを身につけることが目的です

1.プロシージャ

プロシージャとはマクロの最小実行単位
「Subプロシージャ」や「Functionプロシージャ」などがある
マクロはモジュール内にプロシージャとして作成する

Sub マクロ名()
 処理
End Sub

1_1.Callステートメント

Sub A1セルに値を入力()
 Dim A As Long
 A = 100
 Range("A1") = A
 Call A1セルの値を2倍にする
End Sub

Sub A1セルの値を2倍にする()
 Range("A1") = Range("A1") * 2
End Sub

『A1セルに値を入力』を実行すると、『A1セルの値を2倍にする』が呼び出される
Callは省略できるが、他のプロシージャを呼び出していることを明確にするためには省略しない方が可読性があがる
モジュールレベル変数
プロシージャの中で宣言した変数は、そのプロシージャの中でのみ使用できる
モジュール内の全てのプロシージャで使用できる変数を、モジュールレベル変数という。宣言セクションで宣言する

1_2.Functionプロシージャ

Subプロシージャは記述されたコードを実行するのみだが、Functionプロシージャは値を返すことができる

Sub シートの存在を確認する()
 If シートを数えて名前を確認する("Sheet1") = True Then
  MsgBox "存在します"
 else
  MsgBox "存在しません"
 End If
End Sub

Function シートを数えて名前を確認する(A As String)
 Dim i As Long
 For i = 1 To Sheets.Count
  If Sheets(i).Name = A Then
   シートを数えて名前を確認する = True
   Exit Function
  End If
 Next i
 シートを数えて名前を確認する = False
End Function

"Sheet1"という名前のシートの存在を確認する。シート名は文字列なので、A As Stringと定義する。FunctionのAには"Sheet1"が文字列の引数として渡される。シートをカウントしながら、引数と同名のシートを確認し、存在する場合はTrue、存在しない場合はFalseを返す
複数の引数を受け取る時は、引数を,で区切って記述する
コンパイルエラー
引数を指定していない時、引数の数が異なっている時、引数の型が異なっている時には、コンパイルエラーになる
参照渡しと値渡し
引数そのものを渡すことを参照渡しといい、値のみを渡すことを値渡し(引数は変更されない)という
引数の定義にByRefとつけると参照渡し、ByValとつけると値渡しになる
省略すると参照渡しになる。例(ByVal A As String)

2.変数の活用

変数は値をいれる箱のようなもの

2_1.配列

Dim 配列変数名(要素の下限 To 要素の上限)

要素の下限が(0)の場合、省略できる

Dim A As Variant

Sub 配列の表示()
 Call 配列作成
 Dim i As Long
 For i = 0 To UBound(A)
  MsgBox A(i)
 Next i
End Sub

Sub 配列作成()
 A = Split("a-b-c","-")
End Sub

Split関数が返す配列はVariantで受け取る
宣言を省略すると、Variant型(全ての型に対応)になる

2_2.動的配列

宣言時に要素数を指定しない配列を動的配列という

Sub 動的配列()
 Dim A() As String
 ReDim A(1)
 A(0) = "a"
 A(1) = "b"
 ReDim Preserve A(2)
 A(2) = "c"
 MsgBox A(0) & A(1) & A(2)
End Sub

ReDimで要素数を変更すると、配列に格納されていた値は消える。値を残すには、Preserveをつける

2_3.オブジェクト変数

オブジェクトを格納する変数をオブジェクト変数という

Dim A As Range
Dim B As Worksheet
Dim C As Workbook
Dim D As Object

Sub A1セルのフォントを太字にする()
 Set A = Range("A1")
 A.Font.Bold = True
 Set A = Nothing
End Sub

オブジェクト変数がメモリに残らないように、Nothingを代入し、明示的に破棄する

2_4.変数の演算

数値型の初期値は０

Sub A列に文字を入力()
 Cells(1,1) = "a"
 Cells(2,1) = "b"
 Cells(3,1) = "c"
End Sub

Sub B列の値を合計する()
 Dim i As Long, A AS Long
 For i = 1 To 3
  If Cells(i,1) = "a" Then
   Cells(i,2) = 1
  End If
  If Cells(i,1) = "b" Then
   Cells(i,2) = 10
  End If
  If Cells(i,1) = "c" Then
   Cells(i,2) = 100
  End If
  A = A + Cells(i,2)
 Next i
 
 Cells(1,3) = A
End Sub

2_5.文字列を結合する

Sub 文字列を結合()
 Dim i As Long
 For i = 1 To 3
  Cells(i,4) = Cells(i,1) & Cells(i,2)
 Next i
End Sub

3.ステートメント

3_1.Exitステートメント

終了処理:処理を途中で終了する命令

Exit Sub
Exit Function
Exit For
'For nextの繰り返し処理を終了
Exit Do
'Do Loopの繰り返し処理を終了

3_2.Select Caseステートメント

条件分岐:複数の条件を同時に判定する時に使用

Sub 勤務時間()
 Select Case Range("A1").Value
  Case 12
   MsgBox "昼休みです"
  Case 9 To 17
   MsgBox "勤務時間です"
  Case Else
   MsgBox "勤務時間外です"
 End Select
End Sub

3_3.Ifステートメント

条件分岐:TrueまたはFalseで条件を判定する時に使用

'複数条件の指定
If 条件1 Or 条件2 Then
If 条件1 And 条件2 Then
'入れ子での記述(Andの場合)
If 条件1 Then
 If 条件2 Then
  処理
 End If
End If

3_4.Do...Loopステートメント

繰り返し処理:指定した条件によって処理を繰り返す命令。無限ループに注意

Do While 条件
 処理
Loop
'条件が正しい間は繰り返す
Do Until 条件
 処理
Loop
'条件が正しくない間は繰り返す
Do
 処理
Loop While 条件
Do
 処理
Loop Until 条件
'処理を行った後に条件で判定する
Do
 処理
 If 条件 Then Exit Do
Loop
'条件に該当したら終了する

Sub A列のデータをカウントする()
 Dim i As Long
 i = 1
 Do While Cells(i,1) <> ""
  i = i + 1
 Loop
 MsgBox i - 1
End Sub

3_5.ForEach...Nextステートメント

繰り返し処理:グループからメンバーを一つずつ順番に取り出し操作する。順番は重要でなくすべてのメンバーを操作する時に使用する(For...Nextとの違い)。ステートメント内で使うオブジェクト変数には必ずVariantを指定する。StringやLongを指定するとエラーになる
メンバー:コレクション(ブックやシートなど)、複数のセル、配列

For Each 変数 In グループ名
 変数を使った操作
Next 変数

'マクロ実行前に計算するセルを選択する
Sub 選択したセルの値を２倍する()
 Dim A As Range
 For Each A In Selection
  A = A * 2
 Next A
End Sub

Sub 配列の数値を２倍する()
 Dim A(2) As Long, N As Variant
 A(0) = 1
 A(1) = 10
 A(2) = 100
 For Each N In A
  N = N * 2
  MsgBox N
 Next N
End Sub

4.ファイルの操作

4_1.ブックを開く

ブックを開くにはOpenメソッドを使う

Sub ファイルを開く()
 Workbooks.Open "C:¥Users¥<ユーザー名>¥ファイル名.xlsx"
End Sub

4_2.ブックを保存する

アクティブブックに名前を付けて保存するときはSaveAsメソッドを使う

'ファイル名の前に該当年をつけるため、Year関数とNow関数とFormat関数を使う
Sub 指定フォルダに名前を付けてファイルを保存()
 ActiveWorkbook.SaveAs "C:¥Users¥<ユーザー名>¥指定フォルダ¥" &  Format(Now,"yyyy") & "年ファイル名.xlsx"
End Sub

4_3.ファイルをコピーする

FileCopyステートメントを使う。存在しないフォルダにコピーしようとしたり、保存先のフォルダやコピー元のファイルが開いているとエラーになる

Sub ファイルをコピーする()
 FileCopy "C:¥Users¥<ユーザー名>¥指定フォルダ¥" &  Format(Now,"yyyy") & "年ファイル名.xlsx", "C:¥Users¥<ユーザー名>¥既存フォルダ¥" &  Format(Now,"yyyy") & "年ファイル名.xlsx"
End sub

4_4.フォルダーを操作する

MkDirステートメントでフォルダを作成する。すでに存在しているフォルダと同名で作成しようとするとエラーになる

Sub フォルダ作成()
 MkDir "C:¥Users¥<ユーザー名>¥新規フォルダ"
End Sub

5.ワークシート関数の利用

VBAの中でワークシート関数を使うときは、WorksheetFunctionに関数名と引数を渡す

5_1.WorksheetFunctionの使い方

WorksheetFunction.関数名(引数)

Sub カウント合計平均()
 With WorksheetFunction
  Range("D1") = .CountIf(Range("A1:A3"),"a")
  Range("D2") = .SumIf(Range("A1:A3"),"a",Range("B1:B3"))
  Range("D3") = Range("D2") / Range("D1")
 End With
End Sub

Sub データが存在するか調べる()
 If WorkSheetFunction.CountIf(Range("A1:A3"),"a") = 0 Then
  MsgBox "存在しません"
 Else
  MsgBox "存在します"
 End If
End Sub

月末の日を調べるEnd Of Month関数
EoMonth(開始日,月)
開始日はExcelが日付と認識できる形式にする
月は、当月なら0、翌月なら1、先月なら-1
DateSerial関数
DateSerial(年,月,日)

Sub 月末日2月()
 Range("E1") = WorksheetFunction.EoMonth(DateSerial(Year(Now),2,1),0)
End Sub

Sub 月末日Now()
 Range("E2") = WorksheetFunction.EoMonth(Now,0)
End Sub

6.検索とオートフィルター

6_1.Findメソッド

引数:What検索する語句を指定。After指定したセルの次のセルから検索開始。省略すると検索対象の左上が指定。LookIn検索対象の種類(値･数式など)を指定。LookAt完全一致検索を指定。SearchOrder検索の方向を指定。SearchDirection検索の向きを指定。MatchCase大文字と小文字を区別するか指定。MatchByte半角と全角を区別するかを指定。SearchFormat書式を検索の条件に含めるかを指定
必ず指定するのはWhat、指定した方がよいのはLookAt

'完全一致
セル範囲.Find(What:=検索する語句,LookAt:=xlWhole)
'部分一致
セル範囲.Find(What:=検索する語句,LookAt:=xlPart)

Sub A列がaならB列に100を入力する()
 Dim A As Range
 Set A = Range("A:A").Find(What:="a",LookAt:=xlWhole)
 If A Is Nothing Then
  MsgBox "見つかりません"
 Else
  A.Offset(0,1) = 100
 End If
End Sub

6_2.検索結果の操作

Sub aを含む行を削除()
 Dim A As Range
 Set A = Range("A:A").Find(What:="a",LookAt:=xlWhole)
 If A Is Nothing Then
  MsgBox "見つかりません"
 Else
  A.EntireRow.Delete
 End IF
End Sub
'列全体ならEntireColumn

Sub aを含むセルの右隣の列に100を入力する()
 Dim A As Range
 Set A = Range("A:A").Find(What:="a",LookAt:=xlWhole)
 If A Is Nothing Then
  MsgBox "見つかりません"
 Else
  A.Offset(0,1) = 100
 End If
End Sub

Sub aを含むセル範囲をE1にコピー()
 Dim A As Range
 Set A = Range("A:A").Find(What:="a",LookAt:=xlWhole)
 If A Is Nothing Then
  MsgBox "見つかりません"
 Else
  Range(A,A.End(xlToRight)).Copy Range("E1")
 End If
End Sub
'データ範囲が1行×3列の大きさなら、A.Resize(1,3).Copy Range("E1")でも同じ

6_3.オートフィルター操作

表をオートフィルターで絞り込むときはAutoFilterメソッドを使う
引数:Field列位置数値で指定。Criteria1絞り込み条件を""で囲って指定。Operatorひとつの列に２つ以上の条件を指定(xlOrやxlAndなど)。Criteria2は列に2つ目の条件を指定するときに使う

Sub aで絞り込む()
 Range("A1").AutoFilter Field:=1,Criteria1:="a"
End Sub
'Field:=とCriteria1:=は省略できる

Sub 配列で絞り込む()
 Dim A(2) As String
 A(0) = "a"
 A(1) = "b"
 A(2) = "c"
 Range("A1").AutoFilter 1, A, xlFilterValues
End Sub
'aまたはｂまたはｃで絞り込む

Sub 絞り込んだ結果をコピー()
 Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")
End Sub

Sub 絞り込んだ結果をカウントする()
 Dim N As Long
 N = WorksheetFunction.Subtotal(3,Range("A:A"))
  MsgBox N - 1 & "件あります"
End Sub

Sub 絞り込んだ結果のB列に100を入力()
 Range(Range("B2"),Cells(Rows.Count,2).End(xlUP)) = 100
 Range("A1").AutoFilter
End Sub

7.データの並べ替え

Sub 並べ替え条件の指定()
 ActiveSheet.Sort.SortFields.Clear
 ActiveSheet.Sort.SortFields.Add2 _
  Key:= Range("B2"), _
  SortOn:= xlSortOnValues, _
  Order:= xlAscending, _
  DataOption:= xlSortNormal
End Sub

1行目で既存の条件をクリアする
2行目でAdd2メソッドを使用
3行目にKeyを指定。必須
4行目で並べ替えのタイプを指定。xlSortOnValuesセル内データで並べ替え(省略時選択)。xlSortOnCellColorセルの背景色で並べ替え。xlSortOnFontColorセルの文字色で並べ替え。xlSortOnIcon条件付き書式のアイコンで並べ替え
5行目で昇順降順を指定。xlAscending昇順(省略時選択)。xlDescending降順
6行目のxlSortNormalは数値と文字列を別々に並べ替える(省略時選択)。xlSortTextAsNumbersは文字列を数値とみなして並べ替える

Sub 並べ替えの実施()
 With ActiveSheet.Sort
  .SetRange Range("A2:C14")
  .Header = xlNo
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
 End With
End Sub

Header:xlGuessはExcelが自動判定(省略時選択)。xlYes１行目はタイトル行。xlNo１行目はタイトル行ではない
MatchCase:大文字と小文字を区別する場合、True
Orientation:xlTopToBottom上下に並べ替える(省略時選択)。xlLeftToRight左右に並べ替える
SortMethod:xlPinYin日本語をふりがなで並べ替える(省略時選択)。xlStroke日本語を文字コードで並べ替える

Excel2003までの並べ替え

Sub 並べ替えシンプル()
 Range("A1").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub

ふりがなの操作

Sub ふりがなの確認()
 MsgBox Range("B1").Phonetic.Text
End Sub

8.テーブルの操作

テーブルはワークシートではなく、Excelが管理しているデータベース領域

8_1.テーブルを特定する

'セルからテーブルを特定する
テーブル内のセル.ListObject
'シートからテーブルを特定する
対象のシート.ListObjects(インデックス)
対象のシート.ListObjects(テーブル名)
'Rangeとテーブルの名前で特定する
Range("固有のテーブル名")

8_2.テーブルの部位を特定する

'見出し行を含むテーブル全体から特定
Range("A1").ListObject.Range(3)

ListObject.Range(3)はテーブルの左上から数えたセルの数。A1などとは違うので注意

'見出し行を含まないテーブルのデータ全体
Range("A1").ListObject.DataBodyRange
'見出し行
Range("A1").ListObject.HeaderRowRange
'列
Range("A1").ListObject.ListColumns(2).Range
Range("A1").ListObject.ListColumns("見出し名").Range
Range("A1").ListObject.ListColumns(2).DataBodyRange
Range("A1").ListObject.ListColumns("見出し名").DataBodyRange
'行(3行目の場合)
Range("A1").ListObject.ListRows(3).Range

8_3.構造化参照を使って特定する

'構造化参照
テーブル名[[特殊項目指定子],[列指定子]]
'見出し行を含むテーブル全体
Range("テーブル1[#All]")
'見出し行を含まないテーブルのデータ全体(省略時選択)
Range("テーブル1[#Data]")
'列
Range("テーブル1[[#All],[列指定子]]")
Range("テーブル1[列指定子]")

構造化参照では行を指定することはできない

8_4.特定のデータを操作する

'見出し行ごとコピーする
Sub テーブルコピー見出し行含む()
 With Range("A1").ListObject.Range
  .AutoFilter 1, "a"
  .Copy Sheets("Sheet1").Range("A1")
 End With
End Sub
'見出し行を含まない場合はRange("A1").ListObject.DataBodyRange

アクティブセルがテーブルの外にあるとオートフィルターが機能しないので注意

Sub 構造化参照を使ってコピー()
 Range("テーブル1[#All]").Copy Sheets("Sheet1").Range("A1")
End Sub
'テーブル行を含まない場合は[#Data]省略可

Sub 特定の列だけコピー()
 With Range("A1").ListObject
  .ListColumns(1).Range.Copy Sheets("Sheet1").Range("A1")
  .ListColumns(2).Range.Copy Sheets("Sheet1").Range("B1")
 End With
End Sub

Sub 特定の列だけ書式設定()
 Range("A1").ListObject.ListColumns(1).DataBodyRange.Font.Bold = True
End Sub

8_5.行を削除する

Sub テーブルで絞り込んだデータを削除()
 With Range("A1").ListObject.DataBodyRange
  .AutoFilter 1, "a"
  .EntireRow.Delete
  .AutoFilter 1
 End With
End Sub

'構造化参照
Sub テーブルで絞り込んだデータを削除()
 Range("テーブル1").AutoFilter 1, "a"
 Range("テーブル1").EntireRow.Delete
 Range("テーブル1").AutoFilter 1
End Sub

8_6.列を挿入する

Sub 列を挿入し数式を代入する()
 Dim A As Long
 Range("A1").ListObject.ListColumns.Add
 A = Range("A1").ListObject.ListColumns.Count
 Range("A1").ListObject.ListColumns(A).DataBodyRange = "=[@列3]*10"
End Sub

Sub 構造化参照で列を挿入し数値を代入する()
 Range("テーブル1[列4]").Offset(0,1) = "=[列4]*10"
End Sub

9.エラー対策

9_1.エラーの種類

記述エラー
VBAの構文に違反した書式で命令を記述したときに発生する。.と,を間違えたときや、半角スペースが全角スペースになっているときなどによく発生する。記述確定時にすぐに赤字で教えてくれる
論理エラー
VBAの文法に誤りはないが、論理的な矛盾によって発生するエラー。マクロの実行時に発生する
①コンパイルエラー:例えばOption Explictで、すべての変数の宣言が強制されている時に、宣言をせずに変数を使用すると発生する。マクロを実行しなくても、デバッグメニューの｢VBAProjectのコンパイル｣を実行することでコンパイルエラーがないかを確認できる
②実行時エラー:論理的な誤りがあっても実際に実行しないと評価できないエラー。例えば、記述してあるシート名のシートが存在しない場合など。エラーウィンドウの終了ボタンを押すとマクロは終了する。デバッグボタンを押すとマクロが中断された状態になり、止まっている記述部分が黄色く表示される

9_2.エラーへの対応

エラーが発生したら別の処理にジャンプする

'On Errorステートメント
Sub エラー有無確認()
 On Error GoTo Error1
 Sheets("存在する").Range("A1") = "OK"
 MsgBox "処理が完了しました"
 Exit Sub
Error1:
 MsgBox "エラーが発生しました"
End Sub

どんなエラーが発生したか調べる(errオブジェクト)
Numberプロパティ:エラー番号を返す
Descriptionプロパティ:エラーの意味を表す文字列を返す
Clearメソッド:エラー情報をクリア。エラー情報はプロシージャが終了するまで保持されるため、同一プロシージャ内で複数のOn Errorステートメントを使用する時は、Clearメソッドでクリアする

Sub エラーの確認()
 On Error GoTo Error1
 Sheets("存在する").Name = "OK"
 MsgBox "処理が完了しました"
 Exit Sub

Error1:
 Select Case Err.Number
 Case 9
  MsgBox "シートが存在しません"
 Case 1004
  MsgBox "同名のシートが存在します"
 Case Else
  MsgBox "想定しないエラーです"
 End Select
End Sub

発生したエラーを無視する(Resume Next)

Sub ファイルを保存()
 On Error Resume Next
 ActiveWorkbook.SaveAs "Book1.xlsm"
 If ActiveWorkbook.Saved = True Then
  MsgBox "保存されました"
 Else
  MsgBox "保存されていません"
 End If
End Sub

9_3.データのクレンジング

不正なデータを修正する
エラーの主な原因は①コードの間違い②操作の間違い③データの間違い
データのクレンジングは、③への対処法

半角文字列と全角文字列

Sub 半角を全角にする()
 Dim i As Long, A As Long
 A = Cells(1, 1).End(xlDown).Row
 
 For i = 1 To A
  Cells(i, 2) = StrConv(Cells(i, 1), vbWide)
 Next i
End Sub

Sub 文字列に半角が含まれているか確認する()
 Dim i As Long, A As Long
 A = Cells(1, 1).End(xlDown).Row
 
 For i = 1 To A
  If Cells(i, 1) <> StrConv(Cells(i, 1), vbWide) Then
   Cells(i, 2) = "含半角"
  Else
   Cells(i, 2) = "OK"
  End If
 Next i
End Sub

不要な文字を除去する

'Replace関数で置換文字を""にする
Replace(元の文字列,検索文字,置換文字)

日付の操作

Sub 書式を日付に変換する()
 Dim i As Long, A As Long
 A = Cells(1, 1).End(xlDown).Row

 For i = 2 To A
  Cells(i,1).NumberFormat = "yyyy/mm/dd"
  Cells(i,1).Value = Cells(i,1).Value
 Next i
End Sub

10.デバッグ

10_1.イミディエイトウィンドウ

プロパティや変数、実行中のマクロの情報を表示したり、任意の命令を実行できる
1行が一つの命令として認識されるため、エンターキーを押すと実行される
変数の宣言はできない

'?に続けてプロパティや変数を入力すると、プロパティや変数に格納されている値、関数の結果などを調べることができる
?range("A1").value
→A1セルに入力されているデータが表示される

'値の代入
range("A1").value = "データ"
→A1セルに"データ"を代入

'変数
a = "テスト"
msgbox a
→メッセージボックスに"テスト"と表示される

'実行中のマクロに記述する
Sub イミディエイトウィンドウへ出力()
 Debug.Print "テスト"
End Sub

10_2.マクロを一時停止する

ブレイクポイント
マクロを一時停止する行にカーソルを置いて、F9キーを押すか、左端のインジケーターバーをクリックする
Stopステートメント
マクロのコードに記述することで一時停止する
ステップイン
マクロのコードを1行ずつ実行する

'条件を満たした場合、マクロを一時停止する
Sub Stopステートメント()
 Dim a As Long
 a = InputBox("好きな数字を入力してください","入力","")
 Range("A1") = a
 If Range("A1") > 100 Then Stop
 Msgbox a
End Sub

10_3.デバッグでよく使う関数

'TypeName関数
?typename(Range("A1"))
→Range(Rangeオブジェクトの意味)
?typename(Range("A1").Value)
→Double(A1セルのデータが小数の場合)

'IsNumeric関数
?isnumeric(Range("A1").Value)
→True(A1セルのデータが数値の場合)

'IsDate関数
?isdate(Range("A1").Value)
→False(A1セルのデータが日付と認識されなかった場合)

VBAの基本(Excel)

目次

はじめに