VB でエクセル操作 Ⅰ VB 2005 7 プログラムの概要 事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルは データベースソフトで有るアクセスと共に 業界標準 (De Facto Standard) で有ると謂う事が出来る 此処では 其のエクセルを Visual Basic から操作する方法を 重点的に学ぶ 今回は Visual Basic でエクセルを利用する基本と成るオブジェクト生成と 既存のエクセルのデータが実際に入力されて居る範囲の取得を行う 亦 エクセルのフォームを開いた後も 操作フォームを 常に 手前に表示すると謂う従来は Win32API を利用して居たが Visual Basic 2005 では プロパティの設定丈で簡単に実現する方法も 併せて 学習する 今回の課題項目 オブジェクトの参照設定 ( 事前バインディング 実行時バインディング ) フォームを最前面に表示 (TopMost プロパティ ) オブジェクト変数の宣言 (Object 型 As Object) オブジェクトのインスタンス生成 (CreateObject 関数 ) エクセルのオブジェクト (Excel.Application WorkBook WorkSheet) ワークブックのオープン (Workbooks コレクション Open メソッド ) ワークシートの指定 (Worksheet コレクション シート名 ) ワークシートのプロパティ (UsedRange プロパティ ) 今回の重点項目 オブジェクトの参照設定 ( 事前バインディング 実行時バインディング ) フォームを最前面に表示 (TopMost プロパティ ) オブジェクト変数の宣言 (Object 型 As Object) オブジェクトのインスタンス生成 (CreateObject 関数 ) エクセル固有の (Workbook コレクション Worksheet コレクション ) -1-
オブジェクト プロパティ一覧 ラベル 2 ラベル 1 ラベル 3 ラベル 4 ボタン 1 ボタン 2 コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel1 Caption 合唱コンクール審査結果 FormBorderStyle FixedSingle StartPosition CenterScreen ラベル1 name lblrow Align MiddleRight BackColor White 空白 ラベル2 Name lblrows 行 ラベル3 Name lblcolumn Align MiddleRight BackColor White 空白 ラベル4 Name lblcolumns 列 ボタン1 Name btnextract ボタン2 オブジェクト名 btnfinish 終了 Font MS 明朝 太字 14-2-
オブジェクトの参照設定 オブジェクトを使用するには オブジェクト変数でオブジェクトへの参照を維持する必要が有る オブジェクトの参照には 事前バインディングと実行時バインディングの 2 種が有る 事前バインディング ( 参照設定必要 ) 特定のクラスの変数と仕て宣言されたオブジェクト変数を使う場合 Dim EX As Excel.Application EX = New Excel.Application 実行時バインディング ( 参照設定不要 ) 総称オブジェクト型のクラスの変数と仕て宣言されたオブジェクト変数を使う場合 Dim EX As Object EX = CreateObject( "Excel.Application" ) タイプライブラリで定義されて居るオブジェクトへの参照を作成するには 下記の様にする 1. メニューバーより プロジェクト 参照の追加 と進む 2. 参照の追加 ダイアログボックスで 使用するライブラリファイルを選択する 参照設定が不可欠では無い実行時バインディングの場合でも コーディング段階で オブジェクトで使用されて居るプロパティやメソッドの構文チェックが行われる自動メンバ表示等のインテリセンスを使用する事が出来る為 参照設定して置く事が多い 猶 オブジェクト変数を使用してオブジェクトのメソッドにアクセスする速度は バインディング方法に依り異なる 通常 事前バインディングの変数を使用するオブジェクトの参照の方が 実行時バインディングの変数を使用するオブジェクトの参照よりも 処理が高速に成る -3-
プログラムリスト 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 フォーカスを喪失した時でも 常にフォームを手前に表示するには TopMost プロパティを True に設定する ' ボタンのキャプションの設定 btnextract. = " エクセルの使用範囲を " & 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( ) 使用済みの Excel.Application オブジェクトや Workbook オブジェクトや Worksheet オブジェクトは 必ず 閉じて メモリから開放して置く ' オブジェクトとの関連付けを解除 WS = Nothing WB = Nothing EX = Nothing ' フォームをメモリから消去して終了 Me.Dispose( ) End End Sub Quit メソッドは エクセルを終了する丈で メモリから削除するには Nothing を設定する 実際には 此処迄丁寧に記述する必要は無いのだが 使用した総てのフォームをメモリから消去した上で プログラムを正しく終了すると謂う癖を付けて置く事が望ましい -4-
' ボタン ( エクセルの使用範囲を抽出する ) がクリックされた時の処理 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. = T & "(" & R.ToString & ")" lblcolumn. = S & "(" & C.ToString & ")" End Sub End Class 此処で宣言した変数は宣言したサブプロシージャ内でしか値の参照と設定を行う事が出来ない CreateObject 関数は 事前バインディングと実行時バインディングの孰れでも使用する事が出来る 指定したファイルで Workbooks コレクションを開く シート名には 操作対象とするシートの名称を指定する 此処では 確認の為 ワークシートを可視に設定して居る Address プロパティでワークシートの使用範囲を A1 形式で取得する ( 例 :A1:AD23) 右下端のセル位置をアルファベット部分 (S) と数字部分 (T) に分割する アルファベット部分 (S) を序数に変換する 猶 64 は A の1 個前の @ の文字コードで 26 はアルファベットの文字数を表す DisplayAlerts プロパティマクロの実行中に特定の警告やメッセージの表示を制御するプロパティで有る マクロを実行して居る間に ユーザーに入力を促すメッセージや警告メッセージを表示させない場合は 此のプロパティを False を設定する Quit メソッド Quit メソッドを使用すると 開いて居るブックを未だ保存して居ない場合は 変更を保存するか何うかを確認するダイアログボックスが表示される メッセージを表示させない場合は Quit メソッドを実行する前に 総てのブックを保存するか 又は DisplayAlerts プロパティを False に設定する DisplayAlerts プロパティに False が設定されて居ると 確認メッセージは表示されず 変更したブックを保存しないで Excel を終了する Select メソッドセル 又は セル範囲を選択するには Select メソッドを使用する 亦 単一のセルをアクティブセルにするには Activate メソッドを使用する -5-
オブジェクト型変数の宣言 (1) ( 再掲 ) タイプライブラリで定義されたオブジェクトに対して変数を宣言する Dim 変数名 As [New] クラス名 オブジェクトをコードで使用する為に 対象のタイプライブラリに依り提供されるクラス名を変数宣言の中で指定し オブジェクトを変数に格納する クラス名は コンポーネント名. クラス名 ( 例 :Exel.Chart) と謂う形式で構成される事が有る コンポーネント : オブジェクトを提供するコンポーネントの名前 オブジェクトブラウザの プロジェクト / ライブラリ ボックスに表示される ク ラ ス : コンポーネントのタイプライブラリを提供するオブジェクトのクラスの名前オブジェクトブラウザの クラス ボックスに表示される New キーワードを使用してオブジェクト変数 ( 参照型変数 ) を宣言すると 宣言時にオブジェクトが作成され 其の変数に オブジェクトの参照が代入される為 宣言後に 変数へのオブジェクトの参照を代入する必要が無い ( 実際には クラス名と同名のコンストラクタが呼び出される ) 猶 従来は New キーワードを用いて宣言した変数を使用すると アプリケーションの実行速度が遅く成る可能性が有った 此れは Visual Basic では New キーワードを用いて宣言した変数が有ると 其の度に オブジェクトの参照が既に其の変数に代入されて居るか何うかが検証した為で有る 亦 タイプライブラリとは COM(Component Object Model: プログラムをソフトウェアコンポーネントで構成する為のオブジェクト指向技術に基づくモデル ) で利用可能な公開されて居るクラスに関する情報を含むライブラリの事を謂い プロパティーやメソッドの名前と 其等に付いての標準的な説明が含まれる オブジェクト型変数の宣言 (2) タイプライブラリで定義されて居ないオブジェクトに対して変数を宣言する Dim 変数名 As Object 総称オブジェクト型のクラスのオブジェクト変数を宣言する 総称オブジェクト型 (Object) の変数は 32 ビット (4 バイト ) の変数で オブジェクトを参照する為のアドレスを格納する 総称オブジェクト型と仕て宣言した変数には アプリケーションで認識される任意の種類のオブジェクトへの参照を Set ステートメントを使用して 代入する事が出来る 総称オブジェクト型と仕て宣言した変数には 任意のオブジェクトへの参照を格納する事が出来るが 変数と参照先のオブジェクトとのバインディングが遅く成る ( 実行時バインディング ) 事前バインディング ( コンパイル時バインディング ) を実行するには 固有のクラス名を指定して宣言した変数にオブジェクトへの参照を代入する 其の為 オブジェクト変数を宣言する場合 出来る限り 総称オブジェクト型では無く 固有オブジェクト型 ( クラス ) と仕て宣言する 固有オブジェクト型を指定して宣言すると アプリケーションを実行する前に Visual Basic に依り オブジェクトのプロパティとメソッドへの参照が解決される 参照が実行前に解決される事で アプリケーションの実行時のパフォーマンスが向上する 猶 各固有オブジェクト型は オブジェクトブラウザで調べる事が出来る -6-
EXCEL の UsedRange プロパティ 使用セル範囲を返すプロパティ Object.UsedRange 指定されたワークシートで使用されて居るセル範囲 (Range オブジェクト ) を返す 値の取得而巳が可能で有る Object には オブジェクトへの参照を表すオブジェクト式を指定し 必ず指定する ( 以下同じ ) EXCEL の Address プロパティ 参照範囲を取得するプロパティ Object.Address ( 引数 1, 引数 2, 引数 3, 引数 4, 引数 5) Range オブジェクトを対象にして 範囲参照を返す 値の取得而巳が可能で有る 引数 1 には 行部分の参照を絶対参照として返すには True( 既定値 ) を指定する 引数 2 には 列部分の参照を絶対参照として返すには True( 既定値 ) を指定する 引数 3 には A1 形式 ( 既定値 定数 xla1) か R1C1 形式 ( 定数 xlr1c1) かを指定する 引数 4 には 外部参照を返すには True ローカル参照を返すには False( 既定値 ) を指定する 引数 5 には 開始点を定義する Range オブジェクトを指定する 総ての引数は 省略する事が出来る 引数 3 には 下記の定数の孰れかを指定する 定数 値 説明 xla1 1 列を A から始まるアルファベット 行を 1 から始まる数字で表す形式 xlr1c1-4150 行を R 列を C で表し 夫々れ 1 から始まる序数で表す形式 範囲 (Range オブジェクト等 ) の取得では A1 形式では "A1:D11" の様な文字列が R1C1 形式では "R1C1:R11C4" の様な文字列が返される EXCEL の Select メソッド オブジェクトを選択するメソッド Object.Select ( 引数 ) セル 又は セル範囲を選択するには Select メソッドを使用する 亦 単一のセルをアクティブセルにするには Activate メソッドを使用する 引数には オブジェクトの選択方法を指定し 省略する事が出来る シート丈に使用する事が出来 選択位置を指定したオブジェクト而巳を選択するには True を指定し 既に選択されて居たオブジェクトと指定したオブジェクトを選択するには False を指定する -7-
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 メソッドは ワークブックを閉じると謂うのが 夫々れのメソッドので有ると謂える -8-
エクセル操作で使用するオブジェクトの生成 事前バインディング ( 要参照設定 ) CreateObject 関数を使用したオブジェクト参照の代入 Dim EX Dim WB Dim WS As Excel.Application As Excel.Workbook As Excel.Worksheet EX = CreateObject( "Excel.Application" ) WB = EX.Workbooks.Add WS = WB.Worksheet( 1 ) New キーワードを使用したオブジェクト参照の代入 Dim EX Dim WB Dim WS As Excel.Application As Excel.Workbook As Excel.Worksheet EX = New Excel.Application WB = EX.Workbooks.Add WS = WB.Worksheet( 1 ) 実行時バインディング ( 参照設定推奨 ) Dim EX Dim WB Dim WS As Object As Object As Object EX = CreateObject( "Excel.Application" ) WB = EX.Workbooks.Add WS = WB.Worksheet( 1 ) 事前バインディングを使用すると プロパティの値の設定や取得に必要な時間を 大幅に短縮する事が出来る 但し ActiveX コンポーネントが タイプライブラリを提供して居る場合而巳 事前バインディングを使用する事が出来る 事前バインディングでは 変数宣言 又は Set ステートメントで New キーワードを使用して 新規オブジェクトを作成し オブジェクト変数にオブジェクトの参照を代入する事が出来るが CreateObject 関数を使用して 新しくオブジェクトを作成し オブジェクト変数にオブジェクトの参照を代入する事が推奨されて居る 猶 CreateObject 関数は事前バインディング 実行時バインディングの孰れのオブジェクトでも 使用可能で有る ActiveX コンポーネントがタイプライブラリを提供して居るか何うかに関係無く Set ステートメントで CreateObject 関数を使用して 新規オブジェクトを作成し オブジェクト変数にオブジェクトの参照を代入する事が出来る -9-