[XL]VBAでGoalSeekメソッドを使用すると正しい収束値が得られない

詳細

現象

Range オブジェクトの GoalSeek メソッドを実行する際、「対象となるセル」に
ワークシートの値に依存したユーザー定義関数が入力されている場合は正しい
収束値が得られないことがあります。
たとえば、次のような手順を行うと問題が発生します。

手順

1. マクロをモジュールシートに記述します。

        Function aa(aaa)
            aa = aaa * 10
        End Function
        'ゴールシーク [数式入力セル]:$A$1, [目標値]:100,[変化させるセル]:$B$1 を
        '実行
        Sub RunGoalSeek()
            Worksheets("Sheet1").Range("A1").GoalSeek Goal:=100, _
                ChangingCell:=Range("B1")
        End Sub
   

2. Sheet1 のセル A1 に数式「=aa(B1)+B1 」を入力します。
3. Sheet1 のセル B1 に数値「 5 」を入力します。
4. 手順 1. で作成したマクロ RunGoalSeek を実行します。
5. 実行結果は次のようになります。

       セル A1 の値   -18454882.6
       セル B1 の値    -1677716.6
   

6. 手順 1. で作成したマクロ RunGoalSeek と同じ作業を、[ツール] - [ゴールシーク] コマンドで実行します。

        数式入力セル    ： $A$1
        目標値          ： 100
        変化させるセル  ： $B$1
   

7. 実行結果は次のようになり、手順 5.とは異なった値となります。

        セル A1 の値   100
        セル B1 の値     9.090909091
   

状況

この現象は Excel 5.0 側で、セルの値を直接参照しているユーザー定義関数がどの
ような値を返してくるか判断できないために発生します。

回避策

以下のいずれかの方法で回避が可能です。

1. ExecuteExcel4Macro メソッドを使用し GOAL.SEEK 関数を呼び出します。 以下の例のような記述で、正しい結果を得ることができます。

        ExecuteExcel4Macro "GOAL.SEEK(""R1C1"",100,""R1C2"")"
   

2. GoalSeek メソッドで使用するセルにユーザー定義関数の使用を避けてください。
3. [ツール]-[ゴールシーク]メニューから呼び出される ダイアログを使用します。