Main Content

使用VBA宏创建对角矩阵

This example shows how to execute Spreadsheet Link™ functions to export a named range in the worksheet to MATLAB®and create a diagonal matrix using aMicrosoft®Excel®VBA macro.

The example assumes that MATLAB is running afterMicrosoft Excelopens. For details, seeStart and Stop Spreadsheet Link and MATLAB.

To work with VBA code in Excel with Spreadsheet Link, you must enable Spreadsheet Link as a reference in theMicrosoft Visual Basic®Editor. For details, seeInstallation.

In a worksheet, enter the numbers 1 through 5 into the range of cells fromA1throughE1. Define the nametestDatafor this range of cells and select it. For instructions, see Excel Help and enter the search term: define and use names in formulas.

The named rangetestDataappears in theName Box.

Cell range named testData that has numbers 1 through 5 in cells A1 through E1

On theDevelopertab inMicrosoft Excel, clickVisual Basic. The Visual Basic Editor window opens.

Insert a new module and create a diagonal matrix from the data intestData. To insert the module, selectInsert>Module. In the Code section, enter this VBA code that contains a macro namedDiagonal.

Sub Diagonal() MLPutRanges MLEvalString "b = diag(testData);" MLGetMatrix "b", "A3" MatlabRequest End Sub

TheDiagonalmacro exports the named range into the MATLAB variabletestDatausing theMLPutRangesfunction. Then, the macro uses theMLEvalStringfunction to execute MATLAB code. The MATLAB code creates a diagonal matrix from the data intestDatausing thediagfunction. The code assigns the diagonal matrix to the MATLAB variableb. Then, the macro uses theMLGetMatrixfunction to import the diagonal matrix into the worksheet.

Copy and paste the code into the Visual Basic Editor from the HTML version of the documentation.

For details about working with modules, see Excel Help.

运行macro by clickingRun Sub/UserForm (F5). For details about running macros, see Excel Help.

The diagonal matrix displays in the worksheet cellsA3throughE7.

Worksheet contains numbers 1 through 5 in cells A1 through E5 and the resulting diagonal matrix with the same numbers in cells A3 through E7.

See Also

||

Related Topics