Matlab Use Spreadsheet Link

Spreadsheet Link

Spreadsheet Link connects Excel to MATLAB, enabling users to exchange data between Excel and MATLAB, run MATLAB commands, using MATLAB as the compute engine from Excel.

When to Use

• User wants to export Excel data into MATLAB workspace variables
• User wants to import MATLAB variables back into Excel cells or VBA variables
• User wants to execute MATLAB commands from an Excel VBA macro or worksheet cell
• User wants to import MATLAB figures into Excel as images
• User is building a workflow that uses Excel as the data interface and MATLAB as the compute engine

When NOT to Use

• MATLAB-only workflows with no Excel involvement
• Python or .NET integration with MATLAB (use MATLAB Engine API instead)
• Deploying MATLAB as a web service (use MATLAB Production Server instead)

Architecture

Spreadsheet Link uses VBA macros or worksheet functions in Excel to communicate with a locally running MATLAB instance via COM.

Copy code
┌─────────────────────┐         VBA / COM          ┌─────────────────────┐
│   Excel             │  ◄─────────────────────►   │  MATLAB             │
│   (VBA or cells)    │                            │  (workspace)        │
└─────────────────────┘                            └─────────────────────┘

• Windows only (requires MATLAB COM Server)
• MATLAB must be running locally
• Two usage modes: VBA macros or worksheet functions (cell formulas)
• Two data exchange styles: range-based (MLPutMatrix/MLGetMatrix) or VBA-variable-based (MLPutVar/MLGetVar)

Key Functions

Function Reference

MLPutMatrix

Export an Excel range into MATLAB as a workspace variable.

VBA macro syntax:

vbaCopy code
MLPutMatrix "varName", Range("A1:B10")

Worksheet function syntax:

Copy code
=MLPutMatrix("varName", A1:B10)

• First argument: MATLAB variable name (string)
• Second argument: Excel range containing the data
• Dates in Excel are sent as Excel serial date numbers

MLPutVar

Export a VBA variable to the MATLAB workspace. VBA only — not available as a worksheet function.

vbaCopy code
Dim myData As Variant
myData = Range("A1:B100").Value
MLPutVar "matlabVar", myData

• First argument: MATLAB variable name (string)
• Second argument: VBA variable (the variable itself, not a string name)
• Executes immediately (not queued)
• Use when data needs VBA manipulation before sending to MATLAB

MLGetMatrix

Queue a MATLAB variable to be written into Excel starting at a cell location.

vbaCopy code
MLGetMatrix "varName", "A1"

• First argument: MATLAB variable name (string)
• Second argument: destination cell address (string, not a Range object)
• Does NOT execute immediately — queued until MatlabRequest is called
• Supports all MATLAB data types; datetime values are converted to strings in Excel
• As worksheet function: =MLGetMatrix("varName", "E1")

MLGetVar

Import a MATLAB variable into a VBA variable. VBA only — not available as a worksheet function.

vbaCopy code
Dim result As Variant
MLGetVar "matlabVar", result

• First argument: MATLAB variable name (string)
• Second argument: VBA variable to receive the data
• Executes immediately — does NOT require MatlabRequest
• Supports all MATLAB data types; datetime values are converted to strings
• Use when results need VBA manipulation before writing to worksheet

MLPutRanges

Export ALL Excel named ranges to MATLAB in a single call. Each named range becomes a MATLAB variable with the same name.

vbaCopy code
MLPutRanges

• No arguments — exports every named range in the workbook
• Range name becomes the MATLAB variable name (e.g., named range "prices" → MATLAB variable prices)
• As worksheet function: =MLPutRanges()

MLEvalString

Execute a MATLAB command in the connected MATLAB session.

vbaCopy code
MLEvalString "x = magic(3);"

• Argument: MATLAB code as a string
• Executes synchronously — the next VBA line runs after MATLAB completes
• Can call any MATLAB function, script, or expression
• As worksheet function: =MLEvalString("x = magic(3);")

MLAppendMatrix

Append an Excel range to an existing MATLAB variable. The new data is concatenated as additional rows.

VBA macro syntax:

vbaCopy code
MLAppendMatrix "varName", Range("A101:B200")

Worksheet function syntax:

Copy code
=MLAppendMatrix("varName", A101:B200)

• First argument: MATLAB variable name (string) — must already exist in workspace
• Second argument: Excel range to append
• Appends rows to the bottom of the existing variable
• Use when building up a variable incrementally (e.g., streaming data)

MLGetFigure

Import the current MATLAB figure into Excel as an image.

vbaCopy code
Range("I1").Select
MLGetFigure 1, 1

• Select the destination cell first with Range(...).Select
• First argument: horizontal size scaling factor (1 = full size, 0.5 = half width)
• Second argument: vertical size scaling factor (1 = full size, 0.5 = half height)
• Only two arguments — do NOT pass a cell address
• Retrieves whatever figure is currently active in MATLAB
• Queued until MatlabRequest is called (like MLGetMatrix)
• As worksheet function: =MLGetFigure(1, 1) — places figure at the formula cell

MatlabRequest

Process all pending MLGetMatrix and MLGetFigure commands and write results to Excel.

vbaCopy code
MatlabRequest

• Must be the last command in any VBA macro that uses MLGetMatrix or MLGetFigure
• Triggers the actual data transfer from MATLAB to Excel cells/images
• Without this call in a VBA macro, queued results will not appear in the spreadsheet
• NOT needed for MLGetVar (which executes immediately)
• Worksheet functions do NOT need explicit MatlabRequest — Spreadsheet Link's auto-calc functionality calls MatlabRequest automatically as part of Excel's recalculation cycle

Patterns

Pattern 1: VBA with MLPutVar/MLGetVar (preferred for VBA workflows)

vbaCopy code
Sub DataRoundTrip()
    ' Read data from worksheet into VBA variables
    Dim prices As Variant
    Dim weights As Variant
    prices = Range("A1:A100").Value
    weights = Range("B1:B100").Value

    ' Export VBA variables to MATLAB
    MLPutVar "prices", prices
    MLPutVar "weights", weights

    ' Compute in MATLAB
    MLEvalString "portfolio = prices .* weights;"
    MLEvalString "totalValue = sum(portfolio);"

    ' Import results into VBA variables (immediate — no MatlabRequest needed)
    Dim portfolio As Variant
    Dim totalValue As Variant
    MLGetVar "portfolio", portfolio
    MLGetVar "totalValue", totalValue

    ' Write VBA variables to worksheet
    Range("D1").Resize(UBound(portfolio, 1), UBound(portfolio, 2)).Value = portfolio
    Range("E1").Value = totalValue
End Sub

Pattern 2: VBA with MLPutMatrix/MLGetMatrix (range-based)

vbaCopy code
Sub ComputeInMatlab()
    MLPutMatrix "data", Range("A1:C100")
    MLEvalString "result = mean(data, 1);"
    MLGetMatrix "result", "E1"
    MatlabRequest
End Sub

Pattern 3: Named ranges export

vbaCopy code
Sub AnalyzeAllData()
    ' Export all named ranges to MATLAB at once
    ' e.g., "prices", "weights", "benchmark" all become MATLAB variables
    MLPutRanges

    ' MATLAB code references variables by named range names
    MLEvalString "portReturn = prices .* weights;"
    MLEvalString "excessReturn = portReturn - benchmark;"
    MLEvalString "sharpe = mean(excessReturn) / std(excessReturn) * sqrt(252);"

    ' Import result
    Dim sharpe As Variant
    MLGetVar "sharpe", sharpe
    Range("H1").Value = sharpe
End Sub

Pattern 4: Figure generation and import

vbaCopy code
Sub PlotAndImport()
    MLPutVar "data", Range("A1:B100").Value

    ' Generate MATLAB figure
    MLEvalString "figure;"
    MLEvalString "plot(data(:,1), data(:,2), 'LineWidth', 1.5);"
    MLEvalString "title('Analysis'); xlabel('X'); ylabel('Y'); grid on;"

    ' Import figure to Excel
    Range("D1").Select
    MLGetFigure 1, 1
    MatlabRequest
End Sub

Pattern 5: Multiple figures

vbaCopy code
Sub MultipleFigures()
    MLPutVar "data", Range("A1:C100").Value

    ' Create multiple figures
    MLEvalString "figure(1); plot(data(:,1)); title('Series 1');"
    MLEvalString "figure(2); histogram(data(:,2)); title('Distribution');"
    MLEvalString "figure(3); scatter(data(:,1), data(:,2)); title('Scatter');"

    ' Import each — must make figure current before each MLGetFigure
    MLEvalString "figure(1);"
    Range("E1").Select
    MLGetFigure 0.5, 0.5

    MLEvalString "figure(2);"
    Range("E20").Select
    MLGetFigure 0.5, 0.5

    MLEvalString "figure(3);"
    Range("E40").Select
    MLGetFigure 0.5, 0.5

    MatlabRequest
End Sub

Pattern 6: Complete workflow (MLPutVar + compute + MLGetVar + figure)

vbaCopy code
Sub TotalReturnWithFigure()
    ' Read data into VBA
    Dim prices As Variant
    Dim divs As Variant
    prices = Range("A1:B253").Value
    divs = Range("D1:E20").Value

    ' Export to MATLAB
    MLPutVar "prices", prices
    MLPutVar "divs", divs

    ' Compute total return
    MLEvalString "dates = datetime(prices(:,1), 'ConvertFrom', 'excel');"
    MLEvalString "px = prices(:,2);"
    MLEvalString "exDates = datetime(divs(:,1), 'ConvertFrom', 'excel');"
    MLEvalString "divAmounts = divs(:,2);"
    MLEvalString "adjFactor = ones(size(px));"
    MLEvalString "for i = 1:numel(exDates), idx = find(dates >= exDates(i), 1); if ~isempty(idx) && idx > 1, adjFactor(1:idx-1) = adjFactor(1:idx-1) * (1 - divAmounts(i)/px(idx)); end, end"
    MLEvalString "totalReturn = px ./ adjFactor;"
    MLEvalString "totalReturn = 100 * totalReturn / totalReturn(1);"

    ' Generate figure
    MLEvalString "figure;"
    MLEvalString "plot(dates, totalReturn, 'LineWidth', 1.5);"
    MLEvalString "title('Total Return Index');"
    MLEvalString "xlabel('Date'); ylabel('Index (Base = 100)'); grid on;"

    ' Import numeric result into VBA variable
    Dim trResult As Variant
    MLGetVar "totalReturn", trResult
    Range("G1").Resize(UBound(trResult, 1), 1).Value = trResult

    ' Import figure
    Range("I1").Select
    MLGetFigure 1, 1
    MatlabRequest
End Sub

Pattern 7: Worksheet functions (cell formulas)

Enter these in separate Excel cells, in order from top to bottom:

Copy code
Cell F1: =MLPutRanges()
Cell F2: =MLEvalString("result = mean(prices, 1);")
Cell F3: =MLEvalString("figure; plot(prices); title('Prices'); grid on;")
Cell F4: =MLGetMatrix("result", "H1")
Cell F5: =MLGetFigure(1, 1)

Working with dates

Excel dates are sent as serial date numbers. Convert in MATLAB:

vbaCopy code
Dim rawData As Variant
rawData = Range("A1:B253").Value
MLPutVar "rawData", rawData
MLEvalString "dates = datetime(rawData(:,1), 'ConvertFrom', 'excel');"
MLEvalString "values = rawData(:,2);"

When to Use MLPutVar/MLGetVar vs MLPutMatrix/MLGetMatrix

Two parallel APIs exist for exchanging data between Excel and MATLAB:

• MLPutMatrix / MLGetMatrix — operate directly on worksheet ranges. MLPutMatrix reads cells and sends to MATLAB. MLGetMatrix is queued and writes MATLAB data back to cells only after MatlabRequest is called. Available in both VBA and worksheet functions.
• MLPutVar / MLGetVar — operate on VBA variables. MLPutVar exports a VBA variable to MATLAB. MLGetVar imports a MATLAB variable into a VBA variable immediately (no MatlabRequest needed). VBA only — not available as worksheet functions.

Code Generation Rules

When generating VBA code that uses Spreadsheet Link:

1. Do NOT create new .m files — Spreadsheet Link is an orchestration/data-exchange layer. Express MATLAB logic inline via MLEvalString calls. Calling pre-existing MATLAB functions or scripts is fine (e.g., MLEvalString "results = myAnalysis(data);"), but do NOT generate new .m files as part of the solution.
2. Always end with MatlabRequest if the macro contains any MLGetMatrix or MLGetFigure calls. NOT needed if using only MLGetVar.
3. MLGetMatrix takes a string for the cell address, not a Range object — use "G1" not Range("G1")
4. MLGetFigure requires selecting the cell first — use Range("I1").Select then MLGetFigure 1, 1. Only two arguments (width, height scaling). Do NOT pass a cell address.
5. MLPutMatrix takes a Range object for the data source — use Range("A1:B10")
6. MLPutVar takes a VBA variable — use MLPutVar "name", myVar (not a string name of the variable)
7. MLGetVar executes immediately — no MatlabRequest needed. Assign to a Variant.
8. Use multiple MLEvalString calls for multi-line MATLAB logic rather than packing into one string
9. Do NOT write JSON serialization or data conversion code — Spreadsheet Link handles all data type conversion internally
10. Convert dates after exporting — export raw data, then convert in MATLAB with datetime(..., 'ConvertFrom', 'excel')
11. Variable names must be valid MATLAB identifiers — no spaces, no special characters, must start with a letter
12. For multiple figures, make each figure current with figure(N) before each MLGetFigure call
13. Do NOT use exportgraphics + Shapes.AddPicture — use MLGetFigure instead

Notes

• Spreadsheet Link requires Windows (uses MATLAB COM Server)
• MATLAB must be running before executing macros
• VBA Reference required: In the Excel Visual Basic Editor, go to Tools → References and check "SpreadsheetLink" from the list. Without this reference enabled, Spreadsheet Link functions will not be recognized.
• Data types: Excel numeric values become MATLAB doubles; text becomes char arrays
• Large datasets may be slow over COM — consider exporting/importing only what's needed
• MLPutRanges exports ALL named ranges — it is not selective
• Worksheet functions execute in cell evaluation order (top to bottom, left to right)
• Spreadsheet Link auto-calc automatically calls MatlabRequest during Excel recalculation — this is why worksheet functions do not need an explicit MatlabRequest call, while VBA macros do
• Customization functions — configure Spreadsheet Link behavior (all execute immediately):
  • MLShowMatlabErrors "yes" — return MATLAB errors back to Excel (default is no; #COMMAND! is returned)
  • MLOpen — verify or establish connection to MATLAB session
  • MLClose — disconnect from MATLAB session
  • MLAutoStart "yes" — auto-start MATLAB when the Spreadsheet Link add-in loads
  • MLUseFullDesktop "yes" — launch full MATLAB desktop vs Command Window only
  • MLStartDir "C:\myproject" — set MATLAB working folder on connection
  • MLUseCellArray "yes" — toggle cell array mode for MLPutMatrix (sends each cell as a separate cell array element rather than combining into a matrix)
  • MLProgramId "26.1" — set which MATLAB version to connect to when multiple versions are installed
  • MLMissingDataAsNaN "yes" — send empty Excel cells as NaN to MATLAB (default sends as 0)

Copyright 2026 The MathWorks, Inc.