[Help] Advanced Goal Seek in VBA

[SaintTrigger]

I have a pretty complicated spreadsheet that makes use of a whole bunch of different macros I have written. Right now, I solve my spreadsheet using a simple bisection function that I wrote. However, this takes much longer than I would like it to. Thus, I would like to try to take advantage of Excel's Goal Seek. However, the reason I did not use it in the first place is because once the cell in question is changed, a macro must be run in order to determine the outcome of the variable change.

So, goal seek works like (set "x" = "y" by changing "z").

In my case, "x" is the difference between my spreadsheet value and the value I am looking for. I want this to be close to 0 ("y"). As stated before, the problem is that, once "z" is changed, I need to run a macro to actually determine "x".

So I am wondering if there is a way to call a macro from within goalseek. And if not, does anyone know if I can access the goal seek macro and insert a call function in it (and name it something else, of course).

 

[mikerickson]

If given a "z", your macro produces an "x", then it can be cast as a UDF.

Putting that formula in a cell will give GoalSeek something to work on.

If your UDF is continuous, there's a good chance that GoalSeek will find a solution.

 

[tusharm]

It would seem that the more straightforward way would be to use a function (also called a User Defined Function, or UDF) to compute x as a function of z.

Essentially, in the cell with the x value, you should have something like =MyFunction({cell with the z value}, {other cells if relevant})