[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. [ツール]-[ゴールシーク]メニューから呼び出される ダイアログを使用します。