Hi, I've posted this before, but I think due to my approach not many people were willing to contribute their time. This time I am trying a different approach to my problem, and I would greatly appreciate it if anyone could offer any advice or experience in similar problem.
Here is the previous thread: http://vbforums.com/showthread.php?t=487398 Caution: do not read into the details unless you are willing to sacrifice some serious brain cells to optical theory.
Basically, I have this function that uses a few parameters to describe real data, and I need to figure out some of those parameters to make the function fit the data.
Code:
Function Rpc(Lambda As Single, SubstIndex As Single, n As Single, FilmProperty As Variant)
'FilmProperty(2N) made of R(1), T(1), R(2), T(2), ..., R(N), T(N).
'R(I) = FilmIndex
'T(I) = FilmThickness
'The layers are numbered starting from the one most away from the substrate.
'MEDIUM INDEX = R(0)
ReDim R(1 To n), T(1 To n), X(1 To n), C(1 To n), S(1 To n) As Variant
    AA = 1 / SubstIndex
    BB = 1
    CC = 1 / SubstIndex
For i = 1 To n
        R(i) = FilmProperty(2 * i - 1)
        T(i) = FilmProperty(2 * i)
    X(i) = (2 * 3.141592654 * R(i) * T(i)) / Lambda
'CALCULATE SINE AND COSINE TERMS FOR MATRIX ELEMENTS
    C(i) = Cos(X(i))
    S(i) = Sin(X(i))
Next i
'SETUP MATRIX ELEMENTS
    B11 = C(1)
    B12 = S(1) / R(1)
    B21 = S(1) * R(1)
    B22 = B11
'CHECK FOR ONE FILM
If n = 1 Then GoTo 500
For i = 1 To n
    C11 = C(i)
    C12 = S(i) / R(i)
    C21 = S(i) * R(i)
    C22 = C11
    A11 = B11 * C11 - B12 * C21 'C11
    A12 = B11 * C12 + B12 * C22 'C12
    A21 = B21 * C11 + B22 * C21 'C21
    A22 = -B21 * C12 + B22 * C22 'C22
    B11 = A11
    B12 = A12
    B21 = A21
    B22 = A22
Next i
500 CA = (AA * B11 - B22) ^ 2 + (BB * B12 - CC * B21) ^ 2
    CB = (AA * B11 + B22) ^ 2 + (BB * B12 + CC * B21) ^ 2
    Rpc = (CA / CB)
End Function
Right now I'm doing this with Excel Solver, but hitting the limitation of excel's functions. How can I emulate this behavior in VB6, crunching through my function to make the output fit the data by modifying the input values.

this graph shows the raw data, and the curve generated by the above function, plotted through values adjusted for various losses.

The best I could think of was to search through all possible values withing a certain range, but that seems almost impossible since I've never done this before... should I really set up nested loops that will try to pick the best value by incrementing in a certain direction, depending on the trend of the curve?