This post describes a series of matrix functions compiled from the AlgLib library code (covered in previous posts) as a dll that can be called from Excel VBA, and used as User Defined Functions (UDFs). A spreadsheet containing the necessary VBA code to link to the functions, the compiled dll file, and C++ code for the interface functions can be downloaded from AL-Matrixdll2.zip. The C++ code for the actual matrix functions is all taken from the AlgLib site.
The advantages of these functions over the built in Excel functions, or VBA code are:
- The small limit on maximum matrix size imposed on matrix functions in Excel versions before 2007 is removed.
- The functions operate about 10 times faster than the built in functions and 30 times faster than VBA code (comparisons based on Excel 2007).
- The functions operate on complex numbers as well as real numbers.
- Functions include Eigen Values and Eigen Vectors, solution of systems of linear equations, addition and subtraction, and extraction and insertion, as well as all the functions built into excel.
- The functions are available from VBA without loss of performance.
To install the dll file:
- If you do not have Microsoft Visual Studio C++ installed, either install the program (Express version is a free download), or download and install the redistributable package.
- Copy the dll file to the Windows\System32 folder (or another folder on the system search path)
- The dll functions in Matrixdll2.xls(b) should now work in a similar way to built in functions or VBA UDFs.
Screen shots from the worksheet are shown below:
Matrix Functions
Inverse Functions
Eigen Values and Eigen Vectors
RMatSolvedll Function
Newton Excel Bach, not (just) an Excel Blog 
Hi,
I am performing a simulation and give my material properties. Than I change my element axis 45°. and do the simulation again. everything works. Now I want to understand how my results are produced.
I have 6×6 Compliance matrix. and when I rotate the element axis 45°, my compliance matrix is returned 45° as well.
Now I want to calculate it by hand or with the help of a code.
could you please help me to do it? is it possible to do it with your code?
LikeLike
Hi Sartor
Did you post on Eng Tips on this problem?
I’m afraid I’m not very clear about exactly what you are trying to do (which is why I didn’t respond at Eng-Tips), but the links below might provide what you want. They describe how the local stiffness matrix for a beam member is converted to global coordinates to be combined with the other elements of the structure.
https://newtonexcelbach.wordpress.com/2009/02/04/frame-analysis-with-excel-2-single-inclined-beam/
https://newtonexcelbach.wordpress.com/2009/02/10/frame-analysis-with-excel-3-continuous-beam-or-frame/
https://newtonexcelbach.wordpress.com/2009/02/15/frame-analysis-with-excel-4-2d-frame-analysis/
LikeLike
Does anybody knows where can I find a complete, free, Matrix / Algebra (i.e. kind of, complete AlgLib port toolset to use as UDF) add-in for excel 2007? I’ve found the Foxes team one but it’s not available for excel 2007/2010.
Thanks!
LikeLike
Fernando – I think that Alglib is the best of the free VBA libraries that I know of. I will be posting more Excel UDF interfaces to the Alglib routines as I write them. If you search the blog for Alglib you will find everything done so far (linear algebra, spline interpolation, and ordinary differential equation solver).
Other resources you might like to look at include:
Axel Voght’s site: https://newtonexcelbach.wordpress.com/2010/04/01/some-vba-maths-resources/
VBA Numerical Methods
http://www.vbnumericalmethods.com/
LikeLike
Yes, I am the same sartor.
http://www.efunda.com/formulae/solid_mechanics/composites/comp_lamina_arbitrary_detail.cfm#Compliance
Well, in this web page, there is a 3d case with plane stress conditions (I mean the compliance matrix). I do not assume anything like plane stress.
my questions is: I need the values of new S13,S14 and so on which are not included in the lamina compliance matrix.
LikeLike
Dear Doug,
thanks for sharing this! It’s great help being able to speed up calculations like this by use of the C++ DLL.
Unfortunately, I am not familiar with creating C++ DLL’s. Do you think it would be possible that you compile another DLL based on the C++ code, containing all AlgLib packages? (I mean the packages like for instance LinAlg, Solvers, Integration, DataAnalysis, etc.) Or possibly 11 DLL files for the 11 packages – whatever you think makes more sense.
This would be great help for me (and probably for many others as well).
Best regards,
John
LikeLike
bluesea – I’d like to do some more on Alglib but I doubt I will have any time for the next month or so.
Also Sergey at the Alglib Forum (http://forum.alglib.net/viewforum.php?f=2) says they are working on an update to the VBA code, to integrate it with the new Rel 3 libraries. If I understand it correctly the VBA will be a front end to compiled code, so I will wait and see how that works before doing too much myself.
LikeLike
Doug,
Tremendous work in putting this together. Thank you!
I was wondering if you can advise on getting your example sheets to work on 64bit machine (running windows server 2008 R2 and excel 2010). I changed the vba declare statements to ptrsafe (and ‘long’ to ‘longlong’) as outlined here; http://msdn.microsoft.com/en-us/library/ee691831.aspx, but not managing to get the alglib addin appearing in excel. Had no problem doing this on win7 64bit running 32bit excel. Is it that it simply doesn’t work on 64bit excel, or I should look change something else.
Thank you if you or someone else can advise.
Best regards,
Guy
LikeLike
Guy – did you ever manage to find out if it is possible to use the library with 64-bit Excel? I am in the same situation.
Athol
LikeLike
Athol – you might want to try out the latest version:
https://newtonexcelbach.wordpress.com/2012/11/12/compiled-matrix-functions-for-excel/
It is compiled from C# and may work where the C++ version doesn’t. Also it uses the latest Alglib code.
No guarantees, I don’t have a 64 bit version of Office available, so I can’t check it, but it’s worth a try.
LikeLike
Reverted to the vb.net version instead, and then called that as udf from excel (using exceldna). Anyway later ran up against brick wall as couldn’t work out how to write correct syntax for expressing my optimization problem in alglib (in my case portfolio optimization with equal risk contributions) – seems to be limited examples of alglib implemenations available. Since managed to get done with microsoft’s ‘solver foundation’ but unfortunately msdn site suggests limited support for that product going forward.
LikeLike
Guy – I’m afraid I don’t know. I sugest posting a question on the Alglib forum:
http://forum.alglib.net/viewforum.php?f=2
LikeLike
doug, i read with great interest your notes here about how one may use a *.dll from within Excel, via VBA script. i was hoping you could explain the mechanism whereby this actually works. in your notes, you indicate that the basic steps are:
1. INSTALL Microsoft Visual Studio C++ or express
2. copy the *.dll to a dir in your path env var
i am curious as to why one needs to install Microsoft Visual Studio C++ (or Express edition). is there some ‘magic glue’ in this program that allows the *.dll to be used by VBA? can you explain this interaction so that I can understand.
thanks for explaining…
greg
LikeLike
Greg – the dll is compiled with Microsoft C++, so it needs access to the Microsoft C++ library files. That is set up when you install any current version of Microsoft C++, but if you don’t want to do that you can install the redistributable package (see link in the post above), which will be a much smaller download.
Another alternative is to re-compile the C++ routines (both the Alglib files and the interface files) using your compiler of choice, but if you do that note that the interface routines are written for Version 2.6 of the Alglib library, and there are substantial differences in the current version. Installing the distributable package would be the easiest solution.
LikeLike
Pingback: Daily Download 13: The ALGLIB maths library and Excel | Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 18: Simultaneous Equations and Matrix Arithmetic Functions | Newton Excel Bach, not (just) an Excel Blog
Hello,
I am trying to reconcile the eigenvector/value calculations with a Jacobi iterative method I am using in VBA, and to Matlab output, and whilst eigenvalues from your algorithm are identical, the eigenvectors your algorithm returns are not the same. My matrix is a simple **symmetric** 8×8 matrix. Can I Email you the example for you to check? The results do not seem to satisfy the eigendecomposition formula that the original matrix can be obtained by S = VDV’ where V is the eigenvector matrix and D is a matrix of the eigenvalues times the indentity matrix.
Stuart
LikeLike
Pingback: Compiled AlgLib Matrix Functions for Excel | Newton Excel Bach, not (just) an Excel Blog – targa