What Takes to Wrap MATLAB function to Use From Excel?

I've got a MATLAB function that pulls data from an Excel spreadsheet (actually two separate ones) and then writes after processing into a third.

It does not return any values to the spreadsheets containing the data it uses and writes it in a "veritable plethora" of places scattered all around in the target.

What I'd like is for the person who is working in the one spreadsheet containing the source of most of the data to be able to call the MATLAB function to update the other without needing to run MATLAB itself--they don't have the skillset nor the license so I need to be able to wrap the needed runtime.

[Mario Malic]

Hey, you can just create an app in App Designer, containing one button. Define a callback for the button in which your function is called. One issue might be the path to Excel files, you can use uigetdir with TextEdit component. If you don't want to bother with that I can make an app for you. Does your function have input arguments?

[dpb]

Hmmm...sounds intriguing, Mario! :)

I've never built anything with App Designer so need to go look into it to get a little familiarity.

Right now the files/paths are built into the function; but to make it general would need to be able to set a couple of parameters -- the files can be located if know the year being analyzed and whether is 'SP' or 'FA' of the year...right now only the latter is an input to the function.

There's some other data buried in the function, but it would be possible (and probably best) if it were to be retrieved from the spreadsheet as the data are fixed dates/values.

I had presumed it took the MATLAB compiler to wrap the runtime which I didn't/don't have at the moment.

[Mario Malic]

True, MATLAB compiler is required.

I'll take a look whether it's possible to run Runtime somehow. Relevant answer.

The MATLAB Runtime is a standalone set of shared libraries that enables the execution of compiled MATLAB applications or components. I guess you need a compiler for Runtime to be utilized.

Edit: I do remember this question, and I think I suggested Octave because of the license issues, but I do not remember why not?

[dpb]

Well, I guess I finally just figured out that I've got to download a supported C compiler (I don't do C any more being retired now 20+ yr from the consulting gig) and then maybe I can use the MATLAB Compiler to build a Excel macro DLL. I'll see about that route over the weekend, maybe -- I've spent the whole week on pro bono work for the Foundation (this is part of that), though, and I've got to get some farm work done that has been put off, though, so may not get to it.

[Mario Malic]

When you want to buy the Compiler, there's not really the price (in one of other questions 10 years ago, Walter Robertson mentioned that it was ~$4800), maybe TMW will be generous, at the end of the day, it's a pro bono thing. Maybe customer support would be a better place to discuss your case.

The question about Octave from previous comment still stands.

[Mario Malic]

I apologise for tagging you here, but I think we could use your wisdom @Walter Roberson.

[Walter Roberson]

MATLAB Compiler SDK is the appropriate toolbox for building something that can be called from Excel. Requires MATLAB Compiler license as well. I have never tried it (I do not have a Windows license for Excel)

Answer 1

[Image Analyst]

https://www.mathworks.com/products/spreadsheet-link.html

Spreadsheet Link™ connects Excel® spreadsheet software with the MATLAB® workspace, enabling you to access the MATLAB environment from an Excel spreadsheet. With Spreadsheet Link software, you can exchange data between MATLAB and Excel, taking advantage of the familiar Excel interface while accessing the computational speed and visualization capabilities of MATLAB.

If they don't have MATLAB then you can either write your program in VBA in Excel and launch executables created by the MATLAB compiler, OR you can have the compiled standalone MATLAB app control Excel though ActiveX.

[dpb]

I saw that, IA. I don't see how that really works for this situation...and I think it would take me several months to convert to VBA the manipulations am doing with MATLAB on the data given the lack of familiarity I have with the Excel Object Model and the myriad of ismember() and the like lookups and assimilations of data from various fields...converting all that logic to loops and lookups sounds formidable -- I don't even want to think of what that code would look like. :)

[Image Analyst]

I was talking about doing the things in MATLAB that best make sense in MATLAB and convert it to an executable. Then do the things in VBA that are best done in Excel using VBA, like maybe the GUI or whatever. Then have the VBA code "launch" the MATLAB program he built as a "black box" to take in the inputs and export the outputs, either back to VBA or out to a disk file that VBA can then read in if it wants to.

[dpb]

I missed seeing the above reply IA, until now.

I grok -- in this case at the moment I don't need any UI although that might be the way to generalize -- at the moment it's just a black box that reads two workbooks they're working in and updates a third to save making the complex manual edits in it. The working input sheets are the same for a given semester billings so it's just working with the one fixed file that is being updated.

But, it will be needed to generalize somewhat later on, but if I could get this working right now it would solve the immediate crunch.

I found the log; it is/was, indeed, that it didn't find MingGW-w64 compiler; I guess the install I had done separately isn't where it thinks it should be or the particular environment variable isn't/wasn't set. It's pretty rude that the script/batch file doesn't abort with an error message when it doesn't find the compiler.

It's been a long time since have used it at all; it's probably quite out of date now, anyway, so I'll see if i can install fresh and make an executable.

Thanks for the pointers...I was, I think, probably headed down the wrong direction/in the wrong path. :)

[dpb]

A follow-up or two...

1. Does the needed MATLAB runtime library continue to work after an associated license which built the app might expire? IOW, do built applications expire? (Understand wouldn't be able rebuild it, of course, in that case). Or, in another way, when/if I retire am/gone from the volunteer board, does the app soon follow?
2. I presume only way to see any output is to either write to file or have a GUI with output textbox or the like to contain such? I've never tried to package any MATLAB code, my use has always been entirely from command line for my own purposes ere now. There is no way to have a console window like a console Fortran app, say?

[Image Analyst]

Compiled and deployed MATLAB code/programs do not expire. They do not need a license (other than the one you agree to when you install the free run-time library).

The standalone app can be run from a console window by typing the program name and then any command line arguments.

You can create/display output either by

1. writing a file
2. having a GUI
3. using the console window to output things with functions like fprintf() or just having the variable name on its own line without the semicolon.

If the program returns any output via a return argument, I'm not sure how to capture that into something else so if you need to use any output in some kind of subsequent program, it might best to write the output to a file that your other program can read in. It's probably possible, it's just that I've never done that (yet) so you might want to call them if you have something like a python program that calls your MATLAB code and need to accept the MATLAB output directly back into your Python variable.

[dpb]

I thought that was true on the runtime, but wasn't positive, thanks.

I hadn't thought to run in console window -- the splash screen shows; unfortunately the output of disp() isn't echo'ed to the console, but catenating the cells of the array and cleaning up some does let one redirect output by fprintf(), indeed.

Unfortunately, the girls in the office aren't likely to want to use the console. But, the visual output is really only to let know that things are working; I was just echoing what is being put into the file to the screen so I could tell was working. When all that happens is that the splash screen shows up and hangs around for 20-30 seconds but nothing else, it's kinda' disconcerting. :)

I'll have to build a rudimentary GUI to do something for progress indicator; I've got to build a way to set year/semester and be able to handle a non-standard file name/location, anyway, before I can just turn it over in the end even though I can get by for the immediate time crunch this way with a little one-on-one coaching. Right now I've got two versions; one for the SPR and one for FA and both expect a given Excel file and sheet. That works for now to finish getting ready for annual meeting week after next. I can deal with the longer-range later on a more leisurely schedule...."manana!"

This has no subsequent program; it's only updating an Excel that is very time-consuming to do by hand from another.

Thanks for the feedback, muchly appreciated, this was first time I had tried to package anything w/ MATLAB.

[dpb]

OK, I'm off diving into the AppDesigner morass...see if that is something I can get something working in in a reasonably short amount of time...

One more Q? --

You have any idea whether one could make persistent variables work in a packaged app?

Sorta' like rewritable-EEPROM, if could save the last year, semester, filenames that way it would be really easy way to have the last case at hand for next time without having to try to keep an auxiliary data file to go along with the app. I'm playing now with menus to let select new choices from present defaults...

[Image Analyst]

I use persistent variables in compiled apps andy the work fine. I never use disp but I use fprintf() and simply leave semicolons off and both of those work fine in compiled apps. @dpb do you have the MATLAB Compiler toolbox and want to build deplyable GUI apps?

[dpb]

Yes, that was the upshot of the first Q/A? -- I do have the MATLAB Compiler and successfully compiled the existing function as an application and it runs as is.

I'm now trying to figure out whether I can build a little more user-friendly app for the girls in the office with the AppDesigner -- I have figured out how to put some menus and get them to work to be able to select a set of alternative files than the default ones we're working on now -- I can always revert to "plain code" and package it, I'm sure, but was trying to see if the fancy GUI thing would help organize a little more, perhaps.

I discovered I cannot use persistent in a global properties block in AppDesigner; I've yet to experiment on whether can get something to work as want otherwise as a static method in a classdef. That's all new for me, too, never written a MATLAB class.

"I use persistent variables in compiled apps andy the work fine"

Is that between invocations of the app or only on invocations of a function within a single run of the app? What I'd like is

    % Code that executes after component creation
    function startupFcn(app)
      year=2020;                                              % prompt for general use later
      if strcmpi(getenv('username'),'duane.bozarth')          % logged in to SCCC machine
        fd='H:\WPFILES\DF\Financial';                         % head of Financials tree by year
      else                                                    % local machine
        fd='C:\DBNonBusiness\SCCC_Dev_Foundation\Financials\';
      end
      app.awardPath=fd;
      app.awardFile=fullfile(fd,sprintf('%d',year),sprintf('RestrictedAwardSheet_%d.xlsm',year))
      app.awardSheet=sprintf('%d Awards',year);        % revised sheet for fall added columns
    end

    % Menu selected function: BillingFileMenu
    function BillFileFunction(app, event)
      [app.billFile,app.billPath]=uigetfile('*Bill*.xlsx','Select Desired Billing File',"MultiSelect","off");
    end

Instead of the fixed file in the startup code, I'd like that if the user changes that file, directory, that the next time they run it will use that for the default instead. Then, one wouldn't have to always select the right file or update the default somemwhere else or have a default file hanging around to keep current and together after I have left the organization.

[Image Analyst]

What I do is I keep track of the last data folder used and write that to a .mat file with a function called SaveUserSettings() along with the state of various checkboxes, popup lists, etc. Then in the Openinfg function I call a function called LoadUserSettings() where I load that .mat file and send the various settings to the various controls.

[dpb]

OK, thanks. I was hoping to finger out a way w/o the secondary file to keep with the app...

[Image Analyst]

Persistents and globals do not remain somewhere in computer memory once the app exits, so they're not retained from session to session. That's why you need to store them in a small .mat file. It shouldn't be a big deal. I do it all the time, and even have other files, like splash screen images, sample data files, etc. that I ship along with my executable.

[dpb]

I wasn't expecting them to remain in memory; just hoping was a way to get them retained in the executable data space.

Answer 2

[dpb]

While I didn't answer my own Q?, I'll put an Answer here for any others who stumble over the thread and may be as confused as I have been.

Indeed, the solution was as simple as re-installing MingGW-w64 UNDER THE MATLAB APPS BANNER, NOT STANDALONE,

The previous standalone installation I had done years ago still worked/works with the command line tools I use routinely for code development outside MATLAB; but where it is installed wasn't anywhere the MATLAB Compiler looked.

Once reinstalled, it did build the standalone and it runs successfully -- this will be a major help going forward!!!

I've a few things to do to make it work for the actual system on the college server and to handle both semesters, but I updated the local copy here correctly so the functionality is all there!!!

Thanks for all the feedback; it did get me straightened out--the failure when I thought I had everything I needed had me confused--but I didn't find the compilation log until later to see the error and the lack of an error return in the script/batch file when the compiler isn't found is a sizable implementation gaff. I'll submit an enhancement request for that problem.