MATLAB BUILDER EX 1 Guide de l'utilisateur

MATLAB®Builder for Excel®The Language of Technical ComputingComputationVisualizationProgrammingUser’s GuideVersion 1

x Contents

A Producing a COM Object from MATLABWhen a component is created, you can specify a version number (default =1.0). During the develop ment of a specifi

Calling ConventionsCalling Co nventionsThis section describes t he calling conventions for MATLAB Builder for Excelcomponents, including mappings from

A Producing a COM Object from MATLAB...[in] VARIANT varargin);This IDL function definition is generated by producing a function with thesame name as t

Calling Conventionsvarargin As Variant)(See “Programming w ith COM Components C reated by MATLAB Builderfor .NET” in the MATLAB B uilder for .NET docu

A Producing a COM Object from MATLAB(function body)...foo = Y1...(error handling code)...End FunctionBy default, the generated formula function contai

BData ConversionData Conversion Rules (p. B-2)Describes the process of convertingdata betwee n M ATLAB an d C O MvariantsArray Formatting Flags (p. B-

B Data ConversionData Conversion RulesThis topic describes the data co nv ersion rules for MATLAB Builder for Excelcomponents. These components are du

Data Conversion RulesVA RIANT Type Codes SupportedVARIANT Type Code(C/C++) C/C++ TypeVariantType Code(Visual Basic)VisualBasicType DefinitionVT_EMPTY

B Data ConversionVARIANT Type Codes Supported (Continued)VARIANT Type Code(C/C++) C/C++ TypeVariantType Code(Visual Basic)VisualBasicType DefinitionVT

Data Conversion RulesVARIANT Type Codes Supported (Continued)VARIANT Type Code(C/C++) C/C++ TypeVariantType Code(Visual Basic)VisualBasicType Definiti

1Getting StartedWhat Is MATLAB Builder for E xcel?(p. 1-2)Brief d es cription of t he productBuilding a Deployable Application(p. 1-4)Describes the st

B Data ConversionMATLAB to COM VARIANT Conversion Rules (Continued)MATLAB DataTypeVARIANT Type forScalar DataVARIANT Type forArray Data CommentscharA1

Data Conversion RulesMATLAB to COM VARIANT Conversion Rules (Continued)MATLAB DataTypeVARIANT Type forScalar DataVARIANT Type forArray Data Commentsdo

B Data ConversionMATLAB to COM VARIANT Conversion Rules (Continued)MATLAB DataTypeVARIANT Type forScalar DataVARIANT Type forArray Data Commentsuint8A

Data Conversion RulesMATLAB to COM VARIANT Conversion Rules (Continued)MATLAB DataTypeVARIANT Type forScalar DataVARIANT Type forArray Data Commentsin

B Data ConversionCOM VARIANT to MATLAB Conversion RulesVARIANT TypeMATLAB Data Type(scalar or arraydata) CommentsVT_EMPTYN/A Empty array created.VT_I1

Data Conversion RulesCOM VARIANT to MATLAB Conversion Rules (Continued)VARIANT TypeMATLAB Data Type(scalar or arraydata) CommentsVT_INT int32VT_UINT u

B Data ConversionArray Formatting FlagsExcel Builder components have flags that control how array data is formattedin both directions. Generally, you

Array For matting FlagsArray Formatting Flags (Continued)Flag DescriptionInputArrayIndFlagSets the input array indirection level used with theInputArr

B Data ConversionData Conversion FlagsExcel Builder components contain the following flags to control the conversionof certainVARIANT types to M ATLAB

Data Conversion FlagsVT_UINTVT_ERRORVT_BOOLVT_DATEValid values for this flag aremwTypeDefaultmwTypeCharmwTypeDoublemwTypeSinglemwTypeLogicalmwTypeInt8

1 Getting StartedWhat Is MATLAB Builder for Excel?MATLAB®Builder for Excel®(also called Excel Builder) is an extension to theMATLAB Compiler. You use

B Data Conversionthe rule listed in VARIANT Type Codes Supported on page B-3. ThemwDateFormatString flag converts a VARIANT date to its string represe

CUtility LibraryReferencing Utility Classes (p. C-2)How to reference the classes in yourprogramming environment.Utility Library Classes (p. C-3)Descri

C Utility LibraryReferencing Utility ClassesThis section describes the MWComUtil library provided with MATLAB Builderfor Excel. This library is freely

Utility Library ClassesUtility Library Class esThe MATLAB Builder for E xcel Utility library provides these classes:• “Class MWUtil” on page C-3• “Cla

C Utility LibraryParameters.Argument Type DescriptionpApp ObjectA valid reference tothe current ExcelapplicationReturn Value. None.Remarks. This funct

Utility Library ClassesSub MWPack(pVarArg, [Var0], [Var1], ... ,[Var31])Packs a variable length list of Variant argum ents into a single Variantarray.

C Utility LibraryOptional V2 As Variant, _Optional V3 As Variant, _Optional V4 As Variant, _Optional V5 As Variant, _Optional V6 As Variant, _Optional

Utility Library ClassesArgument Type DescriptionnStartAt LongOptional startingindex (zero-based)in the array to beginprocessing. Default =0.bAutoResiz

C Utility LibraryThis function produces a sequence o f nargout random column vectors, withthe length of theith vector equal to i. Assume that this fun

Utility Library ClassesParameters.Argument Type DescriptionpVar VariantVariantto be convertedReturn Value. None.Remarks. MATLAB handles dates as doubl

What Is MATLAB Builder for Excel?name and class name, and which methods (MATLAB functions) go into aparticular class, are purely organizational.As a g

C Utility LibrarySet aUtil = CreateObject("MWComUtil.MWUtil")Call aClass.getdates(1, R, R.Rows.Count, inc)Call aUtil.MWDate2VariantDate(R)Ex

Utility Library ClassesProperty InputArrayFormat As mwArrayFormat. This property of typemwArrayFormat controls the formatting of arrays passed as inpu

C Utility LibraryProperty OutputArrayF orm a t As mwArra yFo rmat. This property of typemwArrayFormat controls the formatting of arrays passed as outp

Utility Library ClassesProperty AutoResizeOutput As Boolean. This flag applies to Excel rangesonly. When the target output from a method call is a ran

C Utility LibraryConversion Rules for Input DatesValue Behavio rmwDateFormatNumericConvert dates to numeric values asindicated by the rule listed in C

Utility Library Classesexplicit type declaration for the aClass variable. As with previous examples,this function assumes thatMWInitApplication has be

C Utility LibraryParameters.Argument Type DescriptionppFlags MWFlagsReferencetoanuninitializedMWFlagsobject that receives thecopyReturn Value. NoneRem

Utility Library ClassesParameters.Argument Type DescriptionvarDims VariantOptional array ofdimensionsvarFieldNames VariantOptional array of fieldnames

C Utility LibraryCall y.Initialize(, Array("name", "age", "salary"))Exit SubHandle_Error:MsgBox(Err.Description)End SubP

Utility Library ClassesField name only This format may be used only in the case of a1-by-1 structure array and returns the namedfield’s value. For exa

1 Getting StartedBuilding a Deployable ApplicationUsing MATLAB Builder for Excel to create a deployable application requiresthe following steps:• “Cre

C Utility LibraryAll indices and field name This format accesses an array element ofa multidim ensional array by specifyingnindices. These statements

Utility Library ClassesDim Index1(1 To 2) As IntegerDim Index2(1 To 2) As IntegerIndex1(1) = 1Index1(2) = 1Index2(1) = 3Index2(2) = 2x(Index1, Index2,

C Utility LibraryExample. The next Visual Basic code sample illustrates how to a ccess atwo-dimensional structure array’s fields when the field names

Utility Library ClassesParameters.Argument Type DescriptionppStruct MWStructReferencetoanuninitializedMWStructobject to receive thecopyReturn Value. N

C Utility LibraryEnd SubClass MWFieldThe MWField class holds a single field reference in an MWStruct object. Thisclass is noncreatable and contains th

Utility Library ClassesParameters.Argument Type DescriptionppField MWFieldReferencetoanuninitializedMWFieldobject to receive thecopyReturn Value. None

C Utility Libraryand type of the Real property’s array, an error results when the object isused in a method call.Example. The following Visual B asic

Utility Library ClassesSub Clone(ppComplex As MWComplex)Creates a copy of an MWComplex object.Parameters.Argument Type DescriptionppComplex MWComplexR

C Utility LibraryProperty NumColumns As LongStores the colum n dimension for the array. The value of NumColumns must benonnegative. If the value is 0,

Utility Library ClassesSub Clone(ppSparse As MWSparse)Creates a copy of an MWSparse object.Parameters.Argument Type DescriptionppSparse MWSparseRefere

Building a Deployable Application3 Create a new project by clicking the New Project button in the toolbar.4 A dd f iles that you want to enc ap su lat

C Utility Libraryvals(K) = -1K=K+1rows(K) = Icols(K) = Ivals(K) = 2K=K+1rows(K) = I + 1cols(K) = Ivals(K) = -1K=K+1Nextrows(K) = 5cols(K) = 5vals(K) =

Utility Library ClassesProperty Value As VariantThe Value property stores the actual argument to pass. Any type that can bepassed to a compiled method

C Utility LibraryEnumerationsThe MATLAB Builder for Excel Utility library provides three enumerations(sets of constants):• “Enum mwArrayFormat” on pag

EnumerationsmwDataType Values (Continued)Constant Numeric Value MATLAB TypemwTypeDouble 6 doublemwTypeSingle 7 singlemwTypeInt8 8 int8mwTypeUint8 9 ui

C Utility LibraryC-34

DTroubleshooting

D TroubleshootingThis appendix provides a table showing errors you may encounter usingMATLAB Builder for Excel, probable causes for these errors, and

TroubleshootingExcel Builder Errors and Sugges ted Solutions (Continued)Message Probable Cause Suggested SolutionLoadLibrary("component_name.dll&

D TroubleshootingRequired L oc ations to Develop and Use ComponentsComponentDevelopment Machine Tar get MachineMCR Makesurethatmatlabroot\bin\win32app

TroubleshootingExcel Errors and Suggested SolutionsMessage Probable Cause Suggested SolutionThe macros in this p roject aredisabled. Please refer to t

1 Getting Started6 Add classes (optional).7 Save the p roject by clicking the Save button in the toolbar.8 Bu ild the component.Click the Buildbuttoni

D TroubleshootingD-6

EExamplesUse this list to find examples in the d o cumentation.

E ExamplesCalling a MATLAB Function from Excel“Magic Square Examples” on page 3-2Using Multiple Files and Variable Arguments“Multiple Files and Variab

IndexIndexAarray formatting flags 2-14Ccapabilities A-2class 1-2class methodcalling 2-6Class MWFlags C-10Class MWUtil C -3class name 1-2COMdefined 1-2

IndexRrequired argument s 4-8Sself-registering component A-7subroutines 2-3TtroubleshootingExcel Builder D-2type library A-5Uunregistering components

Building a Deployable Applicationnot specify the class name (<class_name>), mcc uses the component name asthe default. If you do not specify a v

1 Getting StartedFormula functions of this type are most useful to access a function of one ormore inputs that returns a single scalar value. When you

Building a Deployable Applicationreopening the project in D ep loym ent Tool and clicking the Package buttonin the t oolbar. Repeat this distribution

How to Contact The MathWorkswww.mathworks.comWebcomp.soft-sys.matlab Newsgroupwww.mathworks.com/contact_TS.html Technical [email protected]

1 Getting Started1-10

2Programming withMATLAB Builder forExcelOverview of the Integration Process(p. 2-2)Provides information on integra tingMATLAB Builder for Excelcompone

2 Programming with MATLAB Builder for ExcelOver view of the Integration ProcessEach MATLAB Builder for Ex cel component is built as a CO M object that

When to Use a Formula Function or a SubroutineWhentoUseaFormulaFunctionoraSubroutineVBA provides two basic procedure types: functions and subroutines.

2 Programming with MATLAB Builder for ExcelInitializing M ATLAB Builder for E xcel Libraries with ExcelBefore you use any MATLAB Builder for Excel com

Initializing MATLAB Bu ilder for Exc el Libraries with ExcelThis code is similar to the default initialization code generated in the VBAmodule created

2 Programming with MATLAB Builder for ExcelCreating an Instance of a ClassBefore calling a class method (compiled MATLAB function), you mustcreate an

Creating an Instance of a ClassFunction foo(x1 As Variant, x2 As Variant) As VariantDim aClass As mycomponent.myclassOn Error Goto Handle_ErrorSet aCl

2 Programming with MATLAB Builder for ExcelHandle_Error:foo = Err.DescriptionEnd FunctionHow the MCR Is Shared Among ClassesMATLAB Builder for Excel c

Calling the Methods of a Class InstanceCalling the Me thods of a Class InstanceAfter you have created a class instance, you can call the class methods

Revision HistoryDecember 2001 Online only New for Version 1.0July 2002 First printing Revised for Version 1.1 (Release 13)June 2004 Online only Revise

2 Programming with MATLAB Builder for ExcelFunction foo(x1 As Variant, x2 As Variant) As VariantDim aClass As ObjectDim y As VariantOn Error Goto Hand

Processing varargin and varargout ArgumentsProcessing varargin and varargout ArgumentsWhen varargin and/or varargout are present in the MATLAB functio

2 Programming with MATLAB Builder for ExcelSub foo(Rout1 As Range, Rout2 As Range, Rout3 As Range, _Rin1 As Range, Rin2 As Range)Dim aClass As ObjectD

Handling Errors During a Method CallHandling Errors During a M ethod CallErrors that occur while creating a class instance or during a class methodcre

2 Programming with MATLAB Builder for ExcelModifying FlagsEach MATLAB Builder for Excel component exposes a single read/writeproperty namedMWFlags of

Modifying F lagsvar1(1,2) = 12#var1(2,1) = 21#var1(2,2) = 22#x(1,1) = 11x(1,2) = 12x(2,1) = 21x(2,2) = 22var2 = xSet aClass = New mycomponent.myclassC

2 Programming with MATLAB Builder for ExcelSetting this flag presents all array input to the compiled MATLAB functionas cell arrays.Similarly, you can

Modifying F lagsDim aClass As mycomponent.myclassDim var1, var2 As VariantDim y As VariantOn Error Goto Handle_Errorvar1 = 1var2 = 2#Set aClass = New

2 Programming with MATLAB Builder for ExcelmwDateFormatStringCall aClass.foo(1,y,today)Exit SubHandle_Error:MsgBox(Err.Description)End SubThe n ext ex

3Usage ExamplesMagic Square Examples (p. 3-2)Creates a m agic square from a singleinput integerMultiple Files and VariableArguments Example (p. 3-6)Pl

3 Usage ExamplesMagic Square ExamplesThe M-file mymagic takes a single input, an integer, and creates a magicsquare of that size.The Excel filemymagic

Magic Square ExamplesSettingValueProject namexlmagicClass namexlmagicclassProject d irectory The name of your work directory followed by thecomponent

3 Usage ExamplesIllustration 2. Transpose the OutputReopen the Macro dialog box, select the mymagic_transpose macro and clickRun. This procedure retur

Magic Square ExamplesInspecting the Visual Basic Code1 From the Excelmainwindow,clickTools > Macro > Visual Basic Editor.2 W hen the Visual B as

3 Usage ExamplesMultiple Files and Va riab le Arguments ExampleThe M-file, myplot, takes a single integer input and plo ts a line from 1 tothat number

Multiple Files and Variable Arguments ExampleSettingValueProject namexmultiClass namexmulticlassProject d irectory The name of your work directory fol

3 Usage ExamplesIllustration 4: Calling myplo tThisillustration calls the functionmyplot with a value of 4. To execute thefunction, make A7 the active

Multiple Files and Variable Arguments ExampleThis procedure plots a line from 1 through 4 in a MATLAB Figure window.This graphic can be manipulated as

3 Usage Examples• The second (cell A19) takes a range objectthatisarangeofcellswiththevalues 1 through 10, adds them, and returns the result of 55.• T

Multiple Files and Variable Arguments ExampleTo execute the macro, from the main Excel window (not the Visual BasicEditor), open the Macro dialog box,

ContentsGetting Started1What Is MATLAB Builder for Excel?...1-2Component Naming Conventions...1-2Building a Deployable

3 Usage ExamplesInspecting the Visual Basic Code1 On the Excel main window, click Tools > Macro > Visual Basic Editor.2 On the Visual Basic Edit

Spectral Analysis ExampleSpectral Analysis ExampleThis example illustrates the creation of a comprehensive Excel add-in toperform spectral analysis. I

3 Usage Examplescomputefft.m:function [fftdata, freq, powerspect] = computefft(data, interval)if (isempty(data))fftdata = [];freq = [];powerspect = []

Spectral Analysis Example2 Create a project with the following settings.SettingValueComponent nameFourierClass nameFourierProject d irectory The name

3 Usage ExamplesCreating the M a in VB Code Module for the Application. The add-inrequires some initialization code and som e global variables to hold

Spectral Analysis ExamplePublic theFourier As Fourier.Fourierclass 'Global instance of Fourier objectPublic theFFTData As MWComplex 'Global

3 Usage ExamplesMsgBox (Err.Description)End SubCreating the Visual Basic FormThe next step in the integration process develops a user interface for y

Spectral Analysis Example3 Add a series of controls to the b lank form to complete the dialog b ox, assummarized in the following table:Controls Neede

3 Usage ExamplesControls Needed for Spectral A nalysis Example (Continued)Control Type Control Name Properties PurposeLabel Label3Caption =Frequency:L

Spectral Analysis ExampleControls Needed for Spectral A nalysis Example (Continued)Control Type Control Name Properties PurposeRefEdit refedtImagN/A S

Handling Errors During a Method Call ... 2-13Modifying Flags... 2-14Array Formatting Flags...

3 Usage Examplesforanyofthecontrolsoranyglobalvariable,changethiscodetoreflectthose differences.''frmFourier Event handlers'Private Sub

Spectral Analysis Example'Cancel button click event handler. Exits form without computing fft'or updating variables.Unload MeEnd SubPrivate

3 Usage ExamplesbPlot = chkPlot.Value'Compute the fft and optionally plot power spectral densityIf bPlot ThenCall theFourier.plotfft(3, theFFTDat

Spectral Analysis Example2 Place the following code into ThisWorkbook.Private Sub Workbook_AddinInstall()'Called when Addin is installedCall AddF

3 Usage ExamplesCall RemoveFourierMenuItem'Find Tools menuSet ToolsMenu = Application.CommandBars(1).FindControl(ID:=30007)If ToolsMenu Is Nothin

Spectral Analysis ExampleTesting the Add-InBefore distributing the add-in, test it with a sample problem.Spectral analysis is commonly used to find th

3 Usage Examples3 Click and hold the l ower-right corner of cell A2 and drag the formula downthe column to cell A1001. This procedure fills the range

Spectral Analysis ExampleThe power spectral density reveals the two signals at 15 and 40 Hz.Packaging and Distributing the Add-InAs a final step, pack

3 Usage ExamplesMATLAB Builder for Excel creates the Fourier_pkg.exe self-extractingexecutable.3 To install this add-in onto another computer, copy th

4Function WizardOverview of the Function Wizard(p. 4-2)Describes the purpose and use of theFunction WizardInstalling the Function WizardAdd-In (p. 4-3

Installing the F unction Wizard Add-In ... 4-3Starting the Function Wizard... 4-4Understanding the F unction Viewer...

4 Function WizardOver view of the Function WizardThe Function Wizard enables you to pass Microsoft Excel (Excel 2000 or later)worksheet values to a co

Installing the Function Wizard Add-InInstalling the Function Wizard Add-InThe Function Wizard G U I is contained in an Excel add-in (mlfunction.xla)re

4 Function WizardStar ting the Function WizardTo start the Function Wizard, click Tools > MATLAB Functions fromthe Excel m enu bar. The starting po

Understanding the Function ViewerUnderstanding the Function V iewerThe Function Viewer controls the execution of worksheet functions. Use theFunction

4 Function Wizard3 Click Execute on the Functio n Viewer.When you click Execute, functions execute in the order displayed in thelist.4-6

Component BrowserComponent BrowserThe Component Browser lists all Excel Builder components currentlyinstalled on t he system. When you click New on th

4 Function WizardFunction PropertiesThis group of dialog boxes sets properties and values for the inputs andoutputs. You can map inputs and outputs to

Function Pro perties• Req u ired arguments ap pe ar first on the left or right sides of a MATLABfunction and are not namedvarargin or varargout.•varar

4 Function WizardEditing Required OutputsWhen you are editing required output arguments, the Function Propertiesdialog box appears as shown:The Add an

Function Pro pertiesEditing varargout OutputsWhen you are editing varargout outputs, the Function Properties dialogbox appears as shown:4-11

Component Registration ... A-5Obtaining Registry Information... A-5Self-Registering Components...

4 Function WizardArgument PropertiesThe Argument Properties and related dialog boxes allow you to selectworksheet ranges or optionally enter a specifi

Argument PropertiesOutput Argument Properties Dialog BoxHere is an example of the Argument Properties dialog box for outputarguments. In this example,

4 Function WizardFunction UtilitiesExcel Builder supports several function utilities, which you use via thefollowing dialog boxes:• “Renam e Function

Function Utilities• The Advanced tab creates a rectangular array of copies of the currentfunction in the current worksheet, and optionally copies the

4 Function Wizard• Positive increments move rows down and columns to the right.• Negative increments move rows up and columns to the left.You can also

5Functions — AlphabeticalListcomponentinfodeploytool

componentinfoPurpose Query system registry about component created w ith MATLAB Builderfor ExcelSyntax info = componentinfoinfo = componentinfo(compon

componentinfoThe return value is an array of structures representing all the registryand type information needed to load and use the component.When yo

componentinfoField DescriptionInterfacesAn array of structures defining all interface definition s inthe type library. Each structure contains tw o fi

componentinfo• C - An array of C -Language function prototypes.• VB - An array of VBA function prototypesExamplesFunction CallReturnsInfo = componenti

Class MWSparse ... C-27Class MWArg... C-30Enumerations...

deploytoolPurpose Open GUI for MATLAB Builder for E xcel and MATLAB CompilerSyntax deploytoolDescription The deploytool command displays the Deploymen

AProducing a COM Objectfrom MATLABOverview of Internal Processes(p. A-2)Provides a high-level description ofinternal processesComponent Registration (

A Producing a COM Object from MATLABOver view of Internal ProcessesMATLAB Builder for Excel lets you pass M icrosoft E xcel worksheet valuesto a compi

Overview of Internal ProcessesThe process of creating an Excel Buildercomponentiscompletely automaticfrom a user p oint of v iew. You specify a list o

A Producing a COM Object from MATLABinterface definition in the IDL file. The interface GUID file contains thedefinitions of the GUIDs from all interf

Component RegistrationComponent RegistrationWhen Excel Builder creates a compone nt, it automatically generates a binaryfile called a type library. As

A Producing a COM Object from MATLABans =Name: 'Imyclass'IID: '{3A14AB36-44BE-11D5-B155-00D0B7BA7544}'Info.CoClassesans =Name: &ap

Component Registrationans =function [varargout] = randvectors()ans =function [x] = getdates(n, inc)ans =function Page = myprimes(n)Self-Registering Com

A Producing a COM Object from MATLABNote The mwregsvr utility invokes a process that is similar to regsvr32.exe,except thatmwregsvr does not require i

Component Registration(Continued)Key DefinitionHKEY_CLASSES_ROOT\InterfaceInformation about COM interfaces on thesystem. Each component creates a new