forum.alglib.net
http://forum.alglib.net/

ALGLIB optimisation solver under VBA
http://forum.alglib.net/viewtopic.php?f=2&t=154		 Page 1 of 1
Author:  amonlibert [ Wed Jan 12, 2011 11:02 am ]
Post subject:  ALGLIB optimisation solver under VBA
Hello,

Thank you Sergey for your great work.

I would like to use your optimisation solver "LBFGS" with UDF (user defined function) under Visual Basic for Application (VBA) in Excel. Previously I was using your old VBA version "Alglib-2.6.0.VB6.zip" and it was working very well with UDF function in VBA but there were a lot of VBA modules to upload. Since your optimisation solvers have been improved greatly in the C# version, and there is now a linear equality / inequality constraints. However the code is no longer maintained in the old VBA version.

Therefore I am trying to write a wrapper for ALGLIB for C#, so I can access in VBA (through a dll and COM interface). However I am not an expert in C code and COM interface, and I have some difficulties to interface C code to VBA.

Do you think it is possible to interface your ALGLIB optimisation solvers to VBA excel? Could you please guide me on the best way to do it?
And may be, do you plan to implement a wrapper for VBA?

Thank you in advance for your reply.
Kind regards,
Antoine
Author:  Sergey.Bochkanov [ Thu Jan 13, 2011 12:23 pm ]
Post subject:  Re: ALGLIB optimisation solver under VBA
In the 3.x branch it is hard to interface Excel and C++ (or C#) version of ALGLIB. You have to use some undocumented ALGLIB features like reverse communication interface - because VBA doesn't support function pointers in any form.

I think that VBA version of ALGLIB will be updated somewhere in the March, but it is hard to guarantee it.
Author:  amonlibert [ Mon Jan 17, 2011 10:59 am ]
Post subject:  Re: ALGLIB optimisation solver under VBA
Thank you for your quick reply.

I confirmed that VBA does not support function pointer (and it is really a shame...). I have tried to do it in VBA through VB net deleguate function and the "addressof" function is not accepted by VBA.

However I found one way to interface ALGLIB LBFGS solver from C to VBA. I have presented below the C and VBA codes to interface ALGLIB through COM interface :

C sharp code (to paste into a Visual Studio Class module)
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Runtime.InteropServices;

public class AlglibException : System.ApplicationException
{
public AlglibException(string message)
: base(message)
{
}
}

[Guid("6DB79AF2-F441-44AC-8412-22B06BFDD9E4")]
public interface INTERFACE_MINLBFGS
{
double[] GetX();
void SetF(double F_);
void SetG(ref double[] G_);
void minlbfgscreate(int m, ref double[] x);
void minlbfgssetcond(double epsg, double epsf, double epsx, int maxits);
bool minlbfgsiteration();
void minlbfgsresults(ref double[] x);
}

[ClassInterface(ClassInterfaceType.None), Guid("6DB79AF2-F441-63AC-8412-22B06BFDD9E4")]
public class MINLBFGS_C : INTERFACE_MINLBFGS
{
public alglib.minlbfgsstate csobj;
public alglib.minlbfgsreport rep;

public double[] GetX()
{
return csobj.innerobj.x;
}

public void SetF(double F_)
{
csobj.innerobj.f = F_;
}

public void SetG(ref double[] G_)
{
csobj.innerobj.g = G_;
}


public void minlbfgscreate(int m, ref double[] x)
{
try
{
alglib.minlbfgscreate(m, x, out csobj);
}
catch (alglib.alglibexception _E_Alglib)
{
throw new AlglibException(_E_Alglib.msg);
}
}

public void minlbfgssetcond(double epsg, double epsf, double epsx, int maxits)
{
try
{
alglib.minlbfgssetcond(csobj, epsg, epsf, epsx, maxits);
}
catch (alglib.alglibexception _E_Alglib)
{
throw new AlglibException(_E_Alglib.msg);
}
}

public bool minlbfgsiteration()
{
bool functionReturnValue = false;
try
{
functionReturnValue = alglib.minlbfgsiteration(csobj);
}
catch (alglib.alglibexception _E_Alglib)
{
throw new AlglibException(_E_Alglib.msg);
}
return functionReturnValue;
}

public void minlbfgsresults(ref double[] x)
{
try
{
alglib.minlbfgsresults(csobj, out x, out rep);
}
catch (alglib.alglibexception _E_Alglib)
{
throw new AlglibException(_E_Alglib.msg);
}
}

}


[u]C VBA code (to paste into a VBA module)
Public Sub TestLBFGS()
Dim ALGLIB As MINLBFGS_C
Dim N As Integer
Dim X() As Double
Dim epsf As Double
Dim epsx As Double
Dim epsg As Double
Dim maxits As Long

N = 1
ReDim X(0 To N) As Double
epsf = 0.00001
epsx = 0.00001
epsg = 0.00001
maxits = 100000

X(0) = 1
X(1) = 1

Set ALGLIB = New MINLBFGS_C
ALGLIB.minlbfgscreate 1, X
ALGLIB.minlbfgssetcond epsg, epsf, epsx, maxits
Do While ALGLIB.minlbfgsiteration()
ALGLIB.SetF Func1(ALGLIB.GetX)
ALGLIB.SetG Grad1(ALGLIB.GetX)
Loop
ALGLIB.minlbfgsresults X
Debug.Print X(0), X(1) 'result -3.00668489998603 3.00013617674979
End Sub

Public Function Func1(X() As Double) As Double
Func1 = 100 * (X(0) + 3) ^ 4 + (X(1) - 3) ^ 4
End Function

Public Function Grad1(X() As Double) As Double()
Dim res(0 To 1) As Double
res(0) = 400 * (X(0) + 3) ^ 3
res(1) = 4 * (X(1) - 3) ^ 3
Grad1 = res
End Function


To make the COM interface working, the following steps are necessary :
=> in Visual Studio :
1. create a Class library in Visual C
2. copy C sharp code above into a new class module
3. add a reference to "alglibnet2.dll"
4. select in project properties : application -> assembly information -> make assembly COM-visible
5. select in project properties interface : build -> register for COM interop
6. press F6 to build "dll" and "tlb" files (used to interface with VBA excel)

=> in VBA excel:
1. create a new spreadsheet.
2. in the VBA code add a reference to MINLBFGS_C (select tools -> reference -> and files build in 6.)
3. copy VBA code above into a new VBA module
4. start VBA macro "TestLBFGS" to see the LBFGS result in immediate window. LBFGS solver is running on the VBA UDF functions : "Func1", and gradiant "Grad1"

Sergey, I think it would be very usefull if you could please create an ALGLIB interface for VBA : to my knowledge, the excel addin solver cannot run directly on UDF function within VBA. It is only possible to call the excel solver within the spreadsheet interface...and the performances on UDF are really bad (as we need to pass through the spreadsheet interface).

Thank you again for your help.
Antoine
Author:  Sergey.Bochkanov [ Mon Jan 17, 2011 7:21 pm ]
Post subject:  Re: ALGLIB optimisation solver under VBA
I'll assign higher priority to this problem. But I can't decide what to do: to use COM interface or just to create external DLL and to access it from VBA (as Python wrapper does).

Do you think that COM interface has some benefits over simple external DLL?
Author:  amonlibert [ Tue Jan 18, 2011 3:27 pm ]
Post subject:  Re: ALGLIB optimisation solver under VBA
Thank you for your reply.

I think that COM interface has the following benefit : the function arguments are declared within the COM interface in the C code. There is no need to declare all the functions and arguments in a VBA module (through declare function fct1 lib "..\DLL\Alglib2.dll" (byRef A As Double, ByVal N As Long) as long). And it is directly visible in VBA intellisense. I think it is more user friendly.

If you create external dll, I think it is necessary to define all the functions and arguments in a VBA module... but I could be wrong as I don't have knowledges on how Python export the net functions. May be a COM interface could automatically be generated in Python wrapper? I did not tested yet but I am interested to know.

Kind regards,
Antoine
Author:  Sergey.Bochkanov [ Thu Jan 27, 2011 10:03 am ]
Post subject:  Re: ALGLIB optimisation solver under VBA
I think that external DLL is better, because I use code generation tools, so I can easily generate appropriate VB6 declarations. And DLL gives me more control over communication protocols than COM, I think.
Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/