Setting up to run alglib in excel vba

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

Sergey - thanks for the replies. I'm using XL 2007. I have noticed that earlier versions often let undeclared variables pass even when there was an option explicit statement. In this case I had the problem when I was running just the modules to do matrix inversion, and I removed the Option Explicit line from the module with ap.bas. I then created another file where I imported everything (1 module per .bas file), and it worked with the option explicit in place, but if I run debug-compile VBA Project it raises an error for each undeclared variable, so it seems the Option Explicit statement is still a bit flakey. I have attached a file with added Dim statements for the undeclared variables, but I haven't tested it, other than to check that debug-compile works without raising any errors.

Donly1 - I can confirm that c++ matrix inversion is likely to be at least 10 times faster than VBA. If you are working with matices of the order of 2500x2500 VBA will be very slow; inverting a 500 x 500 matrix was taking about 40 seconds on my machine.

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

Quote:

inverting a 500 x 500 matrix was taking about 40 seconds on my machine.

Correction - that was for extracting eigen values. Inversion of a 500 x 500 matrix took about 9 seconds with the AlgLib VBA code and about 4.5 seconds with the Excel MInverse function (XL 2007). I'd expect c++ code to be well under 1 second.

Donly1 · **Joined:** Mon May 17, 2010 5:25 pm **Posts:** 2

Thanks for the reply's

Guess I'll have to refresh my c++ knowledge. It's been a really long time since I've done anything in c.. But I really need the speed improvement.
I was also wondering if I could use the c++ routine as a dll loaded in vba, or if I should compile and call it using a cmd prompt. I have no knowledge in making dll's to use in vba, so this would be a "learn as I go" kind of thing.

p.s. I'm not a programmer, just a Civil Engineer trying to write some code to create a tool (and this routines are really useful), so if I say anything wrong please feel free to correct me :)

Sergey.Bochkanov · **Joined:** Fri May 07, 2010 7:06 am **Posts:** 927

Donly1 wrote:

I was also wondering if I could use the c++ routine as a dll loaded in vba, or if I should compile and call it using a cmd prompt. I have no knowledge in making dll's to use in vba, so this would be a "learn as I go" kind of thing.

You can use C++ DLL's from VBA, although it will require to write some code at the both ends of the communication channel:
1. you have to convert VBA arrays into pointer-based representation at the VBA side
2. you have to convert arrays from pointer-based representation into ap::real_1d_array/ap::real_2d_array at the C++ side

P.S. I am working on automatic generation of interfaces from several programming languages to high-performance C implementation. When it will be ready, users will be able to transparently switch between "native ALGLIB" (in VBA/C#/etc.) and interface to highly optimized C/asm code. I.e. both variants will provide same interface. But it will take several months.

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

Quote:

p.s. I'm not a programmer, just a Civil Engineer trying to write some code to create a tool (and this routines are really useful), so if I say anything wrong please feel free to correct me

Me too :)

What c++ compiler are you using? If it's Visual Studio c++ you might find the post linked below at my blog (and the following posts) useful (at least I found it useful going back to this after several months).

http://newtonexcelbach.wordpress.com/2008/09/02/linking-excel-to-c/

Also I'll be posting some more about the using the AlgLib code with Excel (when I get time).

Quote:

P.S. I am working on automatic generation of interfaces from several programming languages to high-performance C implementation. When it will be ready, users will be able to transparently switch between "native ALGLIB" (in VBA/C#/etc.) and interface to highly optimized C/asm code. I.e. both variants will provide same interface. But it will take several months.

That sounds great, I'll look forward to it.

Are any of the current routines optimised for solving sparse matrices, or do you have any plans in that direction? I have had a look at the SuperLU library, but as with all these things there is quite an effort in getting them to work the way you want, especially for those of us not that familiar with c.

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

Quote:

You can use C++ DLL's from VBA, although it will require to write some code at the both ends of the communication channel:
1. you have to convert VBA arrays into pointer-based representation at the VBA side
2. you have to convert arrays from pointer-based representation into ap::real_1d_array/ap::real_2d_array at the C++ side

I could do with some help with that. I have previously set up a dll written in c++ to work with Excel (from http://www.crbond.com/).
The relevent code from that was:

VBA:

Code:

Declare Function gelimd Lib "ged2.dll" (ByRef av As Double, ByRef b As Double, ByRef x As Double, ByVal N As Long) As Long
..
Function GE(a As Variant, b As Variant, N As Long) As Variant

' Set up 1D, base 0 arrays, aa and ba, from 2D variant arrays, a and b

a = a.Value
b = b.Value
For i = 1 To N
For j = 1 To N
aa((i - 1) * N + j - 1) = a(i, j)
 Next j
ba(i - 1) = b(i, 1)
Next i
 
' Call gelimd function from ge2.dll

retn = gelimd(aa(0), ba(0), x(0), N)

And the c++ end:

Code:

/* ged.cpp -- Gaussian elimination linear equation solvers.
 *
 *  (C) 2001, 2004, C. Bond. All rights reserved.
 *
 *  Simple pivoting on zero diagonal element supported.
 *   Eliminates unnecessary  zeroing of lower triangle.
 *   Does not scale rows to unity pivot value.
 *   Swaps b[] as well as a[][], so a pivot ID vector
 *   is not required.
 */
#include "ged2.h"
#include <math.h>

#define EPS 1e-10

int gelimd(double* av, double* b, double* x, int n)
{
    double tmp,pvt,*t;
   
    int i,j,k,itmp;
   
   double ** a = new double*[n];
for( i = 0; i < n; i++)
   a[i] = new double[n];

   for (i=0;i<n;i++) {
      for (j=0;j<n;j++) {
         a[i][j] = av[i*n+j];
      }
   }
    

But it isn't clear to me what I need to do to convert the arrays from pointer-based representation into ap::real_1d_array/ap::real_2d_array.

Any help you can give me with that would be appreciated.

Sergey.Bochkanov · **Joined:** Fri May 07, 2010 7:06 am **Posts:** 927

double* => real_1d_array/real_2d_array is done with setcontent() call.
real_1d_array => double* may be done with getcontent() call.
as for real_2d_array => double*, it is stored by rows. you can copy it row-by-row by obtaining pointer to the first element of each row with just &a(i,0). elements within row occupy contigous locations.

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

I have managed to get an Excel UDF working with a dll compiled from the c++ version of rnatrixinverse, but I suspect the code is rather clumsy. I'd be greatful for any comments on how it might be made more elegant.

Here is the VBA code:

Code:

Declare Function vbrmatinv Lib "d:\Users\Doug\Documents\Visual Studio 2010\Projects\Alglib2\Release\Alglib2.dll" _
 (ByRef A2 As Double, ByVal M As Long) As Long

Function RMatInvdll(a As Variant) As Variant
    Dim N As Long, M As Long, N2 As Long, Pivots() As Long
    Dim A2() As Double, i As Long, J As Long, K As Long, Rtn As Long

    If TypeName(a) = "Range" Then a = a.Value2
    M = UBound(a)
    N = UBound(a, 2)

    ' Copy 2D base 1 variant array to 1D base 0 double array
    ReDim A2(0 To M * N - 1)
    For i = 1 To M
        For J = 1 To N
            A2((i - 1) * M + J - 1) = a(i, J)
        Next J
    Next i

    Rtn = vbrmatinv(A2(0), M)

    ' Copy 1D base 0 array to 2D base 1 array
    For i = 1 To M
        For J = 1 To N
            a(i, J) = A2((i - 1) * M + J - 1)
        Next J
    Next i
    ' Assign array to function return value
    RMatInvdll = a
End Function

And the c++

Code:

// vbmat.cpp -- Interface for VBA matrix functions.
 
#include "vbmat.h"

int vbrmatinv(double* av, int n)
{
    int i,j,info;
   matinvreport rep;
    ap::real_2d_array a2;
    
    a2.setcontent(0, n-1, 0, n-1, av);
   
  rmatrixinverse(a2, n, info, rep);

for( i = 0; i < n; i++){
     for (j=0;j<n;j++) {
   av[i*n+j] = a2(i,j);
      }
   }
return 0 ;
}

At the VBA end the variant arrays need to be converted to a base 0 double array anyway, and the only way to do that is by looping through each element, so there is probably not much that can be done there.

I had a lot of trouble getting setcontent() to work, but I don't know why. What I ended up with is what I thought I was doing in the first place (but obviously something must be different, because now it is working :)).

For returning the array back to VBA, is it necessary to convert it to a 1D array, and if so is there a better way to do it?

Doug Jenkins · **Joined:** Sun May 16, 2010 11:42 pm **Posts:** 63

By the way, the 500 x 500 matrix inversion that took 9 seconds with the VBA code, takes about 0.25 seconds with the c++, including data transfer from and to VBA :)

Sergey.Bochkanov · **Joined:** Fri May 07, 2010 7:06 am **Posts:** 927

Doug Jenkins wrote:

What I ended up with is what I thought I was doing in the first place (but obviously something must be different, because now it is working :)

Same problem sometimes :)

Doug Jenkins wrote:

For returning the array back to VBA, is it necessary to convert it to a 1D array, and if so is there a better way to do it?

I think, the only way. You can't leave data in the real_1d_array because it will be destroyed after exit from vbrmatinv(). If you want to use it, you must copy it somewhere - and the way you choose is as good as any other.

forum.alglib.net

Forum rules

Setting up to run alglib in excel vba

Who is online