How can I use Excel Link functions from VBA instead of from an Excel spreadsheet?

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

Answer 1

[MathWorks Support Team]

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.