Microsoft KB Archive/126385

= Microsoft Knowledge Base =

XL: GoalSeek Method Result Incorrect with Custom Function
Last reviewed: June 12, 1997

Article ID: Q126385

5.00 5.00c 7.00 7.00a | 5.00 5.00a

WINDOWS              | MACINTOSH kbprg kbcode kbmacro

The information in this article applies to:


 * Microsoft Excel for Windows 95, versions 7.0, 7.0a
 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows NT, version 5.0
 * Microsoft Excel for Macintosh, versions 5.0, 5.0a

SYMPTOMS
In Microsoft Excel, when you use the GoalSeek method in a Visual Basic procedure, if the specified target cell contains a custom function, the GoalSeek method does not return a correct answer to the cell that is changed.

CAUSE
This behavior occurs only when the target cell (the cell that contains the formula) that you use the GoalSeek method with contains a custom function. For example, if you run a Visual Basic procedure that contains the following command

Range("A3").GoalSeek goal:=50, changingcell:=Range("A1") and cell A3 contains the formula

=MySum(A1,A2) where MySum is a custom function, the value returned to cell A1 is incorrect.

WORKAROUNDS
To work around this problem, use any of the following methods.

Method 1: Use the ExecuteExcel4Macro method with the GOAL.SEEK function to

return the correct results. In the above example, you can use the following in a Visual Basic procedure to return the correct result to cell A1:

ExecuteExcel4Macro "GOAL.SEEK(""R3C1"",50,""R1C1"")" Method 2: Avoid using a custom function in the target cell (the cell that

contains the formula) that you specify using the GoalSeek method. Method 3: Use the GoalSeek dialog box, available by choosing GoalSeek from

the Tools menu.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.