Invoke MATLAB executable with parameter from VBA in Excel

Question

I am designing a Front End for one program. The user should import the initial value in the Excel sheet, and the program will connect the Excel sheet to Matlab and run the Matlab code and show the Result. Hence at first, Excel needs to open the Matlab file. I am using this code in VBA to find the Matlab path and open it:

Dim MatLab As Object

Set MatLab = CreateObject("Matlab.Application")

MatLab.Execute("C:\ProgramFiles\MATLAB\R2022b\toolbox\matlab\addon_enable_disable_management\matlab")

But this address should be generalized for all users since their MATLAB roots are different.

The second question is: To be able to read my data from the Excel sheet, I wrote a MATLAB code that will read the table of numbers in my sheet, and then when I call this Matlab file from VBA code in Excel, it will run the program and give me a ternary plot. But in this section, I also used the specific address for my Excel folder in MATLAB, which needs to be generalized.

My Matlab Code is :

opts = spreadsheetImportOptions("NumVariables", 15);

opts.Sheet = "TernaryPlot1";

opts.DataRange = "A8:O20";

opts.VariableNames = ["Var1", "Var2", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11", "W4", "W5", "W6", "NormalizedResault"];

opts.SelectedVariableNames = ["W4", "W5", "W6", "NormalizedResault"];

opts.VariableTypes = ["char", "char", "char", "char", "char", "char", "char", "char", "char", "char", "char", "double", "double", "double", "double"];

opts = setvaropts(opts, ["Var1", "Var2", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11"], "WhitespaceRule", "preserve");

opts = setvaropts(opts, ["Var1", "Var2", "Var3", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9", "Var10", "Var11"], "EmptyFieldRule", "auto");

opts = setvaropts(opts, ["W4", "W5", "W6", "NormalizedResault"], "FillValue", 0);

% Import the data

B = readtable("Path of my Excell folder ", opts, "UseExcel", false);


clear opts

% % Main file for ternary plot

A=table2array(B)

 warning off MATLAB:griddata:DuplicateDataPoints

l=length(A);

v=0.29./sqrt(A(:,4));

figure;

colormap(jet)
[hg,htick,hcb]=tersurf(A(:,1),A(:,2),A(:,3),A(:,4));

hlabels=terlabel('Weight on First goal','Weight on Second Goal','Weight on Third Goal');

citra3=montage(reshape(V,size(citra)),map,'Indices',3);

Please help me to find how I could generalize these two addresses (one in VBA code regarding my MATLAB program's address and second one in MATLAB program regarding the address of my Excel folder) to be useable for each user in their own camputer.

1. Recent MATLAB install somehow put the binary in the system's search path, so if you type "matlab" in command prompt within any arbitrary directory, the program will start as if the full path is provided. 2. Just pass the excel path as a parameter to matlab during calling. — X Zhang
– X Zhang, Commented Jan 3, 2023 at 4:10
@XZhang: Thanks for your answer. I have the excel path in a parameter with the name of PathCrnt (PathCrnt = ActiveWorkbook.Path). Would you please let me know which line of code shode I use to pass this to MATLAB? The code that I am using on VBA to call my Matlab.m file to run is: Dim PathCrnt As String PathCrnt = ActiveWorkbook.Path fileToRun = PathCrnt & "\name of my matlab file" — Sara
– Sara, Commented Jan 3, 2023 at 5:56
@XZhang: Unfortunately, I didn't get the first part of the answer. I would appreciate it if you could describe it more for me. Thanks. — Sara
– Sara, Commented Jan 3, 2023 at 5:58

X Zhang · Accepted Answer · 2023-01-03 12:34:21Z

1

I don't know much about VBA, so any discussion is appreciated. Basically, when you call

Set MatLab = CreateObject("Matlab.Application")
MatLab.Execute()

The path to matlab executive is already handled by the system through OLE automation. All you need to do is put your MATLAB function in the parentheses with the parameter. I experimented with a little piece of VBA code using the built-in input function as a proof of concept.

Sub matlab()

Dim matlab As Object
Set matlab = CreateObject("Matlab.Application")
matlab.Execute ("input('put parameter here, e.g. enter to continue');")

End Sub

You can observe the result in the pop-up window.

edited Jan 3, 2023 at 12:34

answered Jan 3, 2023 at 12:28

X Zhang

1,3451 gold badge9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invoke MATLAB executable with parameter from VBA in Excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related