VB実用⑦　エクセル操作Ⅰ

Size: px

Start display at page:

Download "VB実用⑦　エクセル操作Ⅰ"

さみらますはら
5 years ago
Views:

VB でエクセル操作 Ⅰ VB 2005 7 プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 (De Facto Standard) で有ると謂う事が出来る此処では其のエクセルを Visual Basic から操作する方法を重点的に学ぶ今回は Visual Basic

1 VB でエクセル操作 Ⅰ VB プログラムの概要事務処理に於いて 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

2 オブジェクトプロパティ一覧ラベル 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" ) タイプライブラリで定義されて居るオブジェクトへの参照を作成するには下記の様にする 1. メニューバーよりプロジェクト参照の追加と進む 2.

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

4 プログラムリスト 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-

5 ' ボタン ( エクセルの使用範囲を抽出する ) がクリックされた時の処理 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-

6 オブジェクト型変数の宣言 (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-

7 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 から始まる数字で表す形式 xlr1c 行を R 列を C で表し夫々れ 1 から始まる序数で表す形式範囲 (Range オブジェクト等 ) の取得では A1 形式では "A1:D11" の様な文字列が R1C1 形式では "R1C1:R11C4" の様な文字列が返される EXCEL の Select メソッドオブジェクトを選択するメソッド Object.Select ( 引数 ) セル又はセル範囲を選択するには Select メソッドを使用する亦単一のセルをアクティブセルにするには Activate メソッドを使用する引数にはオブジェクトの選択方法を指定し省略する事が出来るシート丈に使用する事が出来選択位置を指定したオブジェクト而巳を選択するには True を指定し既に選択されて居たオブジェクトと指定したオブジェクトを選択するには False を指定する -7-

8 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-

9 エクセル操作で使用するオブジェクトの生成事前バインディング ( 要参照設定 ) 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-

VB実用⑧　エクセル操作Ⅱ

VB実用⑧　エクセル操作Ⅱ VB でエクセル操作 Ⅱ VB 2005 8 プログラムの概要事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルはデータベースソフトで有るアクセスと共に業界標準 ( De Facto Standard) で有ると謂う事が出来る今回はエクセルを Visual Basic から操作する為に最も基本と成るセルに設定された値の取得を重点的に学ぶ前回学んだエクセル操作の為のオブジェクトの生成と

VB実用⑦ エクセル操作Ⅰ

VB実用⑦　エクセル操作Ⅰ