ii

Excel

VBA

Excel

VBA

実践教室

2002

対応

実践教室

2002

対応

VBテックラボ

＆

瀬戸 遥

著

ii

本書内容に関するお問い合わせについて

このたびは翔泳社の書籍をお買い上げいただき、誠にありがとうございます。弊社では、読者の皆様からのお問い合わせに適 切に対応させていただくため、以下のガイドラインへのご協力をお願いいたしております。下記項目をお読みいただき、手順 に従ってお問い合わせください。 ●ご質問される前に、http://www.shoeisha.com/info/help.asp をご参照ください。 弊社Webサイトの「Q&Aコーナー」です。これまで受けたご質問への回答（FAQ）や、的確なご質問方法に関する情報 を掲示しています。 ●ご質問はすべて、http://www.shoeisha.com/book/qa/からお願いします。 弊社Webサイトの質問専用フォームサイトです。 お電話や電子メールによるお問い合わせ、独自の用紙を使った郵送によるご質問、本書にはさみ込まれたアンケートは がきに記入されたご質問には、お答えいたしかねます。 ●インターネットがお使いになれない読者の方には、質問専用シートをお送りします。 お客様のお名前、ご住所、郵便番号、FAX番号、「質問専用シート希望」と明記のうえ、FAXか郵便で、下記宛先まで お申し込みください。折り返し質問シートをお送りいたします。郵便の場合は、80円切手を同封してください。 シートがお手元に届きましたら、ご質問と必要事項を漏れなく記入し、「編集部読者サポート係」まで、FAXまたは郵 便にてご返送ください。 ●郵便物送付先およびFAX番号 送付先住所 〒160-0006 東京都新宿区舟町５ FAX番号 03-5362-3818 宛先 （株）翔泳社出版局 編集部読者サポート係 ●すぐにご回答できない場合もあります。 ご回答は、ご質問いただいた手段によってご返事申し上げます。ご質問の内容がむずかしいもののときは、回答の作成 に数日ないしはそれ以上の期間を要する場合があります。 ●このようなご質問には、お答えできません。 本書の説明範囲を超えるもの、記述個所を特定されていないもの、また機械の故障や不具合など、お客様固有の環境に 起因するご質問にはお答えできませんので、あらかじめご了承ください。 ※本書に記載されたURL等は予告なく変更される場合があります。 ※本書の出版にあたっては正確な記述につとめましたが、著者や出版社などのいずれも、本書の内容に対してなんらかの保証をするものではな く、内容やサンプルに基づくいかなる運用結果に関してもいっさいの責任を負いません。 ※本書に掲載されているサンプルプログラムやスクリプト、および実行結果を記した画面イメージなどは、特定の設定に基づいた環境にて 再現される一例です。

Microsoft、Windows、Windows NTは、米国Microsoft Corporationの米国およびその他の国における登録商標です。 その他、記載されている会社名、製品名は、各社の登録商標または商標です。

Excel VBA 実践教室で、VBAプログラミングをパワーアップ！

うこそ、

Excel VBA 実践教室へ!

近年、Excelユーザーの中で、VBAに対する関心とより多くの実践的なテクニッ クを求める要求が強まってきています。 特にビジネスユーザーを中心に、煩雑な業務処理の軽減や、高度で複雑な処理を 実行するために、VBAへの注目度が従来になく高くなってきています。このような 状況の中で、『10日でおぼえるExcel VBA 入門教室』は、多くの方々から支持をい ただき、たくさんのExcelユーザーに読んでいただいています。 引き続き、さらにVBAを使ったマクロプログラミングをパワーアップする、実践 的なテクニックを満載した、『10日でおぼえるExcel VBA 実践教室』を執筆しました。 VBAの基本的な知識や使い方、言語仕様をおぼえたら、次はどんどんいろいろな マクロを作成することが、上達への近道です。そして、多くの応用テクニックに接 して、幅広い知識を身に付けていくことが、一層スキルをアップさせていくことに なります。 本書は、前作『入門教室』でVBAプログラミングをおぼえた人や、もっと幅広い VBAの知識を習得したい人を対象に、1段も2段も高いレベルのプログラミングテク ニックを身に付けてもらう目的でカリキュラムを組みました。 Functionプロシージャの使い方、ユーザー定義型の作成と配列の使用方法、ユー ザーフォームに組み込む便利なコントロールの使い方、名刺管理や領収書整理マク ロ、郵便番号検索マクロなどすぐに役立つExcel VBAを使ったアプリケーション開 発の実例、メニューやツールバーをカスタマイズする方法、データアクセスオブジ ェクトを使ったデータベースとの連携操作など、すぐに役立つ例題を使用して多彩 なテクニックを紹介しています。 さらに本書は、「10日でおぼえる」シリーズの特長を継承し、豊富なサンプルと見 やすいレイアウト、わかりやすい解説で無理なく学習でき、知らず知らずにテクニ ックが身に付くように、全体を構成しています。 本書で習得した知識やテクニックは、多様化するExcelを使った業務に対応するマ クロを作成するために、充分威力を発揮するでしょう。 ぜひ、本書をVBAプログラミングテクニックのパワーアップにお役立てください。 2002年6月 瀬戸 遥

よ

1時限目 Functionプロシージャを使う………2 2時限目 配列を使う ………12 1時限目 ユーザー定義型変数を使う ………22 2時限目 ユーザー定義型の配列の作成と配列の動的確保 ………30 1時限目 ブックのWindowResizeイベントを使う ………44 2時限目 ブックのOpenイベントを使う………50 3時限目 BeforeCloseイベントとSheetChangeイベントを使う ………56 1時限目 コマンドバーを操作する ………62 2時限目 組み込みコントロールを追加する ………76 3時限目 カスタムメニューの作成 ………86

ステップアップテクニックⅠ

1

ステップアップテクニックⅡ

21

ブックのイベントを使う

43

CONTENTS

第

1

日 第

2

日 第

3

日

メニューとツールバーの操作

61 第

4

日

1時限目 オプションボタンコントロールを使う ………98 2時限目 チェックボックスコントロールを使う………110 3時限目 スピンボタンコントロールを使う………118 1時限目 ワークシートに関するテクニック………126 2時限目 ブックに関するテクニック………144 3時限目 その他のテクニック………152 1時限目 はじめてのADO ………172 2時限目 データ転送と検索………184 1時限目 ADOでクエリを実行する ………194 2時限目 ユーザーフォームでデータベースを操作するマクロ………206 1時限目 ユーザーインターフェイスの作成………226 2時限目 コードを組み込む………238

便利なコントロールを使う

97 第

5

日

便利な小技集

125 第

6

日

ExcelからAccessのデータを操作する

171 第

7

日

ADOでAccessからデータを抽出する

193 第

8

日

郵便番号検索マクロの作成

225 第

9

日

1時限目 見積書作成マクロ………252 2時限目 名刺管理マクロ………266 3時限目 領収書を整理するマクロ………278 練習問題の解答 ………292

実例マクロ集

251 第

10

日

索引

317

付録

291

CD-ROMをご使用いただく際のご注意です。CD-ROM中のファイル「最

初にお読みください.txt」もあわせて、ご使用前に必ずお読みください。

CD-ROMをご使用の前に

●CD-ROMについて 巻末に付属のCD-ROMには本書『10日でおぼえるExcel VBA 実践教室 2002対応』 （VBテックラボ＆瀬戸 遥著）の本文中で解説した各レッスンの完成版サンプルファ イルや、レッスンに必要なファイルが入っています。 ●CD-ROMの使い方 (1) CD-ROMをCD-ROMドライブに入れてください。 (2) CD-ROMに入っているファイルを、ハードディスクにフォルダごとドラッグ＆コ ピーしてお使いください。CD-ROMからコピーしたファイルには「読み取り専用」 属性が付いているので、そのままでは上書き保存ができません。「読み取り専用」 属性を外してからご使用ください。 ●免責事項について CD-ROMに収録されたファイルは、通常の運用においては何ら問題ないことを編 集部では確認しています。お客様の環境で運用された結果、万一いかなる損害が発 生したとしても、著者および株式会社翔泳社はいかなる責任も負いません。すべて 自己責任においてご使用ください。 ●CD-ROMの使用環境条件

Microsoft Excel 2002 が最低限動作する環境が必要です。CD-ROM には Microsoft Excel 2002は同梱されていません。本書を使用するにはMicrosoft Excel 2002、また はMicrosoft Office XPを別途用意する必要があります。

●CD-ROMのテスト環境について

CD-ROMは以下の環境で正常に動作することを確認しました。 ・Microsoft Excel 2002/2000/97

・Windows 98/Me/2000/XP

・Compaq Deskpro ENL （Intel Pentium III 865MHz）ほか ●著作権等について 本書に収録したプログラム、およびソースコードの著作権は、著者および株式会 社翔泳社が所有します。ただし読者が個人的に利用する場合に関しては、ソースコ ードやフォーム、コントロールの流用や改変は自由です。商用利用に関しては、株 式会社翔泳社へ電子メール（[email protected]）などでご一報ください。 （株）翔泳社 編集部

12

第

1

日

2

時

限

目

︻

ス

テ

ッ

プ

ア

ッ

プ

テ

ク

ニ

ッ

ク

Ⅰ

2

︼

配

列

を

使

う

配列の宣言方法、要素（インデックス）の使い方、値の出し入れ方法を

身に付けます。

同じ型のデータが何十個もある場合は、その数だけ変数を用意していたので は大変です。配列は、1つの変数で同じデータ型の複数のデータを格納する ことができる便利な機能です。 このレッスンでは、配列の宣言方法、要素（インデックス）の使い方、値の 出し入れ方法を身に付けます。

●このレッスンのねらい

今回作成するマクロ

1-2.xls EXVBA2_2002 サンプルファイルは こちら 1-2 1 行目と 2 行目の 値を配列として格 納して掛け算し… 3 行目に答えを挿 入して、セルを黄 色に塗りつぶす

13 第1日／2時限目●配列を使う

操作

●付録CD-ROMの「1-2」フォルダにある「配列.xls」を、使用しているコン ピュータのハードディスクにコピーして開いてください。次に、VB Editor を起動し、ツールバーの［標準モジュール］をクリックして「標準モジュー ル」を追加してください。

コードの記述

Module1:(General)-配列

Sub 配列()

Dim FstValue(6) As Integer

Dim NextValue(6) As Integer

Dim TtlValue(6) As Integer

Dim i As Integer

Set WS = Worksheets("sheet1")

For i = 0 To 6

FstValue(i) = WS.Cells(1, i + 1)

NextValue(i) = WS.Cells(2, i + 1)

Next

For i = 0 To 6

TtlValue(i) = FstValue(i) * NextValue(i)

WS.Cells(3, i + 1) = TtlValue(i)

WS.Cells(3, i + 1).Interior.ColorIndex = 6

Next

End Sub

解説

配列は、ワークシートのセルのように、行列で値を格納できる変数のことで す。Dimステートメントで宣言した変数の後ろに、（ ）を使って要素数を指 定すれば、その数分の配列を確保できます。例えば、

Dim UserName(3) As String

と宣言すると、String型の変数を4つ確保したことになります。

（ ）内には、確保したい配列の要素数から1を引いた数を指定します。これで、こ のUserNameという変数は、次の4つの変数が使用できるようになります。

14

UserName(0)

UserName(1)

UserName(2)

UserName(3)

1つの変数名で、たくさんのデータを格納できるのが、配列の大きなメリットの1つ です。配列の宣言時は、配列の要素数（インデックスともいいます）を直接整数で指 定します。宣言した配列のインデックスは標準では「0」から始まり、宣言した数ま で確保されます。つまり、

Dim UserName(9) As String

と宣言すると、この配列は「UserName(0)」から「UserName(9)」までの10個の配列 を確保したことになります。 宣言した配列に値を代入する場合は、変数名に代入したい要素を指定して 値を代入します。次の記述は、2番目の配列に文字列「瀬戸」を代入してい ます。

UserName(1) = "

瀬戸

"

このように、何番目の要素に値を代入するのかをインデックス番号で指定し、式を 組み立てます。また、配列に代入された値を参照する場合も、インデックス番号を指 定して値にアクセスします。 次の記述は、今代入した2番目の要素の値を、メッセージボックスで表示します。

MsgBox UserName(1)

配列のインデックス番号に、変数や式を使うことができます。次のプロシー ジャは、Fo r... Nextループを使って配列の7つの要素の値を、メッセージボッ クスで表示します。

For i = 0 To 6

MsgBox UserName(i)

Next

インデックス番号に変数や式が使用できると、配列へのアクセスが自動化できるよ うになります。名前の違う変数をたくさん用意したのでは、このような操作は無理で すね。配列を使うメリットは、ここにもあります。

2

3

15 第1日／2時限目●配列を使う これまでの説明では、「UserName(9)」のように、配列の要素数を1種類しか 指定しませんでした。これを「1次元配列」といいます。ワークシートでい うと、「1行多列」の変数を確保したことになります。 これに対し、VBAの配列では、「多次元配列」を確保できるようになっています。 次の記述は、「2行」「5列」の配列を確保します。

Dim UserName(1, 4) As String

ここでも、配列の要素は「0 , 0」から始まっていることに注意してください。配列 の要素に指定するインデックス番号を行列数で指定すると、多次元配列を使用できる ようになります。そして、確保した配列の各要素にアクセスするには、この行列番号 をインデックス番号に指定します。 次の式は、1次元目の3番目の配列要素に文字列「瀬戸」を代入します。

UserName(0,2) = "

瀬戸

"

実際に配列を使った簡単なマクロを作成してみましょう。配列は、1次元配 列を使用します。 こ の マ ク ロ は 、 ワ ー ク シ ー ト 「 S h e e t 1 」 の 最 初 の 行 に あ る デ ー タ を 、 配 列 「FstValue」に格納します。また、2行目のデータを配列「NextValue」に格納し、各 配列の値を掛け算して、答えを「TtlValue」という配列に格納します。そして、配列 「TtlValue」の値をワークシートのセルに代入し、そのセルを黄色で塗りつぶします。 それぞれの配列は、宣言時に「6」を指定し、7個の要素数を確保します。また、3 つの配列の要素にアクセスするのに変数を使用し、セル番地の指定にも同じ変数を使 用しています。配列のインデックス番号とセル番地の指定を同期させているのがポイ ントです。

4

5

16 では、標準モジュールにプロシージャ「配列」を作成してください。そして、 3つの配列を宣言します。インデックス番号に「6」を指定しているので、「0」 から「6」までの7つの要素数を持った配列が確保されます。また、このインデックス 番号に使用する変数「i」も宣言しておきます。

Sub 配列()

Dim FstValue(6) As Integer

Dim NextValue(6) As Integer

Dim TtlValue(6) As Integer

Dim i As Integer

次に、For ... Nextステートメントで、配列の各要素に、セルの値を代入します。 配列のインデックス番号に変数「i」を指定します。これで、For ... Nextステートメ ントによって、「0」から「6」までの7つの要素に順番に値が代入されます。 セルの参照には［Cells］プロパティを使用し、引数の列番号に変数「i」を使ってい ます。しかし、セルの列番号に「0」は存在しませんので、「1」を加えて列「1」から 「7」までの値が代入できるようにしています。

Set WS = Worksheets("sheet1")

For i = 0 To 6

FstValue(i) = WS.Cells(1,

i + 1

)

NextValue(i) = WS.Cells(2,

i + 1

)

Next

もう1つFor ... Nextループを使用して2つの配列の各要素の値を掛け算し、もう1つ の配列「TtlValue」に代入しています。ここでも、インデックス番号に変数「i」を使 用して、3つの配列のインデックス番号がまったく同じになるようにしています。

For i = 0 To 6

TtlValue(i) = FstValue(i) * NextValue(i)

そして、配列「TtlValue」の値を、3列目のセルに代入し、そのセルの背景色を黄 色に塗りつぶしています。ここでも、配列のインデックス番号とセル番地を式で同期 させています。

WS.Cells(3, i + 1) = TtlValue(i)

WS.Cells(3, i + 1).Interior.ColorIndex = 6

Next

6

列番号を作成

17 第1日／2時限目●配列を使う 配列の作成方法にDimステートメントの宣言を使用しましたが、「Array」関 数を使用して配列を作成することも可能です。Array関数は、変数に配列を 確保し値を代入する関数です。はじめから代入する値が配列で使用できる場合は、こ の関数を使うこともできます。 次の式は、変数「Fruits」に、4つの果物の名前を代入します。

Dim Fruits As Variant

Fruits = Array("

りんご

","

オレンジ

","

バナナ

","

メロン

")

次のプロシージャ「ArrayTest」は、Array関数を使用して代入した果物の名前を、 ワークシートの5行目に出力します。

Sub ArrayTest()

Dim Fruits As Variant

Fruits = Array("

りんご

", "

オレンジ

", "

バナナ

", "

メロン

")

For i = 0 To 3

Worksheets("sheet1").Cells(5, i + 1) = Fruits(i)

Next

End Sub

この場合も、インデックス番号に変数を使うことができ、またインデックス番号は 必ず「0」から始まります。また、配列データを格納する変数は、必ず「バリアント 型」で宣言してください。Array関数の戻り値は「バリアント型」だからです。

7

ここでは、説明のために処理を2つのFor ... Nextループに分けて作成しています。

ワ ン ポ イ ン ト ・ ア ド バ イ ス

18

練習問題2

20個の要素を持つ整数型の配列「NumCount」を宣言し、イン

デックス番号「0」から順番に、インデックス番号と同じ数字を

代入するプロシージャを作成してください。配列に代入したら、

各値はワークシートのセルの行方向に出力します。

……… 解答は巻末に

練習問題

Q

配列を使うと、同じデータ型であれば、1つの変数で多くのデータを格納できるように なりますから、変数やデータの管理がとても楽になります。また、各要素へのアクセスに 使用するインデックス番号に変数が使用できますので、フロー制御処理を自動化すること ができます。多次元配列を使えば、さらに効率的なデータ管理ができるでしょう。 数の多いデータを処理する場合は、配列を上手に使ってください。

まとめ

NumCount(0) = 0

NumCount(1) = 1

NumCount(2) = 2

...

NumCount(19) = 19

19 第1日／2時限目●配列を使う 配列の要素は、標準では常に「0」から始まることに注意してください。この最低値 を変更したい場合は、モジュールの宣言セクションに、「Option Base」ステートメン トを使って、最低値を指定します。 次の記述は、配列の最初の要素数を「0」ではなく「1」に設定します。 Option Base 1 Dimステートメントを使用して、直接、要素数の最小値と最大値を指定することもで きます。 次の記述は、インデックス番号「0」から「9」までの10個の要素数を確保します。

Dim UserName(0 to 9) As String

この場合は、「Option Base」ステートメントの設定よりも、Dimステートメントの

設定が優先されます。

また、Array関数を使用した場合は、「Option Base」ステートメントの設定いかん

にかかわらず、常に最小値が「0」になります。

ワ ン ポ イ ン ト ・ ア ド バ イ ス

Option Base 1

Dim UserName(0 to 9) As String

配列が消費するメモリ量は、次の式で計算できます。 宣言したデータ型のバイト数 * 配列の要素数 例えば、整数型の変数で要素を10個持った配列の場合は、次のようになります。 2バイト * 10 = 20バイト なお、配列が使用できる最大要素数は、使用するシステムで利用できる最大メモリ量 に依存します。

ワ ン ポ イ ン ト ・ ア ド バ イ ス

宣言したデータ型のバイト数

*

配列の要素数

2

バイト

* 10 = 20

バイト