業務の効率化上昇をはかるためVBAを学習したため、忘れないためにメモとして記録する。
私のようにVBA初学者の方にも役に立てれば幸いです。
マクロ・VBA・VBEとは
マクロとは
エクセルでの操作を記録して実行することで、一連の作業を完了することができる機能のこと
VBAとは
VB（Visual Basicの略称）というプログラム言語をMicrisoftのOffice使用に作ったものが、VBA（Visual Basic for Applicationsの略称）というプログラム言語
VBEとは
VBE（Visual Basic Editorの略称）は、VBAのプログラム言語でマクロを作成するための専用の編集エディタのこと
VBA開発の設定
タブにマクロの開発を出現させる方法
タブのファイルを選択→オプションを選択
Excelのオプション
リボンのユーザー設定→開発をチェック→OK
タブ内に開発タブが表示される
マクロ有効の保存形式にする
タブのファイル→オプションを選択
Excelのオプション
保存→ブックの保存内のファイルの保存形式（E）をExcelマクロ有効ブック(*.xlsm)に変更→OK
VBEの設定
VBEの立ち上げ方法
ショートカットキー（alt+F11）を押す
下記のようなVBEが立ち上がる
VBEのオプション設定
ツール → オプションを選択
オプション
コードの設定内の
自動構文チェック（K）のチェックを外す（ポップアップが立ち上がって良いならチェックで可）
変数の宣言を強制する（R）にチェックをする
変数の宣言を強制にすると
標準モジュール作成時に先頭に自動的にOption Explicitが入力される。
変数の宣言を強制する（R）にチェックを入れておくことのメリットは、変数の入力ミスが原因のエラーが発生した時に問題箇所をすぐに見つけられるから
標準モジュールの先頭には必ずこのOption Explicitが入力されるようにする
VBEの使用
標準モジュールの追加方法
挿入 → 標準モジュール(M)を選択
プロジェクトエクスプローラーに標準モジュール（Module1）が作成される
標準モジュールの挿入のショートカットキー
Alt + I → M
標準モジュールの削除
モジュールを左クリック→任意のモジュール名の解放(R)を選択
下記のようなポップアップが表示される。
エクスポートが必要なら、『はい』を必要なら、『いいえ』を選択する。
モジュール名の変更
モジュールを選択→プロパティ内のオブジェクト名で名前を変更する
マクロのリセット
実行中のマクロを停止させるには、上部にあるリセットを押下する
ブレークポイントのやり方
ブレークしたい箇所をクリックし、その行の横（下記の画像の茶色マルの箇所）をクリックする
解除方法は、ブレークポイントの箇所（上記画像の茶色マルの箇所）を再度クリックすると解除される
プロシージャ
プロシージャとは、手続きという意味
プロシージャの作り方
小文字で記載する
１．sub プロシージャ名
２．Enterキーを押し、Tabキーを押す
３．下記のように、最初のSが大文字になり、プロシージャ名の後にカッコが自動で付き、End Subと自動で
入力される
４．SubとEnd Sub内にコードをサンドする
５．F5で実行
Sub sample()
End Sub
サンプルコード
Option Explicit
Sub sample() → Subプロシージャとして、コードをサンドする
MsgBox Sheets.Count
End Sub
上記コードでは、シートの数をメッセージとしてポップアップすることができる。
プロシージャの命名する規則
・ １文字目は英数字、漢字、ひらがな、カタカナのみ（数字は不可）
・ 使える記号はアンダーバー( _ )のみ
・ 空白も使用不可
・ １つの標準モジュール上に同じ名前のプロシージャは２個作れない
画面の更新を止めて処理を高速化する方法
VBAで画面の処理を止める方法は、
Application.ScreenUpdating = False
を使用する
Sub sample()
Application.ScreenUpdating = False → 画面更新停止
メイン処理
Application.ScreenUpdating = True → 画面更新再開
End Sub
画面更新を止めることで無駄な表示がなくなるので、処理が高速化する。
プロパティとメソッド
プロパティ
【取得】
値取得 =オブジェクト.プロパティ
【設定】
オブジェクト.プロパティ = 設定値
オブジェクト：操作の対象となるもの。セル、シート、ブックなど。
プロパティ：オブジェクトの属性（オブジェクトの情報そのもの）。字（Font）についての情報には「太字かどう
か」、「色」、「大きさ」などの種類
サンプルコード
Range("E1").Interior.Color = vbRed
『E1セルのInteriorオブジェクトのColorプロパティの値をvbRedにする』という意味
Rangeとは
・ 固定位置のセルの場合 Range("B2")
・ セル範囲（複数セル）の場合 Range("A1:C5")
・ 名前定義を使う時 Range("名前定義の名前")
Cellsとは
・ 単一（1つの）セルを指定する場合 Cells(行, 列)：Cells(5, “A”)
変数は、Rangeでは使用できない
メソッド
シートやセルの追加、削除、またデータの並べ替えやオートフィルタなど、操作対象の「動作」を命令
オブジェクト.メソッド 引数:=値
サンプルコード
Sub sample()
Sheets.Add After:=Sheets("Sheet1")
ActiveSheet.Name = "sample"
End Sub
『Sheet1のシートの後に、sampleという名前で、シートを追加する』という意味
他のシートの追加方法
'アクティブシートの一つ前にシートを追加
Sheets.Add
'シートを先頭に追加
Sheets.Add Before:=Sheets(1)
'Sheet1シートの前に追加
Sheets.Add Before:=Sheets("Sheet1")
'Sheet1シートの後に追加
Sheets.Add After:=Sheets("Sheet1")
'3番目に追加（2番目の後ろ）
Sheets.Add After:=Sheets(2)
'最後尾に追加
Sheets.Add After:=Sheets(Sheets.Count)
コメントアウトするには
‘Sheet1シートの前に追加 → コメントアウトしたい文の前にカンマ( ‘ )を付ける
Sheets.Add Before:=Sheets("Sheet1")
コピーしたシートを作成する方法
'「test」シートを、最後にコピーする
Sheets("test").Copy After:=Sheets(Sheets.Count)
'「test」シートを、先頭にコピーする
Sheets("test").Copy Before := Sheets(1)
シートの削除方法
Sub sample()
Application.DisplayAlerts = False
‘削除メソッド
Sheets("sample").Delete
End Sub
Application.DisplayAlerts = Falseは、『削除しますか？』というアラートを聞いてこないようにしている設定
アラートをありにする場合は、Trueにするか、記述をしない
ブックを保存する方法
'マクロ実行ブックの上書き保存
ThisWorkBook.Save
'アクティブワークブックの上書き保存
ActiveWorkBook.Save
'ブック名を指定した上書き保存
Workbooks("ブック名").Save
For Next構文
連続処理、繰り返し処理をするVBA構文
For カウント変数 = 初期値 To 終了値
処理
Next カウント変数
例）product_table
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | 商品 | 単価 | 売上数量 | 売上総額 |
| 2 | テレビ | 100,000 | 3 | |
| 3 | 冷蔵庫 | 50,000 | 5 | |
| 4 | 掃除機 | 30,000 | 10 |
上記のテーブルに記載されているテレビ・冷蔵庫・掃除のそれぞれの売上総額をだす場合
Sub product()
Dim i → iを変数宣言
For i = 2 To 4 → 2〜4まで
Cells(i, "D") = Cells(i, "B") * Cells(i, "C")
Next i
End Sub
カラムDのフィールドの値＝カラムBのフィールドの値×カラムCのフィールドの値というプロシージャになる。それを変数iが2～4の間まで繰り返し行われる。
実行結果は、下記になる
例）product_table
| A | B | C | D | E |
|---|---|---|---|---|
| 1 | 商品 | 単価 | 売上数量 | 売上総額 |
| 2 | テレビ | 100,000 | 3 | 300,000 |
| 3 | 冷蔵庫 | 50,000 | 5 | 250,000 |
| 4 | 掃除機 | 30,000 | 10 | 300,000 |
ステップイン（F8）を使用すれば、マクロを1行ずつ実行することもできる
繰り返しカウントの増減を変える場合
For i = 初期値 To 終了値 Step 増加数(+)/(-)
Stepを使用して、増減数を決める
'１ずつ減少していく
For i = 100 To 1 Step -1 → iが、1ずつ100～1まで繰り返す
Next i
'5ずつ増加していく
For i = 1 To 100 Step 5 → iが、5ずつ1～100まで繰り返す
Next i
変数宣言
変数名の命名規則
・ 半角､全角の数字と文字を使えます
・ 変数名の長さは、半角で255文字以内（全角なら127文字以内）です
・ 記号はアンダーバーのみ使用可能（ピリオドやスペースは使用できない
・ 1文字目は、数字、_アンダーバー、記号を使用することができない
・ VBAが意味を持たせている語句（予約語）は使用できない
・ 大文字､小文字の区別がなく､同様に扱われる
型指定
Dim i As データ型
| データ型 | 名称 | 格納できる範囲 |
|---|---|---|
| Integer | 整数型 | -32,768 ～ 32,767 |
| Long | 長整数型 | -2,147,483,648 ～ 2,147,483,647 |
| LongLong | 64ビット符号付き数値 | -9223372036854775808 ～ 9223372036854775807 ※64ビットプラットフォームのみで有効な宣言型 |
| Single | 単精度浮動小数点数型 | -3.402823E38 ～ -1.401298E-45(負の値) 1.401298E-45 ～ 3.402823E38(正の値) |
| Double | 倍精度浮動小数点数型 | -1.79769313486232E308 ～ -4.94065645841247E-324(負の値) 4.94065645841247E-324 ～ 1.79769313486232E308(正の値) |
| Currency | 通貨型 | -922,337,203,685,477.5808 ～ 922,337,203,685,477.00 |
| String | 文字列型 | 最大約20億文字まで |
| Date | 日付型 | 西暦100 年1月1日～西暦9999年12月31日までの日付と時刻 |
| Byte | バイト型 | 0～255の範囲の単精度の正の数値。8 ビット(1 バイト) |
| Boolean | ブール型 | 真 (True) または偽 (False) |
| Object | オブジェクト型 | オブジェクト |
| Variant | バリアント型 | すべてのデータ |
型指定をしなければ、Variant型になり、何でも入れることができる
データの最終行、最終列を自動的に取得する
最終行の取得
データの最終行を取得するには、シートの最終行から記載のある行まで移動する記述をするとよい
Cells(Rows.Count, 1).End(xlUp).Row
Rows.Countは、シートの行数を表す
2007以降では、1048576
2003では、65536
End(xlUp).Rowは、ctrl + ↑のセルの行数へ移動という意味
まとめると・・・
Cells(Rows.Count, 1).End(xlUp).Rowを日本語に訳すと
セルＡ1048576からCtrl + ↑のセルの行数という意味になる（2007年以降）
セルAは、(Rows.Count, 1)の１の部分を指す
Endプロパティの方向
xlUp : ctrl +↑上へ
xlDown : ctrl +↓下へ
xlToLeft : ctrl +←左へ
xlToRight : ctrl +→右へ
Sub sample()
Dim i As Long → iは、整数型指定
For i = 11 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, "C") = Cells(i, "A") / Cells(i, "B") → 処理方法
Next
End Sub
上記のコードは、11行目から記載がある最終行まで、ループ処理（割り算）が繰り返されるという意味になる。
最終列の取得
Cells(1, Columns.Count).End(xlToLeft).Column
最終行と同様で、1行目の最も右端の列から、Ctrl + ←で一番初めのデータのあるセルの列を返す。という意味になる
VBA関数とワークシート関数
VBA関数一覧
| 文字列操作・分岐 | |
|---|---|
| Asc | 整数型 (Integer) の値を返します。指定した文字列内にある先頭の文字の文字コードを返す変換関数 |
| AscB | 最初の文字の文字コードではなく、最初のバイト データを返す |
| AscW | Unicode 文字セットの文字を返す |
| Chr | 指定した文字コードに対応する文字を示す文字列型 (String) の値を返す |
| ChrB | 文字ではなく、1 バイトを返す |
| ChrW | Unicode 文字セットの文字を含む文字列型 (String) で文字を返す |
| LCase | アルファベットの大文字を小文字に変換する文字列処理関数 |
| Left | バリアント型 (内部処理形式 String の Variant) の値を返します。文字列の左端から指定した文字数分の文字列を返す |
| LeftB | 指定したバイト数分の文字列を取り出す |
| Len | 指定した文字列の文字数または指定した変数に必要なバイト数を表す長整数型 (Long) の値を返す |
| LenB | 指定した文字列のバイト数を返す |
| LTrim | 指定した文字列から先頭のスペースを削除した文字列を表すバリアント型 (内部処理形式 String の Variant) の値を返す |
| Format | 式を指定した書式に変換し、その文字列を示すバリアント型 (内部処理形式 String の Variant) の値を返す |
| FormatCurrency | システムの [コントロール パネル] で定義されている書式を使って通貨形式の文字列を返す文字列処理関数 |
| FormatDateTime | 日付形式または時刻形式の文字列を返す文字列処理関数 |
| FormatNumber | 数値形式の文字列を返す |
| FormatPercent | 100 で乗算したパーセント形式の式にパーセント記号 (%) を付加して返す |
| InStr | バリアント型 (内部処理形式 Long の Variant) の値を返します。ある文字列 (string1) の中から指定した文字列 (string2) を検索し、最初に見つかった文字位置 (先頭からその位置までの文字数) を返す文字列処理関数 |
| InStrRev | ある文字列 (string1) の中から指定された文字列 (string2) を最後の文字位置から検索を開始し、最初に見つかった文字位置 (先頭からその位置までの文字数) を返す文字列処理関数 |
| Mid | バリアント型 (内部処理形式 String の Variant) の値を返します。文字列から指定した文字数分の文字列を返す |
| MidB | 指定したバイト数分の文字列を取り出す |
| Replace | 指定された文字列の一部を、別の文字列で指定された回数分で置換した文字列を返す |
| Right | バリアント型 (内部処理形式 String の Variant) の値を返します。文字列の右端から指定した文字数分の文字列を返す |
| RightB | 指定したバイト数分の文字列を取り出す |
| RTrim | 指定した文字列から末尾のスペース (RTrim)を削除した文字列を表すバリアント型 (内部処理形式 String の Variant) の値を返す |
| Space | バリアント型 (内部処理形式 String の Variant) の値を返します。指定した数のスペースからなる文字列を返す文字列処理関数 |
| Str | バリアント型 (内部処理形式 String の Variant) の値を返します。数式の値を文字列で表した値 (数字) で返す文字列処理関数 |
| StrComp | 文字列比較の結果を表すバリアント型 (内部処理形式 String の Variant) の値を返す |
| StrConv | 変換した文字列をバリアント型 (内部処理形式 String の Variant) で返す |
| String | バリアント型 (内部処理形式 String の Variant) の値を返します。指定した文字コード (ASCII またはシフト JIS コード) の示す文字、または文字列の先頭文字を、指定した文字数だけ並べた文字列を返す文字列処理関数 |
| StrReverse | 指定された文字列の文字の並びを逆にした文字列を返す |
| Trim | 指定した文字列から先頭と末尾の両方のスペースを削除した文字列を表すバリアント型 (内部処理形式 String の Variant) の値を返す |
| UCase | バリアント型 (内部処理形式 String の Variant) の値を返します。指定したアルファベットの小文字を大文字に変換する文字列処理関数 |
| Val | 指定した文字列に含まれる数値を適切なデータ型に変換して返す |
| 分岐 | |
|---|---|
| IIf | 式の評価結果によって、2 つの引数のうち 1 つを返す |
| Choose | 引数の一覧から値を選択し、返す |
| Switch | 式のリストを評価し、リストの中で真 (True) となる最初の式に関連付けられたバリアント型 (Variant) の値または式を返す |
| 型変換 | |
|---|---|
| CBool | ブール型 (Boolean)のデータ型に変換す 任意の有効な文字列または数値 |
| CByte | バイト型 (Byte)のデータ型に変換 |
| CCur | 通貨型 (Currency)のデータ型に変換 |
| CDate | 日付型 (Date)のデータ型に変換 任意の有効な日付 |
| CDbl | 倍精度浮動小数点数型 (Double)のデータ型に変換 |
| CDec | 10 進型 (Decimal)のデータ型に変換 |
| CInt | 整数型 (Integer)のデータ型に変換します。-32,768 ～ 32,767。 小数部分は丸められる |
| CLng | 長整数型 (Long)のデータ型に変換します。-2,147,483,648 ～ 2,147,483,647。 小数部分は丸められる |
| CLngLng | -9,223,372,036,854,775,808から9,223,372,036,854,775,807。 小数点以下は丸められます(64 ビット プラットフォームのみで有効)。 |
| CLngPtr | 32ビットのシステムでは-2,147,483,648～2,147,483,647、 64ビットのシステムでは-9,223,372,036,854,775,808～9,223,372,036,854,775,807。 32ビットと64ビットのどちらのシステムでも、小数部分は丸められる。 |
| CSng | 単精度浮動小数点数型 (Single)のデータ型に変換 |
| CStr | 文字列型 (String)のデータ型に変換 CStrの戻り値は引数 expression により異なる |
| CVar | バリアント型 (Variant)のデータ型に変換 数値の場合は倍精度浮動小数点数型の範囲と同じ。 数値以外の場合は、文字列型の範囲と同じ |
| CVErr | ユーザーが指定した数値 (エラー番号) を、バリアント型 (Variant) の内部処理形式であるエラー値に変換した値を返す変換関数 |
| データ判定 | |
|---|---|
| IsArray | 変数が配列であるかどうかを調べ、結果をブール型 (Boolean) で返す |
| IsDate | 式を日付に変換できるかどうかを調べ、結果をブール型 (Boolean) で返す |
| IsEmpty | 変数が Empty 値かどうかを調べ、結果をブール型 (Boolean) で返す |
| IsError | 式がエラー値かどうかを調べ、結果をブール型 (Boolean) で返す |
| IsMissing | プロシージャに省略可能なバリアント型 (Variant) の引数が渡されたかどうかを調べ、結果をブール型 (Boolean) で返す |
| IsNull | 式に Null 値が含まれているかどうかを調べ、結果をブール型 (Boolean) で返す |
| IsNumeric | 式が数値として評価できるかどうかを調べ、結果をブール型 (Boolean) で返す |
| IsObject | 識別子がオブジェクト変数を表しているかどうかを示すブール型 (Boolean) の値を返す |
| TypeName | 変数に関する情報を提供する文字列型 (String) の文字列を返す |
| VarType | 変数の内部処理形式を表す整数型 (Integer) の値を返す |
| 日付時刻 | |
|---|---|
| Date | 現在のシステムの日付を含むバリアント型 (内部処理形式 Date の Variant) の値を返す |
| DateAdd | 指定された時間間隔を加算した日付をバリアント型 (内部処理形式 String の Variant) の値で返す |
| DateDiff | 2つの指定した日付の時間間隔を表すバリアント型 (内部処理形式 Date の Variant) の値を指定 |
| DatePart | 日付の指定した部分を含むバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| DateSerial | 引数に指定した年、月、日に対応するバリアント型 (内部処理形式 Date の Variant) の値を返す関数 |
| DateValue | 日付を表すバリアント型 (内部処理形式 Date の Variant) の値を返す |
| Day | 月の何日かを表す 1～31 の範囲の整数を表すバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| Hour | 1日の時刻を表す 0～23 の範囲の整数を表すバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| Minute | 時刻の分を表す 0～59 の範囲の整数を表すバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| Month | 1年の何月かを表す 0～12 の範囲の整数を表すバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| MonthName | 指定された月を表す文字列を返す |
| Now | コンピュータのシステムの日付と時刻の設定に基づいて、現在の日付と時刻を表すバリアント型 (内部処理形式 Date の Variant) の値を返す |
| Second | 時間の秒を表す 0～59 の範囲の整数を表すバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| Time | 現在のシステムの時刻を表すバリアント型 (内部処理形式 Date の Variant) の値を返す |
| Timer | 午前0時 (真夜中) から経過した秒数を表す単精度浮動小数点数型 (Single) の値を返す |
| TimeSerial | 引数で指定した時、分、および秒に対応する時刻を含むバリアント型 (内部処理形式 Dateの Variant) の値を返す |
| TimeValue | 時刻を表すバリアント型 (内部処理形式 Date の Variant) の値を返す |
| Weekday | 何曜日であるかを表す整数を表すバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| WeekdayName | 指定された曜日を表す文字列を返す |
| Year | 年を表すバリアント型 (内部処理形式 Integer の Variant) の値を返す |
| 配列 | |
|---|---|
| Array | 配列が格納されたバリアント型 (Variant) の値を返す |
| Filter | 指定されたフィルタ条件に基づいた文字列配列のサブセットを含むゼロ ベースの配列を返す |
| Join | 配列に含まれる各要素の内部文字列を結合して作成される文字列を返す |
| LBound | 配列の指定された次元で使用できる最小の添字を、長整数型 (Long)の値で返す |
| Split | 各要素ごとに区切られた文字列から 1 次元配列を作成し、返す |
| UBound | 配列の指定された次元で使用できる添字の最大値を、長整数型 (Long) の値で返す |
| ファイル操作 | |
|---|---|
| CurDir | 指定したドライブの現在のパスを表すバリアント型 (内部処理形式 String の Variant) の値を返す |
| Dir | 指定したパターンやファイル属性と一致するファイルまたはフォルダの名前を表す文字列型 (String) の値を返す。 ドライブのボリューム ラベルも取得できる |
| EOF | ランダム アクセス モード (Random) またはシーケンシャル入力モード (Input) で開いたファイルの現在位置がファイルの末尾に達している場合、ブール型 (Boolean) の値の真 (True) を含む整数型 (Integer) の値を返す |
| FileAttr | Open ステートメントで開いたファイルのファイル モードを示す長整数型 (Long) の値を返す |
| FileDateTime | 指定したファイルの作成日時または最後に修正した日時を示すバリアント型 (内部処理形式 Date の Variant) の値を返す |
| FileLen | ファイルのサイズをバイト単位で表す長整数型 (Long) の値を返す |
| FreeFile | 使用可能なファイル番号を整数型 (Integer) の値で返すファイル入出力関数 |
| GetAttr | ファイルまたはフォルダの属性を表す整数型 (Integer) の整数を返す |
| Input | シーケンシャル入力モード (Input) またはバイナリ モード (Binary) で開いたファイルから指定した文字数の文字列を読み込み、文字列型 (String) の値を返す |
| Loc | 開いたファイル内の現在の読み込み位置または書き込み位置を示す長整数型 (Long) の値を返す |
| LOF | Open ステートメントを使用して開いたファイルの長さをバイト単位で示す長整数型 (Long) の値を返す |
| Seek | Open ステートメントを使用して開いたファイルの現在の読み込み位置または書き込み位置を示す長整数型 (Long) の値を返す |
| Spc | Print # ステートメントまたは Print メソッドと共に使用し、指定した数のスペースを挿入するファイル入出力関数 |
| Tab | Print # ステートメントまたは Print メソッドと共に使用し、次の文字の出力位置を移動するファイル入出力関数 |
| 数学/財務 | |
|---|---|
| Abs | 引き渡した数値の絶対値を同じデータ型で返す |
| Atn | 指定した数値のアークタンジェントを倍精度浮動小数点数型 (Double) で返す |
| Cos | 指定した角度のコサインを倍精度浮動小数点数型 (Double) で返す |
| DDB | 倍精度浮動小数点数型 (Double) の値を返す。 倍率法などの指定した方法を使って特定の期における資産の減価償却費を返す |
| Exp | 指数関数 (e を底とする数式のべき乗) を計算する数値演算関数 |
| Fix | 指定した数値の整数部分を返す |
| FV | 倍精度浮動小数点数型 (Double) の値を返す。 定額の支払いを定期的に行い、利率が一定であると仮定して、投資の将来価値を返す |
| Hex | 指定した値を 16 進数で表した文字列型 (String) を返す |
| Int | 指定した数値の整数部分を返す |
| IPmt | 倍精度浮動小数点数型 (Double) の値を返す。 定額の支払いを定期的に行い、利率が一定であると仮定して、投資期間内の指定した期に支払う金利を返す |
| IRR | 倍精度浮動小数点数型 (Double) の値を返す。 一連の定期的なキャッシュ フロー (支払いと収益) に対する内部利益率を返す |
| Log | 倍精度浮動小数点数型 (Double) の自然対数を返す数値演算関数 |
| MIRR | 倍精度浮動小数点数型 (Double) の値を返す。 一連の定期的なキャッシュ フロー (支払いと収益) に基づいて、修正内部利益率を返す |
| NPer | 倍精度浮動小数点数型 (Double) の値を返す。 定額の支払いを定期的に行い、利率が一定であると仮定して、投資に必要な期間を返す |
| NPV | 倍精度浮動小数点数型 (Double) の値を返す。 一連の定期的なキャッシュ フロー (支払いと収益) と割引率に基づいて、投資の正味現在価値を返す |
| Oct | 引数に指定した値を 8 進数で表すバリアント型 (内部処理形式 String の Variant) の値を返す |
| Pmt | 倍精度浮動小数点数型 (Double) の値を返す。 定額の支払いを定期的に行い、利率が一定であると仮定して、投資に必要な定期支払額を返す |
| PPmt | 倍精度浮動小数点数型 (Double) の値を返す。 定額の支払いを定期的に行い、利率が一定であると仮定して指定した期に支払われる元金を返す |
| PV | 倍精度浮動小数点数型 (Double) の値を返す。 定額の支払いを定期的に行い、利率が一定であると仮定して、投資の現在価値を返す |
| Rate | 倍精度浮動小数点数型 (Double) の値を返す。 投資期間を通じての利率を返す |
| Rnd | 単精度浮動小数点数型 (Single) の乱数を返す |
| Round | 指定された小数点位置で丸めた数値を返す |
| Sgn | 引数に指定した値の符号をバリアント型 (内部処理形式 Integer の Variant) の値で返す数値演算関数 |
| Sin | 指定した角度のサインを倍精度浮動小数点数型 (Double) の値で返す数値演算関数 |
| SLN | 倍精度浮動小数点数型 (Double) の値を返す。 定額法を用いて、資産の1期当たりの減価償却費を返す |
| Sqr | 数式の平方根を倍精度浮動小数点数型 (Double) の値で返す数値演算関数 |
| SYD | 倍精度浮動小数点数型 (Double) の値を返す。 定額逓減法を使って、指定した期の減価償却費を返す |
| Tan | 指定した角度のタンジェントを倍精度浮動小数点数型 (Double) の値で返す数値演算関数 |
| その他 | |
|---|---|
| CallByName | 指定した オブジェクトのメソッドの実行、あるいはプロパティの値の取得や設定を行う |
| Command | Microsoft Visual Basic または Visual Basic で開発した実行可能なプログラムを起動させるために使用するコマンド ラインの引数の部分を返す。 Visual Basic の Commandは Microsoft Office アプリケーションでは使用できない。 |
| CreateObject | ActiveX オブジェクトへの参照を作成して返す |
| DoEvents | 発生したイベントがオペレーティング システムによって処理されるように、プログラムで占有していた制御をオペレーティング システムに渡すフロー制御関数 |
| Environ | オペレーティング システムの環境変数に割り当てられた文字列型 (String) を返します。Macintosh では使用できない |
| Error | 指定したエラー番号に対応するエラー メッセージを返す |
| GetAllSettings | Microsoft Windows のレジストリにあるアプリケーションの項目、または Macintosh の場合にはアプリケーションの初期設定ファイルから、SaveSetting ステートメントを使って作成された項目内のすべてのキー設定および各キー設定に対応する値のリストを返す |
| GetObject | ファイルから取得した ActiveX オブジェクトへの参照を返す |
| GetSetting | Microsoft Windows のレジストリにあるアプリケーションの項目、または Macintosh の場合にはアプリケーションの初期設定ファイルから、キー設定値を返す |
| IMEStatus | 整数型 (Integer) の値を返す。IME の現在の状態を返す |
| InputBox | 文字列型 (String) の値を返す。 ダイアログ ボックスにメッセージとテキスト ボックスを表示し、文字列が入力されるか、またはボタンがクリックされると、テキスト ボックスの内容を返す |
| MacID | Macintosh でのみ使用する関数。 4個の連続した文字定数を Dir、Kill、Shell、および AppActivateで使う値に変換 |
| MacScript | AppleScript のスクリプトを実行。Macintosh (漢字 Talk7 以降) で有効 |
| MsgBox | 整数型 (Integer) の値を返す。 ダイアログ ボックスにメッセージを表示し、ボタンがクリックされるのを待って、どのボタンがクリックされたのかを示す値を返す |
| Partition | ある数値が、区切られた複数の範囲のうち、どの範囲に含まれるかを示すバリアント型 (内部処理形式 String の Variant) の文字列を返す |
| QBColor | 指定した色番号に対応する RGB コードを表す長整数型 (Long) の値を返す |
| RGB | 色の RGB 値を表す長整数型 (Long) の値を返す |
| Shell | 実行可能プログラムを実行し、実行が完了するとプログラムのタスク ID を示すバリアント型 (内部処理形式 Double の Variant) の値を返す。 プログラムの実行に問題が発生した場合は、0を返す |
ワークシート関数
Sub sample() → プロシージャ名は任意
WorksheetFunction.ワークシート関数
End Sub
上記の構文のように、worksheetfunctionと書いてから、.（ピリオド）を打つと使える関数の候補が表示される
ワークシート関数で使用できない関数
・ 文字列関数
・ 日付関数
VBA関数にある同名の関数が用意されているため、ワークシート関数としては使用できない
長いスペルの単語を簡単に入力するショートカットキー
Ctrl + Spaceキーで
上記画像のように候補表示がされる
入力したい単語の先頭文字から入力することで、目的の単語が出現する
worksheetFunctionを選択し、Tabキーで確定すると入力したい単語の全文が表示できる
IF Then構文
条件により処理を分岐させたい場合に使用
If 条件式 Then
真の場合の処理 → 条件を満たした場合の処理
Else
偽の場合の処理 → 条件を満たさない場合の処理
End If
比較演算子
| 比較演算子 | 意味 | 読み方 |
|---|---|---|
| A > B | AはBよりも大きい | 大なり（だいなり） |
| A >= B | AはB以上 大なり | イコール |
| A < B | AはBよりも小さい | 小なり（しょうなり） |
| A <= B | AはB以下 小なり | イコール |
| A = B | AはBと等しい | 等号、イコール |
| A <> B | AとBは等しくない | 山カッコ、大なり小なり |
論理演算子
| 論理演算子 | 意味 | 条件例 | 結果 |
|---|---|---|---|
| And | 論理積 | True And True True And False False And True False And False |
TRUE FALSE FALSE FALSE |
| Or | 論理和 | True Or True True Or False False Or True False Or False |
TRUE TRUE TRUE FALSE |
| Xor | 排他論理和 | True Xor True True Xor False False Xor True False Xor False |
FALSE TRUE TRUE FALSE |
| Not | 論理否定 | Not True Not False |
FALSE TRUE |
| Eqv | 論理等価 | ||
| Imp | 論理包含 |
If Thenの使い方
| A | B | C | |
|---|---|---|---|
| 1 | 出席番号 | 得点 | 評価 |
| 2 | 1 | 68 | |
| 3 | 2 | 91 | |
| 4 | 3 | 20 | |
| 5 | 4 | 27 | |
| 6 | 5 | 62 | |
| 7 | 6 | 97 | |
| 8 | 7 | 91 | |
| 9 | 8 | 82 | |
| 10 | 9 | 92 | |
| 11 | 10 | 31 |
上記、表で、得点が80点以上の場合、『Ａ』、60点以上79点未満の場合、『Ｂ』、59点以下の場合、『Ｃ』となる評価をつける場合
Sub sample() → プロシージャ名は任意
Application.ScreenUpdating = False → 画面更新停止
Dim i As Long → 変数宣言（整数型を指定）
For i = 10 To Cells(Rows.Count, 1).End(xlUp).Row → 最終行を取得
If Cells(i, 3) >= 80 Then → 80点以上の条件式
Cells(i, 4) = "A"
ElseIf Cells(i, 3) >= 60 Then → 60点以上の条件式
Cells(i, 4) = "B"
Else → 上記条件式に当てはまらない
Cells(i, 4) = "C"
End If
Next
End Sub
上記コードを実行すると
以下のように、条件通りに評価が出現する
| A | B | C | |
|---|---|---|---|
| 1 | 出席番号 | 得点 | 評価 |
| 2 | 1 | 68 | B |
| 3 | 2 | 91 | A |
| 4 | 3 | 20 | C |
| 5 | 4 | 27 | C |
| 6 | 5 | 62 | B |
| 7 | 6 | 97 | A |
| 8 | 7 | 91 | A |
| 9 | 8 | 82 | A |
| 10 | 9 | 92 | A |
| 11 | 10 | 31 | C |
このように複数の条件式を使用する場合は、
If 条件式1 Then
条件式1が成立した場合に実行する処理
ElseIf 条件式2 Then
条件式2が成立した場合に実行する処理
Else
どの条件式も成立しなかった場合に実行する処理
End If
を使用する
シートの指定方法
・ シートの名称で指定
・ シートのインデックス番号で指定
・ シートのオブジェクト名で指定
シートの名称で指定
Worksheets("シート名") または Sheets("シート名")
実際のシート名と一字一句合っている必要がある
シートのインデックス番号で指定
Worksheets(1) または Sheets(1)
カッコ内の数字は、シートが左から何番目にあるかというインデックス番号
シートのオブジェクト名で指定
プロジェクトエクスプローラーの各シートのモジュールに、
Sheet1(sample1)とシート名が記載されている。
カッコ内のsample1が実際のシート名で、カッコ手前のSheet1がオブジェクト名
このオブジェクト名を指定する
オブジェクト名をシート名と同じ名前に変更する
With構文
Withに指定したオブジェクトに対してオブジェクト名を再度記述することなく、プロパティやメソッドを記述することができる
オブジェクト.プロパティ = 値
オブジェクト.メソッド
上記のように記述するところを Withを使用すると
With 省略するオブジェクト名
.プロパティ = 値
.メソッド
End With
上記のように、ピリオド（ . ）から記述することができる
Sub sample()
Application.ScreenUpdating = False
Dim i As Long
With Sheets("sample1") → withでシート（ここではsmapleというシート）を指定する
For i = 10 To .Cells(.Rows.Count, 2).End(xlUp).Row → CellsやRowsでシート名が必要な箇所に、ピリオド(.)を記述
If .Cells(i, 3) >= 80 Then
.Cells(i, 4) = "A"
ElseIf .Cells(i, 3) >= 60 Then
.Cells(i, 4) = "B"
Else
.Cells(i, 4) = "C"
End If
Next
End With → withを締めるのを忘れない
End Sub
便利なコード
表範囲のデータ部分の指定
Range("A5").CurrentRegion.Offset(1, 0)
・ Range("A5").CurrentRegion：シート上でA5セルを選択しCtrl+Aを押した範囲
・ Offset(1, 0)：範囲をずらす命令Offset(ずらす行数, ずらす列数)
オートフィルタ
Range("A5").AutoFilter field:=4, Criteria1:="B" → 表範囲4列目にBを抽出するオートフィルタを実行
Range("A5").AutoFilter → オートフィルタを解除
Range.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
| Field | 必ず指定 フィルターの対象となるフィールド番号を整数で指定 フィールド番号は、リストの左側から始まる。 つまり、最も左側にあるフィールドはフィールド番号1 |
| Criteria1 | 省略可能 抽出条件となる文字列 ("101"など) を指定。 ・ "=" と指定すると、空白セルが抽出される。 ・ "<>" と指定すると空白以外のフィールドが抽出される。 ・ この引数を省略すると、抽出条件はALLになる。 ・ 引数OperatorにxlTop10Itemsが指定されている場合は、引数Criteria1に項目数を指定(たとえば "10")。 |
| Operator | 省略可能 フィルターの種類をXlAutoFilterOperatorクラスの定数のいずれかで指定。 |
| Criteria2 | 省略可能 2 番目の抽出条件となる文字列を指定。 引数 Criteria1および引数 Operatorと組み合わせて使い、複合抽出条件を指定。 |
| VisibleDropDown | 省略可能 ・ True：フィルタのフィールドのオートフィルタのドロップダウン矢印を表示。 ・ False：フィルタのフィールドにオートフィルタのドロップダウン矢印を非表示。 既定値は True |
Operatorに指定するXlAutoFilterOperatorクラスの定数
| xlAnd | 抽出条件 1 と抽出条件 2 の論理演算子 AND |
| xlBottom10Items | 表示される最低値項目 (抽出条件 1 で指定される項目数) |
| xlBottom10Percent | 表示される最低値項目 (抽出条件 1 で指定される割合) |
| xlFilterCellColor | セルの色 |
| xlFilterDynamic | 動的フィルター |
| xlFilterFontColor | フォントの色 |
| xlFilterIcon | フィルター アイコン |
| xlFilterValues | フィルターの値 |
| xlOr | 抽出条件 1 または抽出条件 2 の論理演算子 OR |
| xlTop10Items | 表示される最高値項目 (抽出条件 1 で指定される項目数) |
| xlTop10Percent | 表示される最高値項目 (抽出条件 1 で指定される割合) |
並べ替え
Range(セル範囲).Sort メソッド
Sortメソッドの設定一覧
| 引数 | 定数 | 説明 |
|---|---|---|
| Key1 | － | Rangeオブジェクトでセル範囲を指定 |
| Order1 | xlAscending xlDescending |
昇順の並び替え 降順に並び替え |
| Key2 | － | 2番目に優先するRangeオブジェクトを指定 |
| Order2 | xlAscending xlDescending |
昇順の並び替え 降順に並び替え |
| Key3 | － | 3番目に優先するRangeオブジェクトを指定 |
| Order3 | xlAscending xlDescending |
昇順の並び替え 降順に並び替え |
| Header | xlGuess xlNo xlYes |
先頭をタイトルか自動判定 先頭をタイトルとみなさない 先頭をタイトルとみなす |
| MatchCase | TRUE FALSE |
大文字・小文字を区別する 大文字・小文字を区別しない |
| Orientation | xlSortRows xlSortColumn |
行(縦方向)の並び替え 列(横方向)の並び替え |
| OrderCustom | － | ユーザー設定の並べ替え基準 |
| SortMethod | xlPinYin xlStroke |
日本語をふりがなで並べ替え 日本語を文字コードで並べ替え |
Sortオブジェクト
Excel2007以降、Sortオブジェクトも使えるようになった。
| プロパティ | 定数 | 説明 |
|---|---|---|
| Header | xlGuess xlNo xlYes |
先頭をタイトルか自動判定 先頭をタイトルとみなさない 先頭をタイトルとみなす |
| MatchCase | TRUE FALSE |
大文字・小文字を区別する 大文字・小文字を区別しない |
| Orientation | xlSortRows xlSortColumn |
行(縦方向)の並び替え 列(横方向)の並び替え |
| SortMethod | xlPinYin xlStroke |
日本語をふりがなで並べ替え 日本語を文字コードで並べ替え |
SortFieldsコレクションのAddメソッドを使ってソートの設定
| 引数 | 定数 | 説明 |
|---|---|---|
| Key | － | Rangeオブジェクトでセル範囲を指定 |
| SortOn | xlSortOnValues xlSortOnCellColor xlSortOnFontColor xlSortOnIcon |
値で並べ替え 背景色で並べ替え 文字色で並べ替え アイコンで並べ替え |
| Order | xlAscending xlDescending |
昇順 降順 |
| CustomOrder | － | ユーザー設定の並べ替え基準 |
| DataOption | xlSortNormal xlSortTextAsNumbers |
数値とテキストを別々に並べ替え テキストを数値データとして並べ替え |
Sub sample()
Range("A1:K11").Sort _ → A1からK11までの表の範囲で並び替え
Key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, _
Header:=xlYes
End Sub
表範囲の初期化
表範囲のデータ部分のみ消去したいときに使用
セル範囲.ClearContents
または
セル範囲.Clear
Clearは値と書式を消去。何も設定されていない初期状態のセルにする。
ClearContentsは、値のみの消去。セル範囲を選択して、Deleteキーを押す操作
Sub データ初期化()
Range("A5").CurrentRegion.Offset(1, 0).ClearContents
End Sub
上記コードで、表範囲のデータ部分のみ消去できる。
Range("A5").CurrentRegion
表全体の範囲（シート上でA5セルを選択しCtrl+Aを押した範囲）
Offset(1, 0)
表全体の範囲を下に１つずらす。
Offset(ずらす行数, ずらす列数)
ClearContents
セル範囲の値のみを消去
データ範囲に関係なく消去する場合
Cells.ClearContents：全セルを消去
Columns(1).ClearContents：A列を消去
Range("A:B").ClearContents：A:B列を消去
Rows(1).ClearContents：1行目を消去
Range("1:2").ClearContents：1:2行目を消去
他のブックを開く方法
既存ブックを開くには、Wrokbooks.Openを使用する
Workbooks.Open(
FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad
)
| 名前 | 説明 |
|---|---|
| FileName | 開くブックのファイル名 |
| UpdateLinks | ファイル内の外部参照 (リンク) の更新方法を指定 |
| ReadOnly | ブックを読み取り専用モードで開くには、True を指定 |
| Format | テキスト ファイルを開く場合は、この引数で区切り文字を指定 |
| Password | パスワード保護されたブックを開くのに必要なパスワードを指定 |
| WriteResPassword | 書き込み保護されたブックに書き込みをするために必要なパスワードを指定 |
| IgnoreReadOnlyRecommended | [読み取り専用を推奨する] チェック ボックスをオンにして保存されたブックを開くときでも、読み取り専用を推奨するメッセージを非表示にするには、True を指定 |
| Origin | 開こうとしているファイルがテキスト ファイルの場合、それがどのような形式のテキスト ファイルかを指定 |
| Delimiter | 開こうとしているファイルがテキスト ファイルで、引数 Format が 6 の場合は、この引数で区切り文字を使用 |
| Editable | 開こうとしているファイルが Excel 4.0 のアドインの場合、この引数に True を指定すると、アドインがウィンドウとして表示 |
| Notify | ファイルが読み取り/書き込みモードで開けない場合に、ファイルを通知リストに追加するには、True を指定 |
| Converter | ファイルを開くときに最初に使用するファイル コンバーターのインデックス番号を指定 |
| AddToMru | 最近使用したファイルの一覧にブックを追加するには、True を指定。既定値は False |
| Local | Excel の言語設定 に合わせてファイルを保存するには、True を指定 |
| CorruptLoad | 開くモードを、XlCorruptLoad列挙で指定 抽出(xlExtractData)、正常(xlnormalload)、修復(xlRepairFile)から指定 既定の動作はxlNormalLoadになり、回復は行われない |
Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\Test\sample.xls", UpdateLinks:=0, ReadOnly:=True, CorruptLoad:=xlRepairFile
上記は、Cドライブ内のTestフォルダ内にsample.xlsファイルを『外部参照（リンク）は更新しない、読み取り専用、修復モード』で開くという意味。
マクロを書いているブックと同一フォルダにある場合
FileName:=Thisworkbook.Path & "\sample.xls"
Thisworkbook.Path：ブック自身のパスが取得できる。
ファイルが存在するかどうかを判定する
Dir関数を使用する
Dir[(pathname[, attributes])]
| pathname | 省略可能。 ファイル名を表す文字列式を指定。 フォルダ名およびドライブ名も含めて指定。 引数 pathname に指定した内容が見つからないときは、長さ 0 の文字列 (" ") を返す。 "*"または"?" のワイルドカードを使用できる。 |
| attributes | 省略可能。 取得するファイルが持つ属性の値の合計を表す数式または定数を指定。 省略すると、標準ファイルの属性になる。 |
attributesの設定値
| 定数 | 値 | 内容 |
|---|---|---|
| vbNormal | 0 | 標準ファイル |
| vbReadOnly | 1 | 読み取り専用ファイル |
| vbHidden | 2 | 隠しファイル |
| vbSystem | 4 | システム ファイル。 Macintosh では使用できない。 |
| vbVolume | 8 | ボリューム ラベル。この値を指定すると、すべての属性は無効になる。 Macintosh では使用できない。 |
| vbDirectory | 16 | フォルダ |
| vbAlias | 32 | エイリアス ファイル。 Macintosh でのみ使用できない。 |
Dir関数は、pathnameに一致する最初のファイル名を返す。
フォルダ内のすべてのファイルに対して繰り返して処理を実行する場合は、引数を指定せずにDir を実行。
一致するファイル名がなくなると、Dir関数は長さ0の文字列 ("") を返す。
ただし、vbDirectory属性を付けてDirを呼び出しても、継続してサブディレクトリが返されることはない。
複数のファイルを指定するための * または ? のワイルドカードを使用。
使用例
変数 = Dir("C:\test.xls")
"C:\test.xls"が存在する場合はtest.xlsが、存在しない場合は""が変数に入る。
変数 = Dir("C:\*.xls")
Cドライブ直下にある.xlsファイルの先頭のファイル名が入る。
変数 = Dir("C:\", vbNormal + vbReadOnly + vbHidden)
Cドライブ直下にある.標準＋読取専用＋隠しファイルの先頭のファイル名が入る。
変数 = Dir("C:\test", vbDirectory)
"C:\test"のフォルダが存在する場合はtestが、存在しない場合は""が変数に入る。
繰り返し処理をする構文
For～Nextは、繰り返す回数をあらかじめ指定するもの
Do～Loopは、繰り返す回数ではなく、繰り返す条件を指定するもの
Do [While 条件式]
・・・処理・・・
Loop
Do whileの場合は、条件が成立している間は処理内容を繰り返す
Do [Until 条件式]
・・・処理・・・
Loop
Do Untilの場合は、条件が成立するまでの間は処理内容を繰り返す
WhileとUntilの条件は、補集合の関係になる
Not(Whileの条件) = Untilの条件
Whileの条件 = Not(Untilの条件)
Sub sample()
Dim i → 変数iを宣言
i = 1 → 変数iに1を入れる
Do While i <= 10 → 変数iが10以下の間処理を続ける
Cells(i, 1) = 1 → A列のi行に1を入れる
i = i + 1 → '変数iを1増やす
Loop → 条件が成立している間は処理を繰り返すループ
End Sub
Do～Loopのループ処理を終了したい場合
Do
処理を終了したい時に、Exit Do
Loop
Exit Doは、Do～Loopのループを抜ける
ファイルを保存する方法
SaveAsメソッドを使用する。
ブックの内容が保存されるだけでなく、そのブックの更新日時がSaveAsメソッド実行時の日時が設定されるなど、Excelで名前を付けて保存したときと同様の動作となる。
ThisWorkbook.SaveAs ファイル名を含めたファイルパス
| オプション名 | 概要 | 値の入れ方 |
|---|---|---|
| Filename | ファイルパスを含めたファイル名 | 文字列でパス指定 |
| FileFormat | ファイルを保存するフォーマットを指定 | .csv ：xlCSV .txt ：xlText .xlsx ：xlOpenXMLWorkbook .xlsm：xlOpenXMLWorkbookMacroEnabled |
| Password | ファイルを保護するパスワードを指定 (15文字以内) | 文字列でパスワードを指定 |
| CreateBackup | バックアップ ファイルを作成有無 | True/Falseを指定 (Trueで作成) |
オプションを付ける場合は、カンマ区切りでオプション名:=値のように記載
Sub TestSave1()
'ファイル名作成
Dim filename As String
filename = "資料_" & Format(Now, "yyyymmdd") & ".xlsm"
'ファイルパス指定
Dim filepath As String
filepath = ThisWorkbook.Path & "" & filename
'ファイル保存
ThisWorkbook.SaveAs Filename:=filepath, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
'完了メッセージ
MsgBox "ファイルの保存が完了しました" & vbCrLf & _
"保存先は" & filepath & "です。"
End Sub
結果、資料_20221119.xlsxのファイルが完成する。
Format関数
指定した書式に変換した文字列を返す関数