使用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 fromA1
throughE1
. Define the nametestData
for 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 rangetestData
appears in theName Box.
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
TheDiagonal
macro exports the named range into the MATLAB variabletestData
using theMLPutRanges
function. Then, the macro uses theMLEvalString
function to execute MATLAB code. The MATLAB code creates a diagonal matrix from the data intestData
using thediag
function. The code assigns the diagonal matrix to the MATLAB variableb
. Then, the macro uses theMLGetMatrix
function 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 cellsA3
throughE7
.