MATLAB Answers

Syntax for MATLAB Excel add-in

1 view (last 30 days)
Sid
Sid on 4 Apr 2017
Answered: Guillaume on 4 Apr 2017
Hi everyone,
The question relates to setting up a .xla function to be used with Excel, compiled using the MATLAB Library Compiler. The knowledge I have so far is from Dr. Moritz Ernst post here .
I would like to extend the capabilities shown in this example to populate a series of cells.
Let's imagine an example where user calls the function myACTXFunction from cell 'C1', with inputs 'A1 and 'B1'. Using the inputs myACTXFunction calculates 100 datapoints using LINSPACE, which should be populated in cells 'C1:C100'.
My knowledge in VBA syntax is poor, so I started experimenting with 'ACTXSERVER', where I managed to populate the values to a different workbook created new, every time the function is called.
Please see the code below that I compiled using Library Compiler to a .xla file.
function myACTXFunction(x)
% Goal:
% User calls the function from 'C1' after selecting 'A1' and 'B1' to be
% used as inputs for the calculation.
% Desired result:
% 'C1:C100' values are those obtained from running the LINSPACE.
% The original source to get started was from Dr. Moritz Ernst.
% Source: http://moritzernst.com/wp/2015/08/10/creating-an-excel-add-in-with-matlab/
% Sadly, what I have so far will only populate a new workbook.
dataSeries = (linspace(x(1,1),x(1,2),100))';
hServer = actxserver('excel.application');
eWs = hServer.Workbooks;
eW = eWs.Add;
eS = eW.ActiveSheet;
hServer.Visible = 1;
currentCellAddress = hServer.Selection.AddressLocal;
currentCellAddress_split = strsplit(currentCellAddress,'$');
letterID = currentCellAddress_split{2};
rowID = currentCellAddress_split{3};
targetCellAddressString = [letterID rowID ':' letterID num2str(100)];
hServer.ActiveSheet.Range(targetCellAddressString).Cells.Value =dataSeries;
end
My questions are:
  • Is there a non - ACTXSERVER method to do what I need?
  • Reading the information here, it looks like VBA does provide a means to locate the Address of the cell from which the function is called. Is there a way to use this within the MATLAB function?
Any thoughts on the matter would be most welcome. Thanks in advance

Answers (1)

Guillaume
Guillaume on 4 Apr 2017
I don't have matlab compiler but after a quick reading of the doc it doesn't look like it can generate the kind of add-in that you want as there's no facility to give you access to excel object model.
I would also advise against using actxserver from within the add-in. You are effectively starting another instance of excel from within excel (recent versions of excel detect that and reuse the same instance, but in spirit that is what you are doing).

Community Treasure Hunt

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

Start Hunting!