MATLAB: Is it possible to use range objects with the MLGetMatrix function in Excel Link

arraycellnanSpreadsheet Link

I am using the Excel Link MLGetMatrix function in an Excel Macro, but I need to use use a range object returned by the VBA Cells function to specify where to place the data.

For example instead of:

 MLGetMatrix "X", "Sheet1!A1"

I would like to use:

 MLGetMatrix("X",Cells(1,1))

Best Answer

This bug has been fixed in Release 2007a (R2007a). For previous product releases, read below for any possible workarounds:

The MLGetMatrix function can accept ranges expressed as strings; to use a range object, you will first need to convert it to string format.

The MLGetMatrix function in Excel Link takes two string inputs. The first input specifies the MATLAB variable name and the second input specifies the address of the Excel worksheet cell where the first element of the MATLAB variable should be written.

If you have a range object which refers to the location where you want to write your data, you can use the "Address" property of the range object to extract the string. For example:

 Sub Get_Variable()
 MLGetMatrix "X", Cells(3, 2).Address
 MatlabRequest
 End Sub

Related Solutions

MATLAB: How to write a function in VBA which uses data of cells in Excel, work with this data in MATLAB and return a value to Excel

This example has been included in the documentation for MATLAB 7.5 (R2007b).

For previous product releases, read below:

The attached file contain the following:

1) matlabtest1.m

MATLAB function example, which multiplicate the data matrix with itselfs

2) test1.xls

Excel sheet with Macros for using the function

This sample of VBA code assumes that you have configured Excel to work with Excel Link. If not, please see the documentation section about "Installing and Operating Excel Link".

In particular, this example requires that:

1. The MATLAB application has been initialized via "startmatlab" or "matlabinit"

2. In Excel, Excel Link is checked as an Add-In (Tools -> Add-Ins)

3. In Excel VB Editor, Excllink is checked as a reference (Tools -> References).

Here is a step by step procedure to write a simple function in VBA based on this example:

 1) Configure Excel sheet
 a) Excllink is checked as a Add-In
 Tools --> Add-In    
 Use the correct Excel Link Version related to your MATLAB Version
 b) Define Data with a name in Excel:
 Write data i.e. into cells A1:A3, mark this area and define a name:
 Insert --> Name -> Define      type in "DATEN"
 c) Open VBA Editor
 Tools --> Macro --> Visual Basic Editor
 2) CONFIGURE VBA
 a) Excllink is checked as a reference
 Tools --> References    
 Search for file "excllink.xla" in your $MATLAB\toolbox\exlink directory, where $MATLAB is your used MATLAB installation directory. The Version of MATLAB and the related Excel Link has to be the same as your add-in in Excel.
 b) Insert new Module
 Insert --> Module
 3) WRITE SUB IN EXCEL VB EDITOR (here based on attached example)
 Notes:
 - "MLPutMatrix" puts the data into MATLAB workspace
 - "MLEvalString" evaluates String in MATLAB, where "matlabtest1" is the MATLAB function in this example
 - "MLGetMatrix" write value of variable v into the target cells
 Look at the following links for more information about available functions:
 Functions - By Category (Excel Link)
 <http://www.mathworks.com/help/exlink/functionlist.html>
 What the Functions Do :: Getting Started (Excel Link)
 <http://www.mathworks.com/access/helpdesk/help/toolbox/exlink/f2-2311.html>
Sub CommandButton1_Click()
MLPutMatrix "data", Range("B3:C6")
MLEvalString "y = data + 1"
MLGetMatrix "y", Range("E3:F6").Address
MatlabRequest
MLEvalString "y1 = data*data'"
MLGetMatrix "y1", Range("B8:E11").Address
MatlabRequest
MLEvalString "y2 = matlabtest1(data)"
MLGetMatrix "y2", Range("B13:E16").Address
MatlabRequest
End Sub
 3) Specify macro for button in Excel sheet related to this SUB:
 test1.xls!CommandButton1_Click
 c) Save everything and try out (push at the button)

For more information about examples of using Excel Link functions from VBA code in the Excel VB Editor, rather than from an Excel spreadsheet see related solution.

MATLAB: How to determine the range of cells in an Excel worksheet that have values in them using MATLAB 7.9 (R2009b)

When you use XLSREAD to read data from an Excel file, MATLAB creates a COM connection to the Excel Application and reads data. The input argument "range" is passed to the Excel parser and so you can use any valid expression that Excel understands. Unfortunately, there is no direct mechanism to specify reading all values from a specific cell.

To determine the range of cells being used on an Excel worksheet, you can inspect the "UsedRange" variable which is provided by the Excel COM Interface. The code snippet below indicates how this can be done:

Excel = actxserver('Excel.Application');
fullPathToExcelFile = 'C:\work\myFile.xlsx'; % Please modify this to the location of your excel file
ExcelWorkbook = Excel.workbooks.Open(fullPathToExcelFile,0,true);
WorkSheets = Excel.sheets;
TargetSheet = get(WorkSheets,'item','Sheet1');
Activate(TargetSheet);
DataRange = Excel.ActiveSheet.UsedRange;
r = DataRange.Address
ExcelWorkbook.Close
Excel.Quit
Excel.delete
clear Excel WorkSheets TargetSheet DataRange ExcelWorkbook
% Using the range returned to read data
r1 = regexp(r, ':', 'split');
end_cell = regexprep(r1{2}, '\$', '');
start_cell = 'B9';
range = [start_cell ':' end_cell];
val = xlsread('myFile.xlsx', 'Sheet1', range);

Best Answer

Related Solutions

MATLAB: How to write a function in VBA which uses data of cells in Excel, work with this data in MATLAB and return a value to Excel

MATLAB: How to determine the range of cells in an Excel worksheet that have values in them using MATLAB 7.9 (R2009b)

Related Question