Running excel VBA script via Matlab yields different chart position

dear community,

I am trying to run a VBA script in excel using Matlab. Running in Excel, the created chart is positioned 2 columns right from the data (resulting into L) and 4 rows down from top.

The occupied data range is A1 to J78 in each sheet. Running the same code via Matlab will place the resulting chart not at the same posiiton, but near G and y coordinate about 0.75

Any idea? I am using Office 365

best regards

Jonas

To try this we need to enable "Trust Access to the VBA project object model" in Excel over File->Options->Trust Center->Trust Center Settings->MacroSettings

excel VBA code

Sub addBoxplotToEverySheet()
    Dim myChart As Chart
    Dim rng As Range
    Dim excludedRows As Long
    Dim ws As Worksheet
    
    excludedRows = 4
    
    For Each ws In ThisWorkbook.Worksheets
    
        ' select all but e.g. without first row:
        Set rng = ws.UsedRange.Offset(excludedRows, 0).Resize(ws.UsedRange.Rows.Count - excludedRows)
        Set myChart = ws.Shapes.AddChart2(406, xlBoxwhisker).Chart
        With myChart.Parent
            .Top = rng.Rows(1).Top  ' Set the top position
            .Left = rng.Cells(1, rng.Columns.Count).Offset(0, 2).Left  ' Set the left position
        End With
    Next ws
End Sub

Matlab code:

% Create Excel server
Excel = actxserver('Excel.Application');

% Make Excel visible
Excel.Visible = 1;

% Open an Excel file
Workbook = Excel.Workbooks.Open([cd filesep 'E3_langsam_JittFactor.xlsx']);

% Access the VBA project
VBAProject = Workbook.VBProject;

VBAModule = VBAProject.VBComponents.Add(1);  % 1 = vbext_ct_StdModule

% Your VBA code
VBACode = ["Sub addBoxplotToEverySheet()"...
    "Dim myChart As Chart", ...
    "Dim rng As Range", ...
    "Dim excludedRows As Long", ...
    "Dim ws As Worksheet", ...
    "excludedRows = 4", ...
    "For Each ws In ThisWorkbook.Worksheets", ...
    "    Set rng = ws.UsedRange.Offset(excludedRows, 0).Resize(ws.UsedRange.Rows.Count - excludedRows)", ...
    "    Set myChart = ws.Shapes.AddChart2(406, xlBoxwhisker).Chart", ...
    "    With myChart.Parent", ...
    "        .Top = rng.Rows(1).Top", ...
    "        .Left = rng.Cells(1, rng.Columns.Count).Offset(0, 2).Left", ...
    "    End With", ...
    "Next ws"...
    "End Sub"];

VBACode=strjoin(VBACode,'\n');
VBAModule.CodeModule.AddFromString(VBACode);

% Run the VBA code
Excel.Run('addBoxplotToEverySheet');

% Save and close the workbook
% Workbook.Save;
% Workbook.Close;
% 
% % Quit Excel
% Excel.Quit;

[Kautuk Raj]

I tried running the given script using MATLAB R2022a and got the behaviour as you expected. I am not able to reproduce the odd behaviour you observe.

[Jonas]

that's sad. i have attached an example of my behavior, one is the file before, the other after running of the matlab script. here is an image of the resulting misplacement:

Answer 1

[Jonas]

this seemed to be an issue maybe related to the graphics card(s) or the excel application itself. The workaround for me was to set the display setting to compatability mode in the excel settings. That way the sheet boxplot was placed correctly.

neverthe less, this leads to heavy bugs in the display of excel for me which makes it nearly unusable, when running it from matlab:

excel actually filled the whole white screen, but vusually the are placed at 1/4 size at the top left corner. to close the window e.g. I have to click the top right white corner of the big white screen.

This type of bug remains each time i open any excel sheet, but only when i use open it via the matlab explorer "open outside matlab with default application"

if i open any excel sheet normally via the classic windows explorer, the appearance of excel is normal

this seems to be a bug, any way to send this to the bug report directly?

EDIT: so using winopen or rightclick in the matlab explorer leads to the decribed behavior. This was in 2022a. When doing the same in 2024a, excel seems to start normally. I also do not have to change the compatability mode in excel and the script works as expected.

EDIT2: Matlab 2024a prerelease of course