Levmar Excel is a wrapper around levmar, one of the best Levenberg-Marquardt algorithms out there. The wrapper allows you to use levmar in Excel via VBA. This is handy if you want to solve a nonlinear least squares problem. Alternatively you could also try to use the build-in Excel solver. While this solver is excellent, it isn’t too easy to integrate in VBA code. Levmar Excel fills the gap. It gives you an easy to integrate least squares solver for VBA code. In the remainder of the post it will be explained how to set things up to get the demo spreadsheet working. (If you are a developer wanting more details on the source code of this project look here.)
Setting up LevmarExcel requires just 3 steps.
1 - Download the latest package and extract the zip.
2 - Add the “xll” file to Excel
Open Excel and do File > Options > Add-Ins > Go > Browse.
Browse for the “xll” and click “OK”.
We strongly recommend selecting yes, but it isn’t required to make the Add-in work.
Select “OK” and you are done
3 - Test the Setup
Open the demo_vba included in the zip and enable the macro.
Go to the demo tab and hit one of the buttons to check if you install works.
Opening the VBA editor (hit ALT + F11) will give you some example code showing how to call the solver. Create the solver object using
Private m_solver As Object
Private Sub Init()
Set m_solver = CreateObject("LevmarExcelSolver")
End Sub
The simplest use of the code is
Public Sub SolveWoodNoInfo()
If m_solver Is Nothing Then Init
Dim m As Integer, n As Integer
m = 4
n = 6
Dim p() As Double, x() As Double
ReDim p(0 To m - 1)
ReDim x(0 To n - 1)
p(0) = -3#
p(1) = -1#
p(2) = -3#
p(3) = -1#
Call m_solver.Solve("Wood", p, x, m, n)
CleanSheet
Dim top As range
Set top = shtDemo.range("results")
top.Offset(0, 0) = "Result as of " & Now()
top.Offset(1, 0) = ""
Call PrintP(top.Offset(2, 0), p)
End Sub
It is also possible to get information about the quality of the result found. This is demonstrated in the Sub SolveWoodWithInfo
Public Sub SolveWoodWithInfo()
If m_solver Is Nothing Then Init
Dim m As Integer, n As Integer
m = 4
n = 6
Dim p() As Double, x() As Double
ReDim p(0 To m - 1)
ReDim x(0 To n - 1)
p(0) = -3#
p(1) = -1#
p(2) = -3#
p(3) = -1#
Dim info() As Double
ReDim info(0 To m_solver.infoSize())
Call m_solver.SolveWithInfo("Wood", p, x, m, n, info)
CleanSheet
Dim top As range
Set top = shtDemo.range("results")
top.Offset(0, 0) = "Result as of " & Now()
top.Offset(1, 0) = ""
Call PrintP(top.Offset(2, 0), p)
Call PrintInfo(top.Offset(2, 3), info)
Call PrintReasonStopping(top.Offset(2, 7))
End Sub
Happy solving!
The Excel Add-in and its source code can be downloaded here.