We are happy to announce that a new version of Levmar Excel has been released. This version exposes more functionalities from the original levmar package to Excel.

New in this version is the ability to pass a VBA class (i.e. object) as extra parameter to LevmarExcel. This object can contain extra information needed to evaluate the error function. This addition offers a lot of extra flexibility using the package. Complex object models can now be passed as arguments to be used for the evaluation of the error function (the error function can be only a thin wrapper for the “real” error function in the object).

The latest version of Excel Levmar is available here. The package contains the example “demo_vba_object.xlsm” which shows how to use this new functionality. The example contains a module with some code to build the LevmarExcelSolver object.

Private m_solver As Object


Private Sub Init()

   Set m_solver = CreateObject("LevmarExcelSolver")

End Sub

And a subroutine to solve a optimalization problem

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#

   Dim calc As WoodCalculator

   Set calc = BuilWoodCalculator(n, m)

   Call m_solver.SolveWithData("Wood", p, x, m, n, calc)

   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

And error function

Private Function Wood(p() As Double, x() As Double, m As Integer, n As Integer, _

       oWoodCalculator As Object) As Double()

   Wood = oWoodCalculator.Value(p, x)

End Function

This solves the problem in the class (or object) WoodCalculator

Option Explicit



Private m_mParameters As Integer

Private m_nObservations As Integer



Public Function Clone() As WoodCalculator

   Set Clone = New WoodCalculator

   Call Clone.Init(m_nObservations, m_mParameters)

End Function



Public Function Name() As String

   Name = "Wood with n" & m_nObservations & " and m " & m_mParameters

End Function



Public Sub Init(n As Integer, m As Integer)

   m_nObservations = n

   m_mParameters = m

End Sub



Public Function Value(p() As Double, x() As Double) As Double()

   Dim i As Integer

   For i = 0 To m_nObservations - 1 Step 6

       x(i) = 10# * (p(1) - p(0) * p(0))

       x(i + 1) = 1# - p(0)

       x(i + 2) = Sqr(90#) * (p(3) - p(2) * p(2))

       x(i + 3) = 1# - p(2)

       x(i + 4) = Sqr(10#) * (p(1) + p(3) - 2#)

       x(i + 5) = (p(1) - p(3)) / Sqr(10#)

   Next i

   Value = x

End Function

The class passed to LevmarExcel must contain a Clone function. This is needed for technical reasons by LevmarExcel. If you do not provide such a function an exception will be thrown.