Mathworks SPREADSHEET LINK EX 3 user guide

Spreadsheet Link™

User’s Guide

EX 3

How to Contact The MathWorks

www.mathworks.
comp.soft-sys.matlab Newsgroup
www.mathworks.com/contact_TS.html T echnical Support

[e-mail] Product enhancement suggestions

bugs@mathwo
[e-mail] Documentation error reports
[e-mail] Order status, license renewals, passcodes

info@mathwo

com

rks.com

rks.com

Web

Bug reports

Sales, prici

ng, and general information

508-647-7000 (Phone)

508-647-7001 (Fax)

The MathWorks, Inc.
3 Apple Hill Drive
Natick, MA 01760-2098

For contact information about worldwide offices, see the MathWorks Web site.

Spreadsheet Link™ EX User’s Guide

© COPYRIGHT 1996–2010 by The MathWorks, Inc.

The software described in this document is furnished under a license agreement. The software may be used
or copied only under the terms of the license agreement. No part of this manual may be photocopied or
reproduced in any form without prior written consent from The MathW orks, Inc.

FEDERAL ACQUISITION: This provision applies to all acquisitions of the Program and Documentation
by, for, or through the federal government of the United States. By accepting delivery of the Program
or Documentation, the government hereby agrees that this software or documentation qualifies as
commercial computer software or commercial computer software documentation as such terms are used
or defined in FAR 12.212, DFARS Part 227.72, and DFARS 252.227-7014. Accordingly, the terms and
conditions of this Agreement and only those rights specified in this Agreement, shall pertain to and govern
theuse,modification,reproduction,release,performance,display,anddisclosureoftheProgramand
Documentation by the federal government (or other entity acquiring for or through the federal government)
and shall supersede any conflicting contractual terms or conditions. If this License fails to meet the
government’s needs or is inconsistent in any respect with federal procurement law, the government agrees
to return the Program and Docu mentation, unused, to The Mat hWorks, Inc.

Trademarks

MATLAB and Simulink are registered trademarks of The MathWorks, Inc. See

www.mathworks.com/trademarks for a list of additional trademarks. Other product or brand

names may be trademarks or registered trademarks of their respective holders.

Patents

The MathWorks products are protected by one or more U.S. patents. Please see

www.mathworks.com/patents for more information.

Revision History

May 1996 First printing New for Version 1.0
May 1997 Second printing Revised for Version 1.0.3
January 1999 Third printing Revised for Version 1.0.8 (Release 11)
September 2000 Fourth printing Revised for Version 1.1.2
April 2001 Fifth printing Revised for Version 1.1.3
July 2002 Sixth printing Revised for Version 2.0 (Release 13)
September 2003 Online only Revised for Version 2.1 (Release 13SP1)
June 2004 Online only Revised for Version 2.2 (Release 14)
September 2005 Online only Revised for Version 2.3 (Release 14SP3)
March 2006 Online only Revised for Version 2.3.1 (Release 2006a)
September 2006 Online only Revised for Version 2.4 (Release 2006b)
September 2006 Seventh printing Revised for Version 2.4 (Release 2006b)
March 2007 Online only Revised for Version 2.5 (Release 2007a)
September 2007 Online only Revised for Version 3.0 (Release 2007b)
March 2008 Online only Revised for Version 3.0.1 (Release 2008a)
October 2008 Online only Revised for Version 3.0.2 (Release 2008b)
March 2009 Online only Revised for Version 3.0.3 (Release 2009a)
September 2009 Online only Revised for Version 3.1 (Release 2009b)
March 2010 Online only Revised for Version 3.1.1 (Release 2010a)

Getting Started

1

Product Overview ................................. 1-2

Contents

Installing the Spreadsheet Link EX Software

System Require ments
Product Installation
Files and Directories Created by the Installation
Modifying Your System Path

Configuring the Spreadsheet L ink EX Software

Configuring Version 2003 and Earlier Versions of the

Microsoft

Configuring Version 2007 of the Microsoft

Software

Setting Spreadsheet Link EX Preferences

Starting and Stopping the Spreadsheet Link EX

Software

Automatically Starting the Spreadsheet Link EX

Software
Manually Starting the Spreadsheet Link EX Software
Connecting to an Existing MATLAB Session
Stopping the Spreadsheet Link EX Software

About Functions

How Spreadsheet Link EX Functions Differ from Microsoft

Excel Functions ................................. 1-15

Types of Spreadsheet Link EX Functions
Using Worksheets
Working with Arguments in Spreadsheet Link EX

Functions
Using the MATLAB Function Wizard for the Spreadsheet

Link EX Software
Examples: Using Spreadsheet Link EX Functions in

Macros

®

Excel Software ........................ 1-5

....................................... 1-7

........................................ 1-13

....................................... 1-13

...................................... 1-18

........................................ 1-23

.............................. 1-3

............................... 1-3

........................ 1-4

®

.............. 1-11

................................... 1-15

.............. 1-15

................................. 1-16

............................... 1-20

........ 1-3

........ 1-3

...... 1-5

Excel

........... 1-14

........... 1-14

... 1-13

®

v

Working with Dates ................................ 1-27

Localization Information

........................... 1-29

Solving Problems with the Spreadsheet Link

EX Software

2

Running the Examples ............................. 2-2

Modeling Data Sets Using Data Regression and Curve

Fitting

Using Worksheets
Using Macros

Interpolating Data

Pricing Stock Options Using the Binomial M odel

Calculating and Plotting the Efficient Frontier of

Financial Portfolios

.......................................... 2-3

................................. 2-3

..................................... 2-6

................................. 2-11

............................. 2-19

..... 2-15

vi Contents

MappingTimeandBondCashFlows

................ 2-23

Function Reference

3

Link Management ................................. 3-2

Data Management

................................. 3-3

Functions — Alphabetical List

4

Error Messages and Troubleshooting

A

Worksheet Cell Errors ............................. A-2

Microsoft

Data Errors

Matrix Data Errors
Errors When Opening Saved Worksheets

Startup Errors

Audible Error Signals

®

Excel Software Errors ................... A-5

....................................... A-8

................................ A-8

.............. A-8

.................................... A-10

.............................. A-11

Examples

B

Macro Examples ................................... B-2

Financial Examples

................................ B-2

Index

vii

viii Contents

Getting Started

• “Product Overview” on page 1-2

• “Installing the Spreadsheet Link EX Software” on page 1-3

• “Configuring the Spreadsheet Link EX Software” on page 1-5

• “Starting and Stopping the Spreadsheet Link EX Software” on page 1-13

• “About Functions” on page 1-15

• “Working with Dates” on page 1-27

• “Localization Information” on page 1-29

1

1 Getting Started

Product Overview

The Spreadsheet Link™ EX software Add-In integrates the Microsoft®Excel
and MATLAB®products in a computing environment running Microsoft
Windows®. It connects the Excel®interfacetotheMATLABworkspace,
enabling you to use Excel worksheet and macro programming tools to lev erage
the numerical, computational, a nd graphical power of MATLAB.

You can use Spreadsheet Link EX functions in an Excel worksheet or macro
to exchange and synchronize data between Excel and MATLAB, without
leaving the Excel environment. With a small number of functions to manage
the link and manipulate data, the Spreadsheet Link EX software is powerful
in its simplicity.

Note This documentation uses the terms worksheet and spreadsheet
interchangeably.

The Spreadsheet Link EX software supports MATLAB two-dimensional
numeric arrays, one-dimensional character arrays (strings), and
two-dimensional cell arrays. It does not work with MATLAB
multidimensional arrays and structures.

Microsoft Excel

MATLAB

®

®

1-2

Excel workspace

Macro

Worksheet

Spreadsheet

Link EX

MATLAB workspace

Handle

Graphics

Toolboxes

SIMULINK

MATLAB
Compiler

Installing the Spreadsheet Link™ EX Software

Installing the Spreadsheet Link EX Software

In this section...

“System Requirements” on page 1-3

“Product Installation” on page 1-3

“Files and Directories Created by the Installation” on page 1-3

“Modifying Your System Path” o n page 1-4

System Requirements

For informatio n on hardw are and software requirements for this product, see

http://www.mathworks.com/products/excellink/requirements.html.

The Spreadsheet Link EX product requires the MATLAB for Microsoft
Windows software. For best results with MATLA B figures and graphics, set
the color palette of your display to a value greater than 256 colors:

1 Click Start > Settings > Control Panel > Display.

2 Click the Settings tab. Choose an appropriate entry from the Color

Palette menu.

Product Installation

Install the Microsoft Excel product before you install the MATLAB and
Spreadsheet Link EX software. To install the Spreadsheet Link EX Add-In,
follow the instructions in the MATLAB installation documentation. Select the
Spreadsheet Link EX check box when choosing components to install.

Files and Directories Created by the Installation

Note Throughout this document the notation matlabroot is the MATLAB

root folder, the folder where the MATLAB softw a re is installed o n your system.

The Spreadsheet Link EX installation program creates a subdirectory under

matlabroot\toolbox\.Theexlink folder contains the following files:

1-3

1 Getting Started

• excllink.xla: The Spreadsheet Link EX Add-In

ExliSamp.xls: Spreadsheet Link EX example files described in this

•

documentation

The installation also creates a Spreadsheet Link EX initialization file,

exlink.ini, in the appropriate Windows folder (for example, C:\Winnt).

The Spreadsheet Link EX software uses
already be in the appropriate Windows system folder (for example,

C:\Winnt\system32). If not, consult your system administrator.

Kernel32.dll,whichshould

Modifying Your System Path

Add matlabroot\bin to your system path. For more information about
editing your system path, consult your Windows documentat ion or your
system administrator.

1-4

Configuring the Spreadsheet Link™ EX Software

Configuring the Spreadsheet Link EX Software

In this section...

“Configuring Version 2003 and Earlier Versions of the Microsoft®Excel
Software” on page 1-5

“Configuring Version 2007 of the Microsoft®Excel Softw are” on page 1-7

“Setting Spreadsheet Link EX Preferences” on page 1-11

Configuring Version 2003 and Earlier Versions of the
Microsoft Excel Software

1 Start Microsoft Excel.

2 Enable the Spreadsheet Link EX Add-In.

a Click Tools > Add-Ins. The Add-Ins dialog box appears.

b Click Browse.

c Select matlabroot\toolbox\exlink\excllink.xla.

Note Throughout this document the notation matlabroot is the

MATLAB root folder, the folder where the MATLAB software is installed
on your system.

d Click OK.

In the Add-Ins dialog box, the Spreadsheet Link EX for use with
MATLAB check box is now selected.

e Click OK to exit the Add-Ins dialog box.

1-5

1 Getting Started

The Spreadsheet Link EX Add-In loads now and with each subsequent Excel
session.

The MATLAB Command Window button appears on the Microsoft
Windows taskbar.

The Spreadsheet Link EX toolbar appears on your Excel worksheet.

Start

MATLAB

Send data to

MATLAB

The Spreadsheet Link EX software is now r eady for use.

Execute MATLAB

command

Retrieve data

from MATLAB

Import current

MATLAB figure

Start MATLAB

Function Wizard for

Spreadsheet Link EX

Set MATLAB

Preferences

1-6

Configuring the Spreadsheet Link™ EX Software

Configuring Version 2007 of the Microsoft Excel Software

1 Start a Microsoft Excel session.

2 Enable the Spreadsheet Link EX Add-In:

a Click , the Microsoft Office Button.

b Click Excel Options. The Excel Options dialog box appears.

c Click Add-Ins.

d From the Manage selection list, choose Excel Add-Ins.

e Click Go. The Add-Ins dialog box appears.

f Click Browse.

g Select mat labr oot\toolbox\exlink\excllink2007.xlam.

h Click Open.

i In the Add-Ins dialog box, the Spreadsheet Link EX for use with

MATLAB check box is now selected.

1-7

1 Getting Started

1-8

j Click OK to close the Add-Ins dialog box.

k Click OK to close the E xcel Options dialog box.

The Spreadsheet Link EX Add-In loads now and with each subsequent Excel
session.

Configuring the Spreadsheet Link™ EX Software

The MATLAB Command Window button appears on the Microsoft
Windows taskbar:

TheMATLABgroupappearsonthetoprightoftheHometabinyourExcel
worksheet:

The Spreadsheet Link EX software is now r eady for use.

Right-click on a cell for MATL AB options. The following menu appears:

1-9

1 Getting Started

1-10

Note Using both the 2003 and 2007 Add-Ins referenced in Excel 2007 causes
problems w ith the context sensitive menu. Use only one Add-In at a time to
avoid this issue.

Configuring the Spreadsheet Link™ EX Software

Setting Spreadsheet Link EX Preferences

Use the Preferences dialog box to set Spreadsheet Link EX preferences.
Click the preferences button in the Excel toolbar or Matlab group to open
this dialog box.

rences include:

Prefe

• Start

MATL

• MATL

fold

• Use

fol
an E

MATLAB when Excel starts (enabled by default) starts a
AB session automatically when an Excel session starts.

AB current working folder lets you specify the current working

er for your MATLAB session at startup.

MATLAB desktop starts the MATLAB desktop, including the current

der, workspace, command history, and Command Window panes, when

xcel session starts.

1-11

1 Getting Started

• Force use of MATLAB cell arrays with MLPutM atrix enables the

MLPutMatrix function to use cell arrays for data transfer between the Excel

software and the MATLAB workspace.

• Treat missing/empty cells as NaN sets data in missing or empty cells to

NaN or zero.

1-12

Starting and Stopping the Spreadsheet Link™ EX Software

Starting and Stopping the Spreadsheet Link EX Software

In this section...

“Automatically Starting the Spreadsheet Link EX Software” on page 1-13

“Manually Starting the Spreadsheet Link EX Software” on page 1-13

“Connecting to an Existing MATLAB Session” on page 1-14

“Stopping the Spreadsheet Link EX Software” on page 1-14

Automatically Starting the Spreadsheet Link EX Software

When installed and configured according to the instructions in “Configuring
the Spreadsheet Link EX Software” on page 1-5, the Spreadsheet Link EX
and MATLAB software a utomatically start when you start a Microsoft Excel
session.

Manually Starting the Spreadsheet Link EX Software

To start the Spreadsheet Link EX and MATLAB software manually from the
Excel interface:

1 Click Tools > Macro.

2 Enter matlabinit into the Macro Name/Reference box.

For more information about the
“Function Reference”.

3 Click Run.

The MATLAB Comm and Window button appears on the Microsoft Windows
taskbar.

matlabinit function, see Chapter 3,

1-13

1 Getting Started

Connecting to an Existing M ATLAB Session

To connect a new Excel session to an existing MATLAB session, start
MATLAB with the
MATLAB as an automation server. The Command Window is minimized, and
the Desktop is not running.

1 Right-click your MATLAB shortcut icon.

2 Select Properties.

/automation command-line option. This option starts

3 Click the Sho

4 Add the string /automation in the Target field. Remember to leave a

space between

5 Click OK.

Note This option works only if the current MATLAB session is a registered

automation version. If not, the Excel software starts a ne w MATLAB session
rather than connecting to the existing one.

rtcut tab.

matlab.exe and /automation.

Stopping the Spreadsheet Link EX Software

• To stop both the Spreadsheet Link EX and MATLAB software, stop the

Excel session as you normally would.

• To stop the Spreadsheet Link EX and MATLAB software and leave the

Excel session running, enter the
worksheet cell. You can use the
the Spreadsheet Link EX and MATLAB sessions manually.

=MLClose() command into an Excel

MLOpen or matlabinit functions to restart

1-14

About Functions

In this section...

“How Spreadsheet Link EX Functions Differ from Microsoft®Excel
Functions” on page 1-15

“Types of Spreadsheet Link EX Functions” on page 1-15

“Using Worksheets” on page 1-16

“Working with Arguments in Spreadsheet Link EX Functions” on page 1-18

“Using the MATLAB Function Wizard for the Spreadsheet Link EX
Software” on page 1-20

“Examples: Using Spreadsheet Link EX Functions in Macros” on page 1-23

How Spreadsheet Link EX Functions Differ from
Microsoft Ex cel Functions

About Functions

• Spreadsheet Link EX functions perform an action, while Microsoft Excel

functions return a value.

• Spreadsheet Link EX function names are not case sensitive; that is,

MLPutMatrix and mlputmatrix are the same.

• MATLAB function names and variable names are case sensitive; that is,

BONDS, Bonds,andbonds are three different MATLAB variables. Standard

MATLAB function names are always lowercase; for example,

Note Excel operations and function ke ys may behave differently with
Spreadsheet Link EX functions.

plot(f).

Types of Spreadsheet Link EX Functions

Spreadsheet Link EX functions manage the connection and data exchange
between the Excel software and the MATLAB workspace, without your
ever needing to leave the Excel environment. You can run functions as
worksheet cell formulas or in macros. The Spreadsheet Link EX software
enables theExcel product to act as an easy-to-use data-storage and

1-15

1 Getting Started

application-development front end for the MATLAB software, which is a
powerful computational and graphical processor.

There are two types of Spreadsheet Link EX functions: link management
functions and data management functions.

Link management functions initialize, start, and stop the Spreadsheet Link
EX and MATLAB software. You can run any link management function other
than

matlabinit as a worksheet cell formula or in macros. You must run

the

matlabinit function from the Excel Tools > Macro menu, or in macro

subroutines.

Data management functions copy data between the Excel software and
the MATLAB workspace, and execute MATLAB commands in the Excel
interface. You can run any data management function other than
and MLGetVar as a worksheet cell formula or in macros. The MLPutVar and

MLGetVar functions can run only in macros.

For more information about Spreadsheet Link EX functions, see Chapter 3,
“Function Reference”.

MLPutVar

1-16

Using Worksheets

Entering Functions into Worksheet Cells

Spreadsheet Link EX functions expect A1-style worksheet cell reference s;
that is, columns designated with letters and rows with numbers (the default
reference style). If your worksheet shows columns designated w ith numbers
instead of letters:

1 Click Tools > Options.

2 Click the General tab.

3 Under Settings,cleartheR1C1 reference style check box.

Enter Spreadsheet Link EX functions directly into worksheet cells as
worksheet formulas. Begin worksheet formulas with + or = and enclose
function arguments in parentheses. The following example uses
to put the data in cell C10 into matrix A:

MLPutMatrix

About Functions

=MLPutMatrix("A", C10)

For more information on specifying arguments in Spreadsheet Link EX
functions, see “Working with Arguments in Spreadsheet Link EX Functions”
on page 1-18.

Note Do not use the Excel Function Wizard. It can generate unpredictable
results.

After a Spreadsheet Link EX function successfully executes as a worksheet
formula, the cell contains the value

0. While the function executes, the cell

might continue to show the formula you entered.

To change the active cell when an operation completes, click Excel Tools
Options > Edit > Move Selection after Enter. This action p rovides a
useful confirmation for lengthy operations.

Automatic Calculation Mode Vs. Manual Calculation Mode

Spreadsheet Link EX functions are most effective in automatic calculation
mode. To automate the recalculation of a Spreadsheet Link EX function, add
to it a cell whose value changes. In the following example, the
function reexecutes when the value in cell C1 changes:

=MLPutMatrix("bonds", D1:G26) + C1

Note Be careful to avoid creating endless recalculation loops.

To use MLGetMatrix in manual calculation mode:

1 Enter the function into a cell.

2 Press F2.

3 Press Enter. The function executes.

MLPutMatrix

1-17

1 Getting Started

Spreadsheet Link EX functions do not automatically adjust cell addresses.
If you use explicit cell addresses in a function, you must edit the function
arguments to reference a new cell address when you do either of the following:

• Insert or delete rows or columns.

• Move or copy the function to another cell.

Note Pressing F9 to recalculate a worksheet affects only Excel functions.

This key does not operate on Spre adshee t Link EX functions.

Working with Arguments in Spreadsheet Link EX Functions

This section d escribes tips for managing variable-name arguments and
data-location arguments in Spreadsheet Link EX functions.

Variable-Name Arguments

1-18

• You can directly or indirectly specify a variable-name argument in most

Spreadsheet Link EX functions:

- T o specify a variable name directly, encloseitindoublequotationmarks;

for example,

MLDeleteMatrix("Bonds").

- To specify a variable name as an indirect reference, enter it without

quotation marks. The function evaluates the contents of the argument to
get the variable name. The argument must be a worksheet cell address
or range name; for example,

Data-Location Arguments

• A data-location argument must be a worksheet cell address or range name.

• Do not enclose a data-location argument in quotation marks (except in

MLGetMatrix, which has unique argument conventions).

• A data-location argument can include a worksheet number; for example,

Sheet3!B1:C7 or Sheet2!OUTPUT.

MLDeleteMatrix(C1).

About Functions

Note You can reference special characters as part of a worksheet name in

MLGetMatrix or MLPutMatrix by embedding the worksheet name within

single quotation marks (

'').

1-19

1 Getting Started

Using the MATLAB Function Wizard for the
Spreadsheet Link EX Software

The MATLAB Function Wizard for the Spreadsheet Link EX software allows
youtobrowseMATLABdirectoriesandrun functions from within the Excel
interface.

List functions available
for specified directory/category

Display list of MATLAB working directories
and available function categories

1-20

Select function signature
and enter formula into
specified spreadsheet cell

Display help for given
function signature

Refresh
directory/category list

About Functions

You can use this wizard to:

1 DisplayalistofallMATLABworkingdirectoriesandfunction

categories

All directories or categories in the current

MATLABPATH display in the Select

a category field. Click an entry in the list to select it. Each entry in the

list displays as a folder path plus a description read from the

Contents.m

file in that folder. If no Contents.m file is found, the folder/category display
notifies you as follows:

finance\finsupport -(No table of contents file)

To refresh the folder/category list, click the Update button.

2 Choose a particular folder or category, and list functions available

for that folder or category

Afteryouselectafolderorcategory,available functions for that folder or
category display in the Select a function field. Click a function name
to select it.

3 Parse a specified function signature and enter a form ula into the

current spreadsheet cell

After you select a function, available function signatures for the specified
function display in the Select a function signature field. Click a function
signaturetodisplaytheFunctionArgumentspane.

1-21

1 Getting Started

Specify cell for function output
(optional)

Scroll through list of
function input arguments

1-22

Double-click function signature
to display Function Arguments pane ...

Enter function arguments

By default, the output of the selected function appears in the current
spreadsheet cell using the Spreadsheet Link EX function

matlabfcn.In

the following example, the output displays in the current spreadsheet cell
and generates a MATLAB figure:

=matlabfcn("plot",Sheet1!$B$2:$D$4)

Specifying a target range of cells using the Optional output cell(s) field
in the Function Arguments dialog box causes the selected function to
appear in the current spreadsheet cell as an argument of the

matlabsub

function. In addition, matlabsub includes an argument that indicates
where to write the function’s output. In the following example, the data
from

A2 is input to the rand function, whose target cell is B2:

=matlabsub("rand","Sheet1!$B$2",Sheet1!$A$2)

4 Display online help headers for functions

After you select a function signature from the Select a function
signature field, its help header appears in the Function Help field.

About Functions

Examples: Using Spreadsheet Link EX Functions in Macros

About the Examples

This section contains examples that show how to manipulate MATLAB data
using Spreadsheet Link EX.

• For an example of how to exchange data between the MATLAB and Excel

workspaces, see “Importing and Exporting Data between the Microsoft
Excel Interface and the MATLAB Workspace” on page 1-26.

• For an example of how to export data from the M ATLA B workspace and

display it in an Excel worksheet, see “Sending MATLAB Data to an Excel
Worksheet and Displaying the Results” on page 1-24.

®

1-23

1 Getting Started

SendingMATLABDatatoanExcelWorksheetandDisplaying
the Results

In this example, you run MATLAB commands using VBA, send MATLAB
data to the Excel software, and display the results in an Excel dialog box.

1 Start an Excel session.

2 Initialize the MATLAB session by clicking the startmatlab button in the

Spreadsheet Link EX toolbar or by running the

3 If the Spreadsheet Link EX Add-In is not enabled, enable it.

matlabinit function.

• For instructions on enabling this Add-In for the Excel 2003 software, see

“Configuring Version 2003 and Earlier Versions of the Microsoft

®

Excel

Software” on page 1-5.

• For instructions on enabling this Add-In for the Excel 2007 software, see

“Configuring Version 2007 of the Microsoft

4 Enable the Spreadsheet Link EX software as a Reference in the Microsoft

®

Excel Software” on page 1-7.

Visual Basic®editor.

a Open a Visual Basic

®

session.

• If you are running the Excel 2003 software, click

Tools > Macro > Visual Basic Editor.

• If you are running the Excel 2007 software, click the Visual Basic

button,

b In the Visual Basic toolbar, click Tools > References.

c In the References — VBA Project dialog box, select the

,orpressAlt+F11.

SpreadsheetLinkEX check box.

d Click OK.

5 In the Visual Basic editor, create a module.

a Right-click the Microsoft Excel Objects folder in the Project —

VBAProject browser.

®

1-24

b Select Insert > Module.

6 Enter the following code into the module window:

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)

About Functions

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

Tip Copy and paste this code into the Visual Basic editor from the HTML
version of the documentation.

1-25

1 Getting Started

7 Run the code. Press F5 or click Run > Run Sub/UserForm.

The following dialog box appears.

8 Click OK to

Note Do not

function

Tip In ma
argumen

close the dialog box.

include

MatlabRequest in a macro function unless the m acro

is called from a subroutine.

cros, leave a space between the function name and the first

t; do not use parentheses.

Importing and Exporting Data between the Microsoft Excel
Interface and the MATLAB Workspace

• This example uses MLGetMatrix in a macro subroutine to export data from

the MATLAB matrix

est1()

Sub T

tMatrix "A", "Sheet1!A5"

MLGe

labRequest

Mat

Sub

End

A into the Excel worksheet Sheet1.

1-26

Note The MatlabRequest function initializes i nte rnal Spreadsheet Link
EX variables and enables

• This example uses MLPutMatrix in a macro subroutine to import data into

the M ATLAB matrix

Sheet1.

Sub Test2()

Set myRange = Range("A1:C3")
MLPutMatrix "A", myRange

End Sub

Working with Dates

Default Microsoft Excel date numbers represent the number of days that have
passed since January 1, 1900; for example, May 15, 1996 is represented as
35200 in the Excel software.

Working with Dates

MLGetMatrix to function in the subroutine.

A, from a specified cell range in the Excel wo rksheet

However, MATLAB date numbers represent the number of days that have
passed since January 1, 0000, so May 15, 1996 is represented as 729160 in
the MATLAB software. Therefore, the difference in dates between the Excel
software and the MATLAB software is a constant, 693960 (729160 minus

35200).

To use date numbers in MATLAB calculations, apply the 693960 constant as
follows:

• Add it to Excel date numbers that are read into the MATLAB software.

• Subtract it from MATLAB date numbers that are read into the Excel

software.

Note If you use the optional Excel 1904 date system, the constant is

695422.

1-27

1 Getting Started

Dates are stored internally in the Excel software as numbers and are
unaffected by locale.

1-28

Localization Information

This document uses the Microsoft Excel software with an English (United

States)

use the Spreadsheet Link EX software with a non­Windows d esktop environment, certai n syntactical elements may not work as
illustrated. For example, you may have to replace the comma (
within Spreadsheet Link EX commands with a semicolon (

Please consult your Windows documentation to determine which regional
setting differences exist among no n-U.S. versions.

Microsoft Windows regional setting for illustrative purposes. If you

Localization Information

English (United States)

,)delimiter

;) or other operator.

1-29

1 Getting Started

1-30

Solving Problems with the Spreadsheet Link EX Software

• “Running the Examples” on page 2-2

• “Modeling Data Sets Using Data Regression and Curve Fitting” on page 2-3

• “Interpolating Data” on page 2-11

2

• “Pricing Stock Options Using the Binomial Model” on page 2-15

• “Calculating and Plotting the Efficient Frontier of Financial Portfolios”

on page 2-19

• “MappingTimeandBondCashFlows”onpage2-23

Note For other applications, see “Using Spreadsheet Link EX with

Bioinformatic Data”.

2 Solving Problems with the Spreadsheet Link™ EX Software

Running the Exam ples

The following sections show how the Microsoft Excel, Spreadsheet Link EX,
and MATLAB software work together to solve real-world problems.

These e xamples are included with the Spreadsheet Link EX product. To run
them:

1 Start Excel, Spreadsheet Link EX, and MATLAB sessions.

2 Navigate to the folder matlabroot\toolbox\exlink\.

e

3 Open the fil

4 Execute the examples as needed.

Note Examples 1 and 2 use MATLAB functions only. Examples 3, 4, and 5

use Financial Toolbox™ functions. The Financial Toolbox software requires
the S tatistics Toolbox™ and Optimization Toolbox™ products.

ExliSamp.xls

2-2

Modeling Data Sets Using Data Regression and Curve Fitting

Modeling Data Sets Using Data Regression and Curve
Fitting

In this section...

“Using Worksheets” on page 2-3

“Using Macros” on page 2-6

Regression t
describe the
mathematic
simplify th

This examp
thesameex
example us
Spreadsh
then exec
version a

echniques and curve fitting attempt to find functions that

al models of a data set. MATLAB matrix operators and functions

is task.

le show s both data regression and curve fitting. It also executes

ample in a worksheet version and a macro version. The

es Microsoft Excel worksheets to organize and display the data.
eet Link EX functions copy the data to the MATLAB workspace, and
utes MATLAB computational and graphic functions. The macro

lso returns output data to an Excel worksheet.

Using Wo

1 Click t

this ex

he Sheet1 tab on the

ample appears.

relationship among variables. In effect, they attempt to build

rksheets

ExliSamp.xls window. The worksheet for

2-3

2 Solving Problems with the Spreadsheet Link™ EX Software

2-4

The worksheet contains one n amed range: A4:C28 is named DATA and
contains the data set for this example.

2 Make E5 theactivecell. PressF2;thenpressEnter to execute the

Spreadsheet Link EX function that copies the sample data set to the
MATLAB workspace. The data set contains 25 observations of three
variables. There is a strong linear dependence among the observations; in
fact, they are close to being scalar multiples of each other.

3 Move to cell E8 and press F2;thenpressEnter. Repeat with cells E9 and

E10. These Spreadsheet Link EX functions regress the third column of data

on the other two columns, and create the following:

• Asinglevector

• A three-column matrix

y containing the third-column data.

A, that consists of a column of ones followed by

the rest o f the data.

Modeling Data Sets Using Data Regression and Curve Fitting

4 Execute the function in cell E13. This function computes the regression

coefficients by using the MATLAB back slash
(overdetermined) sy stem of linear equations,

5 Execute the function in cell E16. MATLAB matrix-vector multiplication

produces the regressed result (

6 Execute the functions in cells E19, E20,andE21. These functions do the

fit).

(\) operation to solve the

A*beta = y.

following:

a Compare the original data with fit.

b Sort the data in increasing order and apply the same permutation to fit.

c Create a scalar for the number of observations.

7 Execute the functions in cells E24 and E25. Often it is useful to fit a

polynomial equation to data. To do so, you would ordinarily have to set up
a system of simultaneous linear equations and solve for the coefficients.
The MATLAB
fifth-degree polynomial. The
polynomial at each data point to check the goodness of fit (

polyfit function automates this procedure, i n this case for a

polyval function then evaluates the resulting

newfit).

8 Execute the fu n c tio n in cell E28. The MATLAB plot function graphs the

original data (bl ue circles), the regressed result

fit (dashed red line), and

the po lynomial result (solid green line). It also adds a legend.

2-5

2 Solving Problems with the Spreadsheet Link™ EX Software

2-6

Since the data is closely correlated but not exactly linearly dependent, the

fit curve (dashed line) shows a close, but not an exact, fit. The fifth-degree

polynomial curve,

When you finish this version of the example, close the figure window.

newfit, is a more accurate mathematical model for the data.

Using Macros

1 Click the Sheet2 tab on ExliSamp.xls. The worksheet f or this example

appears.

Modeling Data Sets Using Data Regression and Curve Fitting

2 Make cell A4 the active cell, but do not execute it yet.

4

Cell
Visua

calls the macro CurveFit, which you can examine in the Microsoft

A

l Basic environment.

2-7

2 Solving Problems with the Spreadsheet Link™ EX Software

2-8

3 While this module is open, make sure that the Spreadsheet Link EX add-in

is enabled.

• If you are using the Excel 2003 software:

a Click Tools > References.

b In the References dialog box, make sure that the excllink.xla

check box is selected. If not, select it.

c Click OK.

• If you are using the Excel 2007 software:

d Click the Microsoft Office Button, .

e Click Options. The Excel Options pane appears.

f Click Add-Ins.

Modeling Data Sets Using Data Regression and Curve Fitting

g From the Manage selection list, choose Excel Add-Ins.

h Click Go.TheAdd-Inspaneappears.

i Make sure that the Spreadsh eet Link EX 3.0.1 for use with

MATLAB check box is selected. If not, select it.

j Click OK to close the Add-Ins pane.

k Click OK to close the Excel Options pane.

4 In cell A4 of Sheet2,pressF2;thenpressEnter to execute the CurveFit

macro. The macro does the following:

a Runs the same functions as the worksheet example (in a slightly

different order), including plotting the graph.

b Calls the MLGetMatrix function in the CurveFit macro. This macro

copies to the worksheet the original data
regressed data

fit, and the polynomial data newfit.

y (sorted), the corresponding

2-9

2 Solving Problems with the Spreadsheet Link™ EX Software

2-10

Interpolating Data

Interpolation is a process for estimating values that lie between known data
points. It is important for applications such as signal and image processing
and data visualization. MATLAB interpolation functions let you balance the
smoothness of data fit with execution speed and efficient memory use.

This example uses a two-dimensional data-gridding interpolation function
on thermodynamic data, where volume has been measured for time
and temperature values. It finds the volume values underlying the
two-dimensional, time-temperature function for a new set of time and
temperature coordinates.

The example uses a Microsoft Excel worksheet to organize and display the
original data and the interpolated output data. You use Spreadsheet Link
EX functions to copy the data to and from the MATLAB workspace, and then
execute the MATLAB interpolation function. Finally, you invoke MATLAB
graphics to display the interpolated data in a three-dimensional color surface.

Interpolating Data

1 Click the Sheet3 tab on ExliSamp.xls. The worksheet f or this example

appears.

2-11

2 Solving Problems with the Spreadsheet Link™ EX Software

2-12

The worksheet contains the measured thermodynamic data in cells A5:A29,

B5:B29,and C5:C29. The time and temperature values for interpolation

are in cells

2 Make A33 theactivecell. PressF2;thenpressEnter to execute the

Spreadsheet Link EX function that passes the

E7:E30 and F6:T6, respectively.

Time, Temp,andVolume

labels to the MATLAB workspace.

e

3 Mak

A34 theactivecell. PressF2;thenpressEnter to execute the

eadsheet Link EX function that copiestheoriginaltimedatatothe

Spr

LABworkspace. Movetocell

MAT

A35 and execute the function to copy the

Interpolating Data

original temperature data. Execute the function in cell A36 to copy the
original volume data.

4 Move to cell A39 and press F2;thenpressEnter to copy the interpolation

time values to the MATLAB workspace. Execute the function in cell

A40 to

copy the interpolation temperature values.

5 Execute the function in cell A43. griddata is the MATLAB two-dimensional

interpolation function that generates the interpolated volume data using
the inverse distance method.

6 Execute the functions in cells A46 and A47 to transpose the interpolated

volume data and copy it to the Excel worksheet. The data fills cells

F7:T30,

which are enclosed in a border.

7 Execute the function in cell A50. The MATLAB softw are plots and l ab els

the i nterpolated data on a three-dimensional color surface, with the color
proportional to the interpolated volume data.

2-13

2 Solving Problems with the Spreadsheet Link™ EX Software

2-14

When you finish the example, close the figure window.

Pricing Stock Options Using the Binomial Model

Pricing Stock Options Using the Binomial Model

The Financial Toolbox product provides functions that compute prices,
sensitivities, and profits for portfolios of options or other equity derivatives.
This example uses the binomial model to price an option. The binomial
model assumes that the probability of each possible price over time fo llows a
binomial distribution. That is, prices can move to only two values, one up or
one down, over any short time period. Plotting these two values over time is
known as building a binomial tree.

This example organizes and displays input and output data using a Microsoft
Excel worksheet. Spreadsheet Link EX functions copy data to a MATLAB
matrix, calculate the prices, and return data to the worksheet.

Note This example requires the Financial Toolbox software.

1 Click the Sheet4 tab on ExliSamp.xls to open the wo rks he et for this

example.

2-15

2 Solving Problems with the Spreadsheet Link™ EX Software

2-16

The worksheet contains three named ranges:

B4:B10 named bindata.Twocellsinbindata contain formulas:

•

B7 contains =5/12

–

– B8 contains =1/12

• B15 named asset_tree.

B23 named value_tree.

•

2 Make D5 theactivecell. PressF2;thenpressEnter to execute the

Spreadsheet Link EX function that copies the asset data to the MATLAB
workspace.

ve to

3 Mo

D8 and execute the function that computes the binomial prices.

Pricing Stock Options Using the Binomial Model

4 Execute the functions in D11 and D12 to copy the price data to the Excel

worksheet.

The worksheet looks as follows.

Read the asset price tree as follows:

• Period 1 shows the up and down prices.

• Period 2 shows the up-up, up-down, and down-down prices.

• Period 3 shows the up-up-up, up-up, down-down, and down-down-down

prices.

• And so on.
Ignorethezeros.Theoptionvaluetreegivestheassociatedoptionvaluefor
each no de in the price tree. The option value is zero for prices significantly

2-17

2 Solving Problems with the Spreadsheet Link™ EX Software

above the exercise price. Ignore the zeros that correspond to a zero in the
price tree.

5 TrychangingthedatainB4:B10 and reexecuting the Spreadsheet Link

EX functions.

Note If you increase the time to maturity (B7) o r change the time
increment (

6 When you finish the example, close the figure window.

B8), yo u may need to enlarge the output tree areas.

2-18

Calculating and Plotting the Efficient Frontier of Financial Portfolios

Calculating and Plotting the Efficient Frontier of Financial
Portfolios

MATLAB and Financial Toolbox functions compute and plot risks, variances,
rates of return, and the efficient frontier of portfolios. Efficient portfolios have
the lowest aggregate variance, or risk, for a given return.Microsoft Excel
and the Spreadsheet Link EX software let you set up data, execute financial
functions and MATLAB graphics, and display numeric results.

This example analyze s three portfolios, using rates of return for six time
periods. In actu al practice, these functions can analyze many portfolios over
many time periods, limited only by the amount of computer memory available.

Note This example requires the Financial Toolbox software.

1 Click the Sheet5 tab on ExliSamp.xls. The worksheet f or this example

appears.

2-19

2 Solving Problems with the Spreadsheet Link™ EX Software

2-20

2 Make A15

Link EX f
MATLAB s

3 Make A16 theactivecelltocopytheportfolioreturndatatotheMATLAB

theactivecell. PressF2;thenpressEnter. The Spreadsheet

unction transfers the labels that describe the output that the

oftware computes.

workspace.

4 Execute the functions in A19 and A20 to compute the Financial Toolbox

efficient frontier function for 20 points along the frontier.

5 Exec

the o

ute the Spreadsheet Link EX functions in

utput data to the Excel worksheet.

A23, A24,andA25 to copy

The worksheet looks as follows.

Calculating and Plotting the Efficient Frontier of Financial Portfolios

The data describes the efficient frontier for these three portfolios: that set
of points representing the highest rate of return (

ROR) for a given risk. For

each of the 20 points along the frontier, the weighted investment in each
portfolio (

6 Now move to A28 and press F2;thenpressEnter to execute the Financial

Weights) would achieve that rate of return.

Toolbox function that plots the efficient frontier for the same portfolio data.

The following figure appears.

2-21

2 Solving Problems with the Spreadsheet Link™ EX Software

2-22

The light blue line shows the efficient frontier. Note the change in slope
above a 6.8% return because the Corporate Bond portfolio no longer
contributes to the efficient frontier.

7 To try running this example using different data, close the figure window

and change the data in cells
Link EX functions. The worksheet then shows the new frontier data, and
the M ATL AB software displays a new efficient frontier graph.

When you fini s h this example, close the figure window.

B4:D9. Then reexecute all the Spreadsheet

Mapping Time and Bond Cash Flows

ThisexampleshowshowtousetheFinancialToolboxandSpreadsheetLink
EX software to compute a set of cash flow amounts and dates, given a portfolio
of five bonds with known maturity dates and coupon rates.

1 Click the Sheet6 tab on ExliSamp.xls. The worksheet f or this example

appears.

Mapping Time and Bond Cash Flows

e

2 Mak

A18 the active cell. P ress F2,thenEnter to execute the Spreadsheet

k EX function that transfers the column vector

Lin

TLAB workspace.

MA

Maturity to the

2-23

2 Solving Problems with the Spreadsheet Link™ EX Software

3 Make A19 the active cell to transfer the column vector Coupon Rate to the

MATLAB workspace.

4 Make A20 theactivecelltotransferthesettlementdatetotheMATLAB

workspace.

5 Execute the functions in cells A23 and A24 to enable the Financial Toolbox

software to compute cash flow amounts and dates.

6 Now execute the functions in cells A27 through A29 to transform the dates

into string form contained in a cell array.

7 Execute the functions in cells A32 through A34 to transfer the data to the

Excel worksheet.

2-24

Mapping Time and Bond Cash Flows

8 Finally, execute the function in cell A37 to display a plot of the cash flows

for each portfolio item.

2-25

2 Solving Problems with the Spreadsheet Link™ EX Software

2-26

9 When you finish the example, close the figure window.

Function Reference

3

Link M anagement (p. 3-2)

Data Management (p. 3-3)

Work with link management
functions

Work with data management
functions

3 Function Reference

Link Management

matlabinit

MLAutoStart

MLClose

MLOpen

MLUseCellArray

Initialize Spreadsheet Link EX
software and start MATLAB process

Automatically start MATL AB
process

End MATLAB process

Start MATLAB process

Toggle MLPutMatrix to use MATLAB
cell arrays

3-2

Data Management

Data Management

matlabfcn

matlabsub

MLAppendMatrix

MLDeleteMatrix

MLEvalString

MLGetFigure

MLGetMatrix

MLGetVar

MLMissingDataAsNaN

MLPutMatrix

MLPutVar

MLShowMatlabErrors

Evaluate MATLAB command given
Microsoft Excel data

Evaluate MATLAB command given
Microsoft Excel data and designate
output location

Create or append MATLAB matrix
with data from Microsoft Excel
worksheet

Delete MATLAB matrix

Evaluate command in MATLAB
software

Import current MATLAB figure into
Microsoft Excel spreadsheet

Write contents of MATLAB matrix
to Microsoft Excel worksheet

Write contents of MATLAB matrix
in Microsoft Excel VBA variable

Set empty cells to NaN or 0

Create or overwrite MATLAB matrix
with data from Microsoft Excel
worksheet

Create or overwrite MATLAB matrix
with data from Microsoft Excel VBA
variable

Return standard Spreadsheet Link
EX errors or full MATLAB errors
using

MLEvalString

3-3

3 Function Reference

MLStartDir

MLUseFullDesktop

Specify MATLAB current working
folder after startup

Specify whether to use full MATLAB
desktop or MATLAB Command
Window

3-4

Functions — Alphabetical List

4

matlabfcn

Purpose Evaluate MATLAB command given Microsoft Excel data

Syntax

Worksheet: matlabfcn(command, inputs)

command

inputs

MATLAB command to evaluate. Embed the command
in dou ble quotation marks; f or example,

Variable length input argument list passed to a
MATLAB command. The argument list may contain
a range of works heet cells that contain input data.

"command".

Description Passes the command to the MATLAB workspace for evaluation, given

the function input data. The function returns a single value or string
depending upon the MATLAB output. The result is returned to the
calling worksheet cell. This function is intended for use as an Excel
worksheet function.

Examples 1 Add the data in worksheet cells B1 through B10, and then return the

sum to the active worksheet cell:

matlabfcn("sum", B1:B10)

2 Plot the data in worksheet cells B1 through B10,usingx as the

marker type:

matlabfcn("plot", B1:B10, "x")

See Also matlabsub

4-2

matlabinit

Purpose Initialize Spreadsheet Link EX software and start MATLAB process

Syntax matlabinit

Note To run matlabinit from the Microsoft Excel toolbar, click

Tools > Macro.IntheMacro Name/Reference box, enter

matlabinit and click Run. Alternatively, you could include this

function in a macro subroutine. You cannot run
worksheet cell formula or in a macro function.

Description Initializes the Spreadsheet Link EX software and starts MATLAB

process. If the Spreadsheet Link EX software has been initialized and
the M ATLAB software is running, subsequent invocations do nothing.
Use

matlabinit to start Spreadsheet Link EX and MATLAB sessions

manually when you have set
to yes, matlabinit executes automatically.

MLAutoStart to no.IfyousetMLAutoStart

matlabinit as a

See Also MLAutoStart, MLOpen

4-3

matlabsub

Purpose Evaluate M ATLAB command given Microsoft Excel data and designate

output location

Syntax

Worksheet: matlabsub(command, edat, inputs)

command

edat

inputs

MATLAB command to evaluate. Enter the
MATLAB command in double quotation marks,
as

"command".

Worksheet location where the functio n writes
the returned date.
directly specifies the location and it must be a cell
address or a range name.
marks) is an indirect reference: the function
evaluates the contents of

edat must be a worksheet cell address or range

name.

Variable length input argument list passed to
MATLAB command. This argument list can
contain a range of worksheet cells that contain
input data.

"edat" (in quotation marks)

edat (without quotation

edat to get the location.

Description Passes the specified command to the MATLAB workspace for

evaluation, given the function input data. The function returns a single
value or string depending upon the MATLAB output. This function is
intended for use as an Excel worksheet function.

To return an array of data to theMicrosoft Excel Visual Basic for
Applications (VBA) workspace, see

MLEvalString and MLGetVar.

4-4

Caution edat must not include the cell that contains the matlabsub
function. In other words, be careful not to overwrite the function itself.
Also make s ure there is enough room in the worksheet to write the
matrix contents. If there is insufficient room, the function generates a
fatal error.

matlabsub

Examples Sum the data in worksheet cells B1 through B10, and then return the

output to cell

matlabsub("sum", "A1", B1:B10)

See Also matlabfcn

A1:

4-5

MLAppendMatrix

Purpose Create or append MATLAB matrix with data from Microsoft Excel

worksheet

Syntax

Worksheet: MLAppendMatrix(var_name, mdat)

Macro: MLAppendMatrix var_name, mdat

var_name

mdat

Name of MATLA B matrix to which to append
data.

"var_name" (in quotation marks) directly

specifies the matrix name.
quotation marks) is an indirect reference: the
function evaluates the contents of
get the matrix name, and
worksheet cell address or range name

Location of data to append to var_name. mdat
(no quotation marks). Mustbeaworksheetcell
address or range name.

If this argument is not initially an Excel
data type and you call the function from a
worksheet,
necessary type coercion.

If this argument is not an Excel
type and you call the function from within a
Microsoft V isual Basic macro, the call fails. The
error mess age
appears.

MLAppendMatrix performs the

ByRef Argument Type Mismatch

var_name (without

var_name must be a

Range data

var_name to

Range

Description Appends data in mdat to MATLAB matrix var_name.Createsvar_name

if it does not exist. The function checks the dimensions of var_name and

mdat to determine how to append mdat to var_name. If the dimensions

allow appending

mdat to var_name as new rows. If the dimensions do not match, the

function returns an error.
string data. Data types cannot be combined within the range specified

4-6

mdat as either new rows or new columns, it appends

mdat must contain either numeric data or

MLAppendMatrix

in mdat.Emptymdat cells become MATLAB matrix elements containing
zero if the data is numeric, and empty strings if the data is a string.

Examples Example 1: Append data from a worksheet cell range to

aMATLABmatrix

In this example, B isa2-by-2MATLABmatrix. Appendthedatain
worksheet cell range

MLAppendMatrix("B", A1:A2)

A1

A2

B is now a 2-by-3 matrix with the data from A1:A2 in the third column.

Example2: Appenddatafromanamedworksheetcell
range to a MATLAB matrix

B isa2-by-2MATLABmatrix. CellC1 contains the label (string) B,

and

new_data isthenameofthecellrangeA1:B2. Append the data in

cell range

A1:B2 to B:

A1:A2 to B:

MLAppendMatrix(C1, new_data)

B

is now a 4-by-2 matrix with the data from A1:B2 in the last two rows.

A1

A2

See Also MLPutMatrix

B1

B2

4-7

MLAutoStart

Purpose Automatically start MATLAB process

Syntax

Worksheet: MLAutoStart("yes")

MLAutoStart("no")

Macro: MLAutoStart "yes"

MLAutoStart "no"

"yes"

"no"

Automatically start the Spreadsheet Link EX and
MATLAB software every time a Microsoft Excel
session starts (default).

Cancel automatic startup of the Spreadsheet Link
EX and MATLAB software. If these products are
running, it does not stop them.

Description Sets automatic startup of the Spreadsheet Link EX and MATLAB

software. When the Spreadsheet Link EX software is installed, the
default is
session starts.

yes. A change of state takes effect the next time an Excel

Examples Cancel automatic startup of the Spreadsheet Link EX and MATLAB

software:

MLAutoStart("no")

These products do not start on subsequent Excel session invocations.

See Also matlabinit, MLClose, MLOpen

4-8

Purpose End MATLAB process

MLClose

Syntax

Worksheet: MLClose()

Macro: MLClose

Description Ends the MATLAB process, deletes all variables from the MATLAB

workspace, and tells the Microsoft Excel software that the MATLAB
software is no longer running. If no MATLAB process is running,
nothing happens.

See Also MLOpen

4-9

MLDeleteMatrix

Purpose Delete MATLAB matrix

Syntax

Worksheet: MLDeleteMatrix(var_name)

Macro: MLDeleteMatrix var_name

var_name

Name of MATL AB matrix to delete. "var_name"
(in quotation marks) directly specifies the matrix
name.
an indirect reference: the function evaluates the
contents of
and
range name.

var_name (without quotation marks) is

var_name to determine the matrix name,

var_name must be a worksheet cell address or

Description Deletes the named matrix from the MATLAB workspace.

Example Delete matrix A from the MATLAB workspace:

MLDeleteMatrix("A")

4-10

Purpose Evaluate command in MATLAB software

MLEvalString

Syntax

Worksheet: MLEvalString(command)

Macro: MLEvalString command

command

MATLAB command to evaluate. "command" (in
quotation marks) directly specifies the command.

command (without quotation marks) is an indirect

reference: the function evaluates the contents of

command to get the command, and command must be

a w orksheet cell address or range name.

Description Passes a command string to the MATLAB software for evaluation. The

specified action a lters only the MATLAB w ork s p ace . It has n o effect
on the Microsoft Excel workspace.

Examples Divide the MATLAB variable b by 2, and then plot it:

MLEvalString("b = b/2;plot(b)")

This command only modifies the MATLAB variable b.Toupdatedata
in the Excel worksheet, use

MLGetMatrix.

See Also MLGetMatrix

4-11

MLGetFigure

Purpose Import current MATLAB figure into Microsoft Excel spreadsheet

Syntax

Worksheet: MLGetFigure(width,height)

Macro: MLGetFigure width, height

width

height

Specify the width in normalized units of the
MATLAB figure when imported into an Excel
worksheet.

Specify the height in normalized units of the
MATLAB figure when imported into an Excel
worksheet.

Description Import the current MATLAB figure into an Excel worksheet, where the

top-left corner of the figure is the current spreadsheet cell.

If worksheet calculation mode is automatic,
when you enter the formula in a cell. If worksheet calculation mode
is manual, enter the
execute it. However, pressing
other worksheet functions and generate unpredictable results.

If you use
on the line after the MLGetFigure. MatlabRequest initializes internal
Spreadsheet Link EX variables and enables
a subroutine. Do not include
the function is called from a subroutine.

MLGetFigure in a macro subroutine, enter MatlabRequest

MLGetFigure function in a cell, then press F9 to

F9 in this situation may also reexecute

MatlabRequest in a macro function unless

MLGetFigure executes

MLGetFigure to function in

Examples Import the current MATLAB figure into an Excel worksheet. Adjust the

width of the figure to be half that of the original figure, and the height
to be a quarter that of the original figure:

MLGetFigure(.50,.25)

See Also MLGetMatrix, MLGetVar

4-12

MLGetMatrix

Purpose Write contents of M A TLAB matrix to Microsoft Excel worksheet

Syntax

Worksheet: MLGetMatrix(var_name, edat)

Macro: MLGetMatrix var_name, edat

var_name

edat

Name of MATLAB matrix to acces s."var_name" (in
quotation marks) directly specifies the matrix name.

var_name (without quotation marks) is an indirect

reference: the function evaluates the contents of

var_name to get the matrix name, a nd var_name

must be a worksheet cell address or range name.

var_name cannot be the MATLAB variable ans.

Worksheet location where the function writes the
contents of
directly specifies the location and it must be a cell
address or a range name.
marks) is an indirect reference: the function
evaluates the contents of
and

edat must be a worksheet cell address or range

name.

var_name. "edat" (in quotation marks)

edat (without quotation

edat to get the location,

Description Writes the contents of MATLAB matrix var_name in the Excel

worksheet, beginning in the upper-left cell specified by
data exists in the specified w orksheet cells, it is overwritten. If the
dimensions of the MATLAB matrix are larger than that of the specified
cells, the data overflows in to additional rows and columns.

edat.If

Caution

edat must not include the cell that contains the MLGetMatrix function.

In other w ords, be careful not to overwrite the function itself. Also make
sure there is enough room in the worksheet to write the matrix contents.
If there is insufficient room, the function generates a fatal error.

4-13

MLGetMatrix

MLGetMatrix function does not a uto m atically adjust cell addresses. If
edat is an explicit cell address, edit it to correct the address when you

do either of the following:

• Insert or delete rows or columns.

• Move or copy the function to another cell.

If worksheet calculation mode is automatic,
when you enter the formula in a cell. If worksheet calculation mode is
manual, enter the
execute it. However, pressing F9 in this situation may also reexecute
other worksheet functions and generate unpredictable results.

If you use

MLGetMatrix in a macro subroutine, enter MatlabRequest

on the line after the MLGetMatrix. MatlabRequest initializes internal
Spreadsheet Link EX variables and enables
a subroutine. Do not include
the function is called from a subroutine.

Examples Example 1

Write the contents of the MATLAB matrix bonds starting in cell C10 of

Sheet2.Ifbonds isa4-by-3matrix,fillcellsC10..E13 with data:

MLGetMatrix("bonds", "Sheet2!C10")

Example 2

Access the M ATLAB matrix named by the string in worksheet cell

B12. Write the contents of the matrix to the worksheet starting at the

location named by the string in worksheet cell

MLGetMatrix(B12, B13)

MLGetMatrix executes

MLGetMatrix function in a cell, and then press F9 to

MLGetMatrix to function in

MatlabRequest in a macro function unless

B13:

4-14

Example 3

Write the contents of MATLAB matrix A to the worksheet, starting at
the cell named by

RangeA:

Sub Get_RangeA()
MLGetMatrix "A", "RangeA"
MatlabRequest
End Sub

Example 4

In a macro, use the Address property of the range object returned by
the VBA

Cells function to specify where to write the data:

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

See Also MLAppendMatrix, MLPutMatrix

MLGetMatrix

4-15

MLGetVar

Purpose Write contents of MATLAB matrix in Microsoft Excel VBA variable

Syntax

MLGetVar ML_var_name, VBA_var_name

ML_var_name

VBA_var_name

Name of MATLAB matrix to access.

"ML_var_name" (in quotation marks) directly

specifies the matrix name.
(without quotation marks) is an indirect
reference: the function evaluates the contents
of

ML_var_name to get the matrix name,

and

ML_var_name must be a VBA variable

containing the matrix name a s a string.

var_name cannot be the MATLAB variable
ans. If defined, ML_var_name should be of type
VARIANT. Any other type w ill give a "TYPE
MISMATCH"

Name of VBA variable where the function
writes the contents of

VBA_var_name without quotation marks.

error.

ML_var_name

ML_var_name.Use

Description Writes the contents of MATLAB matrix ML_var_name in the

Excel Visual Basic for Applications (VBA) variable
Creates

VBA_var_name.

VBA_var_name if it does not exist. Replaces existing data in

VBA_var_name.

Examples Write the contents of the MATLAB matrix J into the VBA variable

DataJ:

Sub Fetch()
MLGetVar "J", DataJ
End Sub

See Also MLPutVar

4-16

Purpose Set empty cells to NaN or 0

MLMissingDataAsNaN

Syntax

Worksheet: MLMissingDataAsNaN("yes")

MLMissingDataAsNaN("no")

Macro: MLMissingDataAsNaN "yes"

MLMissingDataAsNaN "no"

"yes"

"no"

Sets empty cells to use NaNs.

Sets empty cells to use 0s. (Default)

(Default)

(Default)

Description Sets empty cells to NaN or 0. When the Spreadsheet Link EX software is

installed, the default is
change the value of
effect the next time a Microsoft Excel session starts.

Note A string in an Excel range always forces cell array output and
empty cells as

NaNs.

"no", so empty cells are handled as 0s. If you

MLUseCellArray to "yes", the change remains in

Examples Cancel the use of NaNs for empty cells:

MLMissingDataAsNaN('no")

See Also MLPutMatrix

4-17

MLOpen

Purpose Start MATLAB process

Syntax

Description Starts MATLAB p

Worksheet: MLOpen()

Macro: MLOpen

rocess. If a MATLAB process has already started,

lls to

subsequent ca
MATLAB sessio
Microsoft Ex

Note We recom

matlabinit

Link EX soft

cel session.

starts a MATLAB session and initializes the Spreadsheet

ware.

MLOpen do nothing. Use MLOpen to restart the

nafteryouhavestoppeditwith

mend using

Examples Starts a M ATLA B session:

MLOpen()

it

See Also matlabin

, MLClose

MLClose in a given

matlabinit rather than MLOpen,since

4-18

MLPutMatrix

Purpose Create or overwrite MATLAB matrix with data from Microsoft Excel

worksheet

Syntax

Worksheet: MLPutMatrix(var_name, mdat)

Macro: MLPutMatrix var_name, mdat

var_name

Name of MATLAB matrix to create or overwrite.

"var_name" (in quotation marks) directly specifies the

matrix name.

var_name (without quotation marks)

is an indirect reference: the function evaluates the
contents of

var_name must be a worksheet cell address or range

var_name to get the matrix name, and

name.

mdat

Location of data to copy into var_name. mdat (no
quotation m arks). Must be a worksheet cell address
or range name.

Description Creates or overwrites matrix var_name in MATLAB workspace with

specified data in
exists, this function replaces the contents with mdat. Empty numeric
data cells within the range of
MATLAB matrix identified by

If any element of
MATLAB cell array. Empty string elements within the range of
become NaNs within the MATLAB cell array.

When using
worksheet data using the Microsoft Excel macro

mdat.Createsvar_name if it does not exist. If var_name

mdat become numeric zeros within the

var_name.

mdat contains string data, mdat is exported as a

mdat

MLPutMatrix in a subroutine, indicate the source of the

Range. For example:

Sub test()
MLPutMatrix "a", Range("A1:A3")
End Sub

If you have a named range in your worksheet, you can specify the name
instead of the range; for example:

4-19

MLPutMatrix

Sub test()
MLPutMatrix "a", Range("temp")
End Sub

where temp is a named range in your worksheet.

Examples Example 1 — Create or overwrite a matrix in the MATLAB

workspace

Create or overwrite matrix A in the MATLAB workspace with the data
in the worksheet range

MLPutMatrix "A", Range("A1:C3")

Example 2 — Use the putmatrix toolbar button to import
data from a Microsoft Excel worksheet to the MATLAB
workspace

Use the putmatrix toolbar button to import data from an Excel
worksheet to the MATLAB workspace:

A1:C3:

4-20

1 In the Excel worksheet, select the columns and/or rows you want to

export to the MATLAB workspace.

MLPutMatrix

2 Click the putmatrix button on the Spreadsheet Link EX toolbar.

A window appears that prompts you to specify the name of the
MATLAB variable in which you want to store your d ata.

3 Enter newmatrix for the MATLAB variable name.

k OK.

4 Clic

you can manipulate

Now

dow.

Win

newmatrix in the MATLAB Command

4-21

MLPutMatrix

newmatrix
newmatrix =

123
456

See Also MLAppendMatrix, MLGetMatrix

4-22

MLPutVar

Purpose Create or overwrite MATLAB matrix with data from Microsoft Excel

VBA variable

Syntax

MLPutVar ML_var_name, VBA_var_name

ML_var_name

VBA_var_name

Name of MATLAB m atrix to create or o verwrite.

"ML_var_name" (in quotation marks) directly

specifies the matrix name.
quotation marks) is an indirect reference: the
function evaluates the contents of
to get the matrix nam e, and ML_var_name must
be a VBA variable containing the matrix name
as a string.

Name of VBA variable whose contents are w ritten
to

ML_var_name.UseVBA_var_name without

quotation marks.

ML_var_name (without

ML_var_name

Description Creates or overwrites matrix ML_var_name in MATLAB workspace with

data in

ML_var_name exists, this function replaces the contents with data from
VBA_var_name.UseMLPutVar only in a macro subroutine, not in a

macro function or in a subroutine called by a function.

Empty numeric data cells within
within the MATLAB matrix identified by

If any element of
is exported as a MATLAB cell array. Empty string elements within

VBA_var_name become NaNs within the MATLAB cell array.

VBA_var_name.CreatesML_var_name if it does not exist. If

VBA_var_name become numeric zeros

ML_var_name.

VBA_var_name contains string data, VBA_var_name

Examples Create (or overwrite) the MATLAB matrix K with the data in the Excel

Visual Basic for Applications (VBA) variable

Sub Put()

MLPutVar "K", DataK

DataK.

4-23

MLPutVar

See Also MLGetVar

End Sub

4-24

MLShowMatlabErrors

Purpose Return standard Spreadsheet Link EX errors or full MATLAB errors

using

MLEvalString

Syntax

Descripti

on

Worksheet: MLShowMatlabErrors("yes")

MLShowMatlabErrors("no")

Macro: MLShowMatlabErrors "yes"

MLShowMatlabErrors "no"

"yes"

"no"

Sets the Sp
MATLAB com

readsheet Link EX error display mode when executing

Displays the full MATLAB error string upon

MLEvalString failure.

Displays the standard Spreadsheet Link EX errors
upon

MLEvalString failure.

mands using

MLEvalString.

(Default)

(Default)

Examples • Cause MLEvalString failures to show standard Spreadsheet Link EX

errors, such as

MLShowM

• Cause MLEvalString failures to show MATLAB error strings, such

as

??? Undefined function or variable 'foo'.

MLShow

#COMMAND.

atlabErrors("no")

MatlabErrors("yes")

See Also MLEvalString

4-25

MLStartDir

Purpose Specify MATLAB current working folder after startup

Syntax

Description Sets the MATL

Worksheet: MLStartDir(path)

Macro: MLStartDir path

path

work like th
does not aut
folder.

Note The wo
run this fu
does not c
case, MAT
it is rest

e standard Microsoft Windows Start In setting, be cause it

nction. Running this function while MATLAB is running

hange the working folder for the current session. In this

LAB uses the specified folder as the working folder when

arted.

Specify the current MATLAB working folder after
startup.

AB working folder after startup. This function does not

omatically run

rking folder changes only if you run MATLAB after you

startup.m or matlabrc.m in the specified

Examples Set the MATLAB working folder to d:\work after startup:

MLStar

tDir ( d:\work )

4-26

See A

lso

If your folder path includes a space, embed the path in single quotation
marks within double quotation marks. For example, to set the MATLAB
working folder to

MLStartDir ( 'd:\my work' )

toStart

MLAu

d:\my work, run the command:

Purpose Toggle MLPutMatrix to use MATLAB cell arrays

MLUseCellArray

Syntax

Worksheet: MLUseCellArray("yes")

MLUseCellArray ("no")

Macro: MLUseCellArray "yes"

MLUseCellArray "no"

"yes"

"no"

Automatically uses cell arrays for transfer of data
structures.

Do not automatically use cell arrays for transfer of
data (default).

Description Using MLUseCellArray forces MLPutMatrix to use cell arrays for

transfer of data (for exam ple, dates). When the Spreadsheet Link EX
software is installed, the default is

MLUseCellArray to "yes", the change remains in effect the next time

a Microsoft Excel session starts.

"no". If you change the value of

Examples Cancel automatic use of cell arrays for easy transfer of data:

MLUseCellArray("no")

See Also MLPutMatrix

4-27

MLUseFullDesktop

Purpose Specify whether to use full MATLAB desktop or MATLAB Command

Window

Syntax

Descriptio

n

Worksheet: MLUseFullDesktop("yes")

MLUseFullDesktop("no")

Macro: MLUseFullDesktop "yes"

MLUseFullDesktop "no"

"yes"

"no"

Sets the MAT
Window only
the defaul

tis

Start full MATLAB desktop.

Start the MATLAB Command Window only.

LAB session to start with the full desktop or Command

. When the Spreadsheet Link EX software is installed,

"yes".

Examples Start only the MATLAB Command Window:

MLUseFul

See Also matlabin

lDesktop("no")

it

, MLClose, MLOpen

4-28

Error Messages and Troubleshooting

This appendix covers the following topics:

• “Worksheet Cell Errors” on page A-2

• “Microsoft

• “Data Errors” on page A -8

• “Startup Errors” on page A-10

®

Excel Software Errors” on page A-5

A

• “Audible Error Signals” on page A-11

A Error Messages and Troubleshooting

Worksheet Cell Errors

You may see these error messages displayed in a worksheet cell.

The first column of the following table contains worksheet cell error messages.
The error messages begin with the number sign (
exclamation point (

Worksheet Cell Error Messages

Worksheet Cell
Error Message Meaning Solution

#COLS>#MAXCOLS!

#COMMAND!

#DIMENSION!

#INVALIDNAME!

#INVALIDTYPE!

Your MATLAB variable exceeds
the Microsoft Excel limit of

#MAXCOLS! columns.

The MATLAB software does not
recognize the command in an

MLEvalString function. The

command may be misspelled.

You use
the dim
data d
of the

ntered an illegal variable

You e

.

name

You have specified an illegal
MATLAB data type with

MLGetVar or MLGetMatrix.

!) or with a question mark (?).

d

MLAppendMatrix and

ensions o f the appended
o not match the dimensions
matrix you want to append.

#). Most end with an

This is a limitation in the Excel
product. Try the computation
with a variable containing fewer
columns.

Verify the spelling of the M ATLAB
command. Correct typing errors.

Verify the matrix dimensions and
the appended data dimensions,
and correct the argument.
For more information, see the

MLAppendMatrix reference page.

Make sure to use legal MATLAB
variable names. MATLAB
variable names must start with a
letterfollowedbyupto30letters,
digits, or underscores.

For a list of supported MATLAB
data types, see “Classes (Data
Types)” in the MATLAB
Programming Fundamentals
documentation.

A-2

Worksheet Cell Error Messages (Continued)

Worksheet Cell
Error Message Meaning Solution

#MATLAB?

You used a Spreadsheet Link
EX function and no MATLAB
software session is running.

Start the Spreadsheet Link EX
and MATLAB software. See
“Starting and Stopping the
Spreadsheet Link EX Software”
on page 1-13.

#NAME?

The function name is
unrecognized. The

excllink.xla

add-in is not loaded, or the
function name may be misspelled.

Be sure the
is loaded. See “Configuring the
Spreadsheet Link EX Software”
on pag e 1-5. Check the spelling of
the function name. Correct typing
errors.

#NONEXIST!

#ROWS>#MAXROWS!

You referenced a nonexistent
matrix in an

MLDeleteMatrix function. The

MLGetMatrix or

matrix name may be misspelled.

Your MATLAB variable exceeds
the Excel limit of

#MAXROWS!

rows.

Verify the spelling of the M ATLAB
matrix. Use the MATLAB
command to display existing
matrices. Correct typing errors.

This is a limitation in the Excel
product. Try the computation
with a variable containing fewer
rows.

#SYNTAX?

You entered a Spreadsheet
Link EX function with incorrect
syntax. For example, you did not
specify double quotation marks

(") , or you specified single

Verify and correct the function
syntax. For more information,
see Chapter 4, “Functions —
Alphabetical List”.

quotation marks (’) instead of
double quotation marks.

Worksheet Cell Errors

excllink.xla add-in

whos

A-3

A Error Messages and Troubleshooting

Worksheet Cell Error Messages (Continued)

Worksheet Cell
Error Message Meaning Solution

#VALUE!

#VALUE!

An argument is m iss ing from a
function, or a function argument
is the wrong type.

A macro subroutine uses

MLGetMatrix followed by
MatlabRequest, which is correct

standard usage. A macro function
calls that subroutine, and you
execute that function from a
worksheet cell. The function
works correctly, but this message
appears in the cell.

Supply the correct number of
function arguments, of the correct
type.

Since the function works correctly,
ignore the message. Or, in
this special case, remove

MatlabRequest from the

subroutine.

A-4

Note When you open an Excel worksheet that contains Spreadsheet Link EX
functions, the Excel software tries to execute the functions from the bottom up
and rig ht to left. Excel may generate cell error messages such as

#COMMAND!

or #NONEXIST!. Thisisexpectedbehavior. Dothefollowing:

1 Ignore the messages.

2 CloseMATLABfigurewindows.

3 Reexecute the cell functions one at a time in the correct order by pressing

F2,andthenEnter.