Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Frustrating VBA Goal Seek method (2003)

    The workbook I'm fighting with displays all the steps of a fund value by computing a dozen of monthly complex operations during say 100 years.
    I can manually use "goal seek" in order to guess the initial contribution in order to get a precise amount at a given year.

    When I manually use goal seek, the target cell (goal) is perfectly reached.
    When I use a macro with exactly the same parameters, the iterations apparently stop a little bit before reaching the set goal! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    If I compare the manual and the macro results, the difference of the final value observed in the changing cell is not that big (about 3 per thousand) but it is big enough to provide me with a very wrong result regarding the goal...

    What I would like to understand is the difference between the VBA "goal seek" method and the "Goal seek" tool.
    Is there for example an iterations default setting I missed somewhere?

    Thanks for your help

  2. #2
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frustrating VBA Goal Seek method (2003)

    Hello Dominik!

    One idea to solve this problem has to do with some properties to be found in Extras/OptionsCalculation. There is a CheckBox Iteration. Try to change this status. There you also see the max. allowable number of iterations and a value for max. change. I guess, you use the standard values (iterations: 100, max. change 0.001) Try to increase the iterations and/or decrease the max. change value.

    Hope this helps.

    Many greetings, Porley

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frustrating VBA Goal Seek method (2003)

    Thanks for your quick reply Porley,

    I "played" with this boxes, checked either unchecked, I increased the iterations number till 500, I decreased the maximum change with a lot of zero.... before the 1.

    What I got is only an extended time of calculation, but strangely the result sI obtained were still EXACTLY the same. I mean the difference between the manual and the macro-driven goalseek was still the same as each way provided me with the same results. I just needed some more time to get them...

    I even observed ( under screenupdating=true) the goal cell. It was changing at each iteration from a very far value to a close value of the one I was expecting. In other words, I was not getting closer of the expected result when I was increasing the number of iterations or decreasing the maximum change....

    I'm still thinking that for some (unfortunately unknown to me) reason the manual and macro-driven goalseek do not behave the same way. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Frustrating VBA Goal Seek method (2003)

    Microsoft reports that this is a problem with earlier versions (though XL2003 is not mentioned) and they report a "workaround" using the "Application.ExecuteExcel4Macro command " instead of the GoalSeek method in VB.

    Steve

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frustrating VBA Goal Seek method (2003)

    Thanks a lot sdckapr (btw all your posts are extremely valuable)!

    I feel less crazy now.
    Anyway I'm not sure it will be easy to mix excel4 syntax and the way to mention references in VBA...

    I am afraid that I have to convert my (named ranges) variables in hardcoded cells ref....
    Thanks again Steve

    I would not like to abuse the kindness of the loungers...
    but i'd appreciate if possible extra-help on converting the following in "excel4 code environment" because my first attempts resulted in error messages such as "Compile error expected: end of statement"

    Here below an example of my difficulties:
    (FYI, the constant feature is that the name of the cell to be the goal of goal seek is always named "C_target")

    MyWB = ActiveWorkbook.Name
    GoalCellAddress = Range(MyWB & "!" & "C_Target").Address

    Does somebody knows if excel4 is able to understand the variable GoalCellAddress such as defined in the last line above?

    Thanks to all

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Frustrating VBA Goal Seek method (2003)

    I am not sure which of the "3 variables" the "GoalCell Address is supposed to be and you didn't list the other ones so I just redefined them all.

    Assume (change as desired)
    "Set Cell" is in the cell named "rSetCell"
    "Value" is in a cell named "rValueCell"
    "ChangeCell" is in a cell named "rChangeCell"

    This macro will run the xl4Macro, grabbing the items.

    <pre>Option Explicit
    Sub GoalSeekDemo()
    Dim sSetCell As String
    Dim sChangeCell As String
    Dim sValue As String

    sSetCell = Chr(34) & _
    Range("rSetCell").Address(ReferenceStyle:=xlR1C1) & Chr(34)
    sValue = CStr(Range("rValueCell").Value)
    sChangeCell = Chr(34) & _
    Range("rChangeCell").Address(ReferenceStyle:=xlR1C 1) & Chr(34)

    Application.ExecuteExcel4Macro _
    "GOAL.SEEK(" & sSetCell & "," & _
    sValue & "," & sChangeCell & ")"
    End Sub</pre>


    I think the setcell and changecell must both be on the activesheet when the macro is run. The value cell does not.

    Hope this helps,
    Steve

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frustrating VBA Goal Seek method (2003) -

    <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    It works exactly as you described it: all the named cells have to be on the active sheet except the value one.
    This is a little problem for me as the set cell and the change cell were on different sheets, but it is easy to create a formula to work around that.
    I could not believe you would manage so quickly to provide me with the right code to communicate with excel4!
    Steve, thousand thanks!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Frustrating VBA Goal Seek method (2003) -

    I am not sure what workaround you did. But here is a "relatively simple way"

    Since the value can be anywhere and the SetCell is a formula, the active sheet just needs to the the cell with the change cell. Then you can use (on the same sheet with the change cell a cell that references what you want to be the "set Cell" on a different sheet and create a setcell on the active sheet:

    for example if you want:
    SetCell = Sheet1!A1
    Value = Sheet2!A2
    ChangeCell = Sheet3!A3

    When you run the macro the active sheet should be "Sheet3" (you could use in the code:
    <pre>Range("rChangeCell").parent.select</pre>

    before the "Application... " line, to ensure that it is the activesheet)

    Instead of using the Setcell as the reference you use, use a cell on Sheet3 (eg Sheet3!A1) and in that cell use the formula:
    <pre>= Sheet1!A1</pre>

    or if named
    <pre> =rSetCell </pre>


    so then all can be on different sheets and you will automatically (using the parent property) have the right active sheet.

    Steve

  9. #9
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Frustrating VBA Goal Seek method (2003) -

    Steve, you're spoiling me!

    Exactly what I was needing even without asking!

    "How to grasp an excelsheet just knowing the name of a cell somewhere on it..."

    I confess I'm sometimes ashamed of the pleasure <img src=/S/joy.gif border=0 alt=joy width=23 height=23> I get in discovering new tricks in excel...
    Thank you so much!

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Frustrating VBA Goal Seek method (2003) -

    You are very welcome. Happy to be able to help.

    Didn't you know that become a WMVP you must have a little bit of "psychic ability" especially precognition. It helps to answer questions <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •