Microsoft Office Excel 2003 オブジェクト モデルの初心者向けガイド

Frank C. Rice
Microsoft Corporation

August 2003

適用対象:
MicrosoftR Office Excel 2003

要約: Excel オブジェクト モデルを使用して Excel の操作方法を簡単にカスタマイズする方法を習得します。このレッスンには、サブルーチンの作成、オブジェクト、メソッド、およびプロパティの基礎、さらにダイアログ ボックスの使用が含まれています。

目次

はじめに
Excel オブジェクト モデルの使用法を学習する理由
このガイドの使い方
必要な基礎知識
このレッスンで習得する知識
セット アップ
レッスン 1: 準備作業
レッスン 2: プログラミング概念: Sub. . . End Sub およびプロシージャ
レッスン 3: プログラミング概念: オブジェクト、メソッド、およびプロパティ
レッスン 4: 実際の例 #1
レッスン 5: 独自のダイアログ ボックスを作成する
レッスン 6: プログラミング概念: コレクション
レッスン 7: プログラミング概念: If. . . Then
レッスン 8: 実際の例 #2
さらに詳しく学習するには
付録 A: 実践問題の回答

はじめに

Microsoft Office Excel 2003 は Excel オブジェクト モデルで簡単にカスタマイズできます。とても簡単です。コンピュータ サイエンスの修士や博士の学位は必要ありません。さらに C、C++、またはその他のプログラミング言語の知識も必要ありません。オブジェクト モデルに関する知識は何も必要ありません。

それを明らかにするために、以下のコードを見てみましょう。

次のコードによって Excel で何が実行されるかわかるでしょうか。

これらの数行のコードで、アクティブなワークシート内のデータを含む行に 1 つおきに網掛けを追加できます。ColorEverySecondRow では、最初に、値 15 を持つ gray という定数が宣言されます。この定数の意味は、すぐ後で明らかになります。次に、行 2 が選択されます。その行全体が選択されると、左端の列 A のセルが自動的にアクティブなセルになります。そのアクティブ セルが空でない限り、Do...Loop ステートメントが繰り返されます。このループ内のコードで、選択したセルの内部の色が 15 に設定されます。これは定数 Gray の値です。このコードでは、次に、アクティブなセルから 2 つ下の行全体が選択されます。これは、アクティブなセルが空で While 条件が true でなくなり、ループが終了するまで続けられます。

これで、6 行のコードだけでワークシートにすばやく色を追加し、読みやすくできることがわかりました。

Excel オブジェクト モデルの使用法を学習する理由

Excel オブジェクト モデルを使用すると、自分のニーズに合わせて Excel をカスタマイズできます。Excel に直ちに追加機能が必要な場合は、Excel オブジェクト モデルを使用するのが理想的です。

たとえば、会社の部長が、営業担当者の出張報告の処理において、紙の経費報告書の処理が大きなボトルネックであると考えているとします。この場合、Excel で経費報告書をすばやく作成し、その報告書を XML スキーマにマッピングできます。これにより、営業担当者は、自分のラップトップ コンピュータで報告書を完成し、現場からそれらを送信できます。

このガイドの使い方

このガイドは、8 つのレッスンで構成されています。各レッスンは実践的で、このガイドを読みながら実際に Excel でレッスンを演習します。このガイドは、就寝前に読むより、コンピュータを実際に操作しながら読むほうが適しています。

必要な基礎知識

必要なのは、Microsoft WindowsR オペレーティング システムの使用経験と、Microsoft Excel の知識だけです。それ以上の知識は必要ありません。

既にプログラミングの経験がある場合は、プログラミングの手法に関する項を読むだけでもかまいませんが、それ以外の部分も有益な場合があります。

このレッスンで習得する知識

このガイドをすべて読み、すべての例と練習問題を実行すると、Excel オブジェクト モデルを使用してアプリケーションを開発できるようになります。問題の解決に最適な Excel オブジェクト モデルのツールを見つけることができるようにもなります。最後に、いくつかの重要なプログラミング概念について説明します。これによって、Microsoft Visual BasicR プログラミング言語の実用的な知識が身に付きます。つまり、このガイドによって、組織のニーズに対応した Excel オブジェクト モデルを使用できるようになります。

セットアップ

このガイドを使用し、この中の例を実行するには、Microsoft Office Excel 2003 (Beta 2 以降) が必要です。Excel オブジェクト モデルを使用するために特別な開発ツールは必要ありません。

レッスン 1: 準備作業

このガイドのすべてのレッスンで Microsoft Visual Basic for Applications (VBA) を使用します。VBA は、Microsoft Visual Basic のバージョンの 1 つで、Microsoft Excel を含む Microsoft Office アプリケーションに組み込まれています。VBA で作成するソリューションはマクロともいいます。マクロは、役立つ機能を実行する Visual Basic の一連の命令です。Excel でタスクを実行するマクロを作成するということは、Excel オブジェクト モデルを使用する Visual Basic の命令を作成するということです。Excel では、マクロは入力するか、記録することによって作成できます。

マクロの入力を開始するには

  1. Microsoft Office Excel 2003 を起動します。
  2. [ツール] メニューの [マクロ] をポイントし、[マクロ] をクリックします。
  3. マクロの名前を指定します。ヒント: マクロ名には、スペースを含めることはできないので「MyMacro」と入力します。
  4. [作成] をクリックします。

Excel によって、次の図のような Visual Basic Editor が自動的に起動します。

Aa203714.odc_super01(ja-jp,office.11).gif

図 1. Visual Basic Editor の既定のビュー

既定では、Visual Basic Editor には次の 3 つのウィンドウが表示されます。

  • コード ウィンドウ このウィンドウには、"Book1 - Module1 (コード)" というラベルが付いています。ここでコードを入力します。
  • プロジェクト エクスプローラ ウィンドウ このウィンドウには "プロジェクト - VBAProject" というラベルが付いています。このウィンドウには、使用できる Excel オブジェクトおよびモジュールがすべて表示されます。Excel オブジェクトが何かはまだわからなくても心配する必要はありません。後ほど説明します。このウィンドウで、任意の数の VBA ファイルを簡単に表示および管理できます。
  • プロパティ ウィンドウ このウィンドウには "プロパティ - Module1" というラベルが付いています。このウィンドウには、選択されている項目のプロパティの現在のセットが表示されます。現在、選択されている項目は Module1 です。

これらの 3 つのウィンドウを使ってみましょう。

  • "Module1" というのは、無味乾燥な名前ですね。この名前を変更してみましょう。プロパティ ウィンドウで、"(オブジェクト名) Module" という領域に移動します。テキスト "Module1" をダブルクリックし、「MyFirstModule」と入力します。プロジェクト ウィンドウ内の名前とモジュール ウィンドウのタイトル バーの名前が変わります。
  • プロジェクト エクスプローラ ウィンドウで、[Microsoft Excel Objects] の横にあるプラス記号 (+) をクリックし、ノードを展開し、その内部を表示します。現在、4 つのオブジェクトがあります。Sheet1、Sheet2、Sheet3、および ThisWorkbook です。大部分のアクションは ThisWorkbook オブジェクトから実行されます。

"MyFirstModule" という名前に変更したモジュールのコード ウィンドウを表示します。ヒント: このコード ウィンドウを開くには、プロジェクト エクスプローラ ウィンドウで "MyFirstModule" という名前をダブルクリックします。

マクロを完成するには

  1. MyMacro ルーチンにコードを入力します。

  2. コード ウィンドウで、次のように入力します。

    このコードがどのように機能するのか、なぜ機能するのかはわからなくてもかまいません。それは、後のレッスンで学習します。まずは、実行してみましょう。

  3. 最初に Visual Basic Editor を閉じます。閉じるには、[ファイル] メニューの [終了して Microsoft Excel に戻る] をクリックします。

  4. Excel で、[ツール] メニューの [マクロ] をポイントし、[マクロ] をクリックします。

  5. [マクロ] ダイアログ ボックスが表示されます。MyMacro が既に選択されています。[実行] をクリックします。

セル B に「私の新しいシート」と入力されている新しいワークシートが自動的に作成された場合、コードは正常に実行されました。これで、あなたは正式な Excel オブジェクト モデル プログラマです。

**注: **エラー ダイアログが表示されても心配しないでください。ボタンをクリックします。コードが前に示したものと完全に一致していることを確認してから、手順の 2 ~ 4 を繰り返します。

マクロを作成する方法には、Visual Basic Editor にコードを手動で入力する方法以外に、マクロ レコーダを使用する方法があります。レコーダを使用すると、Excel によってメニュー コマンド、キーボード操作、およびタスクの完了に必要なその他のアクションが記録され、マクロが作成されます。

マクロを記録するプロセスは、3 つのステップで構成されています。最初に、マクロ レコーダを起動し、マクロに名前を付けます。次に、記録するアクションを実行します。最後に、レコーダを停止します。

マクロの記録を開始するには

このプロセスを示すために、ワークシートに会社名と住所を挿入する簡単なマクロを作成してみましょう。最初にすべてのブックを保存して閉じ、新しいブックを開きます。

  1. [ツール] メニューの [マクロ] をポイントし、[新しいマクロの記録] をクリックします。[マクロの記録] ダイアログ ボックスが表示されます。

    Aa203714.odc_super02(ja-jp,office.11).gif

    図 2. [マクロの記録] ダイアログ ボックス

  2. [マクロ名] ダイアログ ボックスに、「CompanyAddress」などのマクロの名前を入力します。

  3. [ショートカット キー] ボックスに、マクロを実行するための組み合わせキーを入力します。たとえば、「A」と入力します。

  4. [マクロの保存先] ボックスで、[作業中のブック] を選択します。

  5. [説明] ボックスに、マクロの説明を入力します。たとえば、「会社の住所の入力」と入力します。

  6. 記録を開始するには、[OK] をクリックします。ステータス バーに [マクロの記録中] というメッセージが表示され、[記録終了] ツールバーが表示されます。

    Aa203714.odc_super03(ja-jp,office.11).gif

    図 3. [記録終了] ツールバー

  7. A1 をクリックし、「Northwind Traders」と入力します。A2 に「1234 West Anywhere Place」と入力します。A3 に「Redmond, WA 98052」と入力します。

  8. [記録終了] ボタンをクリックします。

  9. 新しいマクロをテストするには、ワークシートをクリアして Ctrl キーを押しながら、Shift キーと A キーを押します。マクロが実行され、記録した手順が実行されてワークシートにデータが入力されます。

セキュリティについて

Excel では、起動したときにマクロがあるかどうかがチェックされます。そのため、Excel を起動すると次のようなダイアログ ボックスが表示されることがあります。

Aa203714.odc_super04(ja-jp,office.11).gif

図 4. [マクロ セキュリティ] ダイアログ ボックス

このダイアログ ボックスが表示されたら、[マクロを有効にする] ボタンをクリックします。これによって、作成したマクロを実行できます。トレーニング用以外のブックにはお勧めできませんが、マクロのセキュリティ レベルを低くすることによってこのダイアログ ボックスを表示しないようにすることもできます。そのためには [ツール] メニューの [マクロ] をポイントし、[セキュリティ] をクリックします。次に [中] (または [低]) セキュリティ オプションを選択し、[OK] をクリックします。

重要: マクロ セキュリティの設定を低くすると、安全でないマクロから保護されなくなります。この設定は、ウィルス検索プログラムがインストールされているか、開くドキュメントすべての安全性を確認した場合にのみ使用してください。

実践問題 #1 (回答については、付録 A を参照)

Visual Basic Editor で MyMacro マクロを編集し、セル A2 に自分の名前が表示されるようにコードを変更します。

レッスン 2: プログラミング概念: Sub. . . End Sub およびプロシージャ

コードの作成を体験したので、もう一歩踏み込んでみましょう。作成したコードを見てみましょう。

次のパターンに従って、キーワード Sub...End Sub を使用してマクロを開始し、終了しています。

AnyNameHere は、マクロまたはプロシージャの名前です。"プロシージャ" とは、自分が作成する、何かを実行するコード ステートメントの小さなセットです。MyMacro は、プロシージャの例です。ただし、プロシージャはマクロであるとは限りません。プロシージャを作成し、それを別のプロシージャから呼び出すことができます。たとえば、サンプル Excel スプレッドシートで、MyOtherMacro という 2 番目のマクロを作成し、次のように追加します。

MyOtherMacro を実行すると、プロシージャ MyMacro が実行され、追加のワークシートが作成されます。

なぜ、このようにする必要があるのでしょうか。別のプロシージャを作成すると、コードを整然と編成でき、共通するプロシージャを簡単に実行できるようになります。たとえば、3 つの異なるワークシートを作成および表示する必要があるとします。これは、MyOtherMacro のコードを次のように変えることによって実行できます。

実践問題 #2

MyOtherMacro のコードに 1 行追加して、別のワークシートを追加し、メッセージ "これは簡単です" をセル C1 に挿入します。

レッスン 3: プログラミング概念: オブジェクト、メソッド、およびプロパティ

これまでに、"オブジェクト指向プログラミング" について耳にしたことがあるかもしれません。オブジェクト指向プログラミングは、C++ や C# などの言語の基礎となる重要な概念です。おそらく気が付いていないと思いますが、最初の 2 つのレッスンを終了しただけで、自分自身をオブジェクト指向プログラマと呼ぶことができます。

そうです。Excel オブジェクト モデルは、オブジェクト指向プログラミングを使用しています。幸運なことに、Excel オブジェクト モデルを使用および理解するために、オブジェクト指向プログラミングの講座を受講する必要はありません。Excel オブジェクト モデルの実用的な知識を習得するために必要なのは、次の 3 つの概念を理解することだけです。

概念 説明
オブジェクト "もの" ワークシート
メソッド "もの" が実行できること "もの" の追加
プロパティ "もの" の特性 名前

日常のものをオブジェクト、メソッド、およびプロパティに分類できます。たとえば、自動車をオブジェクトと考えることができます。Car (自動車) オブジェクトには、メソッド、つまりそれが実行できるさまざまなことがあります。たとえば、走行、発進、左折、および右折などです。Car オブジェクトには、その特性を示すプロパティもあります。色が赤で、ヘッドライトが 2 つなどです。

Aa203714.odc_super05(ja-jp,office.11).gif

図 5. 自動車

既に作成したコードを見て、オブジェクト、メソッド、およびプロパティを特定してみましょう。

このコードには、3 つの "オブジェクト" があります。最初は、wrkSheet で、Worksheet オブジェクトを表します。2 番目は、ActiveWorkbook オブジェクトで、アクティブなブックを表します。3 番目は、Range オブジェクトで、このワークシートのセルの範囲を表します。これらを "もの" と見なすのは簡単です。Excel オブジェクト モデルは、Excel のプログラミングに使用できるオブジェクトの一覧です。

最初にオブジェクトを使用するときは常に、Set キーワードで開始します。オブジェクトはコンピュータのメモリを占有します。Set キーワードは、オブジェクトに必要なメモリを割り当てます。

オブジェクトには、任意の名前を付けることができます。上記の例では、Worksheet オブジェクトに wrkSheet という名前を付けましたが、これはお好きな名前に変更できます。Excel によって ThisWorkbook というオブジェクトが作成されました。このオブジェクトは、プロジェクト ウィンドウの [Microsoft Excel Objects] の下に表示されます。

MyMacro マクロには、Add という 1 つの "メソッド" が含まれています。メソッドは、常にオブジェクトに関連付けられています。この場合、Add メソッドは wrkSheet オブジェクトと関連付けられています。メソッドを使用するには、オブジェクトとメソッドの間にピリオドを配置します。たとえば、Worksheets.Add のようにします。前に説明したように、メソッドは、"ものが実行できること" です。作成した例では、新しいワークシートが ActiveWorkbook オブジェクトに追加されます。

MyFirstMacro コードには、Value という 1 つの "プロパティ" が含まれています。メソッドと同様に、プロパティは常にオブジェクトと関連付けられています。この場合、Value は Range オブジェクトと関連付けられています。プロパティを使用するには、オブジェクトとプロパティの間にピリオドを配置します。たとえば、Range.RowHeight のようにします。

繰り返しますが、プロパティを "ものの特性" と考えてください。たとえば、ColumnWidth は、範囲項目の特性です。もう 1 つ例をあげましょう。Name も Range オブジェクトの特性です。

最後に知っておく必要があることは、すべてのオブジェクトは、特定の種類のオブジェクトであるということです。それぞれの種類のオブジェクトには、独自のセットのメソッドとプロパティがあります。前の例では、wrkSheet は Worksheet オブジェクトです。Worksheets には、Visible などのメソッドと Count などのプロパティがあります。これらは、その他の種類のオブジェクトにはない場合があります。さらに、その他の種類のオブジェクトには、Worksheet オブジェクトにないメソッドやプロパティがある場合があります。たとえば、次のような命令があるとします。

これは、機能しません。Worksheet という種類のオブジェクトには、Value というプロパティがないためです。

実践問題 #3

次の架空の例を見て、どの部分がオブジェクト、メソッド、およびプロパティなのかを判別しましょう。ヒント: 3 つずつあります。

レッスン 4: 実際の例 #1

上司は、チーム メンバそれぞれに毎日、タイム シートを配布したいと考えています。そのため、上司から、従業員に電子的に送信できる簡単な Excel テンプレートを作成するプログラムを作成してほしいと依頼されました。

既に習得した知識を使用して、すぐに取り掛かり、マクロを作成しましょう。

TimeSheet マクロを作成するには

  1. TimeSheet というモジュールを作成します。そのためには、Excel で、[ツール] メニューの [マクロ] をポイントし、[マクロ] をクリックします。「TimeSheet」と入力し、[作成] をクリックします。Visual Basic Editor が起動します。新しい TimeSheet マクロに入力できるようになっています。
  2. プロパティ ウィンドウで、[(オブジェクト名)] ボックスをダブルクリックし、「TimeSheet」と入力します。
  3. コード ウィンドウで、次のコードを入力します。
  4. [保存] をクリックし、サンプル ファイルの名前 (Sample2 など) を入力し、[保存] をクリックします。これによって Excel ファイルが保存されます。
  5. マクロを実行し、動作を確認します。このマクロによって、電子メールで各従業員に送信できるタイム シート テンプレートが作成されます。
  6. 以上で完成しました。また、この作業のために追加のオブジェクトやコマンドは使用しませんでした。前の例で説明した概念を発展させただけです。

イベントを使用する

これで、各従業員のタイム シートを印刷する基礎として使用できるタイム シート テンプレートを作成するマクロを作成できました。ただし、このままではマクロを機能させるには実際にそれを実行する必要があります。たとえば、朝、仕事を始めるときに最初に行うことが Excel を起動することだとします。そして、仕事を終了するときに最後に行うことが Excel を終了することだとします。この場合、Excel を起動するたびに、マクロが自動的に実行されるようにすると便利だと思いませんか。そのようにすると、毎朝、何もしなくても毎日のタイム シート テンプレートを作成できるようになります。

これは、"イベント" オブジェクトを使用することによって可能になります。イベントは、オブジェクトに何かが行われたときに発生します。たとえば、フォーム上のボタンをクリックすると、Click イベントがボタン オブジェクトに発生します。Excel を起動すると、Auto_Open イベントが発生します。

さらに詳しく説明するために、自動車の例えをもう一度考えてみましょう。イグニッションにキーを差し込んだときに、Car オブジェクトが何かを実行するようにしたいと考えています。この場合、DriverPutKeyInIgnition のような名前の Car イベントを使用します。そのイベントが呼び出されると、自動車のエンジンを始動し、発進できるようにしたいとします。もちろん、自動車が応答するイベントはいくつかあります。これらのイベントには、イグニッションのキーを回す、ギアをチェンジする、アクセルを踏む、ブレーキを踏むなどがあります。自動車は、どのイベントが起動されたかによって異なる応答をします。

Excel を起動するたびにマクロが実行されるようにするには

  1. Excel で Visual Basic Editor を起動します (既に開いていない場合)。ヒント: Alt キーを押しながら F11 キーを押しても起動できます。

  2. プロジェクト エクスプローラ ウィンドウで [Microsoft Excel Objects] 項目のプラス記号 (+) をクリックします。項目の中に、"ThisWorkbook" というラベルが付いた項目が表示されます。

  3. "ThisWorkbook" をダブルクリックします。"ThisWorkbook" というタイトルが付いたコード ウィンドウが表示されます。

  4. コード ウィンドウの上部に 2 つのドロップダウン リストがあります。最初のリストは、オブジェクト リストと呼ばれ、ThisWorkbook と関連付けられたオブジェクトが表示されます。オブジェクト リストで、[Workbook] をクリックします。

    Visual Basic Editor によって Sub Workbook_Open プロシージャが自動的に作成されます。これは、Excel を起動したときに自動的に実行される Open イベント コンテナです。

  5. マクロを呼び出すには、プロシージャ内にマクロ名を入力します。次に例を示します。

以上で完成しました。マクロを保存し、Visual Basic Editor を閉じ、Excel を再起動し、サンプル ファイルを開いて機能しているかどうかを確認します。

実践問題 #4

週単位のタイム シートのテンプレートを含むマクロを使用し、Excel を終了するたびにそのマクロが呼び出されるようにします (1 日の終わりにのみ Excel を終了すると想定します)。

レッスン 5: 独自のダイアログ ボックスを作成する

次に、VBA の最も洗練されている部分の 1 つであるフォームについて説明します。フォームは、ダイアログ ボックスに似たオブジェクトで、VBA で作成および設計できます。コードを 1 行も作成することなく、フォームにチェック ボックス、テキスト、画像、およびその他の機能を追加できます。

フォームはなぜ必要なのでしょうか。それは、マクロとユーザーとの間で情報のやり取りが必要になる場合があるためです。たとえば、マクロの実行中に進行状況ダイアログ ボックスを表示したり、マクロを実行する前にユーザーが特定のオプションを指定できるようにすることができます。

例を示すために、レッスン 4 で作成した TimeSheet マクロについてもう1度考えてみましょう。TimeSheet マクロによって、サンプル ファイルを開くたびにタイム シート テンプレートが作成されます。しかし、Excel を 1 日のさまざまな時刻に起動する場合はどうなるのでしょうか。また、週末に出勤して Excel を起動した場合はどうなるのでしょうか。1 日に 5 回もタイム シート テンプレートを作成したくはありません。解決策は、Excel を起動するたびにテンプレートを作成するオプションを提示することです。

ダイアログ ボックスを作成するには

  1. Excel で、[ツール] メニューの [マクロ] をポイントし、[マクロ] をクリックします。新しいマクロを作成します。
  2. 「TimeSheetPrompt」という名前を入力し、[作成] をクリックします。これによって Visual Basic Editor が起動します。
  3. [挿入] メニューの [ユーザー フォーム] をクリックします。

Aa203714.odc_super06(ja-jp,office.11).gif

図 6. 空のユーザー フォーム

フォームが、コントロールのツールボックスと共に表示されます。フォームを使用して、ユーザーに表示して対話するためのダイアログ ボックスを設計します。

  1. フォームをクリックして選択します。プロパティ ウィンドウを見ます。[(オブジェクト名)] プロパティを "PromptDialog" に変更します。Enter キーを押します。

  2. Caption プロパティを "社員のタイムシートの作成" に変更します。Enter キーを押します。フォームのタイトルが入力したテキストに変わります。

  3. ツールボックスのさまざまなユーザー コントロールにマウスを移動すると、コントロールの説明がヒントとして表示されます。

    続行する前に、TimeSheetPrompt マクロに必要なものを考えてみましょう。タイム シートを作成するかどうかをユーザーが選択できるようにする必要があります。これを簡単に行うには、説明の文字列を追加し、"タイム シートを作成する" と "タイム シートを作成しない" という 2 つのボタンを作成します。

  4. ツールボックスで、[ラベル] (A と表示されているコンポーネント) をクリックします。

  5. [PromptDialog] ウィンドウで、マウスをクリックしてドラッグし、説明の文字列を表示する領域を選択します。ヒント: これは、作成後いつでも移動およびサイズ変更できるので、厳密に指定する必要はありません。

    注: ここで作成したラベルはオブジェクトです。これには、ワークシートやボタンと同様にメソッドとプロパティがあります。

  6. [プロパティ] ウィンドウで、ラベルに使用できるプロパティの一覧を確認します。最初に表示されるのは、[(オブジェクト名)]、[Accelerator]、[AutoSize] です。

  7. [Caption] ボックスに、Caption プロパティの新しい値 (たとえば、"タイム シートを作成しますか?" ) を入力し、Enter キーを押します。モジュール ウィンドウのラベルが、入力した文字列に更新されます。

    ここで、2 つのボタンを作成する必要があります。

  8. フォームをクリックし、ツールボックスを再度表示します。

  9. [コマンド ボタン] ボタン (単純な長方形) をクリックします。

  10. [社員のタイムシートの作成] ウィンドウで、マウスをクリックしてドラッグし、説明の文字列を表示する領域を選択します。

  11. 2 番目のボタンを作成するには、ツールボックスの [コマンド ボタン] ボタンをクリックし、フォーム上でドラッグしてもう 1 つのボタンの領域を指定します。

  12. 最初のボタンをクリックし、[(オブジェクト名)] プロパティを "Yes" に変更します。[Caption] プロパティを "タイム シートを作成する" に設定します。

  13. 次のボタンをクリックし、プロパティを設定します。[(オブジェクト名)] プロパティを "No" に変更します。[Caption] プロパティを "タイム シートを作成しない" に設定します。

Aa203714.odc_super07(ja-jp,office.11).gif

図 7. 完成したダイアログ ボックス

ダイアログ ボックスを機能させる

これでダイアログ ボックスが完成しました。しかし、このままでは機能しません。機能させるには、4 行のコードを追加する必要があります。注意しなければならないのは、これらの行を 3 か所に挿入する必要があることです。これは、次のように行います。

最初に必要なのは、ユーザーが Excel を起動したときにダイアログ ボックスを表示することです。ThisWorkbook のプロジェクト ウィンドウの [Microsoft Excel Objects] の下の [ThisWorkbook] をダブルクリックします。

Workbook_Open イベントに移動し、"PromptDialog.Show" という行を入力します。コードは次のようになります。

注: PromptDialog はオブジェクトです。Show はそのオブジェクトのメソッドです。

ThisWorkbook コード ウィンドウを閉じます。PromptDialog フォームを開きます。このためには、プロジェクト エクスプローラ ウィンドウの [フォーム] の下の [PromptDialog] をダブルクリックします。

ここで、ユーザーが押したボタンに応じて、マクロが特定のタスクを実行するようにする必要があります。[タイム シートを作成する] ボタンをダブルクリックします。

PromptDialog マクロのコード ウィンドウが表示されます。ユーザーが [タイム シートを作成する] ボタンを押したときに呼び出されるイベントが VBA によって作成されます。ユーザーがこのボタンを押したときに、マクロが 2 つのことを実行するようにします。タイム シートを作成することと、ダイアログ ボックスを非表示にすることです。ここではレッスン 4 の TimeSheet マクロを既に作成してあると想定しています。コードは次のようになっています。

注: PromptDialog は、プロパティ ウィンドウでダイアログ ボックス オブジェクトに指定した名前です。Hide は、そのオブジェクトのメソッドです。

コード ウィンドウを閉じます。次に [タイム シートを作成しない] ボタンをダブルクリックします。そのプロシージャでマクロが実行することは、ダイアログ ボックスを非表示にすることだけです。次に例を示します。

以上で完成しました。変更内容を保存し、Excel を再起動し、サンプル ファイルを開いて、マクロを再実行し、ダイアログ ボックスの動作を確認します。

レッスン 6: プログラミング概念: コレクション

ここまでに、オブジェクト、プロパティ、メソッド、およびイベントについて学習しました。Excel オブジェクト モデルを使用するために理解する必要がある "もの" の最後のカテゴリは、 コレクションです。

"コレクション" は、特別な種類のオブジェクトで、その他のオブジェクトのグループであるオブジェクトです。たとえば、Car がオブジェクトだとすると、Cars はコレクションです。自動車の集合です。

コレクションが、別のオブジェクトのプロパティであることもあります。自動車の例を続けると、Doors は Car オブジェクトのプロパティにも、Door オブジェクトのコレクションにもなります。したがって、この関係を以下のように理解することができます。

Cars コレクションは、Car オブジェクトのコレクションです。各 Car オブジェクトには、Doors というプロパティがあります。Doors コレクションは、Door オブジェクトのコレクションです。

Excel でのコレクション

Excel オブジェクト モデルには、多数の種類のコレクションが含まれています。このガイドの最初にその例があります。

Worksheets は Worksheet オブジェクトのコレクションです。Worksheets は、ActiveWorkbook オブジェクトのプロパティでもあります。コレクションの有用性は簡単にわかります。1 つのブックには、任意の数のワークシートを含めることができます。Worksheets コレクションによって、これらのシートを簡単に管理できます。

Excel のその他のコレクション オブジェクトには、次のものがあります。

Sheets 親ブックのすべてのシートのコレクション。

Workbooks すべての開いているブックのコレクション。

Charts 1 つのブックのグラフ シートのコレクション。

コレクションのメソッドとプロパティ

すべてのコレクションに、コレクション内の個別のオブジェクトへのアクセスを可能にするメソッドとプロパティがあります。ここでは、最も重要な 3 つのメソッドとプロパティについて説明します。

Count プロパティ このプロパティは、1 つのコレクションに含まれる個別のオブジェクトの数を示します。次に例を示します。

この例では、numberOfWorksheets は変数です。"変数" は、情報を記録して後で使用するために定義するものです。この例では、numberOfWorksheets 変数は、ActiveWorkbook オブジェクト内の Worksheet オブジェクトの数に等しくなります。

変数を使用する前に、Dim キーワードを使用して定義する必要があります。変数には、任意の名前を指定できます。次に例を示します。

注: コレクションは空である場合もあります。その場合、Count プロパティはゼロになります。

Item メソッド このメソッドは、コレクション内の特定のオブジェクトにアクセスします。次に例を示します。

かっこ内の数値は、どのワークシートにコードがアクセスするのかを示します。この例では、Worksheets コレクションの 2 番目の Worksheet オブジェクトが変数 myWorksheet に割り当てられます。

また、かっこ内に変数を使用することもできます。次に例を示します。

ここでは、最初に変数 numberOfWorksheets を Worksheets コレクションの Worksheet オブジェクトの数に一致するように設定します。次に、最後のワークシートにアクセスします。つまり、5 つのワークシートがある場合、数値 5 を使用して最後のシートを指定できます。前の行は、次の行と同等になります。

Add メソッド このメソッドで、オブジェクトをコレクションに追加できます。次に例を示します。

Add メソッドの使い方は、アイテムの追加先のコレクションによって異なります。多くの場合、前の例と同様に、Sheet6 などのように新しいオブジェクトの名前を指定する必要があります。

変数について、もう 1 つ注意することがあります。変数は情報を記録するので、その値が変わるのは変更したときだけです。例えとして、変数をオーディオ テープと考えてください。

MyCurrentAge というオーディオ テープを作成するとします。このオーディオ テープに、現在の年齢を記録します。5 年後にそのオーディオ テープを聞いたとします。オーディオ テープを再生すると何が聞こえるでしょうか。再生されるのは、現在の年齢ではなく 5 年前の年齢です。これは重要な概念で、これを使用して、「実践問題 #5」に正しく答えることができます。

実践問題 #5

次の架空の Visual Basic コードを見て、その後の質問に答えてください。最初は、Papers コレクションは空で、その Count プロパティはゼロだとします。ヒント: コードを 1 行ずつ確認し、numberOfPapers 変数と Papers コレクションのオブジェクトを追跡します。

  1. 変数 numberOfPapers の値は何ですか。
  2. paper3 の値は何ですか。
  3. paper4 の値は何ですか。
  4. paper5 の値は何ですか。
  5. paper6 の値は何ですか。

レッスン 7: プログラミング概念: If. . . Then

ものの状態に応じてどのコードを実行するのかを指定する必要がある場合があります。たとえば、TimeSheet マクロでは、毎日タイム シートを作成することに意味がありますが、それは月曜日から金曜日に限ります。また、特定の人々または特定の数の人々に対してだけ実行されるマクロを作成する場合もあります。

If...Then 制御ステートメントを使用してさまざまな条件に応じたプロシージャを作成できます。If...Then 制御ステートメントは、コードの流れを導く多数の Visual Basic ツールの 1 つです。If...Then 制御ステートメントの形式は次のとおりです。

上記のコードでは、<expression> は True または False になるものを表します。<code here> は、<expression> が True であると判別された場合に実行されるコードを表します。

次の表は、<expression> の例です。

説明
Papers.Count = 3 Papers コレクションに Paper オブジェクトが 3 つある場合、True に設定されます。
numEmployees > 0 numEmployees 変数がゼロより大きい場合、True に設定されます。
numEmployees <> 5 numEmployees 変数が 5 でない場合、True に設定されます。

次の 2 つの例は、If...Then 制御ステートメントの使用法を示しています。

この例では、最後の従業員のタイム シート上の名前またはこのブックにタイム シートがないことをユーザーに示すダイアログ ボックスが表示されます。

注: MsgBox は、簡単なメッセージの表示に使用される Visual Basic ステートメントです。このステートメントを使用して、1 つの文字列または "&" 記号を使用して結合した任意の数の文字列を表示できます。前の例では、タイム シートの最後の従業員の名前が "寺山 裕子" の場合、ユーザーには次のように表示されます。

Aa203714.odc_super08(ja-jp,office.11).gif

図 8. 情報メッセージ ボックス

If. . .Then の例 2: このオプションが選択された場合. . .

次のダイアログ ボックスについて考えてみます。

Aa203714.odc_super09(ja-jp,office.11).gif

図 9. タイム シートを保存するオプションがあるダイアログ ボックス

ユーザーが [OK] ボタンをクリックすると、ユーザーが [印刷せずに、下書きフォルダに保存する] というチェック ボックスをオンにしたかどうかに応じて、異なるコードが実行されます。オフになっている場合、タイム シートが印刷されます。オンになっている場合、ユーザーがそれを後で完成して印刷できるように保存されます。

これがどのように機能するのかを理解するために、最初にチェック ボックスが、レッスン 5 で作成したボタンやラベルと同様にフォーム上に描画できるオブジェクトであることを理解する必要があります。これはオブジェクトなので、プロパティがあります。これらのプロパティの 1 つは "Value" と呼ばれます。Value プロパティは、True (オン) または False (オフ) のいずれかになります。

このコードは、ユーザーが [OK] ボタンをクリックしたときに実行する必要があります。そのため、次のコードを [OK] ボタンの Click イベントに追加します。この例では、フォームの名前は PromptDialog、ボタンの名前は OK、チェック ボックスの名前は justSaveDraft です。

ユーザーがチェック ボックスをオンにした場合、Excel によってタイム シートがユーザーの既定のファイルの場所に保存されます。ユーザーがチェック ボックスをオフにした場合、メッセージが用紙に印刷されます。

実践問題 #6

レッスン 5 で作成した TimeSheet マクロに追加します。ダイアログ ボックスに、ユーザーがタイム シートを印刷する前に編集できるようにするチェック ボックスを追加します。チェック ボックスには、"印刷前にタイム シートを編集する" などのキャプションを含めます。これをオンにした場合、マクロによってタイム シートは印刷されませんが、作成されてユーザーに表示されます。

レッスン 8: 実際の例 #2

たとえば、自分が人事部のアシスタントだとします。仕事の 1 つに、会社のさまざまなグループのためにタイム シートを印刷することがあります。現在、タイム シートを頻繁に印刷しなければならないグループが 2 つあります。どちらのグループのタイム シートを印刷するのかは場合によって異なります。

このタスクを簡単にするにはどのようにしたらよいでしょうか。Excel オブジェクト モデルを使用して、タイム シートを印刷するグループを指定できるダイアログ ボックスを作成します。

手順 1: ダイアログ ボックスを作成する

最初に、タイム シートを印刷するグループ、および部署ごとのタイム シートの部数を指定するのに使用できるダイアログ ボックスを作成する必要があります。

  1. Visual Basic Editor で、[挿入] メニューの [ユーザー フォーム] をクリックします。

  2. フォームが次のようになるように 1 つのラベル、2 つのチェック ボックス、および 1 つのボタンを追加します。

    Aa203714.odc_super10(ja-jp,office.11).gif

    図 10. 2 つのオプションがあるダイアログ ボックス

    Caption プロパティは、チェック ボックスでユーザーに表示されるテキストを設定します。

  3. チェック ボックス、テキスト ボックス、ボタン、およびフォーム自体に、以下のような Name プロパティの説明を指定します。

    コントロール 名前
    フォーム PrintTimesheets
    [OK] ボタン cmdOK
    営業部の部数カウンタ Sales
    マーケティング部の部数カウンタ Mrktng
  4. 追加アイテム : 各チェック ボックスおよびテキスト ボックスに、ControlTipText プロパティを指定してコントロールの説明を含めます。たとえば、テキスト ボックスの場合、"部数を入力します" となります。

手順 2: 機能させる

ダイアログ ボックスを機能させるには、最初に、ダイアログ ボックスに追加した [OK] ボタンをユーザーがクリックしたときに発生するイベントのコードを作成する必要があります。

Visual Basic Editor のフォームで [OK] ボタンをダブルクリックします。次のコードを cmdOK_Click プロシージャに追加します。

このイベント プロシージャでは、最初に新しいワークシートを既存のブックに追加します。次に、ダイアログ ボックスの SalesCnt テキスト ボックスに値が含まれているかどうかを調べるためにテストします。含まれている場合、マクロがセル B1 に部門の名前を表示します。次に AddFields サブルーチンを呼び出します。これについては後で説明します。

その後、次の行を Private Sub cmdOK_Click() ステートメントの上に追加します。

このステートメントによって、cmdOK_Click イベント プロシージャで使用するワークシート用のメモリ記憶域が割り当てられます。ワークシート用のメモリを指定しないと、コード内でそれを適切に使用できなくなります。

手順 3: コードを完成する

最後に、次のサブルーチンを cmdOK_Click イベント プロシージャの End Sub ステートメントの後に追加します。このプロシージャは、タイム シートを構成する次のフィールドを書き出します。

さらに詳しく学習するには

Excel オブジェクト モデルに関する知識を習得したので、これでカスタム Excel ソリューションを作成できます。もちろん、このガイドでは、Excel オブジェクト モデルを使用して実行できる効果的な作業の一部を述べたにすぎません。オンライン ヘルプを使用して、自由に使える多数のコレクション、オブジェクト、メソッド、およびイベントを探索してください。

Microsoft Visual Basic Editor で Visual Basic for Applications のオンライン ヘルプを使用するには [表示] メニューの [オブジェクト ブラウザ] をクリックします。

Aa203714.odc_super11(ja-jp,office.11).gif

図 11. オブジェクト ブラウザ

Excel 固有のツールを表示するには、プロジェクト/ライブラリ ドロップダウン リストの [Excel] をクリックします。これによって、Excel に該当するオブジェクト、コレクション、メソッド、およびプロパティの一覧が表示されます。たとえば、検索テキスト ボックスに「Worksheets」とタイプし、[検索] (双眼鏡のアイコン) をクリックします。

検索結果が [検索結果] ウィンドウに表示されます。オブジェクトをクリックすると、ウィンドウ内のオブジェクトおよびそのオブジェクトに適用できるメソッドの一覧が更新されます。下部のウィンドウ ペインのコンテキスト依存のヘルプに、指定したメンバに関する詳細が表示されます。

上部ウィンドウの Worksheets オブジェクトをクリックします。['Worksheets' のメンバ] ウィンドウに表示されるメンバを確認します。各項目の横の記号は、その項目の種類 (メソッドであるのかプロパティであるのかなど) を示しています。

必要な情報はすべてここにあります。下部のウィンドウ ペインのコンテキスト依存のヘルプには、リンクが含まれていて、関連する項目にすばやく移動できます。たとえば、これらのリンクを使用して指定されたオブジェクトの要素やメソッドを識別できます。このウィンドウ ペインのコンテキスト依存のヘルプ ファイルの多くには、学習に役立ち、自分のコードに使用することもできるサンプルも含まれています。

もう Excel オブジェクト モデル プログラマです。これで、Excel オブジェクト モデルを使用して会社の時間と経費を節約できます。

付録 A: 実践問題の回答

実践問題 #1

メッセージの本文を自分の名前に変更します。

実践問題 #2

Visual Basic Editor を使用して MyMacro を編集し、コードを次のように変更します。

実践問題 #3

次の架空のオブジェクト モデル コードを見て、どの部分がオブジェクト、メソッド、およびプロパティであるのかを判別します。3 つずつあります。

オブジェクト メソッド プロパティ
ShoppingMall GetStore OpeningTime
PetStore GetPet
Dog WagTail Color
Breed

実践問題 #4

週単位のタイム シートのテンプレートを含むマクロを作成するには、次のようなコードを追加します。

Sub TimeSheet()

Dim wrkSheet As Worksheet

Set wrkSheet = ActiveWorkbook.Worksheets.Add

Range("A1").Value = "部門"

Range("B2").Value = "従業員名"

Range("C3").Value = "月曜日"

Range("D3").Value = "火曜日"

Range("E3").Value = "水曜日"

Range("F3").Value = "木曜日"

Range("G3").Value = "金曜日"

Range("B4").Value = "勤務時間"

Range("B5").Value = "病欠"

Range("B6").Value = "休暇"

Range("B7").Value = "残業時間"

Range("B9").Value = "集計"

End Sub

次に、Excel を終了するたびにこれを実行するために、Worksheet オブジェクトの Deactivate イベント中に TimeSheet マクロを呼び出します。ヒント: このイベントを編集するには、ThisWorkbook モジュール ウィンドウを使用します。

Private Sub Workbook_Deactivate()

TimeSheet

End Sub

実践問題 #5

次の架空の Visual Basic コードを見て、その後の質問に答えてください。最初は、Papers コレクションは空で、その Count プロパティはゼロだとします。ヒント: コードを 1 行ずつ確認し、numberOfPapers 変数と Papers コレクションのオブジェクトを追跡します。

  1. 変数 currentNumber の値は何ですか。

    答え : 2

    説明 : currentNumber の値を設定するとき、MyDesk.Papers コレクションには、paper1paper2 の 2 つの用紙しかありません。

  2. paper3 の値は何ですか。

    答え : The Garble Report

  3. paper4 の名前は何ですか。

    答え : The Mooble Report

    説明 : currentNumber は 2 なので、これにより paper4 はコレクションの 2 番目の用紙 The Mooble Report に設定されます。

  4. paper5 の名前は何ですか。

    答え : The Foofle Report

  5. paper6 の名前は何ですか。

    The Garble Report

    説明 : (currentNumber + 1) は 3 なので、これにより、paper6 はコレクションの 3 番目の用紙 The Garble Report に設定されます。

実践問題 #6

最初に、名前 "EditSheet" を持つチェック ボックスを PromptDialog フォームに追加する必要があります。

Aa203714.odc_super12(ja-jp,office.11).gif

図 12. 完成したダイアログ ボックス

次に、タイム シートを作成および印刷しない Yes_Click マクロを修正します。EditSheet チェック ボックスがオンになっているかどうかをテストする必要があります。オンになっている場合、タイム シートを作成します。チェック ボックスがオフになっている場合、次のようなタイム シートを作成および印刷します。

作成したタイム シートである最後のワークシートを印刷するためのステートメントを追加しました。最後のシートが確実に印刷されるようにするために、Worksheets コレクションで最後のシートを指定します。

これで、ユーザーがチェック ボックスをオンにした状態で、[タイム シートを作成する] をクリックすると、タイム シートが作成されますが、印刷はされません。