プロジェクト

全般

プロフィール

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

ローカルウィンドウでの変数表示

ローカルウィンドウを表示すると、スコープ内のローカル変数とモジュールの変数の値が確認できます。

localwindow-1.png (ローカルウィンドウの変数表示)

変数

スコープ

関数の中で定義した変数は、その関数の中がスコープとなります。
関数の外(モジュール)に定義した変数は、そのモジュール全体がスコープとなります。

変数の種類

定数

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つの方法があります。
  1. CreateObject("Scripting.Dictionary")
  2. 「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を付けたときは引数の括弧は省略できません。

クリップボードから画像を追加 (サイズの上限: 1 GB)