VBA solver doesn't run but 'steps into' custom function

Hello everyone,

Solving my problem will require digging into several layers of issues, so I really appreciate your efforts should you decide to help. I have not used solver in VBA before but due to the amount of recalculations I have to do I must this time. The 'generic' loop is the following:

Option Explicit
Sub calc_implied_vol()
Dim calc_cell As Range, act_data As Range, imp_vol As Range
Dim i As Integer, t As Integer


Set calc_cell = Range("d13")
Set act_data = Range("d6")
Set imp_vol = Range("d20")


For t = 0 To 5
    For i = 0 To 20
    SolverReset
    SolverOptions Precision:=0.0001
    SolverOk SetCell:=Range(calc_cell).Offset(t, i), ValueOf:=Range(act_data).Offset(t, i), _
    ByChange:=Range(imp_vol).Offset(t, i)
    SolverSolve
    Next i
Next t


End Sub

If you look at the attached sheet, range D6:X11 contains market prices for options. In Range D13:X18, prices are calculated using my custom function/add-in. The custom function uses data in range d20:x25 as inputs. I would like the solver to match the calculated prices to market prices by changing data in range d20:x25. Here is the function, just in case: (i don't think it's relevant)

Function gk(CallPutFlag As String, Spot As Double, Strike As Double, _
Time_to_Mat As Double, domestic_rate As Double, foreign_rate As Double, Volatility As Double) As Double
Dim d1 As Double, d2 As Double
d1 = (Log(Spot / Strike) + (domestic_rate - foreign_rate + Volatility ^ 2 / 2) * Time_to_Mat) / (Volatility * Sqr(Time_to_Mat))
d2 = d1 - Volatility * Sqr(Time_to_Mat)
If CallPutFlag = "Call" Then
gk = Spot * Exp(-foreign_rate * Time_to_Mat) * Application.NormSDist(d1) - Strike * Exp(-domestic_rate * Time_to_Mat) * Application.NormSDist(d2)
ElseIf CallPutFlag = "Put" Then
gk = Strike * Exp(-domestic_rate * Time_to_Mat) * Application.NormSDist(-d2) - Spot * Exp(-foreign_rate * Time_to_Mat) * Application.NormSDist(-d1)
End If
End Function

When running the solver sub, the code doesn't execute but steps into the function when the code gets to -->SolverOptions. What am I overlooking? Am I missing several steps? As always, any input and guidence is greatly appreciated.

thx
milyenabox

Re: VBA solver doesn't run but 'steps into' custom function

does anyone have any suggestions on how to proceeds?

Re: VBA solver doesn't run but 'steps into' custom function

yes, that was the first thing I checked when I tried to figure out the issue...

It must be something with the syntax...

I think because the cells contain the 'gk' custom function instead of formulas the syntax is not properly written....

I am sure we can crack this...help me out fellas

Re: VBA solver doesn't run but 'steps into' custom function

just bumping it up so people will see it..don't want this thread to sink. I need to find a solution to my problem, so hopefully someone can help. thanks

Re: VBA solver doesn't run but 'steps into' custom function

Thanks for the response Andy.... one can always count on Andy.

But unfortunately, eventhough the code does execute the routine none of the values are changing. Without going into 'option pricing and other fun stuff', in a nutshell this is what should happen:

All the values (implied volatilites) in range D20: X25 should be changed by the solver so all values in range D6:X11 and D13:X18 match. In other words D6 should equal D13, E6 should equal E13 etc...but it's not happenning. I am not sure what the solver does when it runs.

Any ideas?

Let me know if you need more info.

Re: VBA solver doesn't run but 'steps into' custom function

it runs smoothly now, although it takes some time to finish, it works properly.
it looks like I did not set a number of options for the solver, I will look into these as I don't know what each represent.

as always, I appreciate the time you spent on this...I am very grateful