0. VBA の資格について

●VBA エキスパート

オデッセイコミュニケーションズが主催する「VBA エキスパート」という
試験があります。試験科目には 「Excel VBA ベーシック」と 「Excel VBA
スタンダード」 の 2 種類があり、本稿はスタンダードを対象としています。


●VBA スタンダード

以下の内容が試験の基本情報となります。

項目 内容
出題数 40 問前後(CBT 方式)
出題形式 選択問題、穴埋め記述
試験時間 50 分
合格基準 65%～ 80%以上
受験料 14,850 円



(出題形式例)













● 出題範囲

  1. プロシージャ
  1. 変数の活用
  1. ステートメント
  1. ファイルの操作
  1. ワークシート関数の利用
  1. 検索とオートフィルター
  1. データの並べ替え
  1. テーブルの操作：テーブル
  1. エラー対策
  1. デバッグ

VBA エキスパートの説明は以上となります。受験を検討されている場合は、
相談いただければと思います。


● 学習開始前の設定




(1)VBE のオプション設定

VBE 上部から、ツール > オプション > エディターの設定 をクリックします。
その後、以下の項目の値を変更しましょう。

・標準コード　 前景：黄緑　 背景：黒
・コメント　　 前景：黄緑　 背景：黒
・キーワード　 前景：水色　 背景：黒
・識別子　　　 前景：白　　 背景：黒


(2)変数宣言の強制

ツール > オプション > 編集タブ > 変数の宣言を強制する(R)をチェック
標準モジュールを右クリック後に標準モジュールを挿入し、Option Explicit の
記述がある事を確認する。


(3)全クリアのマクロ

次章からの演習を行った際の結果を削除する場合、以下のマクロを実行すると便利です。

Sub AllClear()
    Cells.Clear
End Sub




1. セルの検索

ワークシート上の値に処理を実施する際は、目的のデータを特定する
必要があります。多くの場合はセルの検索を行い、そこからデータの
処理に移行します。この章では、セルの検索について学習しましょう。




●Find メソッドと引数

セルの値を検索する際には Find メソッドを利用します。Find メソッドは、
9 種類の引数を指定する事でより詳細な検索を実現します。以下の内容を
確認しましょう。

(1) What：検索語句
(2) After：検索開始の起点セル
(3) LookIn：検索対象(値、数式など)
(4) LookAt：検索精度 → 完全一致：xlWhole, 部分一致：xlPart
(5) SearchOrder：検索の方向(右か下か)
(6) SearchDirection：検索の向き(次か前か)
(7) MatchCase：大文字と小文字の区別
(8) MatchByte：半角と全角の区別
(9) SearchFormat：書式を検索条件に含めるかどうか

上記の中で、必須となる引数は (1)What のみです。また、(4)LookAt は
前回処理の値が記憶されるため、都度指定する方が望ましいです。



●Find メソッドの使い方

Find メソッドの対象は Range オブジェクトです。対象の Range オブジェクトは、
「Range("A1:A9")」のような特定のセル範囲や、「Range("B:B")」のような
B 列全て、「Columns(1)」と記述する全列など、様々な範囲を指定できます。

Find メソッドで対象範囲を検索した結果、条件に一致した値があった場合は
該当セルの位置情報(Range オブジェクト)を返します。そのため、Find メソッドを
用いて検索する際は オブジェクト変数を宣言し、値を格納します。



● 検索結果が見つからない場合の対応

セル検索を行った場合、該当条件のデータが見つからない場合もあります。
この時、Find オブジェクトは Nothing という値を返します。そのため、
見つからなかった際はオブジェクト変数に Nothing が格納される事を利用して、
If ステートメントで見つからなかった場合のマクロを記述する事ができます。

次の演習で記述方法を学習しましょう。





2.データの並び替え

オートフィルタは所定のセル範囲に対して並び替えを実行する機能です。
また、オートフィルタを指定しなくてもメソッドを活用する事で、ワーク
シート上の値を並び替える事が可能です。その方法について学習しましょう。


● 並べ替えの種類

データの並べ替えを行う際は、下記 2 種類の方法があります。

(1) Sort オブジェクト / SortField オブジェクト
(2) Sort メソッド

(1)はより詳細な設定が可能で、(2)は設定が簡単といった違いがあります。
VBA エキスパート試験では両方の設問があるため、各々の特徴や引数
について、学習して行きましょう。


(1) Sort オブジェクト / SortField オブジェクト

主な引数・定数は以下の通りです。

・Key：基準とする列(を含むセル)
・SortOn：並び替え条件
・Order：昇順/降順 → xlAscending / xlDescending (デフォルトは昇順)
・DataOption：数値と文字列の区別 → デフォルトは行方向
・SetRange：並べ替えの範囲 → Range オブジェクトを指定
・Header：タイトル行の判定有無 → xlGuess(自動判定), xlYes(1 行目含まない), xlNo(1 行目含む)
・MatchCase：大文字と小文字の区別
・Orientation：行/列方向の並べ替えを指定
・SortMethod：日本語の並べ替え方法を指定
・Apply：並べ替えの実行(引数なし)

また、SortField の設定は Excel に保存されるため、並び替えを実行する前に
Clear メソッドで初期化する必要があります。

以下の演習を行い、感覚を掴んで行きましょう。



(2) Sort メソッド

主な引数・定数は以下の通りです。
・Key1：並べ替えの基準とする列(を含むセル)
・Order1：昇順/降順 → xlAscending / xlDescending
・Header：タイトル行の判定有無 → xlGuess(自動判定), xlYes(1 行目含まない), xlNo(1 行目含む)

(1)と比較すると、引数が少ないため設定が簡単です。
複雑な並べ替え条件でなければ、Sort メソッドの利用がよいです。

以下の演習を行い、Sort メソッドに慣れて行きましょう。






3. テーブルの操作

Excel をデータベースとして利用する際、テーブルの利用が可能です。
テーブルは並べ替えやフィルターといった機能を持ち、また、オブジェクト
として各種メソッドやプロパティの対象に指定できます。この章では、
テーブルの操作について学習しましょう。




● テーブルの作成

テーブルは ListObject オブジェクトで設定が可能です。Add メソッドを
用いて引数を指定する事で、目的のテーブルを作成します。次の演習で
記述に慣れて行きましょう。


演習 1
次のコードを実行し、A1セルからD5セルをテーブルに指定しましょう。
Sub MakeTable1()
    With ActiveSheet.ListObjects.Add( _
        SourceType:=xlSrcRange, _
        Source:=Range("$A$1:$D$4"))
        .Name = "Table1"
    End With
End Sub



補足：コードの解説
・SourceType：作成するテーブルの種類
・xlSrcRange：セル範囲(規定値)
・Source：元データのセル範囲を指定
・Name：テーブルの名前

今回はテーブルの枠のみを作成しましたが、実際はテーブルにデータが入っていて、
項目毎に並べ替えたりフィルターをかけたりします。次の項では、操作に必要な
テーブルオブジェクトの特定について、学習して行きます。



● テーブルを特定する

テーブルを操作する場合、対象(オブジェクト)として特定する必要があります。
特定方法として、以下の 2 つが主な指定先として挙げられます。

(1) テーブル内のセル
(2) テーブル名

記述としては、以下の通りです。

(1) Range("セル番号").ListObject
(2) Range("テーブル名")

今回は、(1)のセル指定の方法で学習して行きましょう。



● テーブルの部位

テーブル自体がオブジェクトであり、各部位もオブジェクトとして指定できます。

テーブル全体：ListObject.Range
データ全体：ListObject.DataBodyRange
タイトル：ListObject.HeaderRowRange
列：ListObject.ListColumns
行：ListObject.ListRows

コピーや削除といった操作を行う場合は、これらのオブジェクトに対して
各種メソッド及びプロパティを紐づけて行きます。



● オートフィルタ

テーブルではオートフィルタ機能を使って、該当データを絞り込む事が可能です。
記述方式は、以下が基本となります。

[基本構文]

Sub AutoFilterTest()
    Range("セル番号").ListObject.対象のテーブル範囲.AutoFilter 列番号 "条件"
End Sub

対象のテーブル範囲とは、上記のテーブル全体またはデータ全体を指します。

次の演習を実行し、AutoFilter 並びにテーブル操作の感覚を掴んで行きましょう。






4. ファイルの操作

これまでのマクロ操作では、対象のオブジェクトはワークシートやセルでした。
この章では、Workbook を対象のオブジェクトとし、ファイル操作を学びましょう。


● ブックを開く

ブックを開く場合、Open メソッドを利用します。オブジェクトは Workbook
コレクションが対象となり、Opne メソッドの後には対象ファイルのディレクトリ
とファイル名を指定します。

[基本構造]

Sub OpenTest()
    Workbooks.Open "ディレクトリ\ファイル名.拡張子"
End Sub

次の演習を実施し、Open メソッドの記述を学びましょう。


演習 1
(1)デスクトップにファイル名「VBA」を付けた Excelファイルを保存
(2)以下を参照してコードを作成し、マクロを実行する

ディレクトリ名が分からない場合は、Excel ファイルを右クリックし、
プロパティ > 場所で確認しましょう。
ディレクトリとは





[解答例]

' ディレクトリは利用環境によって異なります
Sub OpenTest()
    Workbooks.Open "C:\Users\(UserName)\Desktop\VBA.xlsx"
End Sub



● ブックを保存する

SaveAs メソッドを利用すると、アクティブなブックを保存できます。基本構造は
以下の通りです。

[基本構造]

Sub SaveAsTest1()
    ActiveWorkbook.SaveAs "ファイル名" & "拡張子"
End Sub

次の演習を実行しましょう。



演習 2
開いている Excelファイルの名前を 年月日で保存するマクロを作成し、実行しましょう。
※ 演習1で作成した「VBA.xlsx」ファイルとは異なるファイルで実行する事

以下に解答が記載されています。 



Sub SaveAsTest2()
    ActiveWorkbook.SaveAs Year(Now) & Month(Now) & Day(Now) & ".xlsx"
End Sub



● ファイルのコピー

FileCopy ステートメントでフォルダ内のファイルをコピーする事ができます。

[基本構造]

Sub CopyFile()
    FileCopy "保存元のディレクトリ/ファイル名", "保存する先のディレクトリ/ファイル名"
End Sub

なお、現在開いている編集中のファイルを FileCopy でコピーしようとすると、
70 番のエラーが表示されて実行できませんので、注意しましょう。


● フォルダの作成

MkDir ステートメントを使うと、新規のフォルダの作成ができます。なお、
「Make Directory」の省略表記となります。

[基本構造]

Sub MakeDirectory()
    MkDir "ディレクトリ\フォルダ名"
End Sub

これらのステートメントを利用して、次の課題を実施しましょう。


演習 3
FileCopyステートメントとMkDirステートメントを利用して、デスクトップ配下に
「VBA」フォルダを作成し、演習1で作成した「VBA」ファイルをコピーしましょう。
ファイル名は「VBAコピー」で保存しましょう。

以下に解答が記載されています。 



' ディレクトリは利用環境によって異なります
Sub MakeDirectoryAndCopy()
    MkDir "C:\Users\(UserName)\Desktop\VBA"
    FileCopy "C:\Users\(UserName)\Desktop\VBA.xlsx", "C:\Users\(UserName)\Desktop\VBA\VBAコピー.xlsx"
End Sub






5. エラー対策

マクロ実行中にエラーが発生するとそのマクロは停止します。VBA 記述時に
判明したエラーは修正しやすいですが、完成後の利用時に発生したエラーは
事前に対策が必要となります。この章ではエラー対策の方法を学びましょう。


● エラーの種類

(1)記述エラー

「文字列をダブルクォーテーションで囲っていない」、「() <> . ,
などの記号が不足している」等、文法ミスがあった際に発生するエラーです。
この場合、VBE による文法チェックによってエラーは検知され、メッセージが
表示されます。






上記のエラーメッセージには共通して「コンパイルエラー」の記載があります。
コンパイルとは、記述した VBA を Excel に認識される形に翻訳する事で、この
段階でミスが検知されると、コンパイルエラーとして表示されます。

次の演習で、エラー修正の対応を練習を行いましょう。


演習 1
次のコードには 7つのエラー要因があります。該当コードを修正し、
「1から10の偶数を赤色・フォントサイズ20にする」マクロを完成させましょう。
また、インデントも整えましょう。
・Mod演算子：左の数値を右の数値で割った余りを計算する

Sub ErrorCorrection()
    For i = 1 To 10
    Cell(i, 1)Value = i
    If i Mod 2 = 0
    With Cells(i, 1).Font
    .Color = vbRed
    .Size = 20
End Sub

※ 解答は以下のコードになります。





Sub ErrorCorrection()
Dim i As Integer
    For i = 1 To 10
        Cells(i, 1).Value = i
        If i Mod 2 = 0 Then
            With Cells(i, 1).Font
                .Color = vbRed
                .Size = 20
            End With
        End If
    Next
End Sub



補足：修正箇所の解説
(1)変数の宣言
(2)Cell → Cells
(3)Valueプロパティの前に .
(4)If条件式の後ろに Then
(5)Withステートメントの終わりに End With
(6)Ifステートメントの終わりに End If
(7)Forステートメントの終わりに Next



(2)論理エラー

「存在しないオブジェクトを指定する」など、論理的に矛盾する場合に発生します。
文法的にミスがないため、コンパイル時のエラーにはなりませんが、実行時に矛盾が
発生するため、実行時エラーとなって検知されます。

ワークシート 2 が存在しない状態で以下のコードを実行すると、画像のエラーが発生
します。

Sub ErrorTest1()
    Worksheets(2).Delete
End Sub


実行時エラーの難しい所は、マクロ作成時には気付きにくい事、実行時の利用状況に
よってエラー発生の有無が変わる事です。例えば、上記コードはワークシート 2 が
あればエラーにはなりません。

そのため、予期せぬエラーが発生した場合でもマクロが中断しないような記述が
必要となってきます。


● On Error ステートメント

もしエラーが発生した際に処理を迂回させる方法に、On Error ステートメントが
利用できます。この構文は、エラー発生時に指定した先に処理を飛ばす事が可能です。

[基本構造]

Sub ErrorTest2()
    On Error Goto Error1
        処理1
        処理2
    Error1:
        処理3
End Sub

処理 1 または処理 2 でエラーが発生した場合は、Error1 にジャンプして処理 3 が実行されます。
また、Error1 の事をラベルと呼びます。ラベル以外でのエラー回避策として、エラーを無視
して処理を続ける Resume Next という記述方法もあります。



演習 2
次のコードは、エラーが発生してもエラーを無視して処理を続け、メッセージを表示する
マクロである。空欄に入る適切な語句を入力しなさい。
Sub ErrorTest3()
    On Error 【      】 Error1
    Worksheets(2).delete
【      】
    MsgBox "ワークシートがありません"
End Sub

答えは下記の通りです。







解答
・Goto
・Error1:



● エラーの検知

エラー発生時に処理をラベルへ飛ばす On Error ステートメントと、プロシージャを終了
させる Exit ステートメントを組み合わせると、エラー発生の有無で実行処理が分岐する
マクロを記述する事ができます。

次の演習を実行し、記述に慣れて行きましょう。



演習 3
次のコードを実行し、結果を確認しましょう。
確認したら、別の結果が得られるようワークシート上のデータを変更し、再度実行しましょう。
Sub ErrorTest4()
    On Error Goto Error1
        Worksheets(1).Name = Range("A1")
        Exit Sub
    Error1:
        MsgBox "エラー発生。原因を確認して下さい"
End Sub

補足：コードの解説
・Exit Sub：プロシージャを終了させる。



● Err オブジェクト

エラーが発生した場合、そのエラーの情報は Err オブジェクトに格納されます。
そのため、Err オブジェクトに対してプロパティを紐づけると、エラー内容に
ついて調べる事ができます。以下はその一例です。

・Number プロパティ：エラー番号を返す
・Description プロパティ：エラーメッセージを返す
・Clear メソッド：エラー情報の削除

特に Number プロパティを使用した場合、エラーが発生した場合は 0 以外の
値になるため、If ステートメントとの組み合わせが利用しやすいです。


演習 4
次の空欄をそれぞれ埋めて、エラー発生時もマクロの実行が止まらず、エラー発生が
表示されるコードを完成させましょう。 
Sub ErrorTest5()
    On Error 【      】
    Worksheets(2).Delete
    If 【      】.【      】 <> 0 Then
        MsgBox "エラーが発生"
        MsgBox Err.Number
    End If
End Sub

以下に回答を記載しています。






Sub ErrorTest5()
    On Error Resume Next
    Worksheets(2).Delete
    If Err.Number <> 0 Then
        MsgBox "エラーが発生しました"
        MsgBox Err.Number
    End If
End Sub

補足：コードの解説
・Resume Next：エラー発生を無視する
・Err.Number：発生したエラーコードを取得
・If ～ <> 0 Then：条件式の値が 0以外は Then以降を実行する






6. デバッグ

プログラムに含まれるミスを探して修正する作業をデバッグと言います。
この章では、VBE で利用できるデバッグ機能について学んで行きます。


● デバッグモード

5 章で解説した論理エラーには「実行時エラー」があり、発生した場合は
エラーメッセージが表示されます。この時、ウインドウのデバッグボタンを
押すと、デバッグモードに移行します。デバッグモードでは、マクロが
一時停止しているため、エラーの原因を調べる事が出来ます。

以下に実行時エラーの代表例を記載します。



Option Explicit 設定時に型宣言がない場合に発生。




対象のオブジェクトが存在しない場合等に発生。



● イミディエイトウインドウ


デバッグモードに移行後はマクロが停止した状態のため、プロシージャを
動かす事が出来ません。そのため、Cntrl + G でイミディエイトウインドウ
を呼び出し、変数の確認などを行ってエラーの原因を特定します。


● イミディエイトウインドウの使い方

イミディエイトウインドウには、以下の特徴があります。
(1) イミディエイトウインドウ上でマクロを実行できる
(2) ワークシートに結果を反映させられる

イミディエイトウインドウにマクロの結果を表示させるには、

・プロシージャ上で Debug.Print を実行
・イミディエイトウインドウ上で実行処理
・？をつけて、イミディエイトウインドウ上で命令文を実行

上記の方法が使用できます。
次の演習を実施し、操作に慣れて行きましょう。


演習 1
以下のコードを記述し、結果をイミディエイトウインドウやワークシートに表示させましょう。
※ イミディエイトウインドウの命令文は 1行ずつ Enterを押す事で順次実行されます。 
Sub Immediate()
    Range("A1").Value = 100
    Debug.Print Range("A1")
End Sub

[イミディエイトウインドウ]

'1行ずつEnterで実行
?range("a1").Value

'1行ずつEnterで実行
b = 50
range("b1").Value = b
?range("b1").Value

補足：コードの解説
・Debug.Print：イミディエイトウインドウに結果を表示
・?range("a1").Value：A1セルの値を呼び出し

また、イミディエイトウインドウで実行した 変数 b への値の格納は、宣言を
行っていませんがエラーになりません。これは「暗黙の宣言」と呼ばれ、
Option Explicit の制御がイミディエイトウインドウに反映されない為です。



● マクロの一時停止

デバッグモードに移行せずマクロを一時停止にするには、以下 2 つの方法があります。

(1) ブレークポイント

プロシージャ内のコードを選択し、F9 キーを押すと該当行がブレークポイントに
指定されます。(クリックで解除可能) この状態でマクロを実行すると、指定した
コードが実行される直前に一時停止します。


(2) Stop ステートメント

プロシージャ内に Stop ステートメントを記述すると、その行でマクロが一時停止
します。機能としてはブレークポイントと同じですが、If ステートメントなど、
他の構文と組み合わせる事で、マクロの検証などに利用できます。



● デバッグ時の関数利用

エラーの原因を調べる場合、プロパティや関数を利用して状況の把握や値の
変遷などを調べます。その際、以下のキーワードが有用です。

・Value プロパティ：セルの入力値を取得
・Text プロパティ：セルの表示文字列を取得
・TypeName 関数：引数に指定したデータの型を返す
・IsNumeric 関数：引数に指定した値が数値かどうかを論理値で返す
・IsDate 関数：引数に指定した値が日付かどうかを論理値で返す



演習 2-1
セルA1の値が日付だった場合に、西暦年を求めてセルB1に代入するマクロです。
空欄に入る適切な語句を入力しなさい。 
Sub DebugMacro1()
    If 【     】(Range("A1").Value) = True Then
        Range("B1").Value = Year(Range("A1").Value)
    End If
End Sub

演習 2-2
セルB1の値が数値以外だった場合に、入力を促すマクロです。
空欄に入る適切な語句を入力しなさい。 
Sub DebugMacro2()
    If 【     】(Range("B1").Value) = False Then
        MsgBox "B1セルに数値を入力して下さい"
    End If
End Sub

以下に解答を記載しています。





解答
・3-1：IsDate
・3-2：IsNumeric



VBA テキストはここで終了です。お疲れ様でした！