How to use an excel sheet as function?

I have an issues with a massive excel file. I have big excel model which i need to use for data processing. It could be seen as a black box function with a type unit load cases that are combined into one output.

With Matlab I want to send the variable to excel. Have excel calculate it. And then read it out. However the values are not updated in between and hence my output matrix has all the same rows.

How to make sure excel, updates it calculation in between?

While doing it manualy it works fine, but i want to do it for a n of 1000.....

n = 5; %amount of data poin
DC = linspace(0,1,n);

nameXLS ='File.xlsm';
Sheetname = "Calculation"

for i = 1:length(DC)
   
    DCi = DC([i  (length(DC)-i+1)])'; % variable prep to 2x1 colomn vector
    
    writematrix(DCi,nameXLS,'Sheet',Sheetname,'range','C31:C32' ); % puts in variable in excel
       
    A(i,:)  = readmatrix(nameXLS,'Sheet',Sheetname ,'range','K86:P86' ) %get calcout
    

end

Answer 1

[dpb]

Have to use COM and send <the Excel instruction to force recalculation>...

I'd suggest to move the calculations into MATLAB as being far more efficient; if you try to implement this in the fashion coded above, opening and closing the Excel workbook COM interface every time will become very expensive -- and you're already doing it twice in every loop; this will then be three times.

What do you consider "massive"? With today's amount of memory, I'd venture it's likely you could read the whole thing and do it all in memory with MATLAB vectorized code in a fraction of the time this will take.

[Teunis]

I'll have to read into COM.

Yes i agree in general this is not the preferred way of doing it. However i have to work with this excel as a tool and is a lot of work to rebuild with al the calculations and look-up functions.

Mainly as it used for single parameter inputs and is less suitable for ranges of all kind of parameters, where Matlab comes in. Also rewriting is an issue if my company releases a new version of this calculation tool in excel.

Hence my preference to use an excel as function. Provide some input in Matlab get the output and do the data procesing. Is there a way of working that Matlab can keep excel open, use it and then close it as my attempt?

[dpb]

Unfortunate your company seems caught in a bad techonology paradigm for any such kind of calculations. Sorry about that...

As far as the last Q?, "Sure! Use COM for it all, eschewing the higher-level functions." There's a pretty steep learning curve in doing so, however, but if it's going to be needed to do such things going forward, probably worth the investment.

When I found myself with a big job of doing a bunch of data manipulation for our local community college foundation several years ago, @Image Analyst generously posted his library of COM functions that was a gigantic help in getting over the hurdle...in the years since, I've added to and rewritten/streamlined much of his original work, but it is still the same basic library...I'll attach my version -- credit to @Image Analyst and secondary to myself would be appropriate as is no personal gain, but these should be a good starting point to get started with...the XL* files are class definitions of Excel builtin constants instead of burying "magic" numbers inside code; they have the nicety that tab-completion works although the need for so many is a pain to keep the VBA naming convention.

xlsAddr and xlsColRange are a couple of utility functions for creating Excel range expressions in forms I found useful for my purposes...

All of these I put into my "Utilities" folder that I keep for generally-useful functions; it is included in MATLABPATH at the very top just behind my current working directory/folder so it is searched even before base MATLAB functions. (While I don't do so routinely, this lets you have an aliased version of a builtin; the only time I recall doing this was many years ago for a while when there was a bug in a distributed function that could work around by trapping the call, fixing up an input argument and the passing it on to the builtin.)

[Teunis]

Thanks al lot! Much appreciated.

I'll have to plan some time for the steep learning curve. Your library will help.

For the short time we found an ugly work around in Excel by (mis)using the data table of what if analys as intermediate step. This reduces the amount of call to excel to 2.

[dpb]

Good luck...just one last note--my experience with the community college data was that when I initially tried reading/writing with read/writecell(), it was ok with only a few cells but when tried to do a large sheet that way it became exceedingly slow and eventually just hung the machine. At that point I had to revamp the whole process logic to instead be able to read/process the whole sheet instead of a cell at a time. Owing to the needs, that was somewhat more trouble to code but worked well in the end by using the library to fix up the sheet formatting and such after the base data had been written. An external template wasn't an option here because the formatting and size of the sections within a sheet is variable so the location of where specific formulas, etc., needed to be is variable as well.

The other "trick" that can help for this kind of manipulation is the <File Exchange xlswriteEx> submission that is a version of the venerable xlswrite that lets you write multiple times to the same workbook without closing the handle to the COM server every time as does the builtin (and as do the new replacement versions) since they're all built as standalone, independent functions to be used without any other conditions. Their performance isn't bad, but my experience indicates you can eventually have issues if need too many accesses inside tight loops...