Goalseek in VBA - Is it possible to see how many iterations were used?

**Belisartih** · 10-12-2014, 07:50 AM

Hey there,

i was wondering, if there is any way to see how many iterations were used when goalseek was invoked?
I am writing some routines to solve equation systems and wanted to compare them to the goal seek method.
Sure, i can do a comparison by calculation time, but I want to see how many iterations goal seek needed to solve the problem.
Of course I could set max iterations to 1 and count how many times, I have to restart goal seek to come ton a solution.
But this would be kind of messy.
Unfourtunately, as I see it, goal seek does not return any information on iterations used.

Looking forward to your replies,
Best regards,
Bel.

**fredlo2010** · 10-12-2014, 08:52 AM

Hi,

I am not sure that you can get the amount of iterations from the GoalSeek. What I can tell you is that using Excel's default features in faster than building your own.

I also noticed that there is a huge difference between the use of the GoalSeek in the Excel dialog box and using it through the VBA method; the latest is almost instantaneous.

Here is a small experiment I designed to check the speed. Note that the one hat uses the dialogbox needs a little bit of human intervention which adds a small error but the point is very clear.

These are my settings:

E4 355
E5 Nothing
E6 Formula bellow

Formula:

Please Login or Register  to view this content.

These are the procedures.

The one using the method:

Please Login or Register  to view this content.

The one using the Dialog:

Please Login or Register  to view this content.

My results were more or less the following:

Type	Time	Percent
No Dialog	0.03515625	0.753%
Dialog	4.63281250	99.247%

Thanks

**MrShorty** · 10-12-2014, 04:12 PM

Here's a thread I started with a similar question: http://www.excelforum.com/excel-form...ate-event.html I expect you could adapt this approach, especially if you are in VBA already, to count goal seek or Solver iterations.