I've made a simple function which reads a string from an Excel cell and converts it into a formula:
Function f(x As Double) As Double
Dim q As String
q = Cells(2, 1)
formula = Replace(q, "x", x)
f = Evaluate(formula)
End Function
It works just fine when used in Excel. If, for example, the text in the cell (2, 1) is "x^2-6*x+15", the command "=f(1)" returns 10. However, when I try to use this function inside a sub, it returns a type mismatch error. I've tried to use this function in a following code for golden section search:
Sub goldenSectionSearch()
Dim k As Double, a As Double, b As Double, epsilon As Double, x1 As Double, x2 As Double
k = (Sqr(5) - 1) / 2
a = Cells(2, 2).Value
b = Cells(2, 3).Value
epsilon = Cells(2, 4).Value
x1 = b - k * (b - a)
x2 = a + k * (b - a)
Do While Abs(b - a) > epsilon
If f(x1) > f(x2) Then
a = x1
x1 = x2
x2 = a + k * (b - a)
Else
b = x2
x2 = x1
x1 = b - k * (b - a)
End If
Loop
Cells(6, 2).Value = (a + b) / 2
Cells(7, 2).Value = f((a + b) / 2)
End Sub
After running the code line by line I seem to have pinpointed where the issue is, it happens right after the If, at the "a = x1" line. I'm afraid my lack of experience doesn't allow me to deduce why it's happening though.