Syntax for MATLAB Excel add-in

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

Answer 1

[Guillaume]

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