Excel Link

Tips and Reminders
These tips and reminders help you use Excel Link efficiently.

Syntax

• Excel Link 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, and bonds are three different MATLAB variables. Standard MATLAB function names are always lower case, for example plot(f).
• Begin worksheet formulas with + or =. For example,

  =mlputmatrix("a", C10)
  

• In worksheet formulas, enclose function arguments in parentheses. In macros, leave a space between the function name and the first argument; do not use parentheses.
• You can directly or indirectly specify a variable-name argument in most Excel Link functions.
  • To specify a variable name directly, enclose it in double quotes; for example, MLDeleteMatrix("Bonds").
  • A variable-name argument without quotes is an indirect reference. The function evaluates the contents of the argument to get the variable name. The argument must be a worksheet cell address or range name.
• A data-location argument must be a worksheet cell address or range name. Do not enclose a data-location argument in quotes (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.

Worksheets

• After an Excel Link function successfully executes as a worksheet formula, the cell contains the value 0. While a function is executing, the cell may continue to show the entered formula.
• We suggest checking Move Selection after Enter on the Excel Tools Options... Edit tab. The active cell changes when an operation is complete, providing a useful confirmation for lengthy operations.
• We recommend using Excel Link functions in automatic calculation mode. If you use MLGetMatrix in manual calculation mode, enter the function in a cell, then press F9 to execute it. However, pressing F9 in this situation may also re-execute other worksheet functions and generate unpredictable results.
• To recalculate Excel Link functions in a worksheet, re-execute each function by pressing F2, then Enter.
• Pressing F9 to recalculate a worksheet affects only Excel functions (which return a value). F9 does not operate on Excel Link functions, which perform an action.
• To "automate" the recalculation of an Excel Link function, add to it some cell whose value changes. For example,

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

When the value in cell C1 changes, Excel re-executes the MLPutMatrix function. Be careful, however, not to create endless recalculation loops.

• Excel Link functions expect A1-style worksheet cell references. Select A1 cell Reference Style on the Excel Tools Options... General tab.
• If you use explicit cell addresses in MLGetMatrix and later insert or delete rows or columns, or move or copy the function to another cell, edit the argument to correct the addresses. Excel Link does not automatically adjust cell addresses in MLGetMatrix.
• Enter (type) Excel Link functions directly in worksheet cells. Do not use the Excel Function Wizard; it generates unpredictable results.

Macros

• To create macros that use Excel Link functions, you must first configure Excel to reference the functions from the Excel Link add-in. From the Visual Basic environment pull down the Insert menu and select Module. When the Module page opens, pull down the Tools menu and select References.... In the References window, check the box for EXCLLINK.XLA and click OK. You may have to use Browse to find the EXCLLINK.XLA file.
• If you use MLGetMatrix in a macro subroutine, enter MatlabRequest on the line after MLGetMatrix. MatlabRequest initializes internal Excel Link variables and enables MLGetMatrix to function in a subroutine. For example,

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

Do not include MatlabRequest in a macro function unless the function is called from a subroutine.

Data Types

• Excel Link handles only MATLAB two-dimensional numeric arrays, one-dimensional character arrays (strings), and two-dimensional cell arrays containing only strings. It does not work with MATLAB multidimensional arrays, structures, or cell arrays except those containing only strings.

Dates

• Default Excel date numbers start from January 1, 1900, while MATLAB date numbers start from January 1, 0000. Thus May 15, 1996 is 35200 in Excel and 729160 in MATLAB, a difference of 693960. If you use date numbers in MATLAB calculations, apply the 693960 constant: add it to Excel date numbers going into MATLAB, or subtract it from MATLAB date numbers coming into Excel. If you use the optional Excel 1904 date system, the constant is 695422.

Saved Worksheets

• When you open an Excel worksheet that contains Excel Link functions, Excel tries to execute the functions from the bottom up and right to left, thus possibly generating cell error messages (#COMMAND!, #NONEXIST!, etc.). Such behavior is "normal" for Excel. Simply ignore the messages, close any MATLAB figure windows, and re-execute the cell functions one at a time in the correct order by pressing F2, then Enter.
• If you save an Excel worksheet containing Excel Link functions and later open it under a different computer environment where the EXCLLINK.XLA add-in is in a different location, Excel may display a message box:
  
  

Click No. Then pull down the Edit menu and select Links. In the Links window, click Change Source. In the Change Links window, find and select EXCLLINK.XLA under C:\MATLAB\EXLINK (or your path) and click OK. Excel executes each function as it changes its link. You may see MATLAB figure windows and hear error beeps as the links change and functions execute; ignore them. Back in the Links window, click OK. The worksheet now correctly connects to the Excel Link add-in.

Or, instead of using the Edit Links... menu, you can manually edit the link location in each affected worksheet cell to show the correct location of EXCLLINK.XLA.

What the Functions Do

Using Excel Link