VBAプログラミング¶
開発環境¶
ExcelでVBAの作成¶
- Excelのファイル形式をマクロ有効ブック(拡張子xlsm)にする
- 開発タブがリボンになければリボンの設定で開発を追加する
[ファイル]タブ > [オプション] > [リボンのユーザー設定] を選択、[開発]にチェックを付ける - [開発]タブ > [コード]リボン > [Visual Basic] で、「Microsoft Visual Basic for Application」画面が開く
- プロジェクトペインに、[VBAProject(ファイル名)]があるので、これを選択し右クリックしポップアップメニューから[挿入] > [標準モジュール]を選択する。
[VBAProject(ファイル名)]の下に[標準モジュール]フォルダが現れ、デフォルトで[Module1]が作成される。コードペインに「Module1(コード)」ウィンドウが現れる。 - プロジェクトペイン上で[Module1]を選択し、プロパティペインで[オブジェクト名]欄の値を[Module1]から適切なモジュール名に変更する
おすすめ設定¶
自動構文チェックを外し、変数の宣言を強制する¶
「Microsoft Visual Basic for Application」画面(VBAウィンドウ)の[ツール]メニュー > [オプション] で「オプション」ダイアログを開き、[編集]タブで次の設定にします。
- [自動構文チェック]のチェックを外す
- [変数の宣言を強制する]にチェックを付ける
モジュールの先頭行にOption Explicit
が自動挿入されます。
エディタの色付け¶
VBAウィンドウの[ツール]メニュー > [オプション] で「オプション」ダイアログを開き、[エディターの設定]タブで次の設定にします。
- キーワード を 青に(デフォルトの紺は、黒と見分けがしづらい)
- 識別子 を 茶に(デフォルトは黒)
コーディング¶
小文字で記述するとチェックになる¶
VBAのコードを見ると、キーワード、標準プロシージャ名、プロパティ名などは大文字で始まるキャメルケースです。
しかし、コーディングの際に小文字で入力し、その行でEnterあるいはカーソルキーで上下の行に移動すると、キーワード等の名前が大文字で始まる名前に書き変わります。これをもってスペルミス等がないことの確認ができます。スペルミスであれば該当するキーワード、プロシージャ、プロパティがないので小文字のままです。
デバッグ¶
デバッグ情報の表示¶
コード中にDebug.Print a, b, c...
とデバッグ表示したい要素を指定すると、イミディエイトウィンドウにその要素が表示されます。
イミディエイトウィンドウは、VBA画面の[表示]メニュー > [イミディエイトウィンドウ]で表示します(ショートカットキーは Ctrl + G)。
イミディエイトウィンドウでのFunction実行¶
イミディエイトウィンドウは、Debug.Printの表示だけでなく、Functionの実行も可能です。Funtion add(ByVal a As Long, ByVal b As Long) As Long
の関数を実行するには、イミディエイトウィンドウ上でクエスチョンマークに続き関数名と引数を指定します。
?add(12,24) 36
ローカルウィンドウでの変数表示¶
ローカルウィンドウを表示すると、スコープ内のローカル変数とモジュールの変数の値が確認できます。
変数¶
スコープ¶
関数の中で定義した変数は、その関数の中がスコープとなります。
関数の外(モジュール)に定義した変数は、そのモジュール全体がスコープとなります。
変数の種類¶
定数¶
Const INPUT_FOLDER As String = "入力"
- オブジェクト(Rangeなど)は定数として定義できません
変数¶
Dim result As Long
プロシージャ(Function)¶
プロシージャの定義¶
プロシージャの呼び出し¶
Call と 引数の括弧について¶
Callは省略可能です。引数の括弧は省略可能です。ただし、状況によってCallの有無、括弧の有無でエラーとなる場合があります。
- Callを省略するときは、プロシージャの戻り値を使わない場合、引数の括弧は指定しないのが無難
- Callを省略しないときは、引数の括弧は指定する
サブルーチン(Sub)¶
逆引きメモ¶
ファイル操作¶
ワイルドカードでファイル名を取得(Dir関数)¶
filename = Dir(ThisWorkbook.path & "\処理対象\*.txt")
実行しているExcelブックのあるフォルダの下の"処理対象"フォルダ内にある拡張子.txtに合致するファイルが存在すればそのファイル名を(フォルダ名は付かず)取得します。ファイルが存在しなければ空文字列を返します。合致するファイルが複数あるときは最初の1つ目(拡張子名でソートし、次にファイルベース名でソートし最初の1つ目)のファイル名を取得します。
2つ目以降の合致ファイル名を取得したいときは、上述のDir関数呼び出し後に、繰り返しDir()
と引数なしの呼び出しを、結果が空文字列になるまで続けます。
Excelブックを開いたときに自動で処理を実行¶
標準モジュールにAuto_open()
¶
標準モジュールに、Auto_Open()
のサブルーチンを定義します。Excelブックを開いたときに自動で実行します。
複数の標準モジュールがあり、2つ以上のモジュールにAuto_Open()
を定義するとエラーになりました。
ThisWorkbokにWorkbook_Open()
¶
VBA画面の左側、[Microsoft Excel Objects] > [ThisWorkbook] をダブルクリックし、タイトルバーのすぐ下の左側ドロップダウンリストを[(General)]から[Workbook]に変更します。すると、その右側のドロップダウンリストが[Open]に自動で変更され、エディタ部分にPrivate Sub Workbook_openN()
が生成されます。その中に必要なコードを書きます。
文字列操作¶
文字列中で連続する空白を1つの空白にする¶
ワークシート関数のTrimを使うと、文字列の前後の空白の削除に加えて、文字列の途中にある連続する空白を1つの空白に置き換えます。WorksheetFunction.Trim(line)
正規表現を扱う¶
Dim regexp As Object Set regexp = CreateObject("VBScript.RegExp") With regexp .Pattern = "^\d{4}/\d{2}/\d{2}.*$" End With Dim isYmd As Boolean isYmd = regexp.test(text)
日時・時刻¶
現在日時・時刻の取得¶
いずれも、ローカル時刻
- 現在の日時を得る関数 Now
- 現在の日付を得る関数 Date
- 現在の時刻を得る関数 Time
日付・時刻の計算¶
- 今の日時(Now関数)から8時間後の日時
AddDate("h", 8, Now)
データの格納¶
データの型¶
整数¶
Byteは8bit符号無し整数
Integerは16bit符号付き整数(-32,768~32,767)
Longは32bit符号付き整数(約-20億~+20億)
浮動小数点数¶
Singleは単精度浮動小数点数
Doubleは倍精度浮動小数点数
日付・時刻¶
Date型を使います。日付、時刻、または日付と時刻を入れることができます。
文字列表記をDate型に代入(暗黙の変換)することができます。
Dim theDay As Date theDay = "2018/1/11"
配列¶
サイズ固定の配列、サイズが変更できる配列があります。後者は動的配列と言われます。
配列の次元数を取得する¶
素直に取得する方法がないので、次元を指定してその次元の要素上限数を取得するUBound関数を、エラーがでるまで次元値を増やして呼び続けます。
Public Function dimensionsOf(ByVal data As Variant) As Integer Dim i As Integer Dim temp As Variant On Error Resume Next Do while Err.Number = 0 i = i + 1 temp = UBound(data, i) Loop dimensionsOf = i - 1 End Function
- Err.Numberは、エラーが発生したときのエラー番号が格納されます。0はエラーが発生していないことを示します。
- UBoundの第1引数は配列、第2引数は配列の次元を指定します。
Collection¶
Excel標準のコレクションで、キーも割当可能ですが、後述のDictionaryに比べて機能(メソッド)が少なく貧弱です。可変長配列として使う用途では便利ですが、キーと値の組を扱うなら後述のDictionaryを使うのがよいです。
- キーのリスト、値のリストが取得できない等
Dictionary(連想配列、マップ)¶
Officeアプリケーションに同梱される Microsoft Scripting Runtime ライブラリに含まれます。
Dictionaryは、キーと値のペアを集合として保持するデータ構造です。他のプログラミング言語では、連想配列やマップと呼ばれることもあります。
Dictionaryオブジェクトの生成¶
次の2つの方法があります。-
CreateObject("Scripting.Dictionary")
- 「Microsoft Visual Basic for Application」画面を開き、[ツール]メニュー > [参照設定]から、[Microsoft Scripting Runtime]にチェックを付けた上で
New Dictionary
セルの指定¶
RangeとCells¶
C5セルを指定する方法は2つあります。
Range("C5")
Cells(5, 3)
なお、Cells(5, "C")
も可
Rangeは、セルの名前でセルを指定します。Cellsは、行番号と列番号でセルを指定します。VBAの変数でセルを指定したい場合は、Cellsを使うことになります。
Rangeでセルの範囲指定¶
Range("B2:C5")
Range("B2", "C5")
Range(Cells(2, 2), Cells(5, 3))
CurrentRegionでセルの範囲指定¶
指定したセル(1つ)が含まれる一塊のセル範囲(空白行、空白列で区切られた範囲)を返します。Cells(1,1).CurrentRegion
Excelのシート上であるセルを選択し、Ctrl+Shift+* で選択される範囲と同じ。
CurrentRegionの範囲から見出しを除くには¶
A | B | C | |
1 | 見出し | 見出し | 見出し |
---|---|---|---|
2 | データ | データ | データ |
3 | データ | データ | データ |
4 | データ | データ | データ |
となっているセルから、見出しを除いてデータだけを範囲とするには
Dim data As Range Set data = Cells(1, 1).CurrentRegion Set data = data.Offset(1, 0).Resize(data.Rows.Count - 1, data.Columns.Count)
と1行オフセットしてリサイズします。行方向に1つオフセットするので、リサイズ後の行サイズを1つ少なくします。
エラー処理¶
エラーメッセージを表示してマクロを終了¶
Err.Description = "集計中に回復不能なエラーが発生しました。" Err.Raise (3201)
Functionの戻り値にエラーを返す¶
Functionの戻り値型をVariant型とし、正常なときはその値を、エラーのときはエラー値を返します。
Function searchSomething(ByVal name as String) As Variant Dim ret As Variant ret = CVErr(xlErrNA) ' #N/Aエラー(数値)からCVErr関数でエラー値作成(Variant型にのみ代入可) On Error Resume Next ' 次のMatchは該当が0件のとき実行時エラーとなるためエラー時は次に進む指定 ret = WorksheetFunction.Match(name, Range("A1:A100")) If IsError(ret) Then ' エラー値かどうか判定 searchSomething = ret Else searchSomething = Range(Cells(ret, 1), Cells(ret, 5)) End If End Function
注意点¶
数値計算の注意点¶
整数の計算でオーバーフロー¶
次の計算はオーバーフローとなってしまいます。
Dim result As Long result = 24 * 3600
- 24と3600は整数(Integer型)の即値、Integer型は16bit符号付きなので、-32,768~+32,767が有効範囲です。24 * 3600 は有効範囲を超えるのでオーバーフローとなります。左辺値型がLongでも、まず右辺値がIntegerで計算しようとするのでオーバーフローです。
回避策:24 * 3600#
と#を付けると3600がLong型として扱われます。また、3600!と、!を付けると単精度浮動小数点(Single型)として扱われます。
変数代入の注意点¶
オブジェクト型の代入はSetがないとエラー¶
次のマクロがエラーとなってしまいました。
Dim alfaBook As Workbook alfaBook = Workbook.Add
実行すると、「実行時エラー'91': オブジェクト変数またはWithブロック変数が設定されていません。」
オブジェクト型(Workbook型)の代入には、set命令が必要です。
- alfaBook = Workbook.Add
+ Set alfaBook = Workbook.Add
- Functionプロシージャの戻り値がオブジェクト型の場合もSetが必要
プロシージャの注意点¶
プロシージャ呼び出しで引数を括弧で囲むとエラー¶
戻り値を使用しないプロシージャ呼び出しでオブジェクト型の引数を括弧で囲むとエラーが発生します。
次のマクロがエラー(実行時エラー438 オブジェクトは、このプロパティまたはメソッドをサポートしていません)となりました。
Dim arg As Workbook : workbookをほげる (arg)
引数がStringなどの非オブジェクト型のときは括弧を付けていてもエラーにはなりません。
エラーを回避する方法は、括弧を外すか、Call を付けるかでした。
workbookをほげる arg
Call workbookをほげる (arg)
Callを付けたときは引数の括弧は省略できません。