Running excel VBA script via Matlab yields different chart position

7 views (last 30 days)
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;
  2 Comments
Kautuk Raj
Kautuk Raj on 26 Feb 2024
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
Jonas on 19 Mar 2024
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:

Sign in to comment.

Accepted Answer

Jonas
Jonas on 19 Mar 2024
Edited: Jonas on 19 Mar 2024
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

More Answers (0)

Products


Release

R2022a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!