VB でエクセル操作 Ⅳ VB 2005 10 プログラムの概要 事務処理に於いて Microsoft 社のスプレッドシートソフトで有るエクセルは データベースソフトで有るアクセスと共に 業界標準 (De Facto Standard) で有ると謂う事が出来る 今回は Visual Basic から エクセルのセルに値を設定する手法を 重点的に学ぶ 前回迄に学んだエクセル操作の為のオブジェクトの生成を元に 変数や配列に格納されたデータ値や計算式をエクセルのセルに代入したり ファイルやピクチャボックスから画像を挿入する 亦 事務処理等に活用する事の出来る実用的な手法と仕て エクセルの印刷を用いて 帳票等を印刷する Visual Basic 単独では面倒な印刷が エクセルの印刷を用いる事に依りプレビュー等を含め 簡単に行う事が出来る様に成る 今回の課題項目 オブジェクトの参照設定 ( 事前バインディング 実行時バインディング ) オブジェクト変数の宣言 (Object 型 As Object) オブジェクトのインスタンス生成 (CreateObject 関数 ) エクセルのオブジェクト (Excel.Application WorkBook WorkSheet) ワークシートの操作 (Cells プロパティ Range プロパティ ) ワークシートの操作 (Value プロパティ Formula プロパティ ) ワークシートの (HorizontalAlignment プロパティ Interior プロパティ ) ワークシートの印刷 (PrintPreview メソッド PrintOut メソッド ) 今回の重点項目 エクセルのオブジェクト (Excel.Application WorkBook WorkSheet) ワークシートの操作 (Cells プロパティ Range プロパティ ) ワークシートの操作 (Value プロパティ Formula プロパティ ) ワークシートの (HorizontalAlignment プロパティ Interior プロパティ ) ワークシートの印刷 (PrintPreview メソッド PrintOut メソッド ) -1-
オブジェクト プロパティ一覧 ボタン 1 ボタン 2 ピクチャボックス ボタン 3 ボタン 4 ボタン 5 ファイルオープンダイアログ コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel4 FormBorderStyle FixedSingle StartPosition CenterScreen エクセル書込 ピクチャボックス Name pictemp Appearance 0 フラット BorderStyle None Image 任意の画像 SizeMode AutoSize ボタン1 Name btnopen Font MS 明朝 太字 10 エクセルを開く ボタン2 Name btnset Font MS 明朝 太字 10 データ設定 ボタン3 Name btnpreview Font MS 明朝 太字 10 印刷プレビュー ボタン4 Name btnprint Font MS 明朝 太字 10 印刷 ボタン5 Name btnfinish Font MS 明朝 太字 10 終了 ファイルオープンダイアログ Name dlgfiler FileName -2- 空白
Public Class excel4 プログラムリスト ' フォームレベルでグローバルな定数の宣言 ( エクセル定数 ) Private Const xlcenter As Integer = -4108 ' &HFFFFEFF4 ' フォームレベルでグローバルな変数の宣言 'Private EX As Object ' Excel.Application 'Private WB As Object ' Excel.Workbook 'Private WS As Object ' Excel.Worksheet Private EX As Excel.Application Private WB As Excel.Workbook Private WS As Excel.Worksheet Private SD As String ' 格納場所 ( 起動パス ) Private FN As String ' エクセルファイル名 此処で宣言した変数は 同じモジュール内の総てのサブプロシージャで 値の参照と設定を行う事が出来る ' フォームが読み込まれた時の処理 Private Sub excel4_load(byval sender As Object, ByVal e As System.EventArgs) _ Handles Me.Load ' エクセルファイルの格納場所の設定 ( 起動パス ) SD = Application.StartupPath : If Not SD.EndsWith(" ") Then SD &= " " ' ウィンドウを常に手前に表示 Me.TopMost = True ' ファイルを開くダイアログの設定 dlgfiler.filter = " エクセルファイル (*.xls) *.xls 総てのファイル (*.*) *.*" dlgfiler.initialdirectory = SD ' ボタン ( エクセルを開く ) がクリックされた時の処理 Private Sub btnopen_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnopen.click ' ファイルを開くダイアログの表示 If dlgfiler.showdialog = Windows.Forms.DialogResult.OK Then ' エクセルのファイル名の設定 FN = dlgfiler.filename If Not FN.Substring( FN.LastIndexOf( "." ) + 1 ) = "xls" Then FN = "" : Exit Sub End If End If ' エクセルオブジェクトの新しいインスタンスの生成 EX = CreateObject( "Excel.Application" ) ' ファイルを指定してワークブックのオープン WB = EX.Workbooks.Open( FN ) ' シート名を指定してオブジェクトの定義 WS = WB.Worksheets( "Sheet1" ) ' エクセルの表示 EX.Visible = True CreateObject 関数は 事前バインディングと実行時バインディングの孰れでも使用する事が出来る 指定したファイルで Workbooks コレクションを開く シート名には 操作対象とするシートの名称を指定する 特にエクセルを表示する必要の無い場合は 此の処理を省略する -3-
' ボタン ( 終了 ) がクリックされた時の処理 Private Sub btnfinish_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnfinish.click ' エクセルファイルが開いて居ればクローズ If WS IsNot Nothing Then ' エクセルを終了するサブルーチン呼出 Call CloseExcel( ) End If ' フォームをメモリから消去して終了 Me.Dispose( ) End ' ボタン ( データの設定 ) がクリックされた時の処理 Private Sub btnset_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnset.click Dim I, J, D( 4, 4 ) As Integer Dim R As Long = 1, F As String = "" ' 変数データの書込 For I = 0 To 200 Step 10 R += 1 WS.Cells( R, 1 ).Value = I Next WS.Cells( 1, 1 ).HorizontalAlignment = xlcenter WS.Range( "A1:A22" ).Interior.Color = 128 * &H10000 + 128 * &H100 + 255 WS.Columns( "B:B" ).ColumnWidth /= 2 ' 計算式の書込 WS.Cells( 1, 3 ).Value = " 計算式 " WS.Cells( 1, 4 ).Value = "12+34" WS.Cells( 2, 4 ).Value = "A2+A3" WS.Cells( 1, 5 ).Formula = "=12+34" WS.Cells( 2, 5 ).Formula = "=A2+A3" WS.Range( "C1:D2" ).HorizontalAlignment = xlcenter WS.Range( "C1:E2" ).Interior.Color = 255 * &H10000 + 128 * &H100 + 128 オブジェクトの比較は Is 演算子 (IsNot 演算子 ) を用いて行う ジェネラルプロシージャを呼び出す ( 制御を移す ) には Call ステートメントを用いる 実際には 此処迄丁寧に記述する必要は無いのだが 使用した総てのフォームをメモリから消去した上で プログラムを正しく終了すると謂う癖を付けて置く事が望ましい 此処で宣言した変数は宣言したサ ブプロシージャ内でしか値の参照 と設定を行う事が出来ない エクセルのセルに値を書き込むに は Value プロパティを使用する Color 構造体の値を其の儘設定する事が出来ない為 各要素に分解して数値化して居る エクセルのセルに計算式を書き込むには Formula プロパティを使用する ' 配列データの書込 Color.FromArgb(128, 128, 255) WS.Cells( 1, 7 ).Value = " 配列値 " For I = 0 To 4 For J = 0 To 4 セルの範囲を指定して 配列の値 D( I, J ) = I * 5 + J を一括して書き込む事が出来る Next セルに個別に書き込むより高速で Next WS.Range( "G2:K6" ).Value = D 有る WS.Cells( 1, 7 ).HorizontalAlignment = xlcenter WS.Range( "G1:K1" ).MergeCells = True WS.Range( "G1:K6" ).Interior.Color = 255 * &H10000 + 255 * &H100 + 128 ' 画像の貼付 ( セルに直接貼付 ) 画像が原寸大で表示されない WS.Cells( 9, 3 ).Value = " セルに直接貼付 " F = SD & "akubi.jpg" WS.Cells( 9, 3 ).Activate( ) WS.Pictures.Insert( F ).Select( ) エクセルのシートに画像を貼り付 ける事も出来る -4-
' 画像の貼付 (OLE オブジェクトを使用 ) 画像が原寸大で表示される WS.Cells( 9, 11 ).Value = "OLE オブジェクト使用 " WS.Cells( 10, 11 ).Activate() 此の方法では 画像に枠線が表示 WS.OLEObjects.Add( FileName:=F ) され 何故か 消去出来ない ' 画像の貼付 ( クリップボードを使用 ) 画像が原寸大で表示される WS.Cells( 9, 13 ).Value = " クリップボード使用 " My.Computer.Clipboard.Clear( ) 此の方法では 枠線が表示されず My.Computer.Clipboard.SetImage( pictemp.image ) に 亦 原寸大で表示される WS.Range( "M10" ).Select( ) WS.Paste( ) ' ボタン ( 印刷プレビュー ) がクリックされた時の処理 Private Sub btnpreview_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnpreview.click ' フォームの非表示とエクセルの表示 Me.Hide( ) ' EX.Visible = True ' プレビュー画面の表示 WS.PrintPreview( ) ' エクセルの非表示とフォームの表示 ' EX.Visible = False Me.Show( ) ' ボタン ( 印刷 ) がクリックされた時の処理 Private Sub btnprint_click(byval sender As Object, ByVal e As System.EventArgs) _ Handles btnprint.click ' 印刷用シートの印刷 WS.PrintOut( ) 此処では フォームを最前面に表 示して居る為 一旦 非表示に仕 て居る エクセルの印刷プレビューを表示 するには Worksheet オブジェク トの PrintPreview メソッドを使 用する エクセルのシートを印刷するに は Worksheet オブジェクトの PrintOut メソッドを使用する ' エクセルファイルを閉じるジェネラルプロシージャ Private Sub CloseExcel( ) ' エクセルの終了 EX.application.displayalerts = False EX.application.quit( ) ' オブジェクトとの関連付けの解除 WS = Nothing WB = Nothing EX = Nothing 使用済みの Excel.Application オブジェクトや Workbook オブジェクトや Worksheet オブジェクトは 必ず 閉じて メモリから開放して置く Quit メソッドは エクセルを終了する丈で メモリから削除するには Nothing を設定する End Class -5-
クリップボードの利用 My.Computer 名前空間の Clipboard オブジェクトを使用すると システムのクリップボードへアクセスする事が出来る Clipboard オブジェクトは クリップボード上のテキストやグラフィックスを操作する為のオブジェクトで 此れを使用して アプリケーションの中でテキストやグラフィックスの複写や切取や貼付のを実現する事が出来る Clipboard オブジェクトにデータをコピーする場合は 先ず Clipboard.Clear( ) の様に Clear メソッドを実行し Clipboard オブジェクトの内容を削除して置く Clipboard オブジェクトは 総ての Windows 用アプリケーションに依り共有される為 他のアプリケーションに切り替えると 内容が変更される可能性が有る事に注意を要する Clipboard オブジェクトは 夫々れが異なる形式で有れば 複数のデータを保持する事が出来る為 例えば SetImage メソッドを使用して Dib 形式のビットマップを Clipboard オブジェクト上に配置し 続いて Set メソッドを使用して 形式のテキストを配置すると謂う事が可能で有る 然して Get メソッドを使用してテキストを取得し GetImage メソッドを使用してグラフィックスを取得する事が出来る 猶 同じ形式の他のデータを コードやメニューコマンドを使用して Clipboard オブジェクト上に置くと 古いデータは失われる クリップボードにデータを設定するには My.Computer.Clipboard オブジェクトの SetAudio SetData SetFileDropDownList SetImage Set の各メソッドを使用する 亦 クリップボードからデータを取得するには My.Computer.Clipboard オブジェクトの GetAudioStream GetData GetFileDropDownList GetImage Get の各メソッドを使用する 猶 指定した形式と一致するアイテムが Clipboard オブジェクトに存在するか何うかを調べるには My.Computer.Clipboard オブジェクトの ContainsAudio ContainsData ContainsFileDropList ContainsImage Contains の各メソッドを使用する 下記に My.Computer.Clipboard オブジェクトのメソッドを示す メソッド Clear ContainsAudio ContainsData ContainsFileDropList ContainsImage Contains GetAudioStream GetData GetDataObject GetFileDropList GetImage Get SetAudio SetData SetDataObject SetFileDropList SetImage Set 説明クリップボードをクリアする クリップボードにオーディオデータが含まれて居るか何うかを示す クリップボードに 指定されたカスタム形式データが含まれて居るか何うかを示す クリップボードに FileDropList が含まれるか何うかを示す クリップボードにイメージが含まれるか何うかを示す クリップボードにテキストが含まれるか何うかを示す クリップボードからオーディオストリームを取得する クリップボードからデータを取得する クリップボードからデータオブジェクトを取得する クリップボードから FileDropList を取得する 配列からイメージを取得する クリップボードからテキストを取得する クリップボードにオーディオデータを書き込む データを指定されたカスタム形式でクリップボードに書き込む クリップボードにデータオブジェクトを書き込む クリップボードに FileFropList を書き込む クリップボードにイメージを書き込む クリップボードにテキストを書き込む -6-
EXCEL の Worksheet オブジェクトの Cells プロパティ ( 再掲 ) Range オブジェクトを取得するプロパティ Object.Cells( 第 1 引数, 第 2 引数 ) Object で指定したワークシートの特定のセルを指定する 引数 1 には 1 から始まる行 (Row) を表す数値を指定する 引数 2 には 1 から始まる列 (Column) を表す数値を指定する R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う場合に多用されるプロパティで 単一セルの Range オブジェクトの取得に用いられる 実際にセルを操作する場合には プロパティや Value プロパティや Formula プロパティを使用して 設定や取得する種類を特定する事が多い EXCEL の Worksheet オブジェクトの Range プロパティ Range オブジェクトを取得するプロパティ Object.Range( 引数 ) Object で指定したワークシートの特定のセル範囲を指定する 引数には マクロの言語の A1 形式での範囲を指定する 範囲名には 範囲を表す演算子 (:) 共通部分を表す演算子 ( スペース ) 複数の範囲を表す演算子 (,) を含める事が出来る 猶 ドル記号 ($) は 含める事は出来るが 無視される 亦 範囲の一部にローカルに定義した名前を使用する事も出来る 名前を使用する場合 其の名前はマクロの言語と看做される 此のプロパティは セル範囲の Range オブジェクトの取得に用いられる 下記に セル範囲の指定例を示す Range( "A3" ) Range( "B2:E8" ) Range( Object.Cells( 1, 1 ), Object.Cells( 5, 4 ) ) Range( " 範囲名 " ) 単一セルを取得 セル範囲を取得 セル範囲を取得 セル範囲を取得 上記に於いて 3 番目の例では 2 個の引数を指定する構文を使用して居る 此の構文では 第 1 引数で左上隅のセルを 第 2 引数で右下隅のセルを指定する 此の構文を利用すると 上記の Cells プロパティと併用して R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う事が出来る 実際にセルを操作する場合には プロパティや Value プロパティや Formula プロパティを使用して 設定や取得する種類を特定する事が多い -7-
EXCEL の Range オブジェクトの Value プロパティ 指定したセルの値を設定 取得するプロパティ Object.Value = 値 セルの値を 設定する オブジェクトには Range オブジェクトを指定する 値を取得する場合 セルが空の場合は Empty 値が返される 此れを調べるには IsEmpty 関数を使用する Range オブジェクトに複数のセルが含まれて居る場合は 値の配列が返される 此れを調べるには IsArray 関数を使用する EXCEL の Range オブジェクトの Formula プロパティ 指定したセルの数式を設定 取得するプロパティ Object.Formula = 計算式 セルの数式を A1 形式の表示形式で コード記述時の言語で設定する オブジェクトには Range オブジェクトを指定する 値を取得する場合 セルに定数が入力されて居る時は Formula プロパティは定数を返し セルが空の時は Null 値を返す 亦 セルに数式が格納されて居る時は 数式バーでの表示と同じ形式で 等号を含む数式を文字列と仕て返す セルの値 (Value) 又は 数式 (Formula) に日付を設定すると Excel は 其のセルに日付や時刻が既に設定されて居るか何うかを調査する 此の時 が設定されて居ない場合は 数値は標準の日付の短い形式に成る 対象セル範囲が 1 次元 又は 2 次元のセル範囲の場合は 同じ次元を持つ Visual Basic の配列から各セルの数式を設定する事が出来る 同様に 各セルの数式を Visual Basic の配列に代入する事も可能で有る 亦 セル範囲に数式を設定すると 範囲内の総てのセルに 其の数式が設定される 猶 セルの数式を R1C1 形式の表示形式で コード記述時の言語で設定するには FormulaR1C1 プロパティを使用する 下記に セルへの数式の指定例を示す Range( "A1" ).Formula = "=A4+A10" Range( "B1" ).FormulaR1C1 = "=SQRT(R1C1)" 猶 上記の様に Range( "A1" ) と謂う記述でセル A1 を取得する事も出来るが Cells プロパティでは 行や列の指定に変数を使用する事が出来る為 上記のセル指定で Cells( 1, 1 ) や Cells( 1, 2 ) を使用する事が多い 一般に Visual Basic の文字列関数を使用して A1 形式の参照文字列を変化させる事も出来るが Cells( 1, 1 ) と謂う記述の方が簡単で有り 効率的なプログラミングの方法で有る -8-
EXCEL の Range オブジェクトの Activate メソッド 単一のセルをアクティブにするメソッド Object.Activate 選択範囲の中の単一セルをアクティブにする時に使用する オブジェクトには Range オブジェクトを指定する セル範囲を選択する時は Select メソッドを使用する EXCEL の Range オブジェクトの Select メソッド オブジェクトを選択するメソッド Object.Select セルやセル範囲を選択する時に使用する オブジェクトには Range オブジェクトを指定する 単一のセルをアクティブセルにするには Activate メソッドを使用する EXCEL の Worksheet オブジェクトの PrintPreview メソッド 印刷プレビュー ( 印刷時のイメージ ) を表示するメソッド Object.PrintPreview オブジェクトには Worksheet オブジェクトを指定する EXCEL の Worksheet オブジェクトの PrintOut メソッド オブジェクトを印刷するメソッド Object.PrintOut オブジェクトには Worksheet オブジェクトを指定する 下記の引数を指定する事も出来る ( 孰れも 省略可能 ) 第 1 引数で 印刷を開始するページの番号を指定する 第 2 引数で 印刷を終了するページの番号を指定する 第 3 引数で 印刷部数を指定する 第 4 引数で 印刷をする前に印刷プレビューを実行するか何うかを指定する (True か False) 第 5 引数で アクティブなプリンタの名前を指定する 第 6 引数で ファイルへ出力するか何うかを指定する (True か False) 第 7 引数で 部単位で印刷するか何うかを指定する (True か False) 第 8 引数で 出力するファイルの名前を指定する ( 第 6 引数が True の場合 ) -9-
名前付き引数の利用 Visual Basic では 多くの組込関数 ステートメント 及び メソッドで 引数を指定する時に 名前付き引数を使用する事が出来る 名前付き引数を使用すると 構文で定められた引数の順序に関係無く任意の順序で 必要な引数丈を渡す事が出来る 名前付き引数に値を代入するには 下記の様に 引数名 コロンの後に等号 (:=) と値を記述する WS.OLEObjects.Add FileName := F 指定する引数が複数有る場合は 各引数指定の間をカンマで区切る 指定する順序は 自由で有る 下記の記述例では ユーザー定義サブプロシージャ List の構文で定められた引数の順序とは逆の順序で引数を指定して居る ' ユーザー定義サブプロシージャ ( ジェネラルプロシージャ ) Private Sub List( strname As String, Optional straddress As String ) List1.AddItem strname List2.AddItem straddress ' コマンドボタンがクリックされた時の処理 ( イベントプロシージャ ) Private Sub Command1_Click( ) List straddress:="12345", strname:=" 名前 " 此のは 省略可能な引数が多数有るプロシージャの場合に 特に便利で有る 名前付き引数のサポートを調べる 特定の関数 ステートメント メソッドに付いて 名前付き引数の使用が可能か何うかを調べるには コードエディタウィンドウの自動クイックヒントを使うか オブジェクトブラウザで調べるか 又は ランゲージリファレンスを参照する 名前付き引数を使う場合 下記の点に注意する必要が有る Visual Basic(VB) オブジェクトライブラリで提供されるオブジェクトのメソッドでは 名前付き引数はサポートされて居ない Visual Basic for applications(vba) オブジェクトライブラリで提供される総てのキーワードでは 名前付き引数がサポートされて居る 示されて居る構文では 名前付き引数は 太字の斜体で表記されて居る 名前付き引数以外の引数は標準の斜体で表記されて居る 重要 : 名前付き引数を使用する場合でも 必ず指定する必要の有る引数を省略する事は出来ない 省略する事の出来るのは 省略可能な引数丈で有る Visual Basic(VB) オブジェクトライブラリ 及び Visual Basic for Applications(VBA) オブジェクトライブラリに付いて オブジェクトブラウザで参照した場合 省略可能な引数は角括弧 ( [ ] ) で囲まれて表示される -10-