変化させるセルが変化しない

質問

お世話になります。

マクロ側のソルバー機能を利用して係数を推定しようと考えています。
係数の置き場所はワークシートです。計算値、実測値も同様です。
ただし、ワークシートに打ち出す計算値は外部dllで行います。
コードの概要は以下の通りです。

sub cal
call 外部dll
end

sub optsolver
For i = 1 To 100
Call cal
SolverOk SetCell:="E18", MaxMinVal:=2, ValueOf:="0", ByChange:="D3:D4"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Next i
end

１回目のステップではワークシート上に計算値が得られるのですが、
２回目以降はセルの値（係数や計算値）が更新されませんし、結果として係数も推定できません。
100回くらい画面がちらついて、終わりの表示もなく終わります。

ここでいう係数と計算値は連動していないとソルバーは使えないと
どこかで聞いたことがあるのですが、このためでしょうか。
（連動って、設定の数値を変えれば自動で追随することだろうとおもいます。）

使用環境は
winxp pro sp2
excel 2003
です。

回答No.1

queuerev2 · ベストアンサー

状況が今ひとつはっきりわからないのですが、推定すると以下のようなことでしょうか？

・係数を求めたい。よってこれを変化させるセル(D3:D4)に割り当てた。
・計算値を実測値に近付けたいので、目的セル(E18)には計算値（あるいは計算値と実測値の近さを表す数値）が計算されるようになっている。
・計算値は外部dllを用いて係数から算出する。そのため、係数（変化させるセル、D3:D4）を変更しただけでは目的セル(E18)の値は更新されない。そこで、外部dll呼び出しとソルバーを交互に繰り返し実行する質問文中のコードを書いて実行してみたがうまくいかない。

もしそうであれば、そのとおりうまくいきません。
質問者様も書かれている通り、ソルバーが正常に動作するには「ここでいう係数と計算値が連動している」必要があり、そういう状態にできないのであれば値の計算とソルバーを交互に行ってもうまくいきません。
（連動の意味も質問者様の理解で合っていると思います）

部外者の立場としては、外部dllでの計算をワークシートの数式で実現できないのか、と聞きたくなりますがおそらくだめなのでしょうね。

現実的な解決策ですが、ユーザー定義関数を作り、その中で外部dllを呼び出すようにし、ワークシートの数式にそのユーザー定義関数を用いて化させるセル(D3:D4)から目的セル(E18)を算出するようにしてはいかがでしょうか。
（もちろん、外部dllの呼び出しとソルバーの交互繰り返し実行はやめます）

お礼

結論から言うと、完成しました！

私はqueuerev2様の回答で、世界が広がりました。結構悩んでいたので。
初めての投稿で返信がいただけるかどうか不安だったのですが、回答をいただける方に出会えたことを嬉しく思います。感謝です。

補足

queuerev2様が上3分の１くらいで述べられたことは、まさにその通りです。

>質問者様も書かれている通り、ソルバーが正常に動作するには「ここでいう係数と
>計算値が連動している」必要があり、そういう状態にできないのであれば値の計算
>とソルバーを交互に行ってもうまくいきません。

きっと評価関数が動けないと困るんでしょうね。

>部外者の立場としては、外部dllでの計算をワークシートの数式で実現できないのか、
>と聞きたくなりますがおそらくだめなのでしょうね。

おっしゃるとおりで、外部DLLは外せないですね。実はこの質問の意図は、エクセルのソルバーだけを利用したい、です。ですので、肝心の計算の中身はワークシートやマクロに記述せず外部DLLに集約させています。もちろんy=ax+bとかの簡易なモデル式ならばワークシートに直打ちした方が楽です。しかし、簡易でないときは面倒ですし、なによりもモデル記述の可能性を狭めてしまいますよね。これもまたエクセルの利用法の一つだと思うのです。