looping of Solver in VBA

[bored]

hi, i have some questions with regards to the above.

i am able to record a macro while running the solver manually, and it is not too difficult understanding the codes. however, i do not know how to modify it to fit the following scenario:

- sheet1 contains the model function that i want to fit, with the model outputs in M1:M10

- sheet 2 contains the data points that i want to optimise against. for instance, my data is divided into sets of 8, whereby each set occupies 8 vertical cells, ie A1:A8 is 1 set, B1:B8 is the next set, and so on, until XFD1:XFD8, then the next set would occupy A9:A16, and so on.

- the quantity that has to be minimized is the sum of the squared residues between the model and data, and this quantity is placed in a cell in sheet1 (together with the model), say on M11

- there are 8 parameters to be optimised. lets call these p1, p2...p8

my questions are, how do i make the solver run iteratively (i suppose using a Do Until loop) through all the data sets on sheet2, and then output the parameters on separate sheets (ie p1 on sheet3, p2 on sheet4 etc)?

on a side note, i realise that it takes about a second for 1 solver run, when executed manually. can i linearly extrapolate this to say that it takes 1 hr to run solver 3,600 times? if so, it is mightily slower compared to, say a C program that does the same thing.

thanks for any help!

[shg]

Nobody's likely to be able to help unless you post a workbook, including your code, and explain in context.

... if so, it is mightily slower compared to, say a C program that does the same thing.

Solver is a very capable product that can handle a wide variety of problems. If you can write a C-based DLL that does what you need faster, by all means do.

[bored]

hi shg,

pls take a look at the example i have enclosed here. these are not the actual model or data sets; it is just an example that i have come up with. however, it contains the essence of what i am trying to achieve.

the first sheet contains the model function that i am optimizing, with 5 adjustable parameters, p1 to p5. col C would have been filled with the data set and D with the squares of the residues if solver were called manually. in the case of automatic optimization, i don't think these 2 columns need to be filled, so as to save some computation time. however, i want to output the residuals squared into sheet D10 so that i can determine the goodness of fit for each data set.

sheet 'data' contains the mock-up data. column A is the x-axis of each data point, and it corresponds to the x-axis of the model. the remaining columns are data, divided into sets of 8 consecutive vertical cells. the last column is BG, after which it jumps to the next 8 rows.

sheets p1 to p5 are the optimized outputs. i am hoping to output them on separate sheets for easy downstream manipulation. also, i would like to output the parameters to correspond to the data sets ie for data sets running from B2 to BG9, i want the p1 to run from B2 to BG2, and so on.

thanks.

[shg]

See attached. A simple formula retrieves the data from the table, and the Solver model is set up.

So your code needs to change Grp and Set, run Solver, and copy Prams to wherever they go (not sure what you want).

Post back if you get stuck.

[bored]

i have looked through your spreadsheet, but i don't get what you are trying to show. i also cannot find any code when i opened the VB editor. am i missing something...?

i have managed to write the following:

Please Login or Register  to view this content.

i want to modify the above code thus:

instead of running through just 1 row of data, is there a way to jump to the next row when the last occupied column of the first row is reached? in my case, i would always know the number of col and row, so i can hard-wire these into the code. i just need some code to allow me to run through 1 row, then the next, etc.

thanks.

[bored]

i finally got it working after inserting another For loop to account for the rows. is there anyway to improve the code below?

on a further note, does the Premium Solver Platform developed by Frontline Systems speed up the solver significantly?

thanks!

Please Login or Register  to view this content.

[shg]

I'd do it like this:

Please Login or Register  to view this content.

[bored]

thanks, will look through that and get back if there is any problem.