如何通过VBA代码向MATLAB传递Excel路径参数

修改后的VBA代码 #

Sub CallMatlabTernaryPlot()
    Dim matlabCommand As String
    Dim excelFilePath As String
    Dim matlabScriptPath As String
    
    ' 获取当前Excel文件的完整路径
    excelFilePath = ActiveWorkbook.FullName
    ' 替换路径中的反斜杠为MATLAB能识别的正斜杠，避免转义问题
    excelFilePath = Replace(excelFilePath, "\", "/")
    
    ' 替换为你的MATLAB脚本的完整路径（若Excel与脚本同目录，可直接用相对路径）
    matlabScriptPath = ActiveWorkbook.Path & "/ternary_plot_script.m"
    matlabScriptPath = Replace(matlabScriptPath, "\", "/")
    
    ' 构造MATLAB命令：传入Excel路径作为参数，运行脚本后自动退出
    matlabCommand = "matlab -nosplash -nodesktop -r ""run('" & matlabScriptPath & "','" & excelFilePath & "');exit;"""
    
    ' 启动MATLAB并执行命令
    Shell matlabCommand, vbNormalFocus
End Sub

VBA修改要点 #

• 自动获取当前Excel的完整路径，无需手动配置
• 统一路径分隔符为正斜杠，避免MATLAB解析路径出错
• 直接将Excel路径作为参数传递给MATLAB脚本，移除冗余的CreateObject调用，逻辑更简洁

修改后的MATLAB代码 #

function ternary_plot_script(excelFilePath)
    % 接收VBA传递的Excel文件路径参数
    if nargin < 1
        error("未接收到Excel文件路径，请通过VBA传递参数");
    end

    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);

    % 使用传入的路径读取Excel文件
    B = readtable(excelFilePath, opts, "UseExcel", false);
    clear opts

    % 三元图绘制逻辑（保留原有逻辑）
    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');

    % 注：原代码中citra和V变量未定义，请自行补充定义或注释该行
    % citra3 = montage(reshape(V,size(citra)),map,'Indices',3);
end

MATLAB修改要点 #

• 将脚本改为函数形式，接收外部传入的Excel路径参数
• 增加参数校验，确保路径正常传入
• 替换原固定路径为传入的变量，实现通用化读取
• 保留原有数据处理和绘图逻辑，仅修改路径相关部分

使用注意事项 #

1. 确保MATLAB已加入系统环境变量，否则需在VBA中指定MATLAB的完整安装路径（如"C:\Program Files\MATLAB\R2023a\bin\matlab.exe"）
2. MATLAB脚本需保存为ternary_plot_script.m，与Excel文件同目录或在VBA中指定完整脚本路径
3. 原代码中citra和V变量未定义，需自行补充或注释对应行

内容的提问来源于stack exchange，提问作者Sara