Levmar Excel An Introduction

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.

LevmarExcel-Install-01LevmarExcel-Install-02

Browse for the “xll” and click “OK”.

LevmarExcel-Install-04

We strongly recommend selecting yes, but it isn’t required to make the Add-in work.

LevmarExcel-Install-05

Select “OK” and you are done

LevmarExcel-Install-03

3 - Test the Setup

Open the demo_vba included in the zip and enable the macro.

LevmarExcel-Install-06

Go to the demo tab and hit one of the buttons to check if you install works.

LevmarExcel-Install-07

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.

Leave a Reply

Your email address will not be published. Required fields are marked *