How to bullet proof my goal seek macro?

[MagnusNovak]

Hi there,

I have a pretty simple macro in my excel file that runs a few basic goal seeks. Unfortunately, this code has a tendency to crash frequently, especially when the user's inputs are too off and/or when the result is difficult or impossible to find for Excel. Usually what happens is that the macro starts running and crashes Excel. I've tried adding an "EnableCancelKey = xlErrorHandler" to give a way out for users when the macro crashes but it does not work every time.

Private Sub Goal_Seek()
Dim T As Long

Application.EnableCancelKey = xlErrorHandler
Application.ScreenUpdating = False
On Error GoTo errHandler

T = Range("purchase_price")

Range("COC_value").GoalSeek Goal:=Range("COC_target"), ChangingCell:=Range("purchase_price")
Range("COC_result") = Range("purchase_price")

Range("CF_value").GoalSeek Goal:=Range("CF_target"), ChangingCell:=Range("purchase_price")
Range("CF_result") = Range("purchase_price")

Range("IRR_value").GoalSeek Goal:=Range("IRR_target"), ChangingCell:=Range("purchase_price")
Range("IRR_result") = Range("purchase_price")

Range("MIRR_value").GoalSeek Goal:=Range("MIRR_target"), ChangingCell:=Range("purchase_price")
Range("MIRR_result") = Range("purchase_price")

Range("purchase_price") = T

Application.EnableCancelKey = xlInterupt
Application.ScreenUpdating = True

exitHandler:
    Application.EnableCancelKey = xlInterupt
    Application.ScreenUpdating = True
    Exit Sub
errHandler:
    If Err.Number = 18 Then
    Range("purchase_price") = T
    Resume exitHandler
    Else
    MsgBox "The Maximum Offering Price tool can't run properly under the proposed acquisition and operating parameters. Please review them and make the necessary adjustments before running the tool again."
    Range("purchase_price") = T
    Resume exitHandler
    End If
End Sub

To give some context, the macro is trying to assess the maximum purchase price based on certain targeted return indicators (COC, CF, IRR, MIRR) provided by the user.

So my questions:
- Is there a more "solid" approach to achieve the same result without using .GoalSeek?
- What could I do to make that code less subject to crash? How can I better handle crashes when they occur?
- Anything else I could do to improve the code?

Thanks!

[Cobra17]

When I do a goalseek like you've done, I set the changingcell (purchase_price in your case) to 0 before I run the the rest of the code. I haven't had any problems with them crashing. Since you keep using 'purchase_price' you might want to zero it out before each goalseek.

[MagnusNovak]

When I do a goalseek like you've done, I set the changingcell (purchase_price in your case) to 0 before I run the the rest of the code. I haven't had any problems with them crashing. Since you keep using 'purchase_price' you might want to zero it out before each goalseek.

Thanks, that's a good idea. Might help a bit with the infinite loops. I think the main issue remains when other parameters of the model are too inconsistent for excel to return a result. In this case, the routine starts crashing.

Something I have noticed too with Error Handling is that VBA will tend to catch the first error and handle it properly, but the next one it gets all tripped up. Not sure if that is the problem you are having, but it might be. You can create a function that will perform your goal seek and within that function, have your error handling. This way, each time the function gets called, the error memory is cleared. Works better for me anyways when I do that.

Thanks, I'm not sure to fully follow your approach. What would the macro look like then?

[thomglea]

Something I have noticed too with Error Handling is that VBA will tend to catch the first error and handle it properly, but the next one it gets all tripped up. Not sure if that is the problem you are having, but it might be. You can create a function that will perform your goal seek and within that function, have your error handling. This way, each time the function gets called, the error memory is cleared. Works better for me anyways when I do that.

[MrShorty]

From what I understand, Excel's built in Goal Seek uses a fairly basic Newton-Raphson or secant type of root finding algorithm. I cannot think of any universal, "magic" tricks to make the algorithm foolproof. Things that I think of to do:

1) Do you know enough about the behavior of your functions to be able to test the quality of an initial guess before executing the goal seek?
2) Do you know enough about the behavior of your functions to be know when there is no solution to be found before executing goal seek?
3) Are you required to use the built in goal seek utility? NR or secant for one variable problems are not difficult to program from scratch. I have some ideas here: https://www.excelforum.com/tips-and-...ind-roots.html One advantage I find to programming my own root finding algorithms is that I have better control over the termination of the algorithm and why the algorithm terminated (beyond some kind of "goal seek crashed" termination).

You haven't shared any of the details of your financial/mathematical model, so I cannot look at the details, but maybe something there will prompt some ideas for you to explore.