VB実用⑩　エクセル操作Ⅳ

プログラムの概要

事務処理に於いて、Microsoft 社のスプレッドシート ソフトで有るエクセルは、データベースソフトで有る アクセスと共に、業界標準（De Facto Standard）で 有ると謂う事が出来る。 今回は、Visual Basic から、エクセルのセルに値を設 定する手法を、重点的に学ぶ。 前回迄に学んだエクセル操作の為のオブジェクトの 生成を元に、変数や配列に格納されたデータ値や計算 式をエクセルのセルに代入したり、ファイルやピクチ ャボックスから画像を挿入する。 亦、事務処理等に活用する事の出来る実用的な手法と 仕て、エクセルの印刷機能を用いて、帳票等を印刷す る。Visual Basic 単独では面倒な印刷が、エクセルの 印刷機能を用いる事に依りプレビュー機能等を含め、 簡単に行う事が出来る様に成る。

VB でエクセル操作Ⅳ

□ オブジェクトのインスタンス生成（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 メソッド） 今回の重点項目

■ オブジェクト･プロパティ一覧 ■ コントロールの種類 プロパティ プロパティの設定値 フォーム Name excel4 FormBorderStyle FixedSingle StartPosition CenterScreen Text エクセル書込 ピクチャボックス Name picTemp Appearance 0 – フラット BorderStyle None Image 任意の画像 SizeMode AutoSize ボタン１ Name btnOpen Font ＭＳ明朝、太字、１０ Text エクセルを開く ボタン２ Name btnSet Font ＭＳ明朝、太字、１０ Text データ設定 ボタン３ Name btnPreview Font ＭＳ明朝、太字、１０ Text 印刷プレビュー ボタン４ Name btnPrint Font ＭＳ明朝、太字、１０ Text 印刷 ボタン５ Name btnFinish Font ＭＳ明朝、太字、１０ Text 終了 ファイルオープンダイアログ _{Name dlgFiler} FileName 空白 ファイルオープンダイアログ ボタン1 ボタン２ ボタン３ ボタン４ ボタン５ ピクチャボックス

■ プログラムリスト ■ 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 End Sub ' ボタン（エクセルを開く）がクリックされた時の処理

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 End Sub 此処で宣言した変数は、同じモジ ュール内の総てのサブプロシージ ャで、値の参照と設定を行う事が 出来る。 CreateObject 関数は、事前バイン ディングと実行時バインディング の孰れでも使用する事が出来る。 指定したファイルで、Workbooks コレクションを開く。 シート名には、操作対象とするシ ートの名称を指定する。 特にエクセルを表示する必要の無 い場合は、此の処理を省略する。

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

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 End Sub ' ボタン（データの設定）がクリックされた時の処理

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 ' 配列データの書込 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( ) 実際には、此処迄丁寧に記述する 必要は無いのだが、使用した総て のフォームをメモリから消去した 上で、プログラムを正しく終了す ると謂う癖を付けて置く事が望ま しい。 オブジェクトの比較は、Is 演算子 （IsNot 演算子）を用いて行う。 ジェネラルプロシージャを呼び出 す（制御を移す）には、Call ステ ートメントを用いる。 此処で宣言した変数は宣言したサ ブプロシージャ内でしか値の参照 と設定を行う事が出来ない。 エクセルのセルに値を書き込むに は、Value プロパティを使用する。 エクセルのセルに計算式を書き込 むには、Formula プロパティを使 用する。 Color 構造体の値を其の儘設定す る事が出来ない為、各要素に分解 して数値化して居る。 Color.FromArgb(128, 128, 255) セルの範囲を指定して、配列の値 を一括して書き込む事が出来る。 セルに個別に書き込むより高速で 有る。 エクセルのシートに画像を貼り付 ける事も出来る。

' 画像の貼付（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( ) End Sub ' ボタン（印刷プレビュー）がクリックされた時の処理

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( ) End Sub ' ボタン（印刷）がクリックされた時の処理

Private Sub btnPrint_Click(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles btnPrint.Click ' 印刷用シートの印刷 WS.PrintOut( ) End Sub ' エクセルファイルを閉じるジェネラルプロシージャ Private Sub CloseExcel( )

' エクセルの終了 EX.application.displayalerts = False EX.application.quit( ) ' オブジェクトとの関連付けの解除 WS = Nothing WB = Nothing EX = Nothing End Sub End Class 此の方法では、画像に枠線が表示 され、何故か、消去出来ない。 此の方法では、枠線が表示されず に、亦、原寸大で表示される。 此処では、フォームを最前面に表 示して居る為、一旦、非表示に仕 て居る。 エクセルの印刷プレビューを表示 するには、Worksheet オブジェク トの PrintPreview メソッドを使 用する。 エクセルのシートを印刷するに は、Worksheet オブジェクトの PrintOut メソッドを使用する。 使用済みの Excel.Application オ ブジェクトやWorkbook オブジェ クトや Worksheet オブジェクト は、必ず、閉じて、メモリから開 放して置く。 Quit メソッドは、エクセルを終了 する丈で、メモリから削除するに はNothing を設定する。

クリップボードの利用 My.Computer 名前空間の Clipboard オブジェクトを使用すると、システムのクリップボードへア クセスする事が出来る。 Clipboard オブジェクトは、クリップボード上のテキストやグラフィックスを操作する為のオブ ジェクトで、此れを使用して、アプリケーションの中でテキストやグラフィックスの複写や切取 や貼付の機能を実現する事が出来る。

Clipboard オブジェクトにデータをコピーする場合は、先ず、Clipboard.Clear( ) の様に Clear メソッドを実行し、Clipboard オブジェクトの内容を削除して置く。

Clipboard オブジェクトは、総ての Windows 用アプリケーションに依り共有される為、他のアプ リケーションに切り替えると、内容が変更される可能性が有る事に注意を要する。

Clipboard オブジェクトは、夫々れが異なる形式で有れば、複数のデータを保持する事が出来る 為、例えば、SetImage メソッドを使用して Dib 形式のビットマップを Clipboard オブジェクト 上に配置し、続いて、SetText メソッドを使用して Text 形式のテキストを配置すると謂う事が可 能で有る。然して、GetText メソッドを使用してテキストを取得し、GetImage メソッドを使用 してグラフィックスを取得する事が出来る。猶、同じ形式の他のデータを、コードやメニューコ マンドを使用してClipboard オブジェクト上に置くと、古いデータは失われる。 クリップボードにデータを設定するには、My.Computer.Clipboard オブジェクトの SetAudio、 SetData、SetFileDropDownList、SetImage、SetText の各メソッドを使用する。 亦、クリップボードからデータを取得するには、My.Computer.Clipboard オブジェクトの GetAudioStream、GetData、GetFileDropDownList、GetImage、GetText の各メソッドを使用 する。 猶、指定した形式と一致するアイテムがClipboard オブジェクトに存在するか何うかを調べるに は 、 My.Computer.Clipboard オ ブ ジ ェ ク ト の ContainsAudio 、 ContainsData 、 ContainsFileDropList、ContainsImage、ContainsText の各メソッドを使用する。 下記にMy.Computer.Clipboard オブジェクトのメソッドを示す。 メソッド 説明 Clear クリップボードをクリアする。 ContainsAudio クリップボードにオーディオ データが含まれて居るか何うかを示す。 ContainsData クリップボードに、指定されたカスタム形式データが含まれて居るか何うかを示す。 ContainsFileDropList クリップボードにFileDropList が含まれるか何うかを示す。 ContainsImage クリップボードにイメージが含まれるか何うかを示す。 ContainsText クリップボードにテキストが含まれるか何うかを示す。 GetAudioStream クリップボードからオーディオストリームを取得する。 GetData クリップボードからデータを取得する。 GetDataObject クリップボードからデータオブジェクトを取得する。 GetFileDropList クリップボードからFileDropList を取得する。 GetImage 配列からイメージを取得する。 GetText クリップボードからテキストを取得する。 SetAudio クリップボードにオーディオデータを書き込む。 SetData データを指定されたカスタム形式でクリップボードに書き込む。 SetDataObject クリップボードにデータオブジェクトを書き込む。 SetFileDropList クリップボードにFileFropList を書き込む。 SetImage クリップボードにイメージを書き込む。 SetText クリップボードにテキストを書き込む。

EXCEL の Worksheet オブジェクトの Cells プロパティ （再掲） Range オブジェクトを取得するプロパティ Object.Cells( 第１引数, 第２引数 ) Object で指定したワークシートの特定のセルを指定する。 引数１には、１から始まる行（Row）を表す数値を指定する。 引数２には、１から始まる列（Column）を表す数値を指定する。 R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う場合に多用されるプロパティで、単一 セルのRange オブジェクトの取得に用いられる。

実際にセルを操作する場合には、Text プロパティや 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( "範囲名" ) … セル範囲を取得

上記に於いて、３番目の例では、２個の引数を指定する構文を使用して居る。此の構文では、第１引数 で左上隅のセルを、第２引数で右下隅のセルを指定する。此の構文を利用すると、上記のCells プロパ ティと併用して、R1C1 参照形式の様にセルを座標と仕て縦横共に数字番地で扱う事が出来る。

実際にセルを操作する場合には、Text プロパティや Value プロパティや Formula プロパティを使用し て、設定や取得する種類を特定する事が多い。 機 能 書 式 解 説 機 能 書 式 解 説

EXCEL の Range オブジェクトの Value プロパティ 指定したセルの値を設定・取得するプロパティ Object.Value = 値 セルの値を、設定する。オブジェクトにはRange オブジェクトを指定する。 値を取得する場合、セルが空の場合はEmpty 値が返される。此れを調べるには、IsEmpty 関数を使用 する。 Range オブジェクトに複数のセルが含まれて居る場合は、値の配列が返される。此れを調べるには IsArray 関数を使用する。

EXCEL の Range オブジェクトの Formula プロパティ 指定したセルの数式を設定・取得するプロパティ Object.Formula = 計算式 セルの数式を、A1 形式の表示形式で、コード記述時の言語で設定する。オブジェクトには Range オブジェクトを指定する。 値を取得する場合、セルに定数が入力されて居る時は、Formula プロパティは定数を返し、セルが空の 時は、Null 値を返す。亦、セルに数式が格納されて居る時は、数式バーでの表示と同じ形式で、等号を 含む数式を文字列と仕て返す。 セルの値（Value）、又は、数式（Formula）に日付を設定すると、Excel は、其のセルに日付書式や時 刻書式が既に設定されて居るか何うかを調査する。此の時、書式が設定されて居ない場合は、数値書式 は標準の日付の短い形式に成る。 対象セル範囲が、１次元、又は、２次元のセル範囲の場合は、同じ次元を持つ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 ) と謂う記述の方が簡単で有り、効率的なプログラミングの方法で有る。 機 能 書 式 解 説 機 能 書 式 解 説

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 オブジェクトを指定する。 下記の引数を指定する事も出来る（孰れも、省略可能）。 第１引数で、印刷を開始するページの番号を指定する。 第２引数で、印刷を終了するページの番号を指定する。 第３引数で、印刷部数を指定する。 第４引数で、印刷をする前に印刷プレビューを実行するか何うかを指定する（True か False）。 第５引数で、アクティブなプリンタの名前を指定する。 第６引数で、ファイルへ出力するか何うかを指定する（True か False）。 第７引数で、部単位で印刷するか何うかを指定する（True か False）。 第８引数で、出力するファイルの名前を指定する（第６引数がTrue の場合）。 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説 機 能 書 式 解 説

名前付き引数の利用 Visual Basic では、多くの組込関数、ステートメント、及び、メソッドで、引数を指定する時に、名前 付き引数を使用する事が出来る。名前付き引数を使用すると、構文で定められた引数の順序に関係無く 任意の順序で、必要な引数丈を渡す事が出来る。 名前付き引数に値を代入するには、下記の様に、引数名、コロンの後に等号（:=）と値を記述する。 WS.OLEObjects.Add FileName := F 指定する引数が複数有る場合は、各引数指定の間をカンマで区切る。指定する順序は、自由で有る。 下記の記述例では、ユーザー定義サブプロシージャListText の構文で定められた引数の順序とは逆の順 序で引数を指定して居る。 ' ユーザー定義サブプロシージャ（ジェネラルプロシージャ）

Private Sub ListText( strName As String, Optional strAddress As String ) List1.AddItem strName

List2.AddItem strAddress End Sub

' コマンドボタンがクリックされた時の処理（イベントプロシージャ） Private Sub Command1_Click( )

ListText strAddress:="12345", strName:="名前" End Sub 此の機能は、省略可能な引数が多数有るプロシージャの場合に、特に便利で有る。 名前付き引数のサポートを調べる 特定の関数、ステートメント、メソッドに付いて、名前付き引数の使用が可能か何うかを調べるには、 コードエディタウィンドウの自動クイックヒント機能を使うか、オブジェクトブラウザで調べるか、又 は、ランゲージリファレンスを参照する。名前付き引数を使う場合、下記の点に注意する必要が有る。 ・Visual Basic（VB）オブジェクトライブラリで提供されるオブジェクトのメソッドでは、名前付き引 数はサポートされて居ない。Visual Basic for applications（VBA）オブジェクトライブラリで提供さ れる総てのキーワードでは、名前付き引数がサポートされて居る。

・示されて居る構文では、名前付き引数は、太字の斜体で表記されて居る。名前付き引数以外の引数は 標準の斜体で表記されて居る。

重要：名前付き引数を使用する場合でも、必ず指定する必要の有る引数を省略する事は出来ない。省略 する事の出来るのは、省略可能な引数丈で有る。Visual Basic（VB）オブジェクトライブラリ、及び Visual Basic for Applications（VBA）オブジェクトライブラリに付いて、オブジェクトブラウザで参 照した場合、省略可能な引数は角括弧（ [ ] ）で囲まれて表示される。