VB実用⑦　エクセル操作Ⅰ

プログラムの概要

事務処理に於いて、Microsoft 社のスプレッドシート ソフトで有るエクセルは、データベースソフトで有る アクセスと共に、業界標準（De Facto Standard）で 有ると謂う事が出来る。 此処では、其のエクセルを、Visual Basic から操作す る方法を、重点的に学ぶ。 今回は、Visual Basic でエクセルを利用する基本と成 るオブジェクト生成と、既存のエクセルのデータが実 際に入力されて居る範囲の取得を行う。 亦、エクセルのフォームを開いた後も、操作フォーム を、常に、手前に表示すると謂う従来は Win32API を利用して居た機能が、Visual Basic 2005 では、プ ロパティの設定丈で簡単に実現する方法も、併せて、 学習する。

VB でエクセル操作Ⅰ

□ オブジェクト変数の宣言（Object 型、As Object） □ オブジェクトのインスタンス生成（CreateObject 関数） □ エクセルのオブジェクト（Excel.Application、WorkBook、WorkSheet） □ ワークブックのオープン（Workbooks コレクション、Open メソッド） □ ワークシートの指定（Worksheet コレクション、シート名） □ ワークシートのプロパティ（UsedRange プロパティ） 今回の課題項目 □ オブジェクトの参照設定（事前バインディング、実行時バインディング） □ フォームを最前面に表示（TopMost プロパティ）

□ オブジェクト変数の宣言（Object 型、As Object） □ オブジェクトのインスタンス生成（CreateObject 関数）

□ エクセル固有の機能（Workbook コレクション、Worksheet コレクション） 今回の重点項目

■ オブジェクト･プロパティ一覧 ■ コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel1 Caption 合唱コンクール審査結果 FormBorderStyle FixedSingle StartPosition CenterScreen ラベル１ name lblRow TextAlign MiddleRight BackColor White Text 空白 Font ＭＳ明朝、太字、１２ ラベル２ Name lblRows Text 行 Font ＭＳ明朝、太字、１２ ラベル３ Name lblColumn TextAlign MiddleRight BackColor White Text 空白 Font ＭＳ明朝、太字、１２ ラベル４ Name lblColumns Text 列 Font ＭＳ明朝、太字、１２ ボタン１ Name btnExtract Font ＭＳ明朝、太字、１２ ボタン２ オブジェクト名 btnFinish Text 終了 Font ＭＳ明朝、太字、１４ ボタン２ ラベル１ ボタン1 ラベル２ ラベル３ ラベル４

■ オブジェクトの参照設定 ■ オブジェクトを使用するには、オブジェクト変数でオブジェクトへの参照を維持する必要が有る。オブ ジェクトの参照には、事前バインディングと実行時バインディングの２種が有る。 事前バインディング（参照設定必要） 特定のクラスの変数と仕て宣言されたオブジェクト変数を使う場合 Dim EX As Excel.Application EX = New Excel.Application 実行時バインディング（参照設定不要） 総称オブジェクト型のクラスの変数と仕て宣言されたオブジェクト変数を使う場合 Dim EX As Object EX = CreateObject( "Excel.Application" ) タイプライブラリで定義されて居るオブジェクトへの参照を作成するには、下記の様にする。 １．メニューバーより『プロジェクト』→『参照の追加』と進む。 ２．『参照の追加』ダイアログボックスで、使用するライブラリファイルを選択する。 参照設定が不可欠では無い実行時バインディングの場合でも、コーディング段階で、オブジェクトで使 用されて居るプロパティやメソッドの構文チェックが行われる自動メンバ表示等のインテリセンス機 能を使用する事が出来る為、参照設定して置く事が多い。 猶、オブジェクト変数を使用してオブジェクトのメソッドにアクセスする速度は、バインディング方法 に依り異なる。通常、事前バインディングの変数を使用するオブジェクトの参照の方が、実行時バイン ディングの変数を使用するオブジェクトの参照よりも、処理が高速に成る。

■ プログラムリスト ■ Public Class excel1

' フォームレベルでグローバルな定数の宣言（使用エクセル名） Private Const EXCEL_BOOK_NAME As String = "data.xls" Privete Const xlA1 As Integer = 1

' フォームレベルでグローバルな変数の宣言 Private EX As Object ' Excel.Application Private WB As Object ' Excel.Workbook Private WS As Object ' Excel.Worksheet Private SD As String ' 格納場所（起動パス） Private FN As String ' エクセルファイル名 ' フォームが読み込まれた時の処理

Private Sub excel1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load

' エクセルファイルの格納場所の設定（起動パス）

SD = Application.StartupPath : If Not SD.EndsWith("¥") Then SD &= "¥" ' エクセルファイル名の設定 FN = SD & EXCEL_BOOK_NAME ' ウィンドウを常に手前に表示 Me.TopMost = True ' ボタンのキャプションの設定

btnExtract.Text = "エクセルの使用範囲を" & vbCrLf & "抽出する" End Sub

' ボタン（終了）がクリックされた時の処理

Private Sub btnFinish_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnFinish.Click ' エクセルの終了 EX.Application.DisplayAlerts = False EX.Application.Quit( ) ' オブジェクトとの関連付けを解除 WS = Nothing WB = Nothing EX = Nothing ' フォームをメモリから消去して終了 Me.Dispose( ) End End Sub 此処で宣言した変数は、同じモジ ュール内の総てのサブプロシージ ャで、値の参照と設定を行う事が 出来る。 固定のファイル名等は、コードの 宣言部分で、定数を用いて宣言し て置くと、保守が容易に成る。 実際には、此処迄丁寧に記述する 必要は無いのだが、使用した総て のフォームをメモリから消去した 上で、プログラムを正しく終了す ると謂う癖を付けて置く事が望ま しい。 使用済みの Excel.Application オ ブジェクトやWorkbook オブジェ クトや Worksheet オブジェクト は、必ず、閉じて、メモリから開 放して置く。 Quit メソッドは、エクセルを終了 する丈で、メモリから削除するに はNothing を設定する。 フォーカスを喪失した時でも、常 にフォームを手前に表示するに は、TopMost プロパティを True に設定する。 ボタンのキャプションに改行を入 れ度い時は、此の様に記述すれば 良い（デザイン時にも出来ない事 はないが、面倒で有る）。

' ボタン（エクセルの使用範囲を抽出する）がクリックされた時の処理

Private Sub btnExtract_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnExtract.Click Dim S As String Dim T As String Dim D As String Dim I As Integer Dim N As Integer Dim R As Integer ' 最終行 Dim C As Integer ' 最終列 ' エクセルオブジェクトの新しいインスタンスの生成 EX = CreateObject( "Excel.Application" ) ' ファイルを指定してワークブックのオープン WB = EX.Workbooks.Open( FN ) ' シート名を指定してオブジェクトの定義 WS = WB.Worksheets( "Sheet1" ) ' エクセルの表示 EX.Visible = True ' シートの主要使用領域の取得 WS.UsedRange.Select( )

S = WS.UsedRange.Address( False, False, xlA1 ) D = S.Substring( S.IndexOf( ":" ) + 1 )

For I = 1 To D.Length

If D.Substring( I - 1, 1 ) < "A" Then S = D.Substring( 0, I – 1 ) T = D.Substring( I – 1 ) Exit For End If Next N = S.Length - 1 For I = 1 To S.Length C += ( Asc( S.Substring( I - 1, 1 ) ) – 64 ) * ( 26 ^ N ) N -= 1 Next R = Val( T )

lblRow.Text = T & "(" & R.ToString & ")" lblColumn.Text = S & "(" & C.ToString & ")" End Sub End Class DisplayAlerts プロパティ マクロの実行中に特定の警告やメッセージの表示を制御するプロパティで有る。マクロを実行して居る間に、ユ ーザーに入力を促すメッセージや警告メッセージを表示させない場合は、此のプロパティをFalse を設定する。 Quit メソッド Quit メソッドを使用すると、開いて居るブックを未だ保存して居ない場合は、変更を保存するか何うかを確認す るダイアログボックスが表示される。メッセージを表示させない場合は、Quit メソッドを実行する前に、総ての ブックを保存するか、又は、DisplayAlerts プロパティを False に設定する。DisplayAlerts プロパティに False が設定されて居ると、確認メッセージは表示されず、変更したブックを保存しないで、Excel を終了する。 Select メソッド セル、又は、セル範囲を選択するには、Select メソッドを使用する。亦、単一のセルをアクティブセルにするに は、Activate メソッドを使用する。 此処で宣言した変数は宣言したサ ブプロシージャ内でしか値の参照 と設定を行う事が出来ない。 CreateObject 関数は、事前バイン ディングと実行時バインディング の孰れでも使用する事が出来る。 指定したファイルで、Workbooks コレクションを開く。 シート名には、操作対象とするシ ートの名称を指定する。 此処では、確認の為、ワークシー トを可視に設定して居る。 Address プロパティでワークシー トの使用範囲をA1 形式で取得す る（例：A1:AD23）。 右下端のセル位置をアルファベッ ト部分（S）と数字部分（T）に分 割する。 アルファベット部分（S）を序数 に変換する。猶、64 は A の１個 前の@の文字コードで、26 はアル ファベットの文字数を表す。

オブジェクト型変数の宣言（１） （再掲） タイプライブラリで定義されたオブジェクトに対して変数を宣言する Dim 変数名 As [New] クラス名 オブジェクトをコードで使用する為に、対象のタイプライブラリに依り提供されるクラス名 を変数宣言の中で指定し、オブジェクトを変数に格納する クラス名は、コンポーネント名.クラス名（例:Exel.Chart）と謂う形式で構成される事が有る。 コンポーネント：オブジェクトを提供するコンポーネントの名前 オブジェクトブラウザの『プロジェクト／ライブラリ』ボックスに表示される。 ク ラ ス：コンポーネントのタイプライブラリを提供するオブジェクトのクラスの名前 オブジェクトブラウザの『クラス』ボックスに表示される。 New キーワードを使用してオブジェクト変数（参照型変数）を宣言すると、宣言時にオブジェクトが作 成され、其の変数に、オブジェクトの参照が代入される為、宣言後に、変数へのオブジェクトの参照を 代入する必要が無い（実際には、クラス名と同名のコンストラクタが呼び出される）。 猶、従来は、New キーワードを用いて宣言した変数を使用すると、アプリケーションの実行速度が遅く 成る可能性が有った。此れは、Visual Basic では、New キーワードを用いて宣言した変数が有ると、其 の度に、オブジェクトの参照が既に其の変数に代入されて居るか何うかが検証した為で有る。

亦、タイプライブラリとは、COM（Component Object Model：プログラムをソフトウェアコンポーネ ントで構成する為のオブジェクト指向技術に基づくモデル）で利用可能な公開されて居るクラスに関す る情報を含むライブラリの事を謂い、プロパティーやメソッドの名前と、其等に付いての標準的な説明 が含まれる。 オブジェクト型変数の宣言（２） タイプライブラリで定義されて居ないオブジェクトに対して変数を宣言する Dim 変数名 As Object 総称オブジェクト型のクラスのオブジェクト変数を宣言する 総称オブジェクト型（Object）の変数は、32 ビット（4 バイト）の変数で、オブジェクトを参照する為 のアドレスを格納する。総称オブジェクト型と仕て宣言した変数には、アプリケーションで認識される 任意の種類のオブジェクトへの参照を、Set ステートメントを使用して、代入する事が出来る。 総称オブジェクト型と仕て宣言した変数には、任意のオブジェクトへの参照を格納する事が出来るが、 変数と参照先のオブジェクトとのバインディングが遅く成る（実行時バインディング）。事前バインデ ィング（コンパイル時バインディング）を実行するには、固有のクラス名を指定して宣言した変数にオ ブジェクトへの参照を代入する。 其の為、オブジェクト変数を宣言する場合、出来る限り、総称オブジェクト型では無く、固有オブジェ クト型（クラス）と仕て宣言する。固有オブジェクト型を指定して宣言すると、アプリケーションを実 行する前に、Visual Basic に依り、オブジェクトのプロパティとメソッドへの参照が解決される。参照 が実行前に解決される事で、アプリケーションの実行時のパフォーマンスが向上する。猶、各固有オブ ジェクト型は、オブジェクトブラウザで調べる事が出来る。 機 能 書 式 解 説 機 能 書 式 解 説

EXCEL の UsedRange プロパティ 使用セル範囲を返すプロパティ Object.UsedRange 指定されたワークシートで使用されて居るセル範囲（Range オブジェクト）を返す。値の取 得而巳が可能で有る。 Object には、オブジェクトへの参照を表すオブジェクト式を指定し、必ず指定する（以下同じ）。 EXCEL の Address プロパティ 参照範囲を取得するプロパティ Object.Address (引数１,引数２,引数３,引数４,引数５) Range オブジェクトを対象にして、範囲参照を返す。値の取得而巳が可能で有る。 引数１には、行部分の参照を絶対参照として返すにはTrue（既定値）を指定する。 引数２には、列部分の参照を絶対参照として返すにはTrue（既定値）を指定する。 引数３には、A1 形式（既定値、定数 xlA1）か R1C1 形式（定数 xlR1C1）かを指定する。 引数４には、外部参照を返すにはTrue、ローカル参照を返すには False（既定値）を指定する。 引数５には、開始点を定義するRange オブジェクトを指定する。 総ての引数は、省略する事が出来る。 引数３には、下記の定数の孰れかを指定する。 定数 値 説明 xlA1 1 列をA から始まるアルファベット、行を 1 から始まる数字で表す形式 xlR1C1 -4150 行をR、列を C で表し、夫々れ 1 から始まる序数で表す形式

範囲（Range オブジェクト等）の取得では、A1 形式では "A1:D11" の様な文字列が、R1C1 形式では "R1C1:R11C4" の様な文字列が返される。 EXCEL の Select メソッド オブジェクトを選択するメソッド Object.Select (引数) セル、又は、セル範囲を選択するには、Select メソッドを使用する。亦、単一のセルをアク ティブセルにするには、Activate メソッドを使用する。 引数には、オブジェクトの選択方法を指定し、省略する事が出来る。シート丈に使用する事が出来、選 択位置を指定したオブジェクト而巳を選択するにはTrue を指定し、既に選択されて居たオブジェクト と指定したオブジェクトを選択するにはFalse を指定する。 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説

EXCEL の DisplayAlerts プロパティ マクロの実行中に特定の警告やメッセージの表示を制御するプロパティ Object.DisplayAlerts = 値 値がTrue（既定値）の場合、マクロの実行中に特定の警告やメッセージを表示する。 値には、True か False のブール値を指定する。 マクロを実行して居る間に、ユーザーに入力を促すメッセージや警告メッセージを表示させない場合は 此のプロパティをFalse を設定する。猶、此のプロパティは、False に設定した儘、マクロの実行を終 了しても、自動的にTrue には戻らない。

Visual Basic から操作する場合、Excel シートに加えた変更を保存せずに終了する時には、此のプロパ ティを False に設定して、『変更を保存しますか？』と謂うダイアログボックスを表示しない様にする 事が多い。 EXCEL の Quit メソッド Excel を終了するメソッド Object.Quit Object で指定した現在開いて居る Excel を終了する。 Quit メソッドを使用すると、開いて居るブックを未だ保存して居ない場合は、変更を保存するか何うか を確認するダイアログボックスが表示される。メッセージを表示させない場合は、Quit メソッドを実行 する前に、総てのブックを保存するか、又は、DisplayAlerts プロパティを False に設定する。 DisplayAlerts プロパティに False が設定されて居ると、確認メッセージは表示されず、変更したブッ クを保存しないで、Excel を終了する。 猶、ブックを保存しなくても、其のブックのSaved プロパティに True を設定すると、確認メッセージ を表示せずにExcel を終了させる事が出来る。 注意：此のメソッドを用いてExcel を正しく終了しない場合、例えば、エラー等が発生し実行が途中で 中断された様な場合には、Excel は、終了しないで、メモリに残存した儘と成る。此れを何度も繰り返 すと、メモリ（リソース）不足と成り、システムがフリーズする事が有る。特に、Excel を非表示に仕 て居る場合は、タスクバーにも表示されない為、注意を要する。此の様な場合には、Ctrl+Alt+Delete で『プログラムの強制終了』ダイアログボックスを開き、Excel が残存して居ないか確認し、残存して 居れば終了する。

参考：Excel を終了するには、上記の Quit メソッドの他に、Close メソッドを使用する方法も有る。両 者の違いは、Quit メソッドが Application オブジェクトを対象に仕て居るのに対し、Close メソッドが Workbooks コレクション、又は、Workbook オブジェクトを対象に仕て居る点で有る。其の為、Quit メソッドは、エクセルを終了し、Close メソッドは、ワークブックを閉じると謂うのが、夫々れのメソ ッドの機能で有ると謂える。 機 能 書 式 解 説 機 能 書 式 解 説

■ エクセル操作で使用するオブジェクトの生成 ■ ■ 事前バインディング（要参照設定） CreateObject 関数を使用したオブジェクト参照の代入 Dim EX As Excel.Application Dim WB As Excel.Workbook Dim WS As Excel.Worksheet EX = CreateObject( "Excel.Application" ) WB = EX.Workbooks.Add WS = WB.Worksheet( 1 ) New キーワードを使用したオブジェクト参照の代入 Dim EX As Excel.Application Dim WB As Excel.Workbook Dim WS As Excel.Worksheet EX = New Excel.Application WB = EX.Workbooks.Add WS = WB.Worksheet( 1 ) ■ 実行時バインディング（参照設定推奨） Dim EX As Object Dim WB As Object Dim WS As Object EX = CreateObject( "Excel.Application" ) WB = EX.Workbooks.Add WS = WB.Worksheet( 1 ) 事前バインディングを使用すると、プロパティの値の設定や取得に必要な時間を、大幅に短縮する事が出 来る。但し、ActiveX コンポーネントが、タイプライブラリを提供して居る場合而巳、事前バインディン グを使用する事が出来る。 事前バインディングでは、変数宣言、又は、Set ステートメントで New キーワードを使用して、新規オブ ジェクトを作成し、オブジェクト変数にオブジェクトの参照を代入する事が出来るが、CreateObject 関数 を使用して、新しくオブジェクトを作成し、オブジェクト変数にオブジェクトの参照を代入する事が推奨 されて居る。猶、CreateObject 関数は事前バインディング、実行時バインディングの孰れのオブジェクト でも、使用可能で有る。 ActiveX コンポーネントがタイプライブラリを提供して居るか何うかに関係無く、Set ステートメントで CreateObject 関数を使用して、新規オブジェクトを作成し、オブジェクト変数にオブジェクトの参照を代 入する事が出来る。