Excel VBA Tips
概要
Excel VBA をコーディングする機会が不定期的にあるので、自分用 Tips を残しておこうと思う。
あと、Microsoftが公開している MS-VBAL という VBA 言語のリファレンスの日本語訳（※途中まで）も貼っておく。
ワンライナー（ステートメントの連結）
: を利用すると複数行を一行にまとめて書くことができる。
変数宣言の際、宣言と初期化をまとめて書ける。
Dim foo As Integer: foo = 5
アーリーリターンにも使いやすい。
If文の場合は : が無くてもよい。
' どちらでもよい
If foo = 1 Then: Exit Sub
If foo = 1 Then Exit Sub
変数宣言
変数宣言の強制
Option Explicit
Python などを書き慣れていると不要に思えるが、VBA には linter などがないので事前に変数名のタイポを見つけられず痛い目を見ることがある。お守り的に書いておきましょう。
変数の初期化
変数は必ず初期化した方が良い。
例えば、ブロック内で変数宣言する場合、他言語だと毎回新しい変数が生成されて値も初期化されるが、VBA（VB6）では以前の値を引き継いでしまうことに注意。
For i = 0 to 10
Dim x as Interger ' x が0初期化されるのは最初の1回のみ、2回目以降はずっと1になる
If i = 0 Then
x = 1
End If
Next
バグを防ぐため、明示的に初期化しておくことを推奨。
For i = 0 to 10
Dim x as Interger: x = 0
...
リテラル
データ型ごとのリテラル。
' 整数 (Integer型など)
x = 100 ' 10進数
x = &H5F ' 16進数
' 実数 (Single型、Double型)
x = 10.5 ' 小数表現
x = 10e-3 ' 指数表現
' 真偽値 (Boolean型)
x = True
x = False
' 日付・時刻 (Date型)
x = #12/31/2013# ' 日付 MM/DD/YYYY 形式
' ※覚えなくてもエディタが自動的にこの形式にしてくれるはず
x = #13:24:51# ' 時刻 HH:MM:SS 形式
x = #1:24:51 PM# ' 時刻 AM/PM表記
x = #12/31/2013 13:24:51# ' 日付と時刻の組み合わせ
' 文字列 (String型)
x = "aaa""bbb""ccc" ' 文字列中のダブルクォーテーションは二重打ちする
配列
配列の宣言と開始インデックス
配列の宣言方法をよく忘れてしまうのでメモ。
下記の通り、Option Base の影響を受けるかどうかが配列の作成方法に依存するため注意が必要。
Dim foo() ' 空配列
' Option Base の影響を受ける作成方法
Dim foo(N) ' 0 または 1 から N までの要素を持つ配列となる
foo = Array() ' 空配列
foo = Array(1, 2, 3) '
' Option Base の影響を受けない作成方法
Dim foo(N to M) ' N から M までの要素を持つ配列となる
foo = VBA.Array() ' 空配列、開始インデックスは 0 固定となる
foo = VBA.Array(1, 2, 3) ' 開始インデックスは 0 固定となる
foo = [{1, 2, 3}] ' 開始インデックスは 1 固定となる
Array 関数は呼び出すときのタイプライブラリ名の修飾有無（上の例だと "VBA" を付けるか否か）によって動作が違う点に注意が必要。
様々な宣言方法があるが、利用価値がありそうなのは下記の２点。
- 通常は
Array関数。Option Base の影響を意識しながら用いる。- 通常は
Option Base 0（既定）のまま使えばよい。 - 後述する配列の空チェックを行うことができる点が便利。
- 通常は
- Excel の行列を扱う場合は 1 始まりの方が分かり易いため、
[{1, 2, 3}]の配列リテラルの方法を使用する。- 配列リテラルがそもそも Excel の機能を呼び出して配列生成する方法なので親和性が高い。
可変長配列
既存の配列の要素数を変更して再利用する。
Dim foo(N)
...
ReDim foo(M)
ReDim Preserve foo(M) ' 元の値を保持するならこちら
配列リテラル
簡易的な配列リテラル。
この書き方で作られる配列の開始インデックスは強制的に 1 になる点に注意すること。
[{1, 2, 3}] ' 1次元配列
[{1, 2, 3; 4, 5, 6}] ' 2次元配列
VBAの言語仕様だけではなくExcelの機能によって実現されており、他のOffice VBAでは使用できない。
Excel VBA では、[xxx] は Application.Evaluate("xxx") の糖衣構文として扱われる。Application.Evaluate はExcelアプリケーションに式を評価させるための組み込み関数であり、ここに {1, 2, 3} のようなExcelの配列表現を書くことで簡易的な配列リテラルとして利用することができる。Excelのセルに数式として ={1, 2, 3} と書くと配列として扱われることからも、Excelアプリケーションの評価結果であることが分かる。
; で区切ることで、2次元配列まで表現可能。それ以上の次元は利用できない。Excelが2次元のスプレッドシートであり、3次元以上の配列を表現する手段が用意されていないため。
配列の空チェック
Array もしくは VBA.Array で空の配列を作ったときにのみ利用できる方法。
他の方法で宣言された空の配列を LBound UBound に渡すとエラーになってしまう。
foo = Array() もしくは foo = VBA.Array() のように空の配列を作成すると、LBound(foo) の値より UBound(foo) の値が小さくなる。これを利用して空チェックを行う。
' If文でチェックする場合
If LBound(foo) > UBound(foo) Then
' 配列 foo は空
End If
' 普通にループを書いても問題なし
For i = LBound(foo) To UBound(foo)
' 配列 foo が空ならループに入らない
Next
' もちろん For Each も問題なし
For Each i In foo
' 配列 foo が空ならループに入らない
Next
配列を空の状態から 1 要素ずつ追加していく
Array 関数で作成した空配列で UBound を呼び出せることを利用すると、ロジックの中で空状態から 1 要素ずつ追加していくことが可能になる。
' 空配列を作成しておく
Dim foo As Variant: foo = Array()
' ループする度に配列の末尾に要素を追加していく
Dim i As Integer
For i = 0 To 99
' 配列の要素を追加する
' 末尾に追加するインデックスは UBound() + 1 で求められる
Dim idx As Integer: idx = UBound(foo) + 1
ReDim Preserve foo(idx)
' 新しい要素に値を格納
foo(idx) = i + 1
Next
配列の引数渡し
配列を関数の引数として受け渡す場合は以下に注意。
- 関数の仮引数を配列型で定義する場合、型が厳密に一致する変数しか渡せなくなる。
-
x() as Integerという引数には、Dim x() as Integerのように宣言した変数しか受け渡せない。 -
x as Variantのように Variant 型の（配列ではない）引数にするとあらゆる配列型を受け取れる。ただし、配列以外も受け取れてしまうので引数チェックが必要になる。
-
- 参照渡し (ByRef) とすること。
-
ByVal x as Variantのような引数とすれば値渡し (ByVal) も可能。
-
ソート関数のように、様々な配列型を対象にできる関数を定義する場合、下記のようにすると良い。
Sub Main()
Dim x(0 to 2) as Integer
x(0) = 1
x(1) = 2
x(2) = 3
Call Sub(x)
Dim y(0 to 2) as Double
x(0) = 1.1
x(1) = 2.2
x(2) = 3.3
Call Sub(y)
End Sub
Function Sub(ByRef ary as Varinat)
' 引数チェック
If Not IsArray(ary) Then: Exit Function
Debug.Print ary(0)
...
End Functoin
辞書（連想配列）
外部オブジェクト Scripting.Dictionary で辞書を利用できる。
' オブジェクト生成
Dim dict: Set dict = CreateObject("Scripting.Dictionary")
' メソッド
dict.Add "key1", 5 ' 要素追加
x = dict("key1") ' 要素の参照
dict("key1") = 6 ' 要素の設定
dict.Exists "key1" ' 要素の存在確認
dict.Remove "key1" ' 要素の削除
dict.RemoveAll ' 全要素の削除
' プロパティ
dict.Count ' 要素の数
dict.keys ' キー配列 ※開始インデックスは 0
dict.items ' 値の配列 ※開始インデックスは 0
' 全要素をループ
For Each key In dict ' key にはキーが順に入る
value = dict(key)
Next
正規表現
外部オブジェクト VBScript.RegExp で正規表現を利用することができる。
' オブジェクト初期化
Dim re: Set re = CreateObject("VBScript.RegExp")
re.Pattern = "[1-3]+" ' パターン文字列
re.IgnoreCase = False ' 大文字小文字の無視（デフォルト False）
re.Global = False ' 文字列全体マッチのみ（デフォルト False）
Dim target: target = "abc123def"
re.Test target ' マッチの成否
re.Replace target "456" ' マッチ結果の置換 ("abc456def" になる)
' マッチオブジェクト取得
Dim matchs: Set matchs = re.Execute target
matchs.Count ' マッチした数
Dim i
For Each i In matchs
' マッチオブジェクトを操作
Dim m: Set m = matchs(i)
m.Value ' マッチした文字列
m.FirstIndex ' マッチ位置の先頭インデックス(0～)
m.Length ' マッチした文字列の長さ
' サブマッチオブジェクト取得 (カッコによるキャプチャ結果)
Dim submatchs: Set submatchs = m.SubMatch
submatchs.Count ' サブマッチした数
Dim j
For Each j In submatchs
submatchs(j) ' サブマッチした文字列
Next
Next
関数定義
デフォルト引数（省略可能引数）
Sub/Function の関数定義で引数に Optional を付けると省略可能になる。
また、省略された場合のデフォルト値を設定可能。
Function Func(a As String, Optional b As Integer = 0)
...
End Function
同一関数において、ParamArray と Optional の併用は不可。
省略可能な引数を Variant 型にしておくと、デフォルト値を設定しなくても関数内で IsMissing 関数を使用して省略されたかどうかを判定できるようだが、あまり使い所を思い付かない。
可変長引数
Sub/Function の関数定義で引数に ParamArray を付けて Variant 型の配列にすると、可変長引数になる。
Function Func(a As String, ParamArray array() As Variant)
' 可変長引数を処理
Dim elem As Variant
For elem In array
...
Next
...
End Function
同一関数において、ParamArray と Optional の併用は不可。
ユーザ定義関数(UDF)
Public Function で関数定義すると、SUM 関数のようにワークシート関数として使用することができる。
以下、より使いやすいユーザ定義関数を作成するためのポイントやノウハウ。
引数定義
- 通常、引数は String 型や Integer 型など厳密に型指定しておけば問題ない。ただ、引数の型を Variant にしておくことで、
SUM関数のように値もセル参照も受け取れる柔軟な関数にすることができる点を知っておくと応用範囲が広がる。- この場合は
TypeName、TypeOf、IsNumericといった関数で引数をチェックすることで、実際にどのような型を受け取ったか判別して処理を分岐させる。 - 渡された値が期待したものではない場合は、
CVErr関数を使用してエラーを返すことでセルに適切なエラー値が表示されるようにすること。
- この場合は
- 引数を Boolean 型にすると、
If関数の第1引数のようにC5<>""といった条件式を受け取れるようになる。Variant 型でも受け取れる。 - セル参照を受け取る場合は、Range 型にする。Variant 型や Object 型でも受け取れる。
- 引数名を
対象日のように日本語名にしておくことで、引数の説明を兼ねることを考える。- 他に引数に説明を付ける方法が無さそう。
- ワークシート関数は大抵そうなっている。
テクニック
-
Application.ThisCellで、数式を書かれたセル自身を取得できる。
関数の属性、メタ情報
-
Application.MacroOptionsを使用して関数の説明を付けることを検討する。 - 必要ならば
Application.Volatileを使用して揮発性関数にすることを検討する。- 揮発性関数は、無関係のセルが編集された場合であっても必ず再計算時に呼び出される。
NOW関数のように常に最新の結果を返す必要がある関数で利用する。 - 利用頻度は高くないはず。常に再呼び出しされるということはそれだけ再計算全体のパフォーマンス低下に繋がるので注意して利用すること。
- 揮発性関数は、無関係のセルが編集された場合であっても必ず再計算時に呼び出される。
注意点
- ユーザ定義関数はかなり遅い。大きな表にユーザ定義関数を大量に埋め込んで結果を得るような場合、一連の処理を通常のマクロにまとめて呼び出す方が現実的。
- 厳密には
Private Functionで関数定義しても数式に直接関数名を書くと呼び出すことができてしまう。関数挿入ダイアログや数式入力中の関数候補に表示されなくなるだけ？ - ユーザ定義関数で行えることには制限がある。
その他
高速化
画面表示更新、自動計算、セル編集イベントを一時的に抑制することで高速化させることができる。
処理前後にコードを書く方法
' 高速化のために各種設定をOFF
Application.ScreenUpdating = False ' 画面更新OFF
Application.Calculation = xlManual ' 自動計算OFF
Application.EnableEvents = False ' セル編集イベントOFF
' 高速化したい処理
...
' 元に戻す
Application.ScreenUpdating = True ' 画面更新ON
Application.Calculation = xlAutomatic ' 自動計算ON
Application.EnableEvents = True ' セル編集イベントON
処理が途中で中断すると自動計算がOFF（手動）のままになるので注意。
高速化 ON/OFF を自動で行うクラスを使う方法
以下のようにコンストラクタで高速化ON、デストラクタで高速化OFFするクラスを用意しておくと、C++ の RAII クラスや Python のコンテキストマネージャ風に利用できる。
処理が途中で中断すると自動計算がOFF（手動）のままになる点は解決しないので注意。
' 例) 下記を Concentration クラスとして定義
' 設定値のバックアップ
Private bkScreenUpdating As Boolean
Private bkCalculation As XlCalculation
Private bkEnableEvents As Boolean
'--------------------
' コンストラクタ
'--------------------
Private Sub Class_Initialize()
Call Quiet(True)
End Sub
'--------------------
' デストラクタ
'--------------------
Private Sub Class_Terminate()
Call Quiet(False)
End Sub
'--------------------
' 設定切り替え
'--------------------
Private Sub Quiet(start As Boolean)
' 各種設定OFF
If start Then
' 画面更新
bkScreenUpdating = Application.ScreenUpdating
Application.ScreenUpdating = False
' 自動計算
bkCalculation = Application.Calculation
Application.Calculation = xlManual
' イベント連動
bkEnableEvents = Application.EnableEvents
Application.EnableEvents = False
' 各種設定を元に戻す
Else
' 画面更新
Application.ScreenUpdating = bkScreenUpdating
' 自動計算
Application.Calculation = bkCalculation
' イベント連動
Application.EnableEvents = bkEnableEvents
End If
End Sub
高速化を行いたい関数の冒頭で上記クラスのインスタンスを作成することで高速化が可能。
C++ の RAII クラス風の使い方。関数のスコープを抜けるときに高速化OFFとなる。
Sub Func()
Dim c as Object: Set c = New Concentration ' 高速化
' 高速化したい処理
...
End Sub
ある限定的な範囲のみ高速化したいなら with 文を使用する。
Python のコンテキストマネージャ風の使い方。
' 高速化
With New Concentration
' 高速化したい処理
...
End With
ステータスバーの更新
処理中であることを表示したり、進捗状況を表示したりするときに使える。
' ステータスバーに文字列を表示
Application.StatusBar = "進捗率: " & (i / num) & " %"
' ステータスバーをクリア
Application.StatusBar = False
処理が途中で中断すると文字列がクリアされずに残ったままになるのはご愛嬌。。
一旦OSに制御を返す
処理中に DoEvents を挟む。
中断ボタンを用意して処理中に押せるようにしたいときなどに利用。
Do While True
DoEvents
...
Loop
ただし、DoEvent は遅いので濫用しないこと。上記のように単純に呼び出すのではなく、何回かに1回呼び出すようにするなど工夫した方がよい。
マクロの中断
Esc または Ctrl+Pause/Break キー押下。
処理中にExcelが応答なしになってしまってキーが効かない場合、適宜前述の DoEvents を利用することで改善できることがあるがその分処理全体が遅くなるのでおススメしない。
文字列の書式化関数
VBA には他言語のような文字列を書式化する関数が無いため、長いメッセージ文などを組み立てるときに煩雑なコードになりやすい。
msg = "現在処理中です..." & vbLf _
& "対象データ: " & target_data & vbLf _
& "進捗: " & i & " / " & num & " 件 ( " & (i / num * 100) & " %)"
そこで、Go言語の fmt.Sprintf のような関数を作っておくと便利。
' Sprintf - フォーマットした文字列を返す
'
' fmt: フォーマット対象文字列
' 下記の指定子/エスケープシーケンスを利用可能
' %n: 対応する可変長引数に置き換わる指定子 (n = 1, 2, ...)
' \r, \n: 改行コードに置き換わるエスケープシーケンス
' values: 可変長引数
Function Sprintf(ByVal fmt As String, ParamArray values() As Variant) as String
' 指定子を可変長引数に置き換える
Dim i As Integer: i = 1
Dim v As Variant
For Each v in values
fmt = Replace(fmt, "%" & i, v)
i = i + 1
Next
' 改行コードを置き換える
fmt = Replace(fmt, "\r", vbCr)
fmt = Replace(fmt, "\n", vbLf)
Sprintf = fmt
End Function
前述のメッセージ文は次のように作成可能
msg = Sprintf(
"現在処理中です...\n対象データ: %1\n進捗: %2 / %3 件 ( %4 %)", _
target_data, i, num, (i / num * 100) _
)
Discussion