「エクセルマクロ・VBA発展編１」講座と演習

発展１講座イントロダクション – 使用するファイル、PDFの確認

講座で使用するPDFとデータの確認です。

講座内で使用するデータはこの動画内でダウンロードできます。

発展１講座イントロダクション – デバッグ イミディエイトウィンド

プログラミングとテストの手法のうち、デバッグについて説明します。
マクロをテストする方法で、イミディエイトウィンドに出力するという方法があります。
変数の中身をチェックしたり、For next構文で、今、何行目を実行しているかを出力し、確認することができます。
また、If文で指定した条件で処理したデータのうち、そのデータの氏名をイミディエイトウィンドに書き出すことができます。
とても便利なテスト方法ですので、ぜひここで覚えてください。

発展１講座第１章 – オープニング　第１章１

第1章では「データの型」と「変数」について説明します。

基礎編でも学びましたが、変数を作るときの細かいテクニックなど、もう少し掘り下げて学んでいきます。

発展１講座第１章 – オープニング　第１章２

変数には「一般のデータ」型（名前のついた箱）と「オブジェクト」型（ニックネーム）があります。
基礎編では、「Dim Ziritsu」などと変数を宣言しましたが、これはデータ型の変数となります。
この章では、データ型のさまざまな条件設定について学んでいきます。

発展１講座第１章 – データの「型」１

変数宣言時に、データ型を指定する方法について説明します。

見積書の所定のセルに、「お見積額合計」という文字と、「合計金額」の値を入力するマクロです。
変数宣言時にデータ型を指定していないとき、変数に間違えて違う型の値を入れていてもマクロは実行されてしまいます。
そこで、変数宣言時に、データ型を指定することによって、あり得ない取り違いを防ぐことができます。

発展１講座第１章 – データの「型」２

下記の「データ」型について説明していきます。
「As String 文字列型」
「As Date 日付型」
「As Long 長整数型」
「As Double 倍精度浮動小数型」
「As Boolean ブール型」

発展１講座第１章 – 変数の初期値

変数の初期値について説明します。
変数は指定した「データ」型によって、変数を設定した段階で、初期値が自動的に決まるようになっています。

発展１講座第１章 – ハンガリアン記法

変数のネーミングルール、「ハンガリアン記法」について説明します。
例えば、Date型の変数なら、「Dim dFinal As Date」と変数の前に[d]、
文字列のString型なら、Dim sCompany As Stringと変数の前に[s]をつけるやり方です。

発展１講座第１章 – 変数宣言の強制１

Option Explicitの言葉の意味について説明します。
これは、プログラムを書くときに、「変数の宣言を強制する」というメッセージです。

発展１講座第１章 – 変数宣言の強制２

前回の「変数の宣言を強制する」ということについて、もう少し掘り下げていきます。

Option Explicit があると、変数の宣言は、「しなくてはならない」ですが、
Option Explicit がないと、変数の宣言は、「してもしなくてもよい」となります。
「してはならない」ではありませんので注意してください。

発展１講座第１章 – 総称オブジェクト型

オブジェクト型には、「固有オブジェクト型」と「総称オブジェクト型」というものがあります。
ここでは総称オブジェクト型から説明します。
簡単にいうと、オブジェクトにニックネームを設定して、そのニックネームを使ってオブジェクトを操作することができるというものです。

発展１講座第１章 – 固有オブジェクト型

固有オブジェクト型について説明します。

総称オブジェクト型 As Object は、セル、シート、ファイル、図形など、どんなオブジェクトにも使えます。
それに対し固有オブジェクト型は、より細かいオブジェクトの指定ができます。
As Range で作った変数は、セルに対してニックネームを使用することができますが、セル以外のオブジェクトに対して使用しようとするとエラーが出ます。

発展１講座第１章 – バリアント型

バリアント型で変数を宣言すると、一般のデータ型（名前の付いた箱）としても使用できるし、オブジェクト型（ニックネーム）としても使えます。
「なんでもOK」と宣言することになります。

発展１講座第２章 – オープニング　第２章

第２章はオブジェクトについて学習していきます。

オブジェクトの状態を変えることを、「プロパティを『設定する』」、といいます。
オブジェクトの状態を調べることを、「プロパティを『取得する』」、といいます。

この章では、プロパティという概念とは違う、「メソッド」という概念についても話していきます。
プロパティとメソッドを含めて「メンバー」という言葉もあります。

発展１講座第２章 – コレクション

コレクションという概念について説明します。
コレクションとは、「同じタイプのオブジェクトの集合体」のことです。
エクセルマクロで登場するコレクションは、
セル、図形、コメント、行、ワークシート、ブックなどです。

発展１講座第２章 – コレクション（インデックス）

コレクション（インデックス）について学びます。

これまでWorksheets(“Sheet1”)と書いてきたのが、ここで説明する「コレクション（インデックス）」のことです。インデックスの部分には、名前か数字を指定できます。

Worksheets(“Sheet1”) では、
Worksheets がコレクション、Sheet1がインデックスということです。

発展１講座第２章 – コレクション（インデックス）の具体例

コレクション（インデックス）の具体例を見ていきます。

Worksheets(“GetEnd1”).Tab.Color = vbRed
すべてのシートの集合体の中のシート「GetEnd1」のタブの色を vbRed にする。

Worksheets(3).Tab.Color = vbBlue
すべてのシートの集合体の中の「前から3番目」のシートのタブの色を vbBlue にする、です。
インデックスに数字を指定することもできます。

発展１講座第２章 – プロパティ 従属要素のオブジェクト

メンバーには、「プロパティ」「メソッド」という2つの概念があります。
ここでは、これまで学んできた「プロパティ」についてもう少し掘り下げていきます。

基礎編レベルでは、プロパティは「『値』として表現できるデータのことを指してきましたが、プロパティは、あるオブジェクトと関係するオブジェクトを指すこともできます。
たとえば、従属要素のオブジェクトはプロパティ足り得ます。

発展１講座第２章 – プロパティ 上位のオブジェクト

上位にあるオブジェクトもプロパティとなります。

セルから見て上位にあるシートも、セルのプロパティとなります。シートから見たファイルも同様です。
つまり、階層構造の下にも、上に行くときも、プロパティという言い方ができます。

発展１講座第２章 – プロパティ 同レベルのオブジェクト

セルとセル、シートとシートなど、同レベルにあるオブジェクトもプロパティとなります。
あるシートの隣にあるシートを調べる、ということができます。

発展１講座第２章 – プロパティ 値として表現できるデータ

値として表現できるデータについて掘り下げて説明します。
「.Value」プロパティについては、これまで説明してきましたが、ここでは、
合計数を返す「.Count」、セルの行番号を数値で返す「.Row」、セルの列を数値で返す「.Column」というプロパティを紹介します。

発展１講座第２章 – プロパティ Offset

同レベルのオブジェクトを取ってくるプログラム「Offset」を紹介します。

Offsetは、基準になるセルから、あるセルまでの相対的な位置関係を調べたり、取得したりできます。

発展１講座第２章 – プロパティ Offsetの使い方

Offsetの便利な使い方を紹介します。
基礎編のレベルでは、「For Next構文」を使って、行番号を変数に設定して、行の繰り返し処理をすることはできましたが、横方向、列の繰り返し処理はできませんでした。

列の繰り返し処理は、Offsetを使えば可能になります。

もちろん、Offsetを使って、行の繰り返し処理も可能ですし、行、列の繰り返しも可能です。

発展１講座第２章 – End プロパティの使い方

今回は End プロパティについて説明します。
Endプロパティは、指定されたセルが含まれる領域の終端のセルを示す Range オブジェクトを返します。
エクセル上で、[Ctrl]+方向キー（↑、↓、←、→）の操作に相当します。

Endプロパティを使って、データの最終行の行番号を求められますので、毎月データ数が変わるデータを処理する場合などに利用できます。

発展１講座第２章 – メソッド１

プロパティとメソッドを含めてメンバーといいますが、今回は、メソッドについて紹介していきます。

オブジェクトにはプロパティという特徴があるだけじゃなくて、お願いされたことを実行するという機能があります。これがメソッドです。

発展１講座第２章 – メソッド２

メソッドの構文の基本パターン「オブジェクト .メソッド」の形を意識してください。

条件を指定するときは、
「条件名 := 具体的にやってほしいこと」
複数の条件がくるときは、「,」 で区切ることになっています。

メソッドと、プロパティの取得・設定との見分け方についても学んでいきます。

発展１講座第３章 – マクロの自動記録 イントロダクション

エクセルマクロの自動記録について扱います。
マクロの自動記録とは、エクセル上で操作した手順を、そのままマクロのプログラムにしてくれる機能です。

たとえば、オートフィルタ、並べかえ、シートのコピー、グラフ作成など、どうマクロを書けばわからない場合は、マクロの自動記録でプログラムを書いてみます。
ただ、自動記録で書かれたマクロには「独特のクセ」があります。
このクセを理解して、マクロのコードを読めるようになるスキルを身に付ける必要があります。

発展１講座第３章 – マクロの自動記録 オートフィルタ１

マクロの自動記録を実行して、オートフィルタの操作を自動記録した後、プログラムの中身を見ていきましょう。
オートフィルタの設定、実行、解除の操作を自動記録していきます。

その前提として、Excel2003までの場合と、Excel2007以降の場合では、マクロの自動記録の操作の違いについても説明します。
早く操作できるように、何度かマクロの自動記録を実行して、操作に慣れておきましょう。

発展１講座第３章 – マクロの自動記録 オートフィルタ２

前回作った、オートフィルタを作るマクロについて見ていきます。
このマクロはちゃんと動きますが、このまま放置しておくと問題があります。

問題と対処法について具体的に説明していきます。

発展１講座第３章 – マクロの自動記録 フォントの設定

フォントを変更するマクロを自動記録して、マクロの中身を見ていきます。
フォントの変更を自動記録した場合、自分が設定した項目とは違う項目までマクロに記述されます。
不要な部分は、原則、削除しますが、「コメントブロック」ボタンを利用して、コメントアウトする方法について説明します。

また、マクロで登場する「Withブロック」についても説明します。

発展１講座第３章 – マクロの自動記録 カラーインデックス

Excel2003で、マクロの自動記録で色を設定したときに記述される「ColorIndex」について説明します。
エクセルマクロの世界では、主な色には背番号がついています。
その背番号を使って、色を呼び出すことができます。

エクセルマクロでは、「色は数字で呼び出せる」と覚えておいてください。

発展１講座第３章 – マクロの自動記録 アンダースコア

自動記録でマクロを生成した場合、アンダースコア「_ 」が出現する場合があります。
エクセルマクロのプログラムでは、横長になりすぎたプログラムは、「 _ 」（半角スペース + アンダースコア）を挿入して、途中改行することができます。
自動記録で、プログラムが長いと判断されると、この「 _ 」が自動的に挿入されるわけです。

この状態のままでは、可読性も低いので、まずは、自動改行されたプログラムを一行にして、一行の長いプログラムを改行していく作業が必要になります。タブでずらしてレイアウトを調整すると、さらに見やすくなります。

発展１講座第３章 – マクロの自動記録 途中改行１ 入力支援機能

途中改行を取って、プログラムを一行にする練習をしていきます。

途中改行を取るショートカットは、 [Back], [Delete], [End]。
いきなりキーボードを触らずに、まずは10回、20回と口で唱えてください。
次に、それぞれのキーを押すイメージを持ちながら、口で唱えてみましょう。

発展１講座第３章 – マクロの自動記録 途中改行２

[Back], [Delete], [End]のショートカットを使って、複数行のマクロを一行に、
[Space], [ _ ], [Enter]のショートカットを使って、一行のマクロを途中改行して複数行のマクロにしていく練習をします。

途中改行した部分を[Tab]でずらして、この部分が一行のマクロとわかるようにレイアウト調整もしておきます。

発展１講座第３章 – マクロの自動記録 並べ替え

Excel2003までのバージョンを使って、自動記録しながら「並べ替え」をしてマクロを生成していきます。
生成された一行のマクロは、「_ 」で途中改行されていますので、前回までに習ったショートカットで直していきます。

注意してほしいのは、自動記録で作ったマクロが「操作を完全に再現できるとは限らない」ということです。
自動記録で生成されたマクロには、変更が必要な場合があるということを覚えていてください。

発展１講座第３章 – マクロの自動記録 個人用マクロブック

個人用マクロブックの説明をします。
個人用マクロブックとは、エクセル起動時に必ず読み込まれるエクセルファイルのことです。
個人用マクロブックを利用する目的として、お気に入りのマクロの部品をため込んでおくと、いつでもモジュールを開いて、マクロをコピーできるので便利です。

発展１講座第４章 – 論理演算子 イントロダクション

第４章は、演算子と構造文の話を中心に進めていきます。

はじめに、下記の論理演算子について説明します。

And（積集合）：演算子の左の条件が合う、かつ、右の条件が合っていれば True、そうでなければ False を返す
Or（和集合） ：演算子の左の条件が合う、または、右の条件が合っていれば True、そうでなければ False を返す
Not（補集合）：演算子の条件が合わなければ True、そうでなければ False を返す

発展１講座第４章 – 論理演算子 解説

論理演算子「And」「Or」「Not」について、実際にマクロを動かしながら解説していきます。

条件Aと条件Bに対して、
A And B
A Or B
Not(A or B)

それぞれの結果をみていきます。

発展１講座第４章 – 構造文 イントロダクション

これから構造文について説明していきます。
基礎編ではくり返しの構造文「For Next」、条件分岐の「If文」という構文が出てきました。

次回以降では、新たに3つの構文、
「For Each構文」「Do Loop構文」「Select Case構文」について学んでいきます。

発展１講座第４章 – For Each構文 イントロダクション１

“For Each構文について説明します。
For Each構文は、「コレクション」の各要素を、ひとつずつ「順番に取り出して」処理していく、くり返し構文です。

エクセルマクロでは、下記のオブジェクトのコレクションが登場します。
セル：Range(“”A2″”)、ワークシート：Worksheets(“”Sheet1″”)、グラフ：Charts(“”Chart3″”)、ワークブック：Workbooks(“”Book1.xls””) など。

発展１講座第４章 – For Each構文 イントロダクション２

For Each構文の作り方について説明していきます。
キモは、

Dim 変数（ニックネーム） As データ型
For Each 変数（ニックネーム） In コレクション

変数の宣言をするときのデータ型は「単数形」になり、
For Each構文の In の後のコレクションは「複数形」になります。

発展１講座第４章 – For Each構文 イントロダクション３

For Each構文を実際に書いていく前に、Debug.Print を使って、For Each構文のマクロの動き方を見ていきます。

「開いているファイルにあるすべてのワークシート」からシートがひとつずつ取り出され、ニックネームが割り当てられます。その後、処理が実行されます。
ワークシートが割り当てられるたびに、イミディエイトウィンドにシート名が表示されますので、その変化をよく観察してください。

発展１講座第４章 – For Each構文 演習

実際にFor Each構文のマクロを書く演習です。
「ハナコのステップ」に従って、すべてのシートの色を変更するマクロを書いていきましょう。

まずは、「お団子を作る」。ひとつのシートの色を変更するマクロを書きます。

それから「ハナコのステップ」
・箱を作る
・中に入れる
・加工する

これで完成です。
変数をいろいろ変えながら、For Each構文を書いてみてください。

発展１講座第４章 – For Each構文 伝票作成マクロ解説

取引先ごとの伝票を作るマクロです。
データのシート「main」と、伝票の雛型のシート「main1」があります。
「伝票作成」ボタンを押すと、取引先ごとの伝票がシート別に作成され、「伝票消去」ボタンで、作成された伝票のシートが削除されていきます。

「伝票消去」のマクロは、For Each構文で、すべてのシートをひとつずつ調べていき、シート名の先頭が「main」でなければ、そのシートを削除します。
すべてのシートを調べ、処理を実行するなどといったケースでは、For Each構文はとても便利です。

発展１講座第４章 – For Each構文 セルのコレクション

セルのコレクションに対して、For Each構文を使ったマクロを見ていきます。 指定した範囲のすべてのセルを調べていって、一定条件を満たすときだけチェックをするというマクロです。

この範囲のセルをひとつずつ取ってきて、調べ、「値が40以上」という条件を満たせば処理を実行していきます。

発展１講座第４章 – For Each構文 カンニングペーパー

For Each構文は、抽象的でとっつきにくく、オブジェクトの型によって書き方が変わるなど、覚えにくいです。
そこで、実務で活用できるように、カンニングペーパーを用意しています。(マインドマップ P12)
実務で使用するときは、カンニングペーパーを見ながら真似してください。

発展１講座第４章 – Do Loop構文 概要

くり返しの構文「Do Loop構文」は、プログラムを書く段階では、何回繰り返すのか検討がつかない場合によく使われます。
例えば、桁数がバラバラの数字を「No-数字4桁」の文字列にしたい場合です。
次回、Do Loop構文を使って、この作業をマクロで書いていきます。

発展１講座第４章 – Do Loop構文 マクロの流れ

セルの値「9」を「No-0009」という文字列にするマクロを見ていきます。
数字の文字数を調べ、4文字未満であれば数字の先頭に 0 をつけていき、文字数が4文字になるまで処理をくり返します。

Do Whileという構文が出てきますが、これは、「その後の条件が満たされている間は、Loop に挟まれた内容をくり返す」という構文です。

発展１講座第４章 – Do Loop構文 ４つのパターン

Do Loop構文には下記の「4つのパターン」がありますので、順に説明します。

[1]
Do While 条件
Loop

[2]
Do Until 条件
Loop

[3]
Do
Loop While 条件

[4]
Do
Loop Until 条件

発展１講座第４章 – Do Loop構文 マクロの強制終了

Do Loop構文でよくある失敗は、くり返しを指定する条件をちゃんと書けなくて、永久に Do 〜 Loop から抜け出せなくなるようなマクロを書いてしまうことです。

永久ループにはまった場合、マクロを強制終了してループから抜け出す方法を説明します。

発展１講座第４章 – Exit For、Exit Do、Exit Sub

くり返しの構文で、即座にループの終わりまで移動する「Exit For」と「Exit Do」、即座に End Sub まで飛ぶ「Exit Sub」について説明します。

発展１講座第４章 – Select Case 概要

Select Case構文について説明します。

例えば、営業成績の数値に基づいて段階的に評価を入力していくような場合、Select Case構文を活用できます。
営業成績の平均の数値によって、下記のように評価します。
・105以上は「超優良店舗」
・100以上、105未満は「優良店舗」
・ 95以上、100未満は「やや問題」
・ 95未満は「一層努力」

まずは、マクロを見ていきましょう。

発展１講座第４章 – Select Case If文との比較

If文で書かれたマクロと、Select Case構文で書かれたマクロを比較してみると、

Select Case構文では、Select Case の後の Range(“I2”).Value が比較対象だとわかりやすいのに比べ、
If文では、比較対象である Range(“I2”).Value が何度も登場し、非常にわかりにくくなります。

同じ比較対象に対して、何度も比較をしていくときは、Select Case構文が有効です。

発展１講座第４章 – Select Case 比較する条件の順番

Select Case構文のマクロの書き方について説明します。

Select Case の後ろに比較したいもの、そして、End Select と書きます。
これがセットで、中身を入れるための入れ物になります。
その中に、「Case Is 条件」と書いて、比較する条件をいくつか並べていき、最後にすべての条件に合致しないときのための「Case Else」を書きます。

発展１講座第４章 – Select Case 複数条件の並べ方

Select Case構文の便利な使い方の例を紹介します。
比較対象のデータをカテゴリーに分けて、カテゴリー別に処理をするといった場合に、Select Case構文は便利です。
Select Case構文は、「,」で区切って複数の条件を並べることができます。条件のどれかを満たせばその後に書かれた内容を実行します。

発展１講座第４章 – Select Case 条件の範囲選択

Select Case構文の便利な使い方として、Case の後に「To」を使って、条件の範囲を選択することができます。

Case 0 To 3
Case 4 To 5

「,」で条件を並べるよりも簡単ですので、ぜひ活用してください。

発展１講座第５章 – Sub プロシージャの分割と呼び出し

Subプロシージャの分割と呼び出しについて学んでいきます。

取引データの取引金額の大きい方から順位をつけていく作業を例にとると、
[1] NumberA : 取引データにID番号をつける
[2] SortG : 取引金額の並べ替えを行う
[3] NumberH : 取引金額の大きい方から順位をつける
[4] SortA : データをID番号順に並べ替える（元のID番号順に戻す）

上記のように、それぞれの作業ごとに、合計4つのマクロを書きます。
最後に、4つのマクロを順に実行していくマクロを書きます。
マクロの中で別のマクロ名を書くと、そのマクロを呼び出すことができます。

発展１講座第５章 – Sub プロシージャを分割するメリット

マクロを分割して書くメリットは、大きく下記の3つです。

・全体の流れが分かりやすくなる
・メンテナンスが容易になる
・部品化されたものを他Subプロシージャも利用できる

分割したマクロをひとつのマクロとして書くこともできますが、分割したマクロに対して、どんな作業かわかるような名前をつけておくことが大事です。

発展１講座第５章 – ステップアウト、ステップオーバー

連携したマクロのテストのテクニック、
ステップアウト（[Ctrl]+[Shift]+[F8]）とステップオーバー（[Shift]+[F8]）について説明します。

発展１講座第５章 – モジュールレベル変数

分割したマクロで、ほぼ同じようなプログラムがある場合は、モジュールレベル変数を利用するとひとつにまとめることができます。
モジュールレベル変数は、Option Explicitの後、かつ、最初に出てくるサブプロシージャより前のエリアで宣言します。

発展１講座第５章 – モジュールレベル変数の操作

モジュールレベル変数を宣言して、実際に操作してプログラムの動きを確認していきます。
何回か繰り返し実行してみてください。

発展１講座第５章 – モジュールレベル変数 変数の重複

変数の重複の問題について説明します。
モジュールレベル変数と同じ名前の変数をサブプロシージャの中で宣言すると、モジュールレベル変数よりも、サブプロシージャの中にある変数が優先されてしまいます。

発展１講座第５章 – モジュールレベル変数 まとめ

モジュールレベル変数のまとめです。

・モジュールレベル変数は、Option Explicitの後、かつ、最初に出てくるサブプロシージャより前のエリアで宣言することができます。
・モジュールレベル変数は、同一モジュール内のすべてのサブプロシージャからアクセスが可能です。

発展１講座第５章 – モジュールレベル変数 アクセシビリティ設定

別モジュールからのアクセシビリティ設定について説明します。
別モジュールの変数を呼び出して利用したいという時は、サブプロシージャの場合は、「Sub 〜」の前に Public を付けて、変数の場合は、「Dim 変数 As 〜」の Dim を Public に変えて、「Public 変数 As 〜」と宣言します。
反対に、頭に「Private」と書くと、他のモジュールから呼び出すことができません。