MATLAB BUILDER EX 1 Guide de l'utilisateur

MATLAB Excel BuilderThe Language of Technical ComputingComputationVisualizationProgrammingUser’s GuideVersion 1

PrefaceviiiWhat Is MATLAB Excel Builder?MATLAB® Excel Builder provides the capability to incorporate seamlessly and quickly MATLAB models and functio

5 Function Wizard5-14Copy FunctionUse the Copy Function dialog box to make copies of the current function. The Standard Copy tab creates a specified n

Introduction5-15MoveUse the Move Function dialog box to move the currently selected function to a new position in the current worksheet. When you set

5 Function Wizard5-16

6Function ReferenceThis section provides detailed descriptions of the MATLAB Excel Builder functions.

componentinfo6-26componentinfoPurpose Query system registrySyntax Info = componentinfo(ComponentName, MajorRevision, MinorRevision)ArgumentsDescriptio

componentinfo6-3With a component name and major revision supplied, the function returns information for revision 1.0 of mycomponent.Example 2.Info = c

mxltool6-46mxltoolPurpose Graphical user interface to MATLAB Excel BuilderSyntax mxltoolDescription mxltool displays the graphical user interface (GUI

AProducing a COM Object from MATLABCapabilities . . . . . . . . . . . . . . . . . . . . A-2Calling Conventi

A Producing a COM Object from MATLABA-2CapabilitiesMATLAB Excel Builder enables you to pass Microsoft Excel worksheet values to a compiled MATLAB mode

CapabilitiesA-3Figure A-1: Creating a Stand-Alone COM Object with the MATLAB CompilerThe process of creating a MATLAB Excel Builder component is comp

Suggested BackgroundixSuggested BackgroundUsers of this product need to be familiar with• MATLAB and the MATLAB Compiler• Microsoft Excel• Visual Basi

A Producing a COM Object from MATLABA-4Figure A-2: M-Build Steps and Intermediate Files Created mcc -B cexel:myc

CapabilitiesA-5Step 1. Code GenerationThe first step in the build process generates all source code and other supporting files needed to create the co

A Producing a COM Object from MATLABA-6Step 4. Linking and Resource BindingThe fourth step produces the finished DLL for the component. This step invo

Calling ConventionsA-7Calling ConventionsThis section describes the calling conventions for MATLAB Excel Builder components, including mappings from t

A Producing a COM Object from MATLABA-8Producing a COM ClassProducing a COM class requires the generation of a class definition file in Interface Desc

Calling ConventionsA-9nargout parameter, the outputs are listed in the order they appear on the left side of the MATLAB function, and are tagged as [i

A Producing a COM Object from MATLABA-10MATLAB Compiler OutputThe MATLAB Excel Builder generates a default Visual Basic function wrapper for each clas

Calling ConventionsA-11Variant to pass collectively the varargin1,...,vararginn parameters in the form of a Variant array. The main function body cont

A Producing a COM Object from MATLABA-12

BData ConversionData Conversion Rules . . . . . . . . . . . . . . . B-2Array Formatting Flags . . . . . .

PrefacexRequirements for MATLAB Excel Builder System RequirementsSystem requirements and restrictions on use for Excel Builder are almost identical t

B Data ConversionB-2Data Conversion Rules This section describes the data conversion rules for MATLAB Excel Builder components. Excel Builder componen

Data Conversion RulesB-3VT_UI2unsigned short--Unsigned two-byte integerVT_I4 long vbLong LongSigned four-byte integerVT_UI4unsigned long--Unsigned fou

B Data ConversionB-4VT_DECIMAL DECIMAL+vbDecimal -96-bit (12-byte) unsigned integer, scaled by a variable power of 10VT_BOOL VARIANT_BOOL+vbBoolean Bo

Data Conversion RulesB-5Table B-2: MATLAB to COM VARIANT Conversion RulesMATLAB Data Type VARIANT type for Scalar DataVARIANT type for Array DataComm

B Data ConversionB-6charA 1-by-1 char matrix converts to a VARIANT of type VT_BSTR with string length = 1.A 1-by-L char matrix is assumed to represent

Data Conversion RulesB-7doubleA real 1-by-1 double matrix converts to a VARIANT of type VT_R8. A complex 1-by-1 double matrix converts to a VARIANT

B Data ConversionB-8uint8A real 1-by-1 uint8 matrix converts to a VARIANT of type VT_UI1. A complex 1-by-1 uint8 matrix converts to a VARIANT of typ

Data Conversion RulesB-9int32A 1-by-1 int32 matrix converts to a VARIANT of type VT_I4. A complex 1-by-1 int32 matrix converts to a VARIANT of type

B Data ConversionB-10Table B-3: COM VARIANT to MATLAB Conversion RulesVARIANT Type MATLAB Data Type (scalar or array data)CommentsVT_EMPTYN/A Empty a

Data Conversion RulesB-11VT_DATE double1. VARIANT dates are stored as doubles starting at midnight Dec. 31, 1899. MATLAB dates are stored as doubles s

Requirements for MATLAB Excel BuilderxiLimitations and RestrictionsIn general, limitations and restrictions on the use of MATLAB Excel Builder are the

B Data ConversionB-12Array Formatting FlagsMATLAB Excel Builder components have flags that control how array data is formatted in both directions. Gen

Data Conversion RulesB-13Table B-4 shows the array formatting flags.Table B-4: Array Formatting FlagsFlag DescriptionInputArrayFormatDefines the arra

B Data ConversionB-14Data Conversion FlagsMATLAB Excel Builder components contain flags to control the conversion of certain VARIANT types to MATLAB t

Data Conversion RulesB-15bias (693960) as well as coerced to COM dates. Set this flag to True to convert all output values of type Double.DateBias As

B Data ConversionB-16

CRegistration and VersioningThis section describes the registration and versioning of MATLAB Excel Builder components and how to retrieve information

C Registration and VersioningC-2Component RegistrationWhen the MATLAB Excel Builder creates a component, it automatically generates a binary file call

Component RegistrationC-3Table C-1: KeysKey DefinitionHKEY_CLASSES_ROOT\CLSIDInformation about COM classes on the system. Each component creates a ne

C Registration and VersioningC-4VersioningMATLAB Excel Builder components support a simple versioning mechanism designed to make building and deployin

Obtaining Registry InformationC-5Obtaining Registry InformationMATLAB Excel Builder includes the MATLAB function componentinfo to query the system reg

PrefacexiiRelated Products The MathWorks provides several products relevant to the tasks you can perform with MATLAB Excel Builder.For more informati

C Registration and VersioningC-6Info.CoClassesans = Name: 'myclass'CLSID: '{3A14AB35-44BE-11D5-B155-00D0B7BA7544}'ProgID: 'my

Obtaining Registry InformationC-7ans =function [varargout] = randvectors()ans =function [x] = getdates(n, inc)ans =function Page = myprimes(n)The retur

C Registration and VersioningC-8InterfacesAn array of structures defining all interface definitions in the type library. Each structure contains two f

Obtaining Registry InformationC-9CoClassesAn array of structures defining all COM classes in the component. Each structure contains these fields:•Name

C Registration and VersioningC-10

DUtility LibraryUtility Library Classes . . . . . . . . . . . . . . . D-3Class MWUtil . . . . . . . .

D Utility LibraryD-2This section describes the MWComUtil library provided with the MATLAB Excel Builder. This library is freely distributable and incl

Utility Library ClassesD-3Utility Library ClassesThe Excel Builder Utility Library provides several classes:• “Class MWUtil” on page D-3• “Class MWFla

D Utility LibraryD-4Return Value. None.Remarks. This function must be called once for each session of Excel that uses Excel Builder components. An err

Utility Library ClassesD-5Parameters.Return Value.None.Remarks. This function always frees the contents of pVarArg before processing the list.Example.

Typographical ConventionsxiiiTypographical ConventionsThis manual uses some or all of these conventions.Item Convention ExampleExample code Monospace

D Utility LibraryD-6Dim aClass As ObjectDim aUtil As Object On Error Goto Handle_ErrorSet aClass = CreateObject("mycomponent.myclass.1_0"

Utility Library ClassesD-7Return Value. None.Remarks. This function can process a Variant array in one single call or through multiple calls using the

D Utility LibraryD-8Dim aUtil As ObjectDim v As VariantDim R1 As RangeDim R2 As RangeDim R3 As RangeDim R4 As Range On Error GoTo Handle_ErrorSet a

Utility Library ClassesD-9method performs this transformation and additionally converts dates in string form to COM date types.Example. This example u

D Utility LibraryD-10components contain a reference to an MWFlags object that can modify data conversion rules at the object level. This class contain

Utility Library ClassesD-11Property InputArrayIndFlag As Long. This property governs the level at which to apply the rule set by the InputArrayFormat

D Utility LibraryD-12Property OutputArrayIndFlag As Long. This property is similar to the InputArrayIndFalg property, as it governs the level at which

Utility Library ClassesD-13function returns outputs as row vectors, and you desire to place the data into columns. The default value for this flag is

D Utility LibraryD-14Example. This example uses data conversion flags to reshape the output from a method compiled from a MATLAB function that produce

Utility Library ClassesD-15Sub GenPrimes(R As Range, n As Double)Dim aClass As mycomponent.myclass On Error GoTo Handle_ErrorSet aClass = New mycom

Prefacexiv

D Utility LibraryD-16Remarks. Clone allocates a new MWFlags object and creates a deep copy of the object’s contents. Call this function when a separat

Utility Library ClassesD-17Example. The following Visual Basic code illustrates use of the Initialize method to dimension struct arrays.Sub foo ()Dim

D Utility LibraryD-18Parameters.Remarks.When accessing a named field through this property, you must supply all dimensions of the requested field as w

Utility Library ClassesD-19• All indices and field name. This format accesses an array element of an multidimensional array by specifying n indices. T

D Utility LibraryD-20Index1(1) = 1Index1(2) = 1Index2(1) = 3Index2(2) = 2x(Index1, Index2, 2, "red") = 0.5The last statement resolves to:x(1

Utility Library ClassesD-21On Error Goto Handle_Error'' Call a method that returns an MWStruct in x'Dims = x.DimsFieldNames = x.FieldN

D Utility LibraryD-22Example. The following Visual Basic example illustrates the difference between assignment and Clone for MWStruct objects.Sub foo

Utility Library ClassesD-23Property Value As VariantStores the field’s value (read/write). The Value property is the default property of the MWField c

D Utility LibraryD-24Property Real As VariantStores the real part of a complex array (read/write). The Real property is the default property of the MW

Utility Library ClassesD-25MsgBox(Err.Description)End SubProperty MWFlags As MWFlagsStores a reference to an MWFlags object. This property sets or get

1OverviewBuilding a Deployable Application . . . . . . . . . . 1-2Elements of an Excel Builder Project . . . . . .

D Utility LibraryD-26Property NumRows As LongStores the row dimension for the array. The value of NumRows must be nonnegative. If the value is zero, t

Utility Library ClassesD-27MWSparse object has its own MWFlags property. This property overrides the value of the any flags set on the object whose me

D Utility LibraryD-28cols(K) = I + 1vals(K) = -1K = K + 1rows(K) = Icols(K) = Ivals(K) = 2K = K + 1rows(K) = I + 1cols(K) = Ivals(K) = -1K = K + 1Next

Utility Library ClassesD-29Property Value As VariantThe Value property stores the actual argument to pass. Any type that can be passed to a compiled m

D Utility LibraryD-30EnumerationsThe MATLAB Excel Builder Utility Library provides three enumerations (sets of constants):• “Enum mwArrayFormat” on pa

EnumerationsD-31Enum mwDateFormatThe mwDateFormat enumeration is a set of constants that denote a formatting rule for dates. Table D-6 lists the membe

D Utility LibraryD-32

ETroubleshooting

E TroubleshootingE-2This section provides a table showing errors you may encounter using MATLAB Excel Builder, probable causes for these errors, and s

E-3LoadLibrary("component_name_1_0.dll") failed - The specified module could not be found. You may get this error message while registering

1 Overview1-2Building a Deployable ApplicationUsing MATLAB Excel Builder to create a deployable application is a simple process requiring a sequence o

E TroubleshootingE-4Table E-3: Function Wizard ProblemsProblem Probable Cause Suggested SolutionThe Function Wizard Help does not display.The Functio

I-1IndexAarray formatting flags 3-14CcapabilitiesExcel Builder viiiclass 1-2class methodcalling 3-6Class MWFlags D-9Class MWUtil D-3class name 1-2COMd

IndexI-2IIDL Mapping A-8input command xiLlimitations xiMmbuild xmccsavepath xmethods 1-2missing parameter D-4MWFlags class D-9mwregsvr utility C-2MWUt

Building a Deployable Application1-3organizational. As a general rule, when compiling many MATLAB functions, it helps to determine a scheme of functio

How to Contact The MathWorks:www.mathworks.com Webcomp.soft-sys.matlab [email protected] Technical [email protected] Product e

1 Overview1-4Figure 1-1: MATLAB Excel Builder Main WindowFor a complete description of the features available from this window, see “Graphical User I

Building a Deployable Application1-5Figure 1-2: New Project Settings Dialog BoxComponent name denotes the name of the DLL created later in the build

1 Overview1-6Note You can accept the automatically generated project directory path or choose another of your liking. Once you click OK on this menu,

Building a Deployable Application1-7Figure 1-3: Main Window with Options ActivatedAdd M- and/or MEX-files to the project by clicking the Add File but

1 Overview1-8Building a ProjectAfter you define your project settings and add the desired M- and MEX- functions, you can build a deployable DLL and th

Building a Deployable Application1-9detailed discussion on integrating MATLAB Excel Builder components into Microsoft Excel via Visual Basic for Appli

1 Overview1-10Choose Component -> Package Component to create a self-extracting executable containing these files.The self-extracting executable is

2Graphical User InterfaceGraphical User Interface Menus . . . . . . . . . . . 2-2File Menu . . . . . . . . .

2 Graphical User Interface2-2Graphical User Interface MenusThe MATLAB function mxltool displays the MATLAB Excel Builder graphical user interface (GUI

Graphical User Interface Menus2-3File MenuThe File menu creates and manages MATLAB Excel Builder projects.•New Project opens the project settings dial

iContentsPrefaceWhat Is MATLAB Excel Builder? . . . . . . . . . . . . . . . . . . . . . . . viiiSuggested Background . . . . . . . . . . . . . . .

2 Graphical User Interface2-4• Remove File removes the currently selected files from the project. (The Remove button in the Project files frame of the

Graphical User Interface Menus2-5Help MenuThe Help menu provides access to the context-sensitive help for the MATLAB Excel Builder graphical user inte

2 Graphical User Interface2-6Project SettingsChoosing New Project or Open Project from the File menu or Settings from the Project menu opens the appro

Component Information2-7Component InformationThe Component Info choice under the Component menu displays the Component dialog box.This dialog presents

2 Graphical User Interface2-8

3Programming with Excel Builder ComponentsOverview . . . . . . . . . . . . . . . . . . . . . 3-2When to

3 Programming with Excel Builder Components3-2OverviewEach MATLAB Excel Builder component is built as a stand-alone COM object. You access a component

When to Use a Formula Function or a Subroutine3-3When to Use a Formula Function or a SubroutineVisual Basic for Applications (VBA) provides two basic

3 Programming with Excel Builder Components3-4Initializing Excel Builder Libraries with ExcelBefore you use any MATLAB Excel Builder component, initia

Initializing Excel Builder Libraries with Excel3-5This code is similar to the default initialization code generated in the VBA module created when the

ii Contents2Graphical User InterfaceGraphical User Interface Menus . . . . . . . . . . . . . . . . . . . . . . . . 2-2File Menu . . . . . . . . . .

3 Programming with Excel Builder Components3-6Creating an Instance of a ClassBefore calling a class method (compiled MATLAB function), you must create

Creating an Instance of a Class3-7Dim aClass As mycomponent.myclassOn Error Goto Handle_ErrorSet aClass = New mycomponent.myclass' (call some met

3 Programming with Excel Builder Components3-8The following example illustrates this technique with the second method.Dim aClass As mycomponent.myclas

Calling the Methods of a Class Instance3-9Calling the Methods of a Class InstanceAfter you have created a class instance, you can call the class metho

3 Programming with Excel Builder Components3-10aClass = CreateObject("mycomponent.myclass.1_0")Call aClass.foo(1,y,x1,x2)foo = yExit Functio

Processing varargin and varargout Arguments3-11Processing varargin and varargout Arguments When varargin and/or varargout are present in the original

3 Programming with Excel Builder Components3-12Dim aUtil As ObjectDim v As Variant On Error Goto Handle_ErroraUtil = CreateObject("MWComUtil.MWUt

Handling Errors During a Method Call3-13Handling Errors During a Method CallErrors that occur while creating a class instance or during a class method

3 Programming with Excel Builder Components3-14Modifying FlagsEach MATLAB Excel Builder component exposes a single read/write property named MWFlags o

Modifying Flags3-15x(1,2) = 12x(2,1) = 21x(2,2) = 22var2 = xSet aClass = New mycomponent.myclassCall aClass.foo(1,y1,var1)Call aClass.foo(1,y2,var2)Ex

iii4Usage ExamplesMagic Square Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-2Creating the Project . . . . . . . . . .

3 Programming with Excel Builder Components3-16the resulting array. If this flag is not set, the target range must be at least as large as the output

Modifying Flags3-17Handle_Error:MsgBox(Err.Description)End SubThis example converts var1 of type Variant/Integer to an int16 and var2 of type Variant/

3 Programming with Excel Builder Components3-18Sub foo(y1 As Variant, y2 As Variant)Dim aClass As mycomponent.myclassDim ytemp As MWArgDim today As Da

4Usage ExamplesMagic Square Examples . . . . . . . . . . . . . . . 4-2Creating the Project . . . . . . .

4 Usage Examples4-2Magic Square ExamplesThe M-file mymagic takes a single input, an integer, and creates a magic square of that size.The Excel file my

Magic Square Examples4-3Creating the ProjectFigure 4-1: Empty New Project Settings Dialog BoxFrom the MATLAB command prompt change directories to <

4 Usage Examples4-4• Select C as the code to compile in.• Leave all Compiler options unselected.The New Project Settings dialog now looks like Figure

Magic Square Examples4-5Build debug version = NoShow verbose output = NoBuilding the Project• From the Excel Builder graphical user interface click Ad

4 Usage Examples4-6Illustration 2. Transpose the OutputReopen the Macro dialog, select the mymagic_transpose macro and click the Run button. This proc

Magic Square Examples4-7Inspecting the Visual Basic CodeOn the Excel main window select Visual Basic Editor from the Tools -> Macro menu.From the V

iv ContentsFunction Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-3Component Browser . . . . . . . . . . . .

4 Usage Examples4-8Using Multiple Files and Variable ArgumentsThe M-file, myplot, takes a single integer input and plots a line from 1 to that number.

Using Multiple Files and Variable Arguments4-9Figure 4-7: Empty New Project Settings DialogOn the New Project Settings dialog, enter the settings as

4 Usage Examples4-10The New Project Settings dialog now looks like Figure 4-8.Figure 4-8: New Project Settings with Entries• Click OK to create the x

Using Multiple Files and Variable Arguments4-11Building the Project• From the Excel Builder graphical user interface click Add File … .• Select the fi

4 Usage Examples4-12Figure 4-9: mymulti.xls

Using Multiple Files and Variable Arguments4-13Illustration 4: Calling myplot This illustration calls the function myplot with a value of 4. To execut

4 Usage Examples4-14Figure 4-11: myplot OutputIllustration 5: Calling mysum Four Different WaysThis illustration calls the function mysum in four dif

Using Multiple Files and Variable Arguments4-15Figure 4-12: Four Different Calls to mysumThis illustration runs when the Excel file is opened. To rea

4 Usage Examples4-16Figure 4-14: myprimes Output for Value of 10This function automatically resizes if the returned output is larger than the output

Using Multiple Files and Variable Arguments4-17Figure 4-16: Visual Basic Code for mymulti.xls

vCRegistration and VersioningComponent Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-2Self-Registering Components . .

4 Usage Examples4-18Spectral Analysis ExampleThis example illustrates the creation of a comprehensive Excel add-in to perform spectral analysis. It re

Spectral Analysis Example4-19 powerspect = []; return; end if (interval <= 0) error('Sampling interval must be grea

4 Usage Examples4-20See “Project Settings” on page 2-6 for a description of new project settings.3 Add the computefft.m and plotfft.m M-files to the p

Spectral Analysis Example4-21Figure 4-17: VBA Project: Insert->Module3 Enter the following code in the FourierMain module:'' FourierMain

4 Usage Examples4-22Private Sub LoadFourier()'Initializes globals and Loads the Spectral Analysis form Dim MainForm As frmFourier On Error

Spectral Analysis Example4-23Figure 4-18: Creating the Visual Basic Form3 Now add a series of controls to the blank form to complete the dialog, as s

4 Usage Examples4-24RefEdit refedtInputSelects range for input data.Label Label2Caption = Sampling Interval Labels the TextBox for sampling interval.C

Spectral Analysis Example4-25Figure 4-19, Layout of Controls on Main Form, on page 4-26 shows the controls layout on the form.CommandButton btnOKCapti

4 Usage Examples4-26Figure 4-19: Layout of Controls on Main FormWhen the form and controls are complete, right-click on the form and select View Code

Spectral Analysis Example4-27''frmFourier Event handlers'Private Sub UserForm_Activate()'UserForm Activate event handler. This fun

vi Contents

4 Usage Examples4-28 'Process inputs Set R = Range(refedtInput.Text) If Err <> 0 Then MsgBox ("Invalid range entered f

Spectral Analysis Example4-29Adding The Spectral Analysis Menu Item to ExcelThe last step in the integration process adds a menu item to Excel so that

4 Usage Examples4-30Private Sub Workbook_AddinInstall()'Called when Addin is installed Call AddFourierMenuItemEnd SubPrivate Sub Workbook_Addi

Spectral Analysis Example4-312 When the Workbook Properties dialog appears, select the Summary tab and enter Spectral Analysis as the workbook title.3

4 Usage Examples4-3215 and 40 Hz. Sample the signal for 10 seconds at a sampling rate of 0.01 sec. Put the time points into column A and the signal po

Spectral Analysis Example4-33Figure 4-21: Worksheet with Inputs and Outputs for Test ProblemThe power spectral density reveals the two signals at 15

4 Usage Examples4-34Package the Add-inAs a final step, package the add-in, the COM component, and all supporting libraries into a self-extracting exec

5Function WizardIntroduction . . . . . . . . . . . . . . . . . . . . 5-2Installing the Function Wizard Add-i

5 Function Wizard5-2IntroductionThe Function Wizard enables you to pass Microsoft Excel (Excel 200 or later) worksheet values to a compiled MATLAB mod

Introduction5-3To start the Function Wizard, click on Tools -> MATLAB Functions on the Excel menu bar. The starting point of the Function Wizard, c

PrefaceWhat Is MATLAB Excel Builder? . . . . . . . . . . . viiiSuggested Background . . . . . . . . . . .

5 Function Wizard5-4The Function Viewer controls the execution of worksheet functions. Use the Function Viewer to organize the list of all currently l

Introduction5-5Component BrowserThe Component Browser lists all MATLAB Excel Builder components currently installed on the system. When you click the

5 Function Wizard5-6Function PropertiesThis group of dialog boxes sets properties and values for the inputs and outputs. You can map inputs and output

Introduction5-7Editing Function Arguments. Function arguments may be either required arguments or varargin/varargout arguments: • Required arguments a

5 Function Wizard5-8Editing varargin InputsAdd new varargin argument to list.Delete selected varargin argument.Return to Function Viewer.Invoke help.S

Introduction5-9Editing Required Outputs• The Add and Delete buttons become active when you select varargout Arguments. • Select the Inputs tab to swit

5 Function Wizard5-10Editing varargout OutputsAdd new varargout argument to list.Delete selected varargout argument.Return to Function Viewer.Invoke h

Introduction5-11Argument PropertiesThe Argument Properties and related dialog boxes allow you to select worksheet ranges or optionally enter a specif

5 Function Wizard5-12Input Argument ValueOutput Argument PropertiesEnter value for current argument.Save and return to Function Viewer.Automatically r

Introduction5-13Function UtilitiesRename FunctionUse this dialog box to rename a function. When you click the Rename button on the Function Viewer, th