VB6からEXCELマクロの終了を判断する方法を教えてください。

質問

VB6からEXCELマクロの終了を判断する方法を教えてください。

VB6とEXCEL(VBAも含む)を使って次のような流れの処理を行うソフトを作っています。
そこでどうしてもEXCELマクロの終了をVB6から判断する必要があり、方法を教えて頂けないでしょうか。

1)VB6でEXCELファイルを起動
2)VB6からセルA1に10,A2に20を代入
3)VB6からEXCELファイル内のマクロをコール
4)マクロはA1+A2の計算を行いA3に代入(30)
5)VB6からA3の値を読み込む

(実際にはマクロは複雑な処理をします。)

1)～5)の処理を5000回程度行います。
VB6から送られるデータは毎回変わり、EXCELとマクロで計算させたデータを回収してテキストファイルに書き出します。

一通りのプログラムは完成したのですが、計算したデータを見ると10+20が1.99999とか15.51414とかおかしい結果になっています。
もしかしたらマクロの実行が終わっていない段階でVB6がデータを拾ってしまっている？？と考え、マクロの終了をVB6で判断してからデータを回収するようにしたいと思っています。

何か方法はないでしょうか。（または他に間違っているところがあるのでしょうか。)
長文ですみません。よろしくご教示ねがいます。

回答No.4

Wendy02 · ベストアンサー

#2の回答者です。

あくまでも、Excelを扱う話の一般論です。

>ソルバーは利用せず、マクロ上では足し算のコードのみでも上記のような誤差は発生しています。
データ型はどうなっているのでしょうか？

Excelのワークシートというのは、あれはあれで、浮動小数点誤差の補正処理してのです。どこがというと、足し算・掛け算では、ほぼ演算誤差が出てこないのです。ところが、VB側で、Single, Double型にしていると、まず、数字そのものに、約半分は誤差が発生してしまいます。そこで、Long型、Integer型にすればよいのですが、そう勝手にいかないわけです。そこで、Currency やCDec 型を使いたいというののですが、それも状況にもよります。

そこで、基本的なことですが、あまり大きな数を扱いますと、オーバーフローになってしまいますが、桁を決めて10倍・100倍・1000倍にして、Integer で整数型にして計算するという方法があります。

もうひとつ、セルを使う場合などは、いちいち、Integer などを使わずに、整数法というテクニックがあります。代表的な関数では、Fixを使います。それを再び、数値として使うわけです。Rangeの .Text プロパティを使って、見かけの表示をそのまま値として使う方法もあります。

ここらのテクニックはたぶんご存知かもしれません。この話だけで解決するとは思いますが、これで話がうまく進まないようでしたら、具体的な内容を教えてください。

ところで、
>実はエクセルソルバーの高機能バージョンをVB6で作ろうとしています。
私にとっても、ソルバーは、食指の動く話ですが、Excelについているソルバーというのは、Ver.5マクロで出来上がっています。だから、もう15年前のものということになりそうです。それで、クレジットに入れるデジタル署名の部分だけ新しくして、新バージョンに入れているようで、Excel側も、もうそろそろ新しくしても良いと思うのですが、MS側は、自分では開発できないようです。

http://www.solver.com/
は、なかなか値段が高いですね。ご自分で作るなら、アルゴリズムなどは手に入れているわけですね。

それで、私も、自分で作ろうとして、Webサーチして、知ったことですが、このアルゴリズムというのは、「お金」になるのか、見つかりません。それで、いくつか、まとまった本が出ているのは知りました。だから、「買えば？」ということですが、1つのテクニックだけで、お金を投資する余裕がなくて、購入できない、というのが、今の私の現状です。それに、ある程度のことは、今のまま蓄積した知識で出来てしまうのです。

お礼

ご返答ありがとうございます。

いろいろ試して見ましたが、各計算の間に200msec程度のWAITを
かけて待ってみても結果は変わらず、まったく同じ誤差が再現しました。
ということはやはり浮動小数点誤差と考えるべきでしょうか。
(当たり前ですかね・・・)

桁を決めて10倍・100倍・1000倍というのもエクセルソルバーを作ると
いう観点では、大きい数値から小さい数字まで何が来るか判らないため、
桁を固定にすると不都合が生じそうです。ほんとにオールマイティに
作るというのは難しいです。

最適化については、コードは入手しておらず「これから」という感じです。
仕事でI-SIGHTなどの最適化ソフトを使用しておりました。
その関係でモンテカルロによる大域探索や遺伝的アルゴリズムなどの手法が
判りますのでなんとか自分でコード化できたらと思っています。

リンクを貼っていただいたSolverいいですね。どうしても有料というところで
つまづいてしますが(T_T)

長々と書いてしまいましたが、結論はやはり浮動小数点誤差のようですね。
変数型をうまく整理してコーティングするのはどうも苦手でして・・・
これを期にもう少ししっかり理解して解決策を探って見たいと思います。
ありがとうございました。

回答No.3

temtecomai2

原因が分かっていないなら、先に原因を突き止めたらどうでしょうか？
Excel 内のマクロに不具合がある可能性もあるのではないでしょうか。

というかマクロ部分は VB6 側に移植したほうがいいんじゃないでしょうかね。

お礼

temtecomai2様　ご回答ありがとうございます。

VBA側では、Range("A3").value=Range("A1").value+Range("A2").value
とだけ記載していますので不具合はなさそうです。

ただVB6側では変数と型が入り乱れており、不具合がある可能性もありそうです。
型を整理してプログラムを組むことがどうも苦手ですが、一度総チェックしてみたいと
思います。

今回はVB6でエクセルのソルバーを作ろうとしているため、エクセル内のシートの内容や
マクロの内容はそのままにしておきたいのです。

回答No.2

Wendy02

常識的には、ありえない話ばかりで、良く分かりません。
肝心なことが書かれていないようです。

>マクロの実行が終わっていない段階でVB6がデータを拾ってしまっている？？
印刷のように、背後で動くようなもの以外は、そういうことはありません。途中で取るというコードをいれているなら別ですが。

>EXCELマクロの終了をVB6から判断する必要があり、
VB6から、Excelに命令を送っても、通常、信号待ちになっているはずです。
平行で動くことはありませんね。

>1)～5)の処理を5000回程度行います。
2)からでは？　たかだか、5000回でも、1)の起動含めたら、Excel側のオーバーヘッドが多すぎて、時間が掛かってしまいます。

>計算したデータを見ると10+20が1.99999とか15.51414とかおかしい結果

少数点を使っているわけなら、浮動小数点誤差が発生しているわけですから、セルにいれた値なら、ROUNDや、TRUNC を入れてあげればよいけれども、もしかしたら、ソルバーを利用しているのでは？今のソルバーは簡単なものしか使えませんし、ソルバーの新版は有償ですし、お金を払うつもりがなければ、VB6で作るしかないと思います。

補足

Wendy02様　ご回答ありがとうございます。

確かにPCの処理上、平行で動くことはありえないはずなのですが・・・

>2)からでは？　たかだか、5000回でも、1)の起動含めたら、
>Excel側のオーバーヘッドが多すぎて、時間が掛かってしまいます。

すみません。書き違えましたが2)～5)の処理を繰り返し行っています。

>少数点を使っているわけなら、浮動小数点誤差が発生しているわけですから

いろいろと試してみた結果、浮動小数点誤差と思われる XXXXE-15 の誤差のデータも
ありましたが、中には0.097969413というそこそこ大きい誤差のものもありました。
(10+20が1.99999とか15.51414は言い過ぎでした。すみません！)

>もしかしたら、ソルバーを利用しているのでは？

ソルバーは利用せず、マクロ上では足し算のコードのみでも上記のような誤差は発生しています。
ここでの説明用に同じ動きをするプログラムを作ったのですが、再現しませんでした(T_T)
実はエクセルソルバーの高機能バージョンをVB6で作ろうとしています。原因はどうあれ、この誤差があると使い物にならず困りました・・・

回答No.1

ShowMeHow

エクセルマクロ側で終了フラグを立てる。
VB側でそれを確認して次の処理に入る。
具体的にどのようなフラグにするかはお好みですが
所定の場所に簡単なログファイル（テキスト）を作り
VB側で内容を確認するのが一番簡単かな。

補足

ShowMeHow様　ご回答ありがとうございます。

終了フラグで判断する方法であれば、解決できそうですね。
(私の言う「途中で値を拾う」が本当であればですが・・・)

ただ、今回エクセルソルバーの高機能バージョンをVB6で作ろうとしています。
エクセルでユーザ作った計算シートとマクロは変更せずに、VB6から値を送ったり読み取ったりして
最適解を求めるようにしたいのです。

説明不足で失礼しました。