MATLAB: How to use Excel Link functions from VBA instead of from an Excel spreadsheet

activecelladdressmatlabrequestmlevalstringmlgetmatrixmlgetvarmlputmatrixmlputvarobjectrangeselectionSpreadsheet Link

Are there examples of using Excel Link functions from VBA code in the Excel VB Editor, rather than from an Excel spreadsheet, available?

Best Answer

This example assumes that you have configured Excel to work with Excel Link. If not, please see the Excel Link User's Guide for instructions on how to do so.

In particular, this example requires that:

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

2.Excel Link has been enabled as an Excel Add-In (Tools -> Add-Ins)

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

Option Base 1
Sub Method1()
      MLShowMatlabErrors "yes"
      '''To MATLAB:
      Dim Vone(2, 2) As Double    'Input
      Vone(1, 1) = 1
      Vone(1, 2) = 2
      Vone(2, 1) = 3
      Vone(2, 2) = 4
      MLPutMatrix "a", Range("A1:B2")
      MLPutVar "b", Vone
      MLEvalString ("c = a*b")
      MLEvalString ("d = eig(c)")
      '''From MATLAB:
      Dim Vtwo As Variant         'Output
      MLGetVar "c", Vtwo
      MsgBox "c is" & Vtwo(1, 1)
      MLGetMatrix "b", Range("A7:B8").Address
      MatlabRequest
      MLGetMatrix "c", "Sheet1!A4:B5"
      MatlabRequest
      Sheets("Sheet1").Select
      Range("A10").Select
      MLGetMatrix "d", ActiveCell.Address
      MatlabRequest
End Sub

Also see the related solution concerning how the MLGetMatrix function can handle range objects using their "Address" properties.

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 use MLGetMatrix and MLPutMatrix in a Macro Function or Macro Subroutine

Here is an example of a macro subroutine that uses MLGetMatrix:

 Sub Test1()
    MLGetMatrix "X", "Sheet1!A5"
    MatlabRequest
 End Sub

NOTE: The name of the sheet, Sheet1, is used here so that the location where the data is put is assured to be in the intended worksheet. Please make sure that the matrix that you want to import into Excel exists in the MATLAB Workspace before running the macro.

Here is an example of a macro subroutine that uses MLPutMatrix:

 Sub Test2()
    MLPutMatrix "A", Range("A1:C3")
 End Sub

NOTE: The Range function is needed here due to VBA for Excel requirement. Before running this example, please make sure that there are values in cell range A1:C3.

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 use MLGetMatrix and MLPutMatrix in a Macro Function or Macro Subroutine

Related Question