-
OLEオートメーションを使ったVisual Basic
業務アプリケーション開発の実際
Microsoft EXCELの計算機能をOLEオートメーションサーバーとしてVB4から利用し，
エンドユーザーによる保守の範囲を拡大する
秋月 巌 AKIZUKI, Iwao
首都圏コンピューター技術者組合
図1：給料管理システム メインフォーム |
図2：給料計算表ワークシート |
オブジェクト指向とVisual Basic
一般的には，C++のようにクラスをサポートし「カプセル化」「継承」「多義性」などの特性を実現できるものを真のオブジェクト指向言語と呼んでいるようです．
Visual Basicは，オブジェクト指向開発ツールではないと言われています． ver.4.0になるまでクラスはサポートされていませんでしたし，言語仕様は従来の構造化言語の枠を超えるものではありません． しかし，ビジュアルなフォームビルダーや，コントロールの扱いなどは間違いなくオブジェクトを指向しています． つまり，Visual Basicはオブジェクト指向でない言語を搭載した，オブジェクト指向開発ツールということができます． オブジェクト指向のツールと単なる構造化言語という組み合わせは，一見アンバランスなようですが，実際には非常にうまく機能します． イベントドリブンプログラミングとの相性もよく，WindowsのGUI環境の開発ツールとして，すでに多くの実績もあります． Visual BasicにはC++のような言語仕様の美しさはありませんが，わかり易く，実用的なプログラムの作成という製品コンセプトは十分に達成されていると思います． マニアうけよりは作りやすさを優先するという意味で非常にMicrosoft社的な製品であるといえます．
Visual Basic ver4.0のクラス
オブジェクト指向のカプセル化の機能は，コンポーネント指向へとつながっています． 例えば，今回の記事ではサンプルとなる給料管理システム（図1）を作成するためのコンポーネントとしてExcelを使用しています． もちろんExcelは継承も多義性もサポートしていません． しかし，Excelは完全に独立したオブジェクトであり，その機能とデータはカプセルの内部に収められています．
OLEオートメーションの旗手として登場したVisual Basic 4.0のクラスが，オブジェクト指向の大切な特徴である継承をサポートしなかったのには，何らかの意図があるのかもしれません． 継承を最大限に利用したアプリケーションを作成するには，優秀なクラス設計者が必要です． しかし，（少なくとも日本ではまだ）クラス設計者という職種は存在しません． プログラマーが自分の範囲の中で個々勝手にクラスを作成しているような状況です． 継承を使用しなければ，一つのクラスはそのまま一つのコンポーネントに対応します． それ以上になったり，それ以下になったりすることはありません． Excelのように独立した一つの関数となるのです．
これにより，オブジェクトの性格がはっきりし，目的が明確になります． 使用する側としては，コンポーネントの特性を把握し，自分が実現したい機能を利用することによってプログラミングの効率を高めることができます． つまり，オブジェクト（クラス）という抽象的な存在が，継承と多義性を除くことによって具体的な存在（コンポーネント）となるのです． これによって，オブジェクトの持つ機能は具体化され，イメージしやすくなります．
もちろん，オブジェクトはコンポーネントの上位互換であり，その優位性は間違いありません． しかし，多くの専門家がVisual Basicのことを批判するにも関わらず，これだけの普及を遂げたのはなぜでしょうか． もちろん一つの理由は他に適切なツールがなかったということもあります． しかし，Delphiのように明らかにVisual Basicよりも優れたツールが登場した後でも，Visual Basicの優位性は揺らいでいません． Delphiの実行スピードの速さに惹かれて採用する人がいても，Delphiが継承をサポートしているという理由で採用する人はあまり多くないはずです（もちろんDelphiは優れたコンポーネント指向開発ツールでもあります）．
オブジェクトからコンポーネントへ
確かにC++はLiterary的にすばらしく，言語仕様の表現力と整合性はマニアと学者を魅了するに十分です． しかし，それだけでは，明日の納期に間に合わせなくてはならないプログラマーを虜にすることはできません． 彼らが望んでいるのは，とりあえず今日実装しなければならない機能を持っているコンポーネントなのです．
コンポーネント指向を助長する援護射撃の一つはソフトウェアの低価格化です． どんなに優れたコンポーネントでも，価格が高ければ作った方が安い，あるいは購入することができないということになります． 現在ではVBXなどの開発者向けのコンポーネントで数万円，Microsoft Officeのような，OLEサーバーとしての機能を備えたソフトウェアが実売で4万円以下で購入できます． これを利用しない手はありません． 給料管理システムではExcelが持つ計算機能を使用して，税金や保険料の計算を行い，その結果をOLEオートメーションを利用してJETデータべースエンジンに反映させています． MS Office自体はインストールベースですでに1000万のオーダーで普及しているといわれる製品ですし，Visual Basicと合算してもさして大きな投資額ではありません．
埋め込みOLEとOLEオートメーションの違い
ただ，単にOLEと呼んだときはOLEテクノロジー全体を指すこともあり，Microsoftのマニュアルでは，OLEとOLEオートメーションを区別をしないことも多いようです． OLEと言えば，やはり一番イメージしやすいのはExcelのグラフや表をWordの編集領域に貼り付けることではないでしょうか． 実はこの操作にも2種類の方法があります． 一つはツールバーのExcelボタンを押して表を作成する方法です． これによって作られたオブジェクトを埋め込みオブジェクトといいます． Object Linking and Embedingの“Embeding”です． Excelのシート上から直接Word上にドラッグしてもこの方式になります． この方法によって作成されたオブジェクトは，保存するときにはWordのDOCファイルに収納されます．
もう一つの方法は，対象となるオブジェクトをExcel上で選択してからクリップボードにコピーし，それをWordのメニューから「形式を選択して貼り付け」を選択し，ダイアローグのオプションボタンで「リンク貼り付け」を選択して貼り付ける方法です． この方式で作成したオブジェクトがリンクオブジェクトです． リンクオブジェクトはWordの文書の中にあるように見えますが，その実体はExcelのオリジナルシートに保存され，Word文書にはリンク情報だけが記録されています． つまり，Word上で，その表なりグラフを編集した結果はExcelのオリジナルシートに保存されのです．
もっとも，これらの違いはファイル管理の問題であり，目的としていることに大きな差はありません． ですが，OLEオートメーションとなると意味も目的も違ってきます． 本来，こうして比べて説明することが間違いなのですが，言葉が似ているので混乱しがちです． もう少し違う名前をつければいいと思うのですが，それらを実現するために使われたテクノロージーが共通だったということと，OLEという言葉を強調したくて，このような呼び方になったのでしょう． ただ，どちらもコンポーネントのリンクテクノロジーであるということに違いはありません．
OLEではWord上のExcelのシートを編集するときにはアクティブとなっているプログラムの機能がExcelに切り替わります． ツールバーもメニューもExcel独自のものになります． Excelの表は画面上でWordの文書に納まっているだけで，それぞれはまったく別のプログラムによって操作されます．
これに対して，OLEオートメーションは一方のアプリケーションの機能を，もう一方によって制御する機能のことです． オブジェクトが画面上で，他のアプリケーション上にあるかどうかは関係ありません． 埋め込んであり，かつ制御している場合はOLE + OLEオートメーションですし，オブジェクトを埋め込まないで制御だけ行っている場合はただのOLEオートメーションです．
OLEオートメーションの具体例
OLEオートメーションでは，サーバーが提供するオブジェクトを操作することによって，他のコンポーネントを操作することを実現しています． Excelの場合なら，VBAの言語エンジンが提供オブシェクトということになります． つまり，ExcelをOLEオートメーションサーバーとするVisual Basicプログラムでは，Visual Basicのコード内で，ExcelのVBAを使用することができます．
たとえば，ボタンが一つだけあるフォームがあり，同じフォルダー内にExcelのワークシートKyuryo.xlsがあるとします． ボタンのClickイベントプロシージャにはリスト1に示すコードが書かれています．
リスト1：Ecxelを起動するボタンのコード例
Private Sub Command1_Click()
Static excelapp As Object
Set excelapp = CreateObject("excel.application")
excelapp.Visible = True
excelapp.Workbooks.Open filename:="Kyuryo.XLS"
excelapp.ActiveWindow.LargeScroll Down:=1
End Sub
|
OLEオートメーションサーバーであるExcelを起動し，変数にオブジェクトのハンドルを取得しているのは，コードの以下の部分です．
Static excelapp As Object
Set excelapp = _
CreateObject("excel.application")
|
excel.applicationはExcelを特定する識別子です． Windows95のレジストリーに記録されており，この識別子か指定されるとOSはExcelを起動します． この識別子はサーバーアプリケーションのインストールが正常に行われていないとレジストリーに登録されないので注意が必要です．
ハンドルを取得したオブジェクト型の変数excelappは以後，OLEオートメーションで対象オブジェクトを操作するときに使用されます． このコードで起動されたExcelは画面には表示されていません． CreateObjectは非表示の状態でObjectを起動するからです． 次のコードにより，Excelは表示されます．
excelapp.Visible = True |
次のコードではExcelのワークシートKyuryo.xlsを開いています． WorkbooksというオブジェクトはVisual Basicには存在しないので，今度は区別しやすいかもしれません．
excelapp.Workbooks.Open filename _ :="Kyuryo.XLS" |
次のコードではワークシートを下に1回スクロールしています．
excelapp.ActiveWindow.LargeScroll _ Down:=1 |
要約して説明しますと，まず始めに変数にオブジェクトのハンドルを取得し，以降はその変数名の後にサーバーが提供するメソッドや関数を記述することで，サーバーの操作を行います． 繰り返していいますが，注意してほしいのはOLEオートメーションの操作は，Visual Basicのコード中に記述されていますが，Excelの言語仕様に依存しているという点です．
OLEオートメーション対応のサーバーアプリケーション
他社製品ではジャストシステムの一太郎がPlayRiteという独自のスクリプトをOLEオートメーションに提供しています． 他にもジャストシステムは一太郎の最新バージョン（32ビット版）で，一太郎の機能をOLEカスタムコントロールとして提供するとアナウンスしています． ワープロの機能をOCXで利用できるというのも面白そうです． Visual Basicで特製ワープロが作成できるということでしょうか．
市販アプリケーションではありませんが，Visual Basic ver4.0や，最新のC++コンパイラーによって作成されたプログラムもOLEオートメーションを介して，機能を他のアプリケーションに提供することができます．
日立製作所が昨年発表したVisual APPGALLERYというコンポーネント指向の開発ツールもOLEオートメーションサーバー機能を持ったアプリケーションが作成可能です． このツールはOCXをいち早くサポートしたり，OLEオートメーションのコードを自動生成する機能を用意したり，優れたツールのようです． ベンチャー企業がひしめくパソコン業界で，日立製作所のような大メーカーの底力を感じさせます．
では，これからのアプリケーションはどれもOLEオートメーション機能を提供していくのでしょうか． これはちょっと難しい問題です． 今のところ，OLEオートメーションサーバーのサービスを提供することを発表しているソフトハウスは多くありません． これにはいくつかの理由があります．
OLEテクノロジーの将来がまだ不確定だということや，OLEを利用するプログラムの開発が難しいということも理由の一つです． OLEテクノロジーはOSに深く依存する技術ですから，それらを開発する環境であるC++クラスライブラリーや，OSに含まれるバグ，さらにOLE用のAPIの不整合なども理由になっているようです．
しかし，OLEが現在もっとも注目を浴びている技術の一つであることも確かです． Visual Basic 4.0 Enterprise Editionでサポートされた3分化アーキテクチャーなどは，間違いなく，これからのクライアントサーバーシステムの方向性を指し示しています．
OpenDocがいかに優れた技術だとしても，現在すでに利用できる環境を用意できているOLEのアドバンテージを覆すのは難しいでしょう． 逆に言えば，中途半端なシステムを普及させ，優れた技術の普及を妨げてきたのがMicrosoftがパソコンの歴史に残してきた問題点です．
OLEオートメーションの問題点
サンプルプログラム「Excelを表示」ボタンを押した後で，データを変更したときに，Excel上でデータが変更されているのを見ることができます． この時のデータ処理の速度速度もあまり早いとはいえません． もちろん，これもOLEの問題というよりはExcelVBAの処理速度の問題です． しかし，今のところ高速でデータ処理をできる市販のOLEオートメーションサーバーアプリケーションがないこともまた事実なのです．
もっとも，これらは市販アプリケーションをOLEオートメーションサーバーとして使った場合の話です． Visual Basicで作ったOLEオートメーションサーバープログラムや，C++でIn processサーバーを作成すればオーバーヘッドの増加はOLE周りだけで済むはずです． しかし，パッケージソフトメーカーで開発している以外の人が，このようなソフトを作成する機会は，今のところあまりないはずです．
In processサーバーとOut of processサーバーの違い
In processサーバーは呼び出したクライアントと同じタスクで実行されます． これは関数ゃメソッドを提供するという意味でDLLの動作とよく似ていますし，実際，ファイルの拡張子もDLLです． では，普通のDLLとは何が違うかと言えば，それは呼び出す時のインターフェースが異なります． 具体的に言えば，呼び出すための書式が違い，クライアントに要求される仕様が違います．
一般のDLLファイルはDLL呼び出しをサポートした開発環境で作られたアプリケーションで使用しますが，OLEオートメーションサーバーのDLLは，OLEオートメーションクライアント機能をサポートしているアプリケーションから呼び出します． MS WordからOLEオートメーションサーバーの機能を呼び出すことも可能です． また，呼び出しの書式がシンプルなので開発者の負担も少なくなります．
Out processサーバーはIn processに対して，クライアントとは別のタスクで実行されます． Excelをサーバーとして呼び出すというのはまさしく，この方式です． この方式はどうしてもIn proccessサーバーよりもオーバーヘッドが大きくなりますが，既存のアプリケーションをOLEオートメーションサーバーとして使う場合には，この方法に依らざるをえません．
いずれは各アプリケーションが自分の機能をIn procesサーバーとしてDLLファイルで提供したり，OCX可されたりするのかもしれません． これも単にOLEテクノロジーの普及にかかっています．
OLEオートメーションを利用したシステムの設計
OLEを使用する利点はサーバーの機能によって様々です． では，給料計算システムにExcelを使用する利点は何でしょうか． 答えはその計算能力と融通性です． Visual Basicで作ったアプリケーションは定型業務での利用には有利ですが，融通のきくシステムを作るためには，かなりの労力を必要とします． 複雑な処理をした際の，実行速度の低下も気になるところです．
給料管理システムでは，給料の計算部分をすべてExcelの表計算機能で行っています． 特に所得税の計算と雇用保険の計算にはExcelのVBAで作られたユーザー定義関数を使って計算しています． 保険料の計算や，税金の計算は複雑な上に，計算方法が変更される可能性があります．
もし，Visual Basicのアプリケーションにこの計算機能を埋め込むと，変更になったときのプログラム修正は容易ではありません． しかし，Excelで計算部分を処理しておけば，ワークシートを変更するだけで対応できます． Excelは非定型業務を処理するためのツールですから，大きな変更があっても平気です． それに，これが一番大切な部分なのですが，Excelならばエンドユーザーによるシステムの変更が期待できます．
メンテナンスの負担を，開発者からエンドユーザーに移行するのは運用の上で大切な課題です． しかし，Visual Basicアプリケーションのメンテナンスをエンドユーザーに任せるのは，難度が高いばかりでなく，システムの整合性の点で多くのリスクを伴います． エンドユーザーコンピューティングはこれからのシステム開発で重要な概念ですが，エンドーユーザーに任せることができるのは，表計算ソフトか，せめてデータベースのレポートライターの操作位までにしておきたい，というのが開発者の本音ではないでしょうか．
このシステムで，例えばExcelをアプリケーションサーバーマシンに搭載することにより，3階層アーキテクチャーを実現することもできます． 一つの部門でエンドユーザーが修正した結果を皆で共有することが可能です． 給料計算を多部門で行う企業は多くないでしょうが，変更が多く計算が複雑な業務，金利や保険料の計算システムなどでは有効な対処でしょう．
給料計算システムはVisual Basicだけでも実現が不可能だというわけではありませんが，今回は，Excelを利用して保守性の高いシステムを実現するために，OLEオートメーションを採用しています．
給料管理システムの概要
このプログラムは社員ごとに給料を管理するシステムですが，社員マスターを保守するプログラムは付属していません． すでに入力されている社員を表示するだけです． 給料管理が目的なので，「社員」テーブルの項目も最低限に抑えられています． また，給料明細を印刷するプログラムも付属していません． 社員マスターの管理や，帳票の印刷はMicrosoft Accessでダイレクトに行うことができますし，Visual Basicのコードを追加してプログラムを完成させることも，それほど難しくないはずです． エンドユーザーが使うことを考えると，OLEオートメーションサーバーの起動エラーなどの例外処理を加える必要があるかもしれません． それらの問題をクリアすれば，十分に実用に耐えるレベルのシステムにできているはずですが，実務に使うためには，各々の責任で十分なテストを行ってください．
メインフォームであるKyuryo.frmは呼び出されたときに，OLEオートメーョンサーバーであるExcelを起動し，給料計算用のワークシートであるKyuryo.xlsをロードします． 後でコードの説明を行いますが，これら一連の処理をOLEオートメーションで行っています．
リンクしている「社員」テーブルの社員番号に合わせて，「給料」テーブルよりデータが抽出されます． リンクは動的SQLを使ってプログラムによって実装されています． JETエンジンの機能による参照の整合性は設定されていません．
給料明細の各項目を離れるときやEnterキーが押されたときに，給料計算を行っています． フォーカスを失ったばかりの項目の値をOLEオートメーションでExcelシートに転送し，計算結果をExcelワークシートからVisual Basicのフォームに戻しています．
これらの操作にはOLEオートメーションを利用して，プロセス間通信を行っています． 同じ操作をクリップボード経由や，DDEを使用しても行えますが，OLEを使用するのが最も安定していて，コーディングもシンプルになります．
ExcelのワークシートにはSyotokuZeiと Koyouという2つのユーザー定義関数がモジュールで定義されていて，それぞれ所得税と雇用保険の算出を行います． 「給料計算表」ワークシートには全部で5つの表があります． そのうちの一つである給料計算表で，実際の計算処理を行っています． 他の4つの表は税率と保険料の一覧表で，ここを変更すると給料計算表での計算結果に反映します．
一般的な所得税率の変更や，雇用保険料の変更ならば，これらの表の数字を変更するだけで対応できるはずです． 計算方法そのものを変更しなければいけないような場面ではワークシートの計算式や，モジュール内の関数を変更しなければならなくなりますが，それでもExcelのワークシートの変更だけで済みます． また，実務に詳しければ社会保険や厚生年金の計算処理機能をExcelのワークシート上に加えることもできるはずです．
Visual Basicのフォームに連結されているデータベースはデータを管理しているだけで，まったく計算をしていないからです． つまりExcelによって計算処理がカプセル化されているのです．
「給料計算表」ワークシートの作成
| 図3 源泉徴収税額表と雇用保険額表 |
図4：給料計算表 |
図5：給料計算表の式
|
給料計算ワークシートは次の5つの表から成り立っています．
1. 源泉徴収税額を計算するための3つの表（図3 左）
2. 雇用保険額を算出するための保険額表（図3 右）
3. 給料計算を行うための給料計算表（図4）
1)，2)は税金や，保険料を求めるためのパラメーターとして利用されます．
3)は実際に給料の計算を行い，OLEオートメーションによってVisual Basicのプログラムと値のやりとりをします．
給料計算表に埋め込まれている計算式は図5のようなものになります．
EXCELのユーザー定義VBA関数
所得税計算関数 SyotokuZeiのExcelVBAコードはリスト2のようなものです．
リスト2：所得税の計算
'所得税計算関数
Function SyotokuZei()
Dim KazeiTaisyo, Kojo, kisokojo, KazeiSyotoku, Haigusya, Fuyosya
KazeiTaisyo = Range("C45")
Haigusya = Range("C55")
Fuyosya = Range("C56")
If KazeiTaisyo > 0 Then
'控除額の計算
Kojo = (KazeiTaisyo _
* Application.VLookup _
(KazeiTaisyo, Range("$C$8:$G$13"), 3) _
+ Application.VLookup _
(KazeiTaisyo, Range("$C$8:$G$13"), 5))
'1 円単位で切り上げ
Kojo = Application.RoundUp(Kojo, 0)
'配偶者控除の額
Haigusya = Range("$D$16") * Haigusya
'扶養控除の額
Fuyosya = Range("$D$17") * Fuyosya
'基礎控除の額
kisokojo = Range("$D$18")
'課税される所得
KazeiSyotoku = KazeiTaisyo - Kojo - Haigusya - Fuyosya - kisokojo
'課税される所得が0以下の時は計算しない． |
次のコードにより，給与所得控除額の計算を行っています．
Kojo = (KazeiTaisyo _
* Application.VLookup _
(KazeiTaisyo, Range("$C$8:$G$13"), 3) _
+ Application.VLookup _
(KazeiTaisyo, Range("$C$8:$G$13"), 5))
|
VlookUp関数は指定された範囲でワークシート内の検索を行います．
源泉徴収税額表の表2より，該当する課税対象の控除を算出し，計算します．
同様に次のコードでは表3より，該当する課税額を検索し，税額を算出しています．
SyotokuZei = (KazeiSyotoku _
* Application.VLookup _
(KazeiSyotoku, Range("$C$22:$G$26"), 3) _
- Application.VLookup _
(KazeiSyotoku, Range("$C$22:$G$26"), 5))
|
同様に，雇用保険料を計算するユーザー定義関数Koyouの全コードはリスト3に示してあります．
リスト3：雇用保険料の計算
Function Koyou()
Dim Sikyu, Hokenritu
Sikyu = Range("C40")
Hokenritu = 0.004
Koyou = Application.VLookup(Sikyu, Range("$J$5:$K$54"), 2)
'賃金額が保険料表にない場合
If Koyou = 0 Then
Koyou = Int(Sikyu * Hokenritu)
End If
End Function
|
雇用保険料額表からVlookUp関数を用いて雇用保険額を求めています．
Koyou = Application.VLookup _
(Sikyu, Range("$J$5:$K$54"), 2)
|
このようにワークシートを準備することにより，給料計算をExcelで行うことができるようになります．
試しに基本給の項目に数字を入力してみてください．
計算が自動的に行われるのがわかるはずです．
給料計算の方法が変更になったときも，このワークシートの変更で対処できます．
Visual Basicの内蔵データベースエンジンを利用した給料管理システムの作成
従業員テーブル「Jugyoin」は給料テーブル「Kyuryo」の親テーブルで，従業員に関するデータが収納されています．
実際に使用するケースでは，従業員のデータを統合して管理するために「住所」「電話番号」「生年月日」等，項目数が増えることになるでしょう．
給料テーブルは給料に関するデータが納められています．
「Jugyoin id」フィールドと，従業員テーブルの「id」フィールドとはプログラムのコードによって関連づけられています．
各フィールドは給料フォーム「Kyuryo」上のテキストボックスと一対一で対応しています（図7）．
図6：テーブル関係図 |
図7：デザインモードの給料フォーム（Kyuro.frm） |
給料フォームの作成
|
給料フォーム「Kyuryo」（図7）は本システムの中心となるものです．
2つのデータコントロールとそれらに関連づけられたラベルコントロールとテキストボックス，および2つのコマンドボタンによって構成されています．
フォームの上部を占めるのは従業員データを表示するセクションです．
従業員データコントロール「DATA_Jugyoin」に連結された従業員ラベルコントロール「LABEL_Jugyoin」にデータが表示されます．
従業員ラベルコントロールはコントロール配列になっています．
それぞれのコントロールに行ったプロパティの変更は次のようなものです（表1・表2）．
|
給料フォームの表示セクションの下部は，従業員の給料を給料データコントロール「DATA_Kyuryo」が保持しているデータから，給料テキストボックス「TEXT_Kyuryo」に月ごとに表示します．
給料データコントロールと給料テキストボックスのプロパティ変更箇所を表にします（表3・表4）．
この他に背景色が灰色の「給料」テキストボックスに関しては，EnabledプロパティをFalseに設定しています．
これらはExcelのワークシートで自動計算するため値を入力する必要がないためです．
給料フォームにコードを追加する
リスト4：Load時の処理
Private Sub Form_Load()
Dim pathname As String
'Excelのアプリケーションオブシェクトを作成
Set excelapp = _
CreateObject("excel.application")
'アプリケーションのあるディレクトリ名を取得
pathname = App.Path
'Excelの「給料計算表」ワークシートをオープン
excelapp.Workbooks.Open filename:=pathname + "\kyuryo.XLS"
End Sub
|
リスト4中の以下のコードにより，給料フォームの起動時にExcelを自動的起動し，OLEオブジェクトが作成されます．
Set excelapp = _
CreateObject("excel.application")
|
以後，オブジェクト変数excelqppを使ってOLEオートメーションによる制御が可能となります．
オブジェクト変数excelappはフォームのGeneralのDeclarationで宣言されています．
Public excelapp As Object |
先に述べたように，オブジェクトの寿命は変数の寿命と一致します．
イベントプロシージャの内部で宣言すると，そのイベントプロシージャが終了した時点でExcelを閉じてしまいます．
それに，フォームに記述されている様々なイベントプロシージャから，オブジエクト変数Exceappを参照する必要があります．
次のコードは先程作った給料計算表ワークシート「Kyuryo.xls]を読み込むためのものです．
これにより，OLEオートメーションでExcelをサーバーとして利用するための準備が整います．
pathname = App.Path excelapp.Workbooks.Open filename _ :=pathname + "\kyuryo.XLS" |
SQLによるデータの取得
Repositionイベントプロシージャはデータコントロールのカレントレコードが変更されるときと，コントロールが作成される初期化処理の最中に呼び出されます．
リスト5：給料データコントロールの制御
Private Sub DATA_Jugyoin_Reposition()
'表示する社員が変更になった時，「給料」データコントロールのデータを再取得
DATA_kyuryo.RecordSource = "SELECT jugyoin_id, nen, tuki, kihon, kaikin, chokin, yakusyoku, syokumu, kazoku, jutaku, etc_teate, _
sikyu_gaku, kotuhi, sikyu_gokei, kyuryo.kenko, kyuryo.kosei, kyuryo.koyo, kyuryo.hoken_gokei, kazei_tasyo, syotoku, jumin, zaikei, _
ryoko, etc_kojo, kojo_gokei, kyuryo FROM kyuryo WHERE jugyoin_id = " + LABEL_jugyoin(0).Caption
DATA_kyuryo.Refresh
'配偶者の有無と扶養者の数をExcelシートに転送
excelapp.Range("C55").Value = Val(LABEL_jugyoin(4).Caption)
excelapp.Range("C56").Value = Val(LABEL_jugyoin(5).Caption)
'ヒットしているレコードがない時にMoveLastメソッドは使用できない． |
DATA_kyuro.RecordSource = 以下，+ LABEL_jugyoin(0).Captionまでは，実際は1行のコードです
DATA_kyuro.RecordSource = 以下，+ LABEL_jugyoin(0).Captionまでのコードにより，フォームが開いたときに，従業員データコントロールが作成されると，[id]フィールドの値をパラメーターとしたSQL文が作成され「給料」データコントロールのRecordSourceプロパティにセットされます．
OLEオートメーションを使ってEXCELにデータを転送する
excelapp.Range("C55").Value = _
Val(LABEL_jugyoin(4).Caption)
excelapp.Range("C56").Value = _
Val(LABEL_jugyoin(5).Caption)
|
今度は給料データコントロールのRepositionイベントプロシージャに，リスト6に示すコードを記述します．
これも先のコード同様OLEオートメーションを利用しています．
リスト6：データの転送と再計算
Private Sub DATA_kyuryo_Reposition()
'「給料」テーブルのカレントレコードのデータをExcelに転記
excelapp.Range("C29").Value = Val(TEXT_kyuryo(2).Text)
excelapp.Range("C30").Value = Val(TEXT_kyuryo(3).Text)
excelapp.Range("C31").Value = Val(TEXT_kyuryo(4).Text)
excelapp.Range("C32").Value = Val(TEXT_kyuryo(5).Text)
excelapp.Range("C33").Value = Val(TEXT_kyuryo(6).Text)
excelapp.Range("C34").Value = Val(TEXT_kyuryo(7).Text)
excelapp.Range("C35").Value = Val(TEXT_kyuryo(8).Text)
excelapp.Range("C36").Value = Val(TEXT_kyuryo(9).Text)
excelapp.Range("C39").Value = Val(TEXT_kyuryo(11).Text)
excelapp.Range("C41").Value = Val(TEXT_kyuryo(13).Text)
excelapp.Range("C42").Value = Val(TEXT_kyuryo(14).Text)
'VBA関数の埋め込まれているセルを再計算
excelapp.Range("C43").Select
excelapp.ActiveCell.FormulaR1C1 = "=Koyou()"
excelapp.Range("C46").Select
excelapp.ActiveCell.FormulaR1C1 = "=SyotokuZei()"
excelapp.Range("C47").Value = Val(TEXT_kyuryo(18).Text)
excelapp.Range("C48").Value = Val(TEXT_kyuryo(19).Text)
excelapp.Range("C49").Value = Val(TEXT_kyuryo(20).Text)
excelapp.Range("C50").Value = Val(TEXT_kyuryo(21).Text)
End Sub
|
次のコードではVisual Basicのフォーム上にあるTEXT_kyuryo(2)テキストボックスの値を数値型に変換し，Excelの給料計算表ワークシートの[C29]に代入しています．
excelapp.Range("C29").Value = _
Val(TEXT_kyuryo(2).Text)
|
値が代入されるとExcelではワークシートで設定された計算式に基づいて計算処理を実行します．
次のコードはワークシートの[C43]に埋め込まれているユーザー定義関数[Koyou]を実行するためのものです．
excelapp.ActiveCell.FormulaR1C1 _ = "=Koyou()" |
OLEオートメーションを使って，EXCELのデータを取得する
この操作は2つのプロセスに分けることができます．
まず始めに変更された値をExcelのワークシートに代入します．
次に計算された値をフォームの給料テキストボックスに代入します．
給料テキストボックスは給料テーブルに連結されているので，代入された値はカレントレコードが変更されたときに自動的にコミットされます．
堅牢なシステムを望んでいるならば，EDITメソッドとUPDATEメソッドを使って明示的にコミットすべきでしょう．
コードはLostFocusイベントプロシージャに記述されます．
ただ，LostFocusイベントで，これらの操作を指定すると，値が変更されていなくても操作を実行するので，フォーカスを移動するときに，データの変更がされていなくてもレスポンスが低下します．
今回は改善策を施していませんが，気になるようでしたら，Keypressイベントでフラグを立て，変更がない場合は処理をしないようにすべきです．
リスト7：最新の状態に更新
Private Sub TEXT_kyuryo_LostFocus(Index As Integer)
Dim selposition, add As Integer
Select Case True
Case 2 <= Index And Index <= 9
add = 27
Case 11 <= Index And Index <= 14
add = 28
Case 18 <= Index And Index <= 21
add = 29
End Select
'Indexを使って転送するセルを求める
selposition = "C" + Trim(Str(Index + add))
'テキストボックスの内容をExcelに転送
excelapp.Range(selposition).Value = Val(TEXT_kyuryo(Index).Text)
'ユーザー定義関数の再計算をする
excelapp.Range("C43").Select
excelapp.ActiveCell.FormulaR1C1 = "=Koyou()"
excelapp.Range("C46").Select
excelapp.ActiveCell.FormulaR1C1 = "=SyotokuZei()"
'計算結果を取得し，フォーム上のテキストボックスに代入
TEXT_kyuryo(10).Text = excelapp.Range("C38").Value
TEXT_kyuryo(12).Text = excelapp.Range("C40").Value
TEXT_kyuryo(15).Text = excelapp.Range("C43").Value
TEXT_kyuryo(16).Text = excelapp.Range("C44").Value
TEXT_kyuryo(17).Text = excelapp.Range("C46").Value
TEXT_kyuryo(22).Text = excelapp.Range("C52").Value
TEXT_kyuryo(23).Text = excelapp.Range("C53").Value
End Sub
|
次のコードは給料テキストボックスのIndex番号から，転送先となるワークシートのセル番号を求め，転送を実行しています．
selposition = "C" _ + Trim(Str(Index + add)) excelapp.Range(selposition).Value _ = Val(TEXT_kyuryo(Index).Text) |
転送を行った後はユーザー定義関数による計算を以下のコードで実行します．
excelapp.Range("C43").Select
excelapp.ActiveCell.FormulaR1C1 _
= "=Koyou()"
excelapp.Range("C46").Select
excelapp.ActiveCell.FormulaR1C1 = _
"=SyotokuZei()"
|
最後に計算結果を取得し，テキストボックスに代入します．
書式は転送のときと，左辺と右辺が逆になる（リスト7のTEXT_kyuryo(10).Text = excelapp.Range("C38").Value以下の部分）だけです．
計算結果が代入されるのは，フォーム上で背景が灰色になっている給料テキストボックスです．
OLEオブジェクトの開放
以下のコードではフォームをセーブし，Excelを閉じた後で，オブジェクト変数からオブジェクトを開放しています．
Private Sub Form_Unload(Cancel As Integer) 'セーブをしないと変更をセーブするか訊ねる 'ダイアログボックスが出てしまう excelapp.ActiveWorkbook.Save excelapp.Quit Set excelapp = Nothing End Sub |
本当はExcelワークシートは計算用に一時的に使用するだけですから，セーブする必要はありません．
しかし，セーブしないで強制的に閉じようとすると，ワークシートが変更されているため，セーブするかどうかを訊ねる，ダイアログボックスが表示されてしまいます．
SendKeyメソッドを使ってダイアログボックスを閉じる方法もありますが，表示されないに超したことはありません．
そのために次のコードがExcelを閉じる前に記述されています．
excelapp.ActiveWorkbook.Save |
コードの次の箇所では，Excelを閉じた後でオブジェクト変数を開放しています．
excelapp.Quit Set excelapp = Nothing |
こういった終了時の処理は大切です．
これを怠るとフォームを開く度に，新しいExcelをオープンしていきます．
Excelは重いソフトであるため，Excelが複数オープンされると，かなりのメモリを消費したまま開放できなくなります．
また，Excelを非表示にしたまま，終了処理を行わないでハンドルを失うと，OSをシャットダウンするまでExcelを閉じることができなくなります．
非表示属性で起動すると，タスクバーに現れないので，手動によるオペレーションができなくなるためです．
給料管理システムでも，Visual Basicの開発環境上から実行して，フォームを閉じないで，開発環境にある「プログラムの終了」ボタンを押して実行を終了すると，Unloadイベントが発生しないためExcelが開いたまま，ハンドルを失う状態になります．
必ずフォームを閉じて終了するようにしてください．
実行中にExcelを表示する
OLEオブジェクトを作成し，Excelをロードしただけでは，非表示属性でロードされるので画面には現れません．
視覚化するには明示的に指定する必要があります．
そのためにEXCELボタンのクリックイベントに次のコードを加えます．
Private Sub CB_Excel_Click() 'Excel を表示する excelapp.Visible = True End Sub |
VisibleプロパティはExcelアプリケーションの属性です．
このプログラムでは「Excelを表示」ボタンをクリックしてExcelを表示した後で，Excelを先に閉じてからプログラムを閉じようとするとOLEリンクエラーが発生します．
このプログラムは実用に供されるときには，「Excelを表示」ボタンが取り外されるという前提で作られています．
このボタンの目的は開発者が動作を確認するのに便利だという理由で取り付けられています．
Excelを表示したときは，先に給料フォームを閉じるようにしてください．
操作性の向上のために
給料管理システムで操作性を向上させるための処理を，「給料」テキストボックスのKeyPressイベントで行っています（リスト8）．
リスト8：操作性に工夫
Private Sub TEXT_kyuryo_KeyPress(Index As Integer, KeyAscii As Integer)
'Enterキーが押されたら計算を実施
If KeyAscii = 13 Then
TEXT_kyuryo_LostFocus Index
End If
'数値以外の入力はビープをならして拒絶する
If Not ((Asc("0") <= KeyAscii And KeyAscii <= Asc("9")) Or KeyAscii = 8 _
Or KeyAscii = Asc("-") Or KeyAscii = Asc(".")) Then
KeyAscii = 0
Beep
End If
End Sub
|
たとえば次のコードは，Enterキーを押したときにも自動的に計算を行うために用意しています．
If KeyAscii = 13 Then
TEXT_kyuryo_LostFocus Index
End If
|
このプログラムでは実装していませんが，Enterキーを押したときに，フォーカスが自動的に次のフィールドに移動したり，矢印キーでフォーカスが移動できるようにすると，さらに使いやすくなります．
矢印キーが押されたことを検出するにはKeyPressイベントではなく，KeyDownイベントで行います．
給料テキストボックスのデータは数値型であるため，文字列を入力するとエラーを発生します．
次のコードでは数値か，それに関する記号以外のデータが入力されたとき，入力を取り消します．
If Not ((Asc("0") <= KeyAscii _
And KeyAscii <= Asc("9")) _
Or KeyAscii = 8 _
Or KeyAscii = Asc("-") _
Or KeyAscii = Asc(".")) Then
KeyAscii = 0
Beep
End If
|
新規データを追加する機能の実装
次のコードは「新しいデータの入力」ボタンを押したときに，別のフォームである新規作成フォーム「Date_dlg」（図8）を呼び出すように指示しています．
Private Sub CB_shinki_Click() '「新規ダイアローグ」フォームを表示 Date_dlg.Show End Sub |
図8：新規追加フォーム
|
新規追加フォーム
コードはフォームのLoadイベントに記述されており，フォームをロードした後，最初にテキストボックスに表示する年と月の設定を行っています．
これにより，登録してある最後の月の翌月の年と月がデフォルトで表示されます（リスト9）．
リスト9：新規登録
Private Sub Form_Load()
Dim here, nenone As Integer, tukione As Integer
Date_dlg.Show
If Kyuryo!DATA_kyuryo.Recordset.RecordCount > 0 Then
here = Kyuryo!DATA_kyuryo.Recordset.Bookmark
'最新のデータがあるレコードに移動
Kyuryo!DATA_kyuryo.Recordset.MoveLast
'12月だったら1月をセット
If Kyuryo!DATA_kyuryo.Recordset("tuki") = 12 Then
nenone = 1
tukione = -11
Else
nenone = 0
tukione = 1
End If
'最終の月の翌月をフォームに表示
TEXT_date(0) = Kyuryo!DATA_kyuryo.Recordset("nen") + nenone
TEXT_date(1) = Kyuryo!DATA_kyuryo.Recordset("tuki") + tukione
Kyuryo!DATA_kyuryo.Recordset.Bookmark = here
Else ' データがない時は今月を表示
TEXT_date(0) = Year(Date)
TEXT_date(1) = Month(Date)
End If
'カーソルを「月」フィールドに設定
TEXT_date(1).SetFocus
End Sub
|
OKボタンの追加
既存の月が指定されると，メッセージを出して処理をキャンセルします．
この処理を省略した場合，すでに存在する年月が指定されると，テーブルのキー違反が発生します．
リスト10：OKボタン
Private Sub CB_ok_Click()
Dim here
'「給料」データコントロールにレコードがあるときは
'月がダブっていないか確認する
If Kyuryo!DATA_kyuryo.Recordset.RecordCount > 0 Then
'元のレコード位置を記録
here = Kyuryo!DATA_kyuryo.Recordset.Bookmark
'同じ月がないか検索を実施
Kyuryo!DATA_kyuryo.Recordset.FindFirst _
"[nen] = " & TEXT_date(0) + " and [tuki] = " & TEXT_date(1)
'該当の月が見つかったら，メッセージを出し，処理を終了
If Kyuryo!DATA_kyuryo.Recordset.NoMatch = False Then
Kyuryo!DATA_kyuryo.Recordset.Bookmark = here
MsgBox "対象にする年と月が重複しています"
TEXT_date(1).SetFocus
Else
'新しいレコードを作成する
Kyuryo!DATA_kyuryo.Recordset.AddNew
'リンクのための従業員IDを代入
Kyuryo!DATA_kyuryo.Recordset("jugyoin_id") = _
Val(Kyuryo!LABEL_jugyoin(0))
'年月を代入
Kyuryo!TEXT_kyuryo(0).Text = TEXT_date(0).Text
Kyuryo!TEXT_kyuryo(1).Text = TEXT_date(1).Text
Unload Me
Kyuryo!TEXT_kyuryo(2).SetFocus
End If
Else
Kyuryo!DATA_kyuryo.Recordset.AddNew
Kyuryo!DATA_kyuryo.Recordset("jugyoin_id") = _
Val(Kyuryo!LABEL_jugyoin(0))
Kyuryo!TEXT_kyuryo(0).Text = TEXT_date(0).Text
Kyuryo!TEXT_kyuryo(1).Text = TEXT_date(1).Text
Unload Me
Kyuryo!TEXT_kyuryo(2).SetFocus
End If
End Sub
|
「キャンセル」ボタンの追加
コードを以下に記します．
Private Sub CB_cancel_Click() Unload Me Kyuryo!TEXT_kyuryo(2).SetFocus End Sub |
まとめ
今回紹介したのが，Visual BasicによるOLEオートメーションプログラムの典型的なスタイルかは判断しかねます．
しかし，他の市販アプリケーションとの間で，プロセス間通信をしながらデータを操作をすることは，他にも色々な応用法が考えられます．
Microsoft Wordや一太郎の印刷機能を使い，非定型の文書にデータを送信して，書類を作成するような操作も可能です．
また，プログラムのフォーム上に埋め込んだOLE部品と組み合わせれば，別の可能性も生まれてきます．
しかし，今の時点ではまだ，意外と応用を考えにくいという印象があるのも事実です．
市販アプリケーションというのは，本来ユーザーが直接操作するのが目的であって，開発ツールから使用されることを前提としているわけではありません．
もし，開発部品として利用されることを前提として作成されるならば，その設計スタイルは大幅に変るはずです．
実際に市販アプリケーションをOLEサーバーとして利用するプログラムを使っていると，アプリケーションをコンポーネントとして使っているというよりも，アプリケーションの機能の一部を使わせてもらっているという印象を持ちます．
給料管理システムでは，Excelの膨大な機能のうち，計算機能を使っているだけです．
にもかかわらず，そのためにExcelのような巨大なプログラムをロードする必要があります．
例えば，Excelが機能単位でDLLに分割されていて，必要な時に必要な機能だけ呼び出せるようになっていれば，ずっと効率のいいプログラミングができるはずです．
そして，Excelのワークシートをエンドユーザーが編集するときには，Excel本体を起動して編集できればいいわけです．
いずれにしても，OLEオートメーションのサーバー機能を持ったアプリケーションや，市販のOCXができるだけ多く市場に出回ることが，開発者にとって，OLEが真に魅力的なコンポーネントのバインドテクノロジーになる条件でしょう．
|
サンプルデータの利用方法
サンプルプログラム「給料管理システム」は，Windows95あるいはWindowsNT3.51上のVisual Basic ver4.0 standerd editionで編集可能です．
本誌Vol4に付属していたトライアル版でもロードできるはずです．その他に正常にインストールしてあるMicrosoft Excelが必要です．
OLEサーバーとして自動的に起動するにはレジストリーへの登録が必要です．プログラムファイルと，DLLをコピーしただけですと，アイコンから起動することはできても，OLEオブジェクトの作成に失敗します． |
給料管理システムの給料計算のロジックは「Microsoft Excel5.0ビジュアル活用法」志村修一著 日経BP出版センター，を参考にしました．
VB Magazine ライブラリ | Visual Basicコースホームページ
int21 ホームページ | PCDN ホームページ
