Excel VBAマクロ メモ

VBAの記述、処理メモ。都度追加、修正します。

Excel基本知識

R1C1...セル参照の方式の1つ。Excelでは【A1形式】と【R1C1形式】の２つのセルの参照方式がある。

【A1形式】...A列1行…のようにセル位置を表す。
【R1C1形式】ではRow（行）Column（列）を用いて表示する。R1C1は１行１列、R2C1は2行１列。

VBAを記述する上で意識するべきこと

プロシージャは小さくまとめて記述する
１つの長いプロシージャ内で処理を完結させるようなコードを書かない。
　→可読性が悪い。コードの変更、修正に対して適応しづらい。

コード内のコメントは過不足なく書く
・コメントの記述の仕方に一貫性を持たせる
・基本的には変数名とプロシージャ名で理解できるようにする（不要なコメントを書かない）
・全ての処理に対してコメントを残さない
・コードの可読性を高めるためのコメントを意識する
　（わかりやすく、誤解を生じないコメントにする）

変数、プロシージャ、モジュールの範囲を意識する
有効な範囲を意識して変数、プロシージャを定義する。
定義する場所、定義の仕方（変数宣言の記述等）を意識する。

コード記述に関する基本ルール

・シートの指定がない場合は、Activeなシートに対して処理が行われる。
そのため、基本的にはシートの指定をして書く方が良い。

・Dimで変数を宣言する際に型を指定しない場合は、
バリアント型(Variant)となる。この型はどんなものでも代入可能。

Dim 変数名 As Variant

または、

Dim 変数名

上記の２つで定義できる。

ただしデメリットあり
　・実行速度が遅くなる
　・誤作動の原因となる場合がある

そのため、基本的には変数を定義するときには型も定義する。

・VBAコード内での改行
半角スペース　+ アンダーバー →　[ _]

例:

Worksheets(1).Range("A1").CurrentRegion.Select

' 上のコードを２行に分けて記述すると

Worksheets(1).Range("A1") _
.CurrentRegion.Select

プログラム実行時のデバック

・ブレークポイント

ブレークポイントを設定して処理を確認する
この状態でマクロを実行するとこのブレークポイントで処理が止まる。

・ステップイン
ステップイン実行キー
　Windows...F8
　Mac...Command＋Shift＋I

１行ずつプログラムを実行していくことができる。

・ステップオーバー
　Shift＋F8

基本はステップインと同じだが、
呼び出しプロシージャがあった場合にその中には入らずに次の行に進む。

・ステップアウト
　Ctr+Shift＋F8

プロシージャの終わりまで一気に処理を実行。プロシージャ内である程度動きを確認した後、プロシージャから出たい時などに使用。

・カーソルの手前の行までを実行
　Ctr+F8

マウスクリックしたカーソルの行の手前までのプログラムを実行。

セルの選択

具体的な範囲を直接指定して選択

Sub A1からC5の範囲を選択()
Worksheets(1).Range("A1:C5").Select
End Sub

指定したセルを含む空白で囲まれた範囲までを選択する↓

Worksheets(1).Range("A1").CurrentRegion.Select

例：下のシートでいう緑色の部分が空白のため、
A1セルをこの方法で選択した場合　A1:B7までが選択される。

次に、空白行まで含めて使用されているセルを含んだ範囲を指定する↓

Worksheets(1).UsedRange.Select

例：下のシートのように緑色の空白部分を含めて、使用されているセルの最後の範囲までを選択する。

・一番下のセルを選択する

検索開始セルを指定してその最も下のセルを選択する↓
（注意点...途中に空白行がある場合、空白行の一つ上が選択される。）

Range("検索開始セル").End(Direction:=xlDown).select

シートの一番下を選択し、そこから上へ最初に入力があるセルを探して選択する↓
（そのセルの列内に空白があってもその列の最終セルが選択される）

Cells(Rows.count,1).End(Direction:=xlUp).select

補足：「Direction:=」は省略可能

Range("検索開始セル").End(xlDown).select

Cells(Rows.count,1).End(xlUp).select

セルの削除

行の削除

Rows(3).Delete ' 3行目を削除

指定したセルを含んだ行を削除

Range("A2").EntireRow.Delete ' セルのA2が含まれている行全体を削除

複数行をまとめて削除

Range("1:3").Delete ' 1から3行目までを削除

以下の項目の削除、非表示についても上のような記述の仕方でセルの範囲を選択することが可能。

列の削除

Columns(3).Delete '　3列目を削除

行or列の非表示

Rows(3).Hidden = True '3行目を非表示
Columns("C").Hidden = True　' C列を非表示

' TrueをFalseに変更すれば表示になる

クリアにする（値だけを消して、消した列or行は空白となり残る）

Rows(2).Clear ' 2行目の値を全てクリアにする

セルへの代入

Range("A2").Value = 123 ' セルA2に123を代入

数値や文字列など単純なデータを入力する場合は、Valueプロパティを省略可能。

他のブック操作

FileSystemObject

FileSystemObject...ファイル・フォルダ操作用のオブジェクト。
フォルダ・ファイル操作が簡単になる。
外部オブジェクトのため呼び出しのための記述が必要。

Dim fso As Object
Dim path As String
Set fso = CreateObject("Scripting.FileSystemObject")
path = "フォルダーのフルパス"

Dim file As File
Set file = fso.GetFolder(path).files

Dim wb As Workbook
Set wb = Workbooks.Open(file)

' 開いたファイルのワークブックに対する処理

'保存せずに閉じる
Call wb.Close(SaveChanges:=False)

別のブックを開く

Workbooks.Open "ブックのフルパス"

または、

Workbooks.Open Filename:="フォルダのパス¥ファイル名"

' ファイルが同じフォルダにある場合、以下のようにも指定できる
Workbooks.Open Filename:=ThisWorkbook.Path & "¥ファイル名"

特定のフォルダーにあるファイルを指定して開く

• ファイル名の部分一致を使う場合

Const Fo As String = “フォルダのパス”
Dim fi As String
fi = Dir(Fo & “*.xlsx”) ' .xlsxで終わるファイルをワイルドカードを用いて指定

Workbooks.Open Filename:=Fo & fi

• ファイル名によらないで取得する場合
  （条件：フォルダ内のファイルが１つであること）

Dim Fo As Folder
Fo = GetFolder("フォルダーのパス") ' フォルダを取得

Dim fi As File
fi = Fo.Files ' フォルダ内のファイルを取得

Workbooks.Open Filename:=Fo.Path & fi.Path

ブックを閉じる

' 保存せず閉じる
Workbooks.Close SaveChanges:=False

フォルダーの新規作成

MkDir "保存先パス\test1" ' 保存先パスの下にtest1フォルダーを作成

' フォルダーの名前を今日の日付にして作成する場合
MkDir "保存先パス" & "\" & Format(Date, "yyyymmdd")  

ユーザーが指定してエクセルファイルを開く

' 変数OpenfileNameをString（文字列）型で宣言
Dim OpenFileName As String
' ダイアログボックスを開き指定したファイルをOpenFileNameに代入（引数でダイアログボックスの開くファイルの拡張子を指定）
OpenFileName = Application.GetOpenFilename("Microsoft Excelブック,*.xls?")
' OpenFileNameを開く
Workbooks.Open OpenFileName

データのテーブル化

ActiveSheet.ListObjects.Add xlSrcRange, Range("A1").CurrentRegion

ピポッドテーブルの作成

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
　　"テーブル1", Version:=6).CreatePivotTable TableDestination:= _
    "Sheet2!R3C1", TableName:="ピボットテーブル1", DefaultVersion:=6

簡素化↓

ActiveWorkbook.PivotCaches.Create(xlDatabase, _
"テーブル1").CreatePivotTable Sheets.Add.Range("A3")

元のデータがテーブル形式ではなく、ワークシートだった場合の指定方法

SourceData:="Sheet1!R1C1:R100C150"

注：Sheet1はシート名、R1C1:R100C150は１行１列目から１００行１５０列目までを元データの範囲として指定する。

シートのプリント

Worksheets("Sheet1").PrintOut ' Sheet1のプリント

' 複数シートを指定
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).PrintOut ' Sheet1,2,3をプリント

Withを用いたコードの簡略化

Worksheets("Sheet1").Range(Worksheets("Sheet1").Cell(1,2), Worksheets("Sheet1").Cells(3,4).Value = 5)

上記のコードをWithを用いて簡略化すると、

With Worksheets("Sheet1")
 .Range(.Cell(1,2), .Cells(3,4).Value = 5)
End With

このように複数の繰り返しの記述→Worksheets("Sheet1")
をWithを用いることで簡略化して記述することができる。

処理実行前にメッセージボックスを表示

If Msgbox(prompt:="〜しますか？",buttons:=vbOKCancel)=vbOK then
  実行したい処理
End If