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.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, Page 213

For more information about the GOAL.SEEK function, choose the Search button in Microsoft Excel Macro Functions Help and type: 

   GOAL.SEEK

KBCategory: kbprg kbcode kbmacro
KBSubcategory:

Additional reference words: 5.00 5.00a 5.00c 7.00 7.00a
Keywords : kbcode kbmacro kbprg
Version : 5.00 5.00c 7.00 7.00a | 5.00 5.0
Platform : MACINTOSH WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 12, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.