Executing Matlab.m script from Excel VBA macro

The Matlab code that we wish to run reads in an “input.xls” file, performs calculations in Matlab .m modules, then outputs results to an “output.xls” file. There is an Excel VBA macro in a separate workbook that populates the “input.xls” file before calling the Matlab .m file with the following code:

Dim MatLab As Object
Set MatLab = CreateObject("Matlab.Application")
MatLab.Execute (“PATH TO MATLAB.m”)

The Matlab code should then populate the “output.xls” file (using xlswrite functions) with the final results once the run is complete. The Matlab code now fails to write to the “output.xls” file. There is evidence that the Excel macro is successfully calling the Matlab code since the Matlab application is opening in the background and running for the expected amount of time. However, the instance of Matlab is not visible and without the “output.xls” file, there is no way to diagnose the potential errors. Also, please note that run from the Matlab command line, the Matlab code populates the “output.xls” file perfectly so there should be no issues with the Matlab code.

Any help would be much appreciated!

[David Barry]

Not really answering your question here, but why not use MATLAB to do the populating of the input.xls spreadsheet rather than using VBA? Or, why not ditch Excel completely and do it all in MATLAB? I'm sure you have good reasons for doing it the way you are but I'm just curious.

[Erik Engwall]

Time management is zero sum. If the VBA already exists, then using it enables you to do something else.

Answer 1

[Aaron Close]

you can use in VBA

fileToRun = "C:\path\path\file.m"
matlabCommand = "matlab -nodisplay -nosplash -nodesktop -r "" run('" & fileToRun & "');exit;"" "
Shell (matlabCommand)

[Nallely Hernandez]

Hi, could you help me please. I run my macro whit the code, but I don't have response

Function call_r() As Double
    fileToRun = "C:\Users\Dell\OneDrive\Documentos2\MATLAB\suma.m"
    matlabCommand = "matlab -nodisplay -nosplash -nodesktop -r "" run('" & fileToRun & "');exit;"" "
    call_r = Shell(matlabCommand)      
End Function

The code in Matlab:

function [res] = suma()
    num1 = 7;
    num2 = 5;
    res = num1 + num2;  

end

Answer 2

[Antonio Gugin]

Hey guys,

I am using this very helpful code (Thanks a lot), however, I was wondering if there was a way to use an already opened matlab window (if there is such) instead of always opening a new one each time this is ran. Thanks a lot!

fileToRun = "C:\path\path\file.m"
matlabCommand = "matlab -r "" run('" & fileToRun & "');"" "
Shell (matlabCommand)

[ashwin charles]

Hey, did you find a away to not a new window every time?

Answer 3

[Vishal Rane]

Have you explored the Spreadsheet Link EX toolbox ?

http://www.mathworks.in/help/exlink/product-description.html

From its description I think it might help.

Answer 4

[Karabo Magoro]

Hi Yale, I'm also having the same problem. If you find/found the solution please share.

Regards, Karabo

Answer 5

[Long Cheng]

Hi, Yale,

I am having similar problem. Please share if you have any good solutions. Thank you.

Answer 6

[Bruce Siskowski]

Can anyone answer any of these 4 questions that have been overlooked in previous posts?

When using Shell in Excel VBA as shown above with command Line arguments, two problems seem to exist:

1.) A brand new instance of Matlab is loaded (even if a session is open already which eats up memory and clogs the screen)

How can an already open Matlab instance be used instead of a new one every time?

2.) There is no way to edit the Matlab code in the editor since it is a new instance and pausing and re-running VBA will create another new instance .

Any previously set Breakpoint lines in any previous instance are not executed due to the new continual instances.

(I have .m scripts that work fine when called from Matlab, but not for some users when called from Excel VBA and there is now way to debug)

Is there any way to use Breakpoints and STEP in ,m scripts that are called using the VBA Shell() command?

3.) As an alternative to the Excel VBA Shell() command, the use of "CreateObject" and "Matlab.Execute" in VBA both work as described in many posts, BUT, the Matlab command screen does NOT appear (to show feedback results of disp command so users know something is going on) and the Figure that is created DISAPPEARS as soon as the Excel VBA Subroutine ends.

Is there ny way to show Command (session) window "disp" feedback to the user that works when the .m script is called directly from Matlab?

4.) Why do many posts use the the word RUN in the Shell statement when this works fine without the word RUN?

'MATLAB.EXE -nosplash -nodesktop -r MCT('DataFilePath','DatFileName.xlsm',ExelSheet');

Thanks.

[Alexandre]

Hi,

About question 1), I have similar issue and no one seems to know what is the cause : Link.

In my case, I magically have no problem on only one PC and I don't know why. I'll keep you posted if I find a solution and I hope you'll do the same.

Thanks.

[Andy Hall]

Hi, I think I have provided an example of a single session that creates a matlab object and interacts with it multiple times (individual commands) once it has been created. CreateObject does exactly that so each time you call it you are creating a new Matlab Object. You need to do this only once. Once created you can send commands to the object using the <MatlabObject>.execute("<MatlabCommand>") method. Feedback can be collected (the text that is normally returned to the command window) as the string returned by the .execute method hence:-

<returnstring> = <MatlabObject>.execute("<MatlabCommand>") or explicitly,

fb = myMatlab.Execute("cd C:\Users\andyh\Documents\MATLAB\")

The other thing that is not obvious in the example below is that to have a Matlab.Application Class definition available you need to select Tools, References in the VBA editor and tick the checkbox for Matlab Application Type Library. Then the follwoing CreateObject command will work:-

Set myMatlab = CreateObject("Matlab.Application")

'change directory to my matlab user files path

fb = myMatlab.Execute("cd C:\Users\andyh\Documents\MATLAB\")

Hope this helps.

Answer 7

[Andy Hall]

I have been working in Excel VBA and wanted to call Matlab. I have exisitng .m file code that I wanted to call and basically, once I found that I could open an Matlab application Window with the Create Object VBA command wanted to work with the open matlab session interactively as though I was typing commands into the Matlab window and, obviously wanted the matlab command line feedback (results, error responses etc.). I think I can answer some of the questions above from my experience.

Dim myMatlab As Object
Dim fb As String
'Author: Andy Hall
'Date:06/08/2023

'Connect to Matlab - I only need to do this once to create a session object I can interact with
Set myMatlab = CreateObject("Matlab.Application")
'change directory to my matlab user files path
fb = myMatlab.Execute("cd C:\Users\andyh\Documents\MATLAB\")       
Debug.Print "Matlab Feedback1" & fb
fb = myMatlab.Execute("echo on")   'Echos in-script output commands to the command line
Debug.Print "Matlab Feedback2" & fb
fb = myMatlab.Execute("mp")       ' Command to Execute the mp.m file in my matlab path
Debug.Print "Matlab Feedback3" & fb
fb = myMatlab.Execute("c")           ' Command to Display the value of the variable c from my script 
'Output Matlab command line output in the Excel VBA immediate window.
Debug.Print "Matlab Feedback3" & fb  
myMatlab.Execute ("exit")   'Close up the matlab session

This code describes a single, incremental matlab session where the VBA script interacts wth a matlab object as through using the command line with feedback strings avaiable to be parsed in VBA.

The fact that a matlab object, accessed through the .Execute method provides the command line output as a feedback string seems to be what was missing from previous posts and the fact that once created a matlab object persists until closed (the problem of creating new objects for every command, mentioned above).

[Andy Hall]

I forgot to mention you will need to got into excel VBA editor and select Tools, References and tick the Matlab Automation Server Type Library add-in for the Matlab.Application type to be available.