Frustrating VBA Goal Seek method (2003)

Topic

New Reply

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!

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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:=xlR1C1) & Chr(34)

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

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

Reply | Quote

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!

Reply | Quote

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:

Range("rChangeCell").parent.select

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:

= Sheet1!A1

or if named

 =rSetCell 

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

Steve

Reply | Quote

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 I get in discovering new tricks in excel…
Thank you so much!

Reply | Quote

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

Steve

Reply | Quote

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

Steve

Reply | Quote

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 I get in discovering new tricks in excel…
Thank you so much!

Reply | Quote

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:

Range("rChangeCell").parent.select

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:

= Sheet1!A1

or if named

 =rSetCell 

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

Steve

Reply | Quote

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!

Reply | Quote

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.

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:=xlR1C1) & Chr(34)

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

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote