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.