【覚書】Excel VBA の要点まとめ

2022年7月25日
上村晃史

突然ですが、私は VBA のコーディングがあまり好きではありません！
（VB繋がりの VB6、VB.Net、Access なども含めると、なんだかんだ一番触れてきた言語ではありますが C# が良すぎました。）

そもそもの用途を考えると一定以上の規模のシステムにはよっぽど採用されないでしょうから、がっつりコーディングにするのに不向きなのは仕方ないと思います。
今後バージョンアップもされないだろうし今更・・・という感は否めませんが、ちょっとした仕組みならチャチャっと作れてしまうのと、基本的には追加のセットアップをせずとも動いてくれるのは確かに利点ではあります。

仕組み上、バージョン管理もしづらく秘伝のタレ化しやすいのもあり、今後は新規で開発される機会は徐々に減っていくでしょう。
【参考リンク】：さらばエクセルの“大企業病”　パナソニックが実践する3大鉄則

とはいえ、既存システムのメンテ／回収を含め、未だにVBAを利用しないといけない機会が多くあります。VBA による開発時はとにかく時間をかけたくないため、構文やハマリどころだけぱぱっと振り返れるようにざっくり纏めます。

概要

1993年登場、バージョン7.1が最新（恐らく2012年リリース）
コンパイラ型およびインタプリタ型両方の性質を持つ
.Net Framework は【一部だけ】利用可能

初期設定

Excel VBAで初めて開発するときにやるべきこと

Excel ブック単位ではなく共通の設定
- 保存時に自動でコンパイルしてくれるが、手動コンパイルしないとエラーが発生する謎の事象により数日無駄にしたことがあるので、リリース時などは手動コンパイル推奨

参照設定

VBAで.NET FrameworkのArrayListを使う

Excel ブック単位の設定
CreateObject を使えば参照設定は不要

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

参照設定を追加すると何故か毎回コンパイルエラーが発生する謎の事象が起こったので個人的には非推奨
- インテリセンスが効かなくなるが、公式の関数がやたらと Variant 型で返すためにどのみち使えない・・・

モジュール

Excel VBA 標準モジュールとクラスモジュールの違い

モジュール名に全角文字を直接入力すると文字化けるがコピー&ペーストなら可

プロシージャ（メソッド）

【VBA】SubとFunctionの違い【戻り値の有無、参照が可能か、関数として使えるか】

プロシージャ（メソッド）を呼び出す際は、基本的には Call を省略しない
VBAでの括弧()の使い方、括弧が必要な場合
引数がプリミティブ型とオブジェクト型でByValを付けたときの挙動が変わる
VBA 省略可能な引数 Optional と名前付き引数
VBAの参照渡し（ByRef）と値渡し（ByVal）の違い

変数宣言

Dim <変数名> As <型名>

(余談) Dim は「Dimension」の略で “次元” という意味

データ型

各データ型の特徴と変数宣言

Variant 型はあらゆる種類のデータを格納することができる。初期値は Empty
- 「固定長の文字列型(String)データとユーザー定義型を除く」とあるが代入はできる。

Object型は参照型のみ格納できる。初期値は Nothing

VBAのEmptyやNothingやNull等の違いを整理

クラスのキャストは不可。（スーパークラスからダウンキャストできない）
Object型によるポリモーフィズム

変数の初期化

知らずにバグになってしまったことがあるので要注意！
VBA オブジェクト変数の宣言時にNewすると何がまずいのか

' 下記の場合、value1 は Variant 型となる点に注意
Dim value1, value2 As Integer
' 正しい記載
Dim value1 As Integer, value2 As Integer

１行で表現（ワンライナー）する場合、間にコロンを入れる
- VB.Net ならそのまま Dim col As Collection = New Collection と書ける
- 定数の場合もそのまま Const str = "文字列" と書ける（型は自動で判断される）

Dim col As Collection: Set col = New Collection

スコープ

プロシージャ内で宣言したローカル変数はプロシージャ内で有効となる
- プロシージャ外で宣言した変数は “モジュールレベル変数” と呼ぶ

Dim obj As Object
For Each obj in objects

    ～～　何らかの処理　～～

    Dim counter As Integer: counter = counter + 1
Next

Debug.Print "処理件数：" & counter

変数 “counter” は For Each 内で宣言しても、同一プロシージャであれば有効
- ループの度に再宣言されて初期化されそうに見えるが問題ない
- (余談) インクリメント／デクリメント演算子は存在しない

シングルクォーテーション(‘)を付ける
- 複数行にわたるコメントアウトは存在しない
- コードの折り畳み（#region ～ #endgreion ディレクティブ的な）は存在しない
XMLコメント（ドキュメントコメント）は以下のように記載
- コードスニペットは存在しない

''' <summary>
''' Workbook オブジェクトを 現在開いているか に関わらず取得
''' </summary>
''' <param name="filePath">Excel ファイルパス</param>
''' <param name="isReadOnly">読取専用で開くか（既定値：True）</param>
''' <returns>Workbook オブジェクト</returns>
Public Function GetWorkBook(filePath As String, Optional isReadOnly As Boolean = True) As Workbook

クラス

コンストラクタ／デストラクタ

''' <summary>コンストラクタ</summary>
Private Sub Class_Initialize()
　　～～　何らかの処理　～～
End Sub

''' <summary>デストラクタ</summary>
Private Sub Class_Terminate()
　　～～　何らかの処理　～～
End Sub

プロパティ

【Property Setは不要】Property Let と Set の違い | Ribbit works

Private pプロパティ As String

' プロパティ
Public Property Get プロパティ() As String
    プロパティ = pプロパティ
End Property

Public Property Let プロパティ(ByVal argプロパティ As String)
    pプロパティ = argプロパティ
End Property

C# ならたった１行・・・
- public string プロパティ { get; set; }

文字列操作

文字結合はアンパサンド( & )で繋ぐ

Dim あ行 As String: あ行 = "あいう" & "えお"

Left, Right, Mid, InStr など色々充実している
- 【VBA入門】Mid関数で文字列の抜き出し(Left、InStr関数も解説)
長くなる場合はアンダースコア( _ )で繋ぐことで複数行で記載できる
- （文字結合以外でも複数行記載は可能）

Dim あ―さ行 As String
あ―さ行 = "あいうえお" & vbNewLine & _
          "かきくけこ" & vbNewLine & _
          "さしすせそ"

配列

Excel VBAで使える可変長なコレクションまとめ

ArrayList は「Windows の機能の有効化または無効化」で「.NET Framework 3.5 (.NET 2.0および3.0を含む)」を有効にする必要があったため使用しなかった。
- 使い勝手の観点から、VBAで配列を利用するときは基本的に Collection、または Dictionary でいいのでは・・・
- ただし下記のように、Excel の複数セルの情報は Variant 型の Array となるため触れざるを得ない

■ 複数セルのデータ取得内容（サンプル）

条件分岐

VBAのEmptyやNothingやNull等の違いを整理

種類	型	判定例
Empty	Variant	If (IsEmpty(s) = True) Then
Nothing	Object	If (obj Is Nothing) Then
Null	Variant	If (IsNull(a) = True) Then ⇒　自分で Null を代入する時ぐらいしか使わない
“”	String	If (s = “”) Then　or　If (s = vbNullString) Then
vbNullString	String	If (StrPtr(s) = 0) Then ⇒　主に　空入力か、未入力か　といった判断に利用

動的配列が初期化されているかどうかの判定

三項演算子は存在しない
短絡評価（ショートサーキット）にはならない

ループ処理

For 文

【サンプル】0 から 10 までを 2ずつ増加（1ずつの場合は Step を省略可能）
- To はその数字を含む。（小なりイコール（<=）の意味合いと同様）

Dim i As Integer
For i = 0 To 10 Step 2
　　' Forの処理を抜ける
　　If (チェック処理１) Then Exit For

　　' 今回のループをスキップして次のループへ
　　If (チェック処理２) Then GoTo Continue

　　～～　何らかの処理　～～

Continue：
Next i

Continue や Skip といった次のループへ進む仕組みがないため、GoTo 文を利用する

For Each 文

Dim obj As Object
For Each obj In objects

　　何らかの処理

Next

objects が Collection であれば obj はユーザー定義型で問題ないが、Dictionary の Items の場合は「424: オブジェクトが必要です。」のエラーが出るため、Object 型で宣言する必要がある。

■ エラー発生例

Do While, Do Until, Do Loop 文

VBA Do While, Do Loop でループする

例外処理

Try、Catch、Finally といった例外処理が存在しないため、下記のように対応

    ' 例外処理：Try
    On Error GoTo ErrorHandler

　　～～　何らかの処理　～～

    ' Finally:へ飛ぶ
    GoTo Finally

' 例外処理：Catch
ErrorHandler:
    Dim errorMessage As String
    Select Case Err.Number
        Case Else
            errorMessage = "[No:" & Err.Number & "] " & Err.Description
    End Select

    MsgBox errorMessage, vbOKOnly + vbCritical, "エラー"

    Resume Finally

Finally:
    ～～　解放処理　～～

デバッグ

オプション設定の「エラートラップ」が【エラー発生時に中断】になっていると、クラスモジュールでエラーが発生した場合に呼び出し元がブレークポイントとなってしまい、どこでエラーになったか分からないので注意！（最初の「初期設定」の項を参照）

高速化 Tips

セルの値を１つずつ参照／書き込みをせずに、纏めて行うようにする。
画面描画を伴う処理の場合、処理中は描画の更新を止める。

Application.ScreenUpdating = False

～～　画面描画を伴う何らかの処理　～～

Application.ScreenUpdating = True

VBA高速化テクニック

まとめ

要点を絞って記載したつもりですが、思ったよりも記載量が多くなってしまいました。

正直なところ、Excel VBA でできることは PowerShell でも出来るだろうと思っており、VBA から PowerShell を実行したり、逆に PowerShell から Excel マクロを呼ぶことすら可能なので、今後新規で作る必要があるものは PowerShell を採用したいです、、、
（Visual Studio Code などのエディタが使える環境に限る）

特に、C# だと Linq、PowerShell だとパイプラインに相当するような仕組みがないのが辛いです。
Excel VBA を採用する選定条件には PowerShell も該当するのではと思うので、Excel VBA の記事を書いておいてアレですが、最後に一言、PowerShell はいいぞ！

前の投稿に戻る

次の投稿へ進む

ユニフェイスの開発者ブログ

概要