forum.alglib.net

ALGLIB forum
It is currently Fri May 24, 2024 5:02 am

All times are UTC


Forum rules


1. This forum can be used for discussion of both ALGLIB-related and general numerical analysis questions
2. This forum is English-only - postings in other languages will be removed.



Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2, 3  Next
Author Message
 Post subject: Setting up to run alglib in excel vba
PostPosted: Sat May 15, 2010 4:00 am 
Offline

Joined: Sat May 15, 2010 3:54 am
Posts: 3
The tools in alglib look great.

I am familiar with vba within excel spreadsheet. Unfortunately, I am not familiar with what to do with a bas file. As far as I can tell, we need access to the code in ap.bas as well as whatever other function we are calling.

I tried the following in attempt to invert a complex matrix
I copied ap.bas into one module.
I copied matinv.bas into another module.
In a 3rd module I tried to create my own test code:
Sub test1()
Dim A(1 To 2, 1 To 2) As Complex
Dim XX As MatInvReport

' Initialize Matrix
A(1, 1).X = 5
A(1, 1).Y = 2
A(1, 2).X = 6
A(1, 2).Y = 2
A(2, 1).X = 3
A(2, 1).Y = 9
A(2, 2).X = 1
A(2, 2).Y = 2

Call CMatrixInverse(A, 2, 0, XX)
Stop
End Sub

The result of trying to run this sub is an error message within module CMatrixInverse:Sub or Function Not defined: CMatrixLU

Can anyone shed light on what I am doing wrong?
Thanks in advance for any tips.


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Sat May 15, 2010 6:58 am 
Offline
Site Admin

Joined: Fri May 07, 2010 7:06 am
Posts: 915
You have to import files into VBA by right-clicking on "Modules" tab, then "Import file..." It isn't good to copy all code into one big file.

You got this error message because you didn't copy all units you need. VBA units don't have a list of units they rely on (I think that I'll add something like this in the next release), so you may just add all units from ./src directory. As for matrix inversed, it uses following units:
ap
reflections
creflections
hqrnd
matgen
ablasf
ablas
trfac
trlinsolve
safesolve
rcond


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Sat May 15, 2010 3:39 pm 
Offline

Joined: Sat May 15, 2010 3:54 am
Posts: 3
Thanks.


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Sat May 15, 2010 10:06 pm 
Offline

Joined: Sat May 15, 2010 3:54 am
Posts: 3
Two things I wanted to mention.

1 - Matrix array indexing must start at 0 for these routines (the vba version) to work. I'm not sure if it applies to the other programs.
2 - I adapted some VBA code from Walkenbach that provides an easy way to "import" all 101 files into modules in excel. I named each module starting with z_ so it sorts to the end of the module list. Just edit the filepath to identify wherever those source files are on your computer:

Code:
Sub BatchProcess()
Dim FS As FileSearch
Dim FilePath As String, FileSpec As String
Dim I As Integer
Dim myfilename As String

    ' Specify path and file spec
    FilePath = "C:\Documents and Settings\peter\My Documents\AlgLib\SourceBas\"
    FileSpec = "*.bas"
   
    ' Create a FileSearch object
    Set FS = Application.FileSearch
    With FS
        .LookIn = FilePath
        .FileName = FileSpec
        .Execute
        ' Exit if no files are found
        If .FoundFiles.Count = 0 Then
            MsgBox "No files were found"
            Exit Sub
        End If
    End With
   
    ' Loop through the files and process them
    For I = 1 To FS.FoundFiles.Count
        Application.Modules.Add.InsertFile (FS.FoundFiles(I))
        ' Append a z_ onto beginning of filename so it will sort to the end of the list
        myfilename = "z_" & FileNameOnly(FS.FoundFiles(I))
        myfilename = Mid(myfilename, 1, Len(myfilename) - 4)
        Modules(Modules.Count).Name = myfilename

    Next I
End Sub

Public Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim I As Integer, length As Integer, Temp As String
    length = Len(pname)
    Temp = ""
    For I = length To 1 Step -1
        If Mid(pname, I, 1) = Application.PathSeparator Then
            FileNameOnly = Temp
            Exit Function
        End If
        Temp = Mid(pname, I, 1) & Temp
    Next I
    FileNameOnly = pname
End Function


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Sun May 16, 2010 11:48 pm 
Offline

Joined: Sun May 16, 2010 11:42 pm
Posts: 63
I have been discussing installing the AlgLib code to run with Excel VBA with electricpete at the Eng-Tips forum.

One issue I had was that there were undeclared variables in ap.bas. I have gone through and added missing dim statements, but I was wondering if there was a reason why they were left out in this one module, and if there are any resources for testing my work.

_________________
Doug Jenkins
http://newtonexcelbach.wordpress.com/


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Mon May 17, 2010 1:53 am 
Offline

Joined: Sun May 16, 2010 11:42 pm
Posts: 63
Another question - in the ABLAS routines there is a function for ABLASBlockSize, which is set to 32. Increasing this value (I tried 512) seems to considerably improve performance of the matrix inversion routine. How is the best value for this parameter determined, and will increasing it have unexpected consequences?

_________________
Doug Jenkins
http://newtonexcelbach.wordpress.com/


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Mon May 17, 2010 6:16 am 
Offline
Site Admin

Joined: Fri May 07, 2010 7:06 am
Posts: 915
Increasing ABLASBlockSize will increase memory consumption (several subroutines allocate blocks as large as N*BlockSize). I recommend you not to make it larger than 512.

This parameter controls cache-related optimization. High performance implementation of ALGLIB written in C++ is so fast that sometimes it is beneficial to process data in small chunks (NxBlockSize). It require additional FLOPs, but computations become faster because all data are contained within CPU cache. However, VBA is too slow, so cache-related optimizations give no performance benefits, and even performance penalty. Large BlockSize prevents linear algebra subroutines from using cache-optimized code (it is used only for matrices larger than BlockSize*BlockSize).

I think that one of the next releases will solve this issues by explicitly turning off cache optimization in VBA.


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Mon May 17, 2010 5:31 pm 
Offline

Joined: Mon May 17, 2010 5:25 pm
Posts: 2
Hello

I'm also using algilb in vba for excel and wanted to ask what would be the best (fastest) choices to get the inverse of a symetric matrix, and a symetric positive matrix (and what modules would I need to import just for this case).

I was also wondering about using c++ rather than vb to get better performance. Would it make a diference? Im using aprox 2500x2500 matrix.

Thanks


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Mon May 17, 2010 7:17 pm 
Offline
Site Admin

Joined: Fri May 07, 2010 7:06 am
Posts: 915
Donly1 wrote:
what would be the best (fastest) choices to get the inverse of a symetric matrix, and a symetric positive matrix (and what modules would I need to import just for this case).

sinverse.bas - for symmetric matrices
matinv.bas - for general or SPD matrices. It doesn't support symmetric imdefinite matrices - positive definite or general only.

Donly1 wrote:
I was also wondering about using c++ rather than vb to get better performance. Would it make a diference? Im using aprox 2500x2500 matrix.

I've never made exact comparison between VBA and C++, but generally C++ may be 10 times as fast as VBA. With linear algebra this difference may be even larger. It is really good idea to use C++ for such large task. And I recommend you to invert symmetric indefinite matrices as general matrices. The reason is that C++ code for SPD and general matrices is well optimized, and code for symmetric indefinite matrices is less optimized. So in C++ it is faster to invert symmetric indefinite
matrix as general matrix, than to use specialized subroutine.


Top
 Profile  
 
 Post subject: Re: Setting up to run alglib in excel vba
PostPosted: Mon May 17, 2010 7:20 pm 
Offline
Site Admin

Joined: Fri May 07, 2010 7:06 am
Posts: 915
Doug Jenkins wrote:
I have been discussing installing the AlgLib code to run with Excel VBA with electricpete at the Eng-Tips forum.

One issue I had was that there were undeclared variables in ap.bas. I have gone through and added missing dim statements, but I was wondering if there was a reason why they were left out in this one module

This module was hand-written, and other modules are automatically generated.

But what version of Excel you used? My Excel gives me no warnings regarding uninitialized variables...


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 22 posts ]  Go to page 1, 2, 3  Next

All times are UTC


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group