Excel のユーザー定義関数でソルバーが動作しない

多くの変数がある中での最大最小値計算にソルバーを利用しています．
各変数およびその制約条件が縦に並び，数字違いが横に並んでいます．

1 列毎に最大値・最小値を計算したいのですが，ソルバーですと 1 条件毎 (1 列毎) に条件をハンドで入れ直さねばならないですよね...
そこでユーザー定義関数内にソルバーを組み込み，各変数とその制約条件はユーザー関数の引数として指定という形にして，行全体に数式をコピーすれば結果を求められるようにしたいです．

VBA は全くの素人で，まずは簡単なものでと思い以下のようなユーザー定義関数を作成しましたが，エラーこそ出ないものの変数セル (D6, D7) が初期値のままで全く変化しません (ソルバーが動作している気配無し，関数の戻り値も初期値によって計算された値)．
何が問題かアドバイスをお願いします．

------------------------------------------------------------------------------
Function Test1(Target As Range) As Double

SolverReset

SolverAdd CellRef:="$D$6", Relation:=1, FormulaText:="$D$2"
SolverAdd CellRef:="$D$6", Relation:=3, FormulaText:="$D$3"
SolverAdd CellRef:="$D$7", Relation:=1, FormulaText:="$D$4"
SolverAdd CellRef:="$D$7", Relation:=3, FormulaText:="$D$5"
SolverOk SetCell:=Target.Address, MaxMinVal:=1, ValueOf:=0, ByChange:="$D$6:$D$7", Engine:=1, EngineDesc:="GRG Nonlinear"

SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1

Test1 = Target.Value

End Function
------------------------------------------------------------------------------

D9 セルにこのユーザー定義関数を入力 (=Test1(D10))，
D10 セルには D6, D7 を使用した数式が入っています．

No.1 ベストアンサー 回答者： fujillin 回答日時： 2022/09/06 12:41

こんにちは

ソルバーは利用していないので、推測での回答になってしまいますが・・

「ユーザ定義関数」の場合、（ソルバーに限らず）他のセルの値を変更することはできないように制限がかけられています。
（シート関数は基本的に、設定されたセルに値を返すものなので・・）

ソルバーの場合、直接には値を変更してはいませんけれど、間接的に値を変更することになるので、（多分）実行を停止しているのではないかと推測します。
（VBAのエラーにはならないようですが・・）

試しに、ご提示のコードはそのままで、以下のマクロを実行すると結果が得られませんか？
（ご提示のコードに誤りがないことが条件になります）
Sub Test1Test()
　Range("D9").Value = Test1(Range("D10"))
End Sub

もしも結果が得られるようであるなら、上記の推測が原因と考えられます。
対策としては、「ユーザ定義関数」を諦めて、通常のマクロから実行するということになると思われます。
もしも、関数のように即時に結果が反映されることをお望みであるなら、シートのChangeイベントなどで、条件となるセルの変更を検知して実行するような仕組みにしておけば、ほぼ同様の効果を得られるのではないかと推測します。
ただし、ソルバーの場合、セルの値がいろいろ変化するので、イベントの制御をきちんと行わないと危険ですけれど。


余談ですが・・
はっきりとはしませんが、条件設定の際に、順序が影響するような感じがします。
SolverOk よりも前に、SolverAdd で Relation:=4 を設定すると、その条件だけ無視されるということがテストしていて発生し、はまりました。
（ByChange のセル範囲を整数に指定しようとしたのですが・・）

同じセルに対する、他の数式での条件はちゃんと設定できるのですが。
あまり、いろいろと試したわけではないのでハッキリとはしませんけれど、上記に関しては順序を変えることで解決しました。

この回答へのお礼

ご回答ありがとうございます！
まさにご指摘の通りで、ご提示いただいたマクロを走らせたところ意図通りにソルバーが動作しました。根本的な制約を理解できていませんでした。。

別のやり方を考えようと思います。自分ではなかなか解決に至りそうになかったので助かりました。ありがとうございました。

お礼日時：2022/09/06 23:57