CODEWITHSUNDEEP
excelvbamatlab
Sara
Sara

Reputation: 1

Invoke MATLAB executable with parameter from VBA in Excel

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.

Upvotes: 0

Views: 190

Answers (1)

X Zhang
X Zhang

Reputation: 1325

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.

enter image description here

Upvotes: 1

Related Questions