Is there a VBA source for Levenberg-Marquardt ?

AVt · May 10th, 2003, 4:43 pm

It would save me typing it in

TIA.

audetto · May 12th, 2003, 8:24 am

I think that the Reference for Levenberg-Marquardt is in "netlib" www.netlib.org in the packet "minpack" but it is in fortran...

AVt · May 12th, 2003, 10:46 am

audetto, thank you for answering. I made up my mind to translate a Maple solution: it would cover my requirements and would give me a base for intrinsic testing.

AVt · May 19th, 2003, 4:36 am

Find enclosed a solution for Excel. It is a little bit dirtyand small, but it works. In the enclosed sheet i restrict toonly a few (16) observations (since dynamic arrays are awfullin Excel, but can be done), ignored weights (due to speed) andused a logistic function as test.Due to L-M one has to choose a rough but not completely absurdinitial guess for the parameters.Excel can not compute symbolic or reasonable parse expressionsi use 'precomputed' functions and gradients (which is ok forme). For a different parametric function one has to adapt fand gradf within the VBA source and possibly the number ofparameters (and observations).I also added a protocol from the original Maple solution (sincethe easiest is to let an CAS do the work and link it to Excelgetting the desired flexibility) showing the statistics for it:it is an example that a good statistics is not good enough.

vlife · September 9th, 2003, 6:50 am

@avtis there any chance that you could walk me through your excel macro?

AVt · September 9th, 2003, 10:04 am

äääh ... it is the VBA translate of the Maple code (statistics kicked out, dim=1, static length)... i will try to understand it again

... send me a private mail, address is in my profileadded: David Holmgren once gave me that link to look at the section "Solving for viewpoint and model parameters"

ymikhale · November 6th, 2003, 10:20 am

Avt,I was trying to use your code and changed the initial p1 parameter, but the VB gave me an error "compilation error - cannot assign to a table" for tmp_beta parameter.any thoughts?

AVt · November 6th, 2003, 12:56 pm

ymikhale,What exactly did you change where? Changing the input in the shiet i can not reproduce it (except forcing the algo to fail).The error message typically occurs, if you change the source code. For this please note that p_start is thought as a vectorin IR^3 (for the parameters of f) and f(xi, tau1, tau2, tau3) is a 1-dim fct. So take care to change everything consistently.tmp_beta is the matrix gradF*gradF^(transpose) [and depends in size on f and data feed in]. Perhaps you may checkwhether matrix operations work within your Excel in a sheet first and make sure that 'all dimensions fit together' usingpaper and pencil

ymikhale · November 6th, 2003, 2:23 pm

Avt, the only thing I changed was the p_start parameter p1 from 2000 to 1000 in the spreadsheet. At first I thought it was the MMult function that it did not recognize, but then it worked fine for beta 2 lines before. So I am at a loss. Is it possible it has something to do with me having the French version of Excel ?

AVt · November 6th, 2003, 2:48 pm

Hmm ... tmp_beta is not what i wrote, it is a vector, so you may work around looping through it explicitely.But for me entering 1000 works. It should not depend on language versions (even if i have a german one).I dont have a good idea and no TechNet CD at hand. May you wish to store after entering '1000' andupload the thing? I will look at it in the evening.

ymikhale · November 6th, 2003, 4:41 pm

Hi Avt,I have attached 2 files - one is the original code that I downloaded from the site (***test2), the other is the one where I tried to work around tmp_beta and alpha (it seems that it has a problem with variables declared as variants with ()). None of them worked.Really appreciate your help since I am not a VB expert...

ymikhale · November 6th, 2003, 4:42 pm

Avt,Here are the files

AVt · November 6th, 2003, 6:38 pm

ymikhale,The shiet works, on different PCs with different operating systems and different Excels ...The last idea i have on it: may be you miss references to libraries.Within VBA check through the menu bar tools / references whether you havethe following 4 'standard ones' activated: Visual Basic for Applications,Microsoft Excel 9.0 Object Library (EXCEL9.OLB), OLE Automation (not need) and Microsoft Office 9.0 Object Library (MSO9.DLL).If it works: fine. If not ... then send me a private mail, may be i look for work arounds