How can you store a VBA array in Matlab Matrix?

I try to open an excel file, run a macro that creates a VBA array and then get this array into Matlab. On way to implement this would be to call the macro from Matlab i.e.

Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open('C:\ExcelFile.xlsm')
Excel.Run('Macro1');
'The above Macro creates an array (~1million rows x 60 columns)
'Paste the array in Excel
'read range in a cell matrix

The code above works fine, but writing big arrays into excel spreadsheet slows down the application. So, ideally I would like to get the array from VBA without printing it into Excel.

[dpb]

My first attempt would be to try to eliminate the macro/VBA/Excel link entirely...if can write it in VBA, why not in Matlab directly with, at most, the required input data?

[sm]

That would be ideal in case that the functionality of the macro could be replicated in Matlab. Unfortunately, this is not possible in my case since the macro uses custom add-ins whose functionality cannot easily be replicated.

[dpb]

What kind of functionality might that be? Given the flexibility of Matlab seems unlikely would be too difficult.

I'm sure there's a way to use ActiveX/COM/whatever is the MS flavor-of-the-day to directly invoke a macro in Excel from Matlab but while I've used some VBA under duress, I've no klews on the actual syntax. It'd be a question more suited to Excel whizards, anyway, as it's the intercom syntax that's the issue here, not Matlab per se.

Alternatively, if this is a sizable something, you can build a DLL from VBA can't one? In that case, wrap it as a mex function have at it directly...

[Konstantinos Sofos]

Do you have with your MATLAB the Spreadsheet Link EX for Microsoft Excel?

[dpb]

As Yair is almost always a fount of knowledge and I don't know much in the area, I did another search and found the following---it's for PowerPoint specifically, but perhaps gives some useful hints for Excel.

running-vb-code-in-matlab/

[sm]

@Konstantinos - I do have the Spreadsheet Link EX for Microsoft Excel, but in my case it does not help. Let me give you an idea of what I am trying to achieve here since it might help you suggest other solutions. I m pulling data from a third-party server using the custom add-in. Then I clean the data and store them in my database - the process is fully automated and is executed without any user interaction. The process was performed in excel, but lately the volume of the data has increased (> 1 million rows) and I thought that Matlab might be able to run the process faster. As mentioned before, the data from the third-party server are pulled into excel from the third-party's add-in which was not created by me and thus I cannot replicate it in Matlab.Unless I can pass on the VBA array from excel to Matlab without having to paste the results, it may not be optimal for me to replicate the process in Matlab (for example, I will have to break the array into smaller pieces so that I can paste them in the spreadsheet in addition to the slow performance due to read/write commands).

[dpb]

If this is a continuing project with that much data, I'd surely be looking to the 3rd party add-in to something other than Excel. Is this the gizmo that is doing the specialty processing I gather? Do they not have an API for C/C++/Fortran developers besides the Excel/VBA combination? With that much data it certainly ought to have the option to use something other than a text format simply for the size. If you can get a stream file, that's trivial to import to Matlab; if there's some other way to process the data then you'd be in tall cotton. Lacking that, sounds like it's going to be painful at best, no matter what you do.

[dpb]

"Unless I can pass on the VBA array from excel to Matlab without having to paste the results, ..."

Another thought struck my mind...obviously figuring out how to actually pass a VB safe array would be ideal it seems if there's no other alternative but Excel, but another VBA macro could write the array into a separate file as a unformatted array (stream file). That file could be read much faster than text file and not need the Excel COM engine, either, nor require splitting it to paste. An "almost" pass the array and might even actually execute faster than the COM w/o the overhead of it, I don't know.

If you want to get esoteric, with mex in Matlab and some Windows API stuff you could even create a shared memory object to avoid the actual physical disk i/o. I've not done this with VB but don't see why it's not possible. I have some sample Fortran code in a set of apps that use shared memory database between a set of standalone executables that are otherwise totally independent that could share the basics of the creation/use as model. It really isn't nearly as difficult as it sounds altho I haven't ever tried it w/ VB and especially not VBA but presume there's nothing but writing the interfaces to the system APIs in VB instead of Fortran.

PS. One really, really nice thing w/ Matlab/VB/Fortran is that all use same storage order so don't even have to deal with transposing as with C/C++.

[sm]

@dpb thank you for your insightful comments. I will try to implement your last solution which seems the most "optimal" given current conditions/constraints.

[dpb]

As an addendum, I did finally get a while and after a struggle, managed to get a VBA function to work...it's not that hard excepting that the TMW documentation has an error in missing a vital syntax point in creating the Object.

In brief, the following works to place an array in the Matlab base workspace from VBA function in Excel...

    Option Base 1
    Dim A() As Double

    Sub putArray()
      ReDim A(10, 2)
      Dim ML As Object

      Set ML = New MLApp.MLApp

      A(1, 1) = 3.13
      Call ML.PutWorkspaceData("A", "base", A)
    End Sub

I've not tried to see how big A can be before performance is a real bottleneck or whether it really is or not. NB: that besides the Set keyword, the Call was mandatory in the version of VBA here (with this old version of Excel, anyway).

This used the OLE MLApp DLL; remember to go to the References under the Tools menu in VBA and select the Matlab Automation Library to make it visible.

Or, there's the .Net option on newer systems with support installed...this would be from the Excel side when you run the widget that it calls another function. You can do it similarly from Matlab I'd think but I didn't explore how to refer to a variable name from that side )altho I'd think if it were global could/should be possible?).

[sm]

@dpb your solution seems to create a new Matlab session and thus I cannot store the "A" array in the current workspace. Do you know if instead of using

Set ML = New MLApp.MLApp

I could set ML to current workspace?

[dpb]

I'm sure you can; seems like I saw something about that in the doc's somewhere but it'll take me a while to find it...I'm almost a complete (well drop the "almost" :) ) neophyte when it comes to COM so it's all a learning experience...I'll try to get some time later on this evening and see if can figure out where in the doc the above was again...I'm struggling at the moment to get a mex Fortran compiler working in a recent release--thought I had it last night but it seems either I did something then I'm not now or I was simply wrong and was actually running a different mexw32 routine than I thought I was...anyway, that's not your problem... :)

ADDENDUM

OK, there's mention in the Matlab doc's under MATLAB COM Automation Server Interface in the External Programming Language Interface chapter that indicates should be able to do it with a VBA GetObject call instead of CreateObject. You now know as much as I... :) Good luck. There's a section I see in the VBA help in a Chapter Visual Basic Conceptual Topics of Working Across Applications that may be useful...I suspect that's where I found the code to get started on the previous. I don't know; I had some trouble that without the New keyword nothing seemed to happen; maybe with GetObect instead it'll behave differently. I know "almost nothink!" about this and understand less, sorry...

[sm]

@dpb thank you for all your help.

[dpb]

OBTW, you may get more help at an Excel forum on the basics of automation; that it's Matlab you're trying to talk to is probably of minimal importance in the "how" of writing the VBA side of things. I just get buried in the morass of detail in the VBA documentation such that it takes forever to wade through it when totally unfamiliar with it.

I'll be really interested to see what it looks like when you do finally get there, though.

Have you gotten the file read/write option to work as a workaround or have you just tried the more direct way first?

I've got the first hurdle w/ mex finally licked, maybe if I get some of those "round tuits" I'll see if I can get anywhere with creating a shared memory object from VBA--I've got Fortran code that could use from mex on the Matlab side for that.