MATLAB Excel Builder The Language of Technical Computing Computation Visualization Programming User’s Guide Version 1
How to Contact The MathWorks: www.mathworks.com comp.soft-sys.matlab Web Newsgroup info@mathworks.com Technical support Product enhancement suggestions Bug reports Documentation error reports Order status, license renewals, passcodes Sales, pricing, and general information 508-647-7000 Phone 508-647-7001 Fax The MathWorks, Inc. 3 Apple Hill Drive Natick, MA 01760-2098 Mail support@mathworks.com suggest@mathworks.com bugs@mathworks.com doc@mathworks.com service@mathworks.
Contents Preface What Is MATLAB Excel Builder? . . . . . . . . . . . . . . . . . . . . . . . viii Suggested Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix Requirements for MATLAB Excel Builder . . . . . . . . . . . . . . . . . x System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Compiler Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x Excel Requirements . . . . . . . . . . . . . . . . . . . . . . . .
Graphical User Interface 2 Graphical User Interface Menus . . . . . . . . . . . . . . . . . . . . . . . . File Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Project Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Build Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Component Menu . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Help Menu . . . . . . . . . . .
Usage Examples 4 Magic Square Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating the Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Building the Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding the Excel Builder COM Function to Excel . . . . . . . . . . . Illustration 1. Output Magic Square Results to Excel . . . . . . . . Illustration 2. Transpose the Output . . . . . . . . . . . . . . . . . . . . .
Function Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-3 Component Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-5 Function Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-6 Argument Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-11 Function Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Registration and Versioning C Component Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-2 Self-Registering Components . . . . . . . . . . . . . . . . . . . . . . . . . . . C-2 Globally Unique Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-2 Versioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-4 Obtaining Registry Information . . . . . . . . . . . . . . . . . . . . . . . .
vi Contents
Preface What Is MATLAB Excel Builder? . . . . . . . . . . . viii Suggested Background . . . . . . . . . . . . . . . . ix Requirements for MATLAB Excel Builder System Requirements . . . . . . . . . . . Compiler Requirements . . . . . . . . . . Excel Requirements . . . . . . . . . . . Limitations and Restrictions . . . . . . . . Upgrading from a Previous Release . . . . . Related Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x x x x xi xi . . . . . . . .
Preface What Is MATLAB Excel Builder? MATLAB® Excel Builder provides the capability to incorporate seamlessly and quickly MATLAB models and functions into Excel worksheets. The graphical user interface enables you to build and deploy Excel add-ins containing functionality designed in MATLAB but accessed from the Excel environment.
Suggested Background Suggested Background Users of this product need to be familiar with • MATLAB and the MATLAB Compiler • Microsoft Excel • Visual Basic for Applications (VBA) It is helpful to have some background in Component Object Model (COM) objects (DLLs). See the documentation provided by the vendors for detailed information.
Preface Requirements for MATLAB Excel Builder System Requirements System requirements and restrictions on use for Excel Builder are almost identical to those listed in the MATLAB Compiler documentation. For specific information see the “System Requirements” section under “Microsoft Windows on PCs” in your Compiler document.
Requirements for MATLAB Excel Builder Limitations and Restrictions In general, limitations and restrictions on the use of MATLAB Excel Builder are the same as those for the MATLAB Compiler. See the “Limitations and Restrictions” section of the MATLAB Compiler documentation for details. Note that although the Compiler supports some usage of the MATLAB input command, MATLAB Excel Builder does not support this command at all.
Preface Related Products The MathWorks provides several products relevant to the tasks you can perform with MATLAB Excel Builder. For more information about any of these products, see either: • The online documentation for that product if it is installed or if you are reading the documentation from the CD • The MathWorks Web site, at http://www.mathworks.com; see the “products” section Note The toolboxes listed below all include functions that extend the capabilities of MATLAB.
Typographical Conventions Typographical Conventions This manual uses some or all of these conventions. Item Convention Example Example code Monospace font To assign the value 5 to A, enter A = 5 Function names, syntax, filenames, directory/folder names, and user input Monospace font The cos function finds the cosine of each array element. Syntax line example is MLGetVar ML_var_name Buttons and keys Boldface with book title caps Press the Enter key.
Preface xiv
1 Overview Building a Deployable Application . . Elements of an Excel Builder Project . . Creating a Project . . . . . . . . . . Managing M-Files and MEX-Files . . . . Building a Project . . . . . . . . . . Testing the Model . . . . . . . . . . Creating an Excel Add-In . . . . . . . Packaging and Distributing the Component . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1 Overview Building a Deployable Application Using MATLAB Excel Builder to create a deployable application is a simple process requiring a sequence of six steps.
Building a Deployable Application organizational. As a general rule, when compiling many MATLAB functions, it helps to determine a scheme of function categories and to create a separate class for each category. The name of each class should be descriptive of what the class does. Organizing related functions into classes in this way has the added advantage of reducing the amount of code to rebuild and redeploy when one function is changed.
1 Overview Figure 1-1: MATLAB Excel Builder Main Window For a complete description of the features available from this window, see “Graphical User Interface Menus” on page 2-2. Select File -> New Project on this window to view the New Project Settings dialog box.
Building a Deployable Application Figure 1-2: New Project Settings Dialog Box Component name denotes the name of the DLL created later in the build process. After you enter the component name, the GUI automatically enters a Class name identical to the component name. You can change the class name to something more descriptive. Although the component name and class name can be the same, the component name cannot match the name of any M- or MEX-files added to the project later.
1 Overview Note You can accept the automatically generated project directory path or choose another of your liking. Once you click OK on this menu, this path is saved. If you later decide to move the project or change anything on its path, you need to redo the entire project specification process, including adding files to the project (see “Project Settings” on page 2-6) and respecifying the project directory path. You can choose to generate C or C++ code.
Building a Deployable Application Figure 1-3: Main Window with Options Activated Add M- and/or MEX-files to the project by clicking the Add File button or selecting the Project -> Add File… menu choice. You can add only a single file at a time to the project. Note The name of any file added to the project cannot duplicate the name of any function existing in the library of precompiled functions. The Remove button or Project -> Remove File menu choice removes any selected M- or MEX-files.
1 Overview Building a Project After you define your project settings and add the desired M- and MEXfunctions, you can build a deployable DLL and the necessary Visual Basic for Applications (VBA) code that allows Excel to access the DLL. Choosing Build -> EXCEL/COM Files or clicking the Build button invokes the MATLAB Compiler, writing the intermediate source files to \src and the output files necessary for deployment to \distrib.
Building a Deployable Application detailed discussion on integrating MATLAB Excel Builder components into Microsoft Excel via Visual Basic for Applications, see Chapter 3, “Programming with Excel Builder Components.” Application Deployment Now create an Excel add-in (.xla) from your VBA code. Return to the Excel worksheet window and save the file as an .xla file to the \distrib directory. Here are the steps necessary to create an Excel add-in from the generated VBA code.
1 Overview Choose Component -> Package Component to create a self-extracting executable containing these files. File Purpose _install.bat Script run by the self-extracting executable .dll Compiled component mglinstaller.exe MATLAB math and graphics installer mwcomutil.dll Excel Builder utility library mwregsvr.exe Executable that registers DLLs on target machines *.
2 Graphical User Interface Graphical User Interface Menus File Menu . . . . . . . . . . . Project Menu . . . . . . . . . Build Menu . . . . . . . . . . Component Menu . . . . . . . Help Menu . . . . . . . . . . Project Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-2 2-3 2-3 2-4 2-4 2-5 . . . . . . . . . . . . . . . . . . 2-6 Component Information . . . . . . . . . . . . . .
2 Graphical User Interface Graphical User Interface Menus The MATLAB function mxltool displays the MATLAB Excel Builder graphical user interface (GUI) main window. The information below describes the use of the various menus that the main window provides.
Graphical User Interface Menus File Menu The File menu creates and manages MATLAB Excel Builder projects. • New Project opens the project settings dialog box. This menu item creates a project workspace where you can add M- and MEX-files to the project and store project settings. • Open Project allows you to load a previously saved project. • Save Project saves the current project. If you have not yet saved the current project, you are prompted for a filename.
2 Graphical User Interface • Remove File removes the currently selected files from the project. (The Remove button in the Project files frame of the main window performs the same task.) • Settings opens the project settings dialog box showing the current project’s information. See “Project Settings” on page 2-6 for details. Build Menu The Build menu controls the building of the project’s files into an Excel-accessible COM object.
Graphical User Interface Menus Help Menu The Help menu provides access to the context-sensitive help for the MATLAB Excel Builder graphical user interface.
2 Graphical User Interface Project Settings Choosing New Project or Open Project from the File menu or Settings from the Project menu opens the appropriate Project Settings dialog box. New Project Settings Existing Project Settings See “Versioning” on page C-4 for a description of Component name, Class name and Project version. Project directory is the location of any project output files. You can choose to generate C or C++ code.
Component Information Component Information The Component Info choice under the Component menu displays the Component dialog box. This dialog presents the component information that is stored in the registry. See Table C-2, Registry Information Returned by componentinfo, on page C-7, for an explanation of these fields. The Methods listbox shows the name and M-file calling syntax of each function within the component.
2 Graphical User Interface 2-8
3 Programming with Excel Builder Components Overview . . . . . . . . . . . . . . . . . . . . . 3-2 When to Use a Formula Function or a Subroutine . . . 3-3 Initializing Excel Builder Libraries with Excel . . . . 3-4 Creating an Instance of a Class . . . . . . . . . . . 3-6 CreateObject Function . . . . . . . . . . . . . . . . 3-6 Visual Basic New Operator . . . . . . . . . . . . . . 3-6 Calling the Methods of a Class Instance . . . . . . . 3-9 Processing varargin and varargout Arguments . . . .
3 Programming with Excel Builder Components Overview Each MATLAB Excel Builder component is built as a stand-alone COM object. You access a component from Microsoft Excel through Visual Basic for Applications (VBA). This section provides general information on how to integrate MATLAB Excel Builder components into Excel using the VBA programming environment. It assumes that you have a working knowledge of VBA and is not intended to be a discussion on how to program in Visual Basic.
When to Use a Formula Function or a Subroutine When to Use a Formula Function or a Subroutine Visual Basic for Applications (VBA) provides two basic procedure types, functions and subroutines. You access a VBA function directly from a cell in a worksheet as a formula function and access a subroutine as a general macro. Function procedures are useful when the original MATLAB function takes one or more inputs and returns one scalar output.
3 Programming with Excel Builder Components Initializing Excel Builder Libraries with Excel Before you use any MATLAB Excel Builder component, initialize the supporting libraries with the current instance of Excel. Do this once for an Excel session that uses MATLAB Excel Builder components. To do this initialization, call the utility library function MWInitApplication, a member of the MWUtil class. This class is part of the MWComUtil library.
Initializing Excel Builder Libraries with Excel This code is similar to the default initialization code generated in the VBA module created when the component is built. Each function that uses MATLAB Excel Builder components can include a call to InitModule at the beginning to ensure that the initialization always gets performed as needed.
3 Programming with Excel Builder Components Creating an Instance of a Class Before calling a class method (compiled MATLAB function), you must create an instance of the class that contains the method. VBA provides two techniques for doing this: • CreateObject function • Visual Basic New operator CreateObject Function This method uses the Visual Basic application program interface (API) CreateObject function to create an instance of the class.
Creating an Instance of a Class Dim aClass As mycomponent.myclass On Error Goto Handle_Error Set aClass = New mycomponent.myclass ' (call some methods on aClass) Exit Function Handle_Error: foo = Err.Description End Function In this example, the class instance could be dimensioned as simply myclass. The full declaration in the form . guards against name collisions that could occur if other libraries in the current project contain types named myclass.
3 Programming with Excel Builder Components The following example illustrates this technique with the second method. Dim aClass As mycomponent.myclass Function foo(x1 As Variant, x2 As Variant) As Variant On Error Goto Handle_Error If aClass Is Nothing Then Set aClass = New mycomponent.myclass End If ' (call some methods on aClass) Exit Function Handle_Error: foo = Err.
Calling the Methods of a Class Instance Calling the Methods of a Class Instance After you have created a class instance, you can call the class methods to access the compiled MATLAB functions. MATLAB Excel Builder applies a standard mapping from the original MATLAB function syntax to the method’s argument list. See section “Calling Conventions” on page A-7 for a detailed description of the mapping from MATLAB functions to COM class method calls.
3 Programming with Excel Builder Components aClass = CreateObject("mycomponent.myclass.1_0") Call aClass.foo(1,y,x1,x2) foo = y Exit Function Handle_Error: foo = Err.Description End Function The second example rewrites the same function as a subroutine and uses Excel ranges for input and output. Sub foo(Rout As Range, Rin1 As Range, Rin2 As Range) Dim aClass As Object On Error Goto Handle_Error aClass = CreateObject("mycomponent.myclass.1_0") Call aClass.
Processing varargin and varargout Arguments Processing varargin and varargout Arguments When varargin and/or varargout are present in the original MATLAB function, these parameters are added to the argument list of the class method as the last input/output parameters in the list. You can pass multiple arguments as a varargin array by creating a Variant array, assigning each element of the array to the respective input argument.
3 Programming with Excel Builder Components Dim aUtil As Object Dim v As Variant On Error Goto Handle_Error aUtil = CreateObject("MWComUtil.MWUtil") aClass = CreateObject("mycomponent.myclass.1_0") Call aClass.foo(3,v,Rin1,Rin2) Call aUtil.MWUnpack(v,0,True,Rout1,Rout2,Rout3) Exit Sub Handle_Error: MsgBox(Err.
Handling Errors During a Method Call Handling Errors During a Method Call Errors that occur while creating a class instance or during a class method create an exception in the current procedure. Visual Basic provides an exception handling capability through the On Error Goto
3 Programming with Excel Builder Components Modifying Flags Each MATLAB Excel Builder component exposes a single read/write property named MWFlags of type MWFlags. The MWFlags property consists of two sets of constants: array formatting flags and data conversion flags. The data conversion flags change selected behaviors of the data conversion process from Variants to MATLAB types and vice versa.
Modifying Flags x(1,2) = 12 x(2,1) = 21 x(2,2) = 22 var2 = x Set aClass = New mycomponent.myclass Call aClass.foo(1,y1,var1) Call aClass.foo(1,y2,var2) Exit Sub Handle_Error: MsgBox(Err.Description) End Sub Here, two Variant variables, var1 and var2 are constructed with the same numerical data, but internally they are structured differently. var1 is a 2-by-2 array of Variants with each element containing a 1-by-1 Double, while var2 is a 1-by-1 Variant containing a 2-by-2 array of Doubles.
3 Programming with Excel Builder Components the resulting array. If this flag is not set, the target range must be at least as large as the output array or the data is truncated. The TransposeOutput flag transposes all array output. This flag is useful when dealing with MATLAB functions that output one-dimensional arrays. By default, MATLAB realizes one-dimensional arrays as 1-by-n matrices (row vectors) that become rows in an Excel worksheet. You may prefer worksheet columns from row vector output.
Modifying Flags Handle_Error: MsgBox(Err.Description) End Sub This example converts var1 of type Variant/Integer to an int16 and var2 of type Variant/Double to a double. If the original MATLAB function expects doubles for both arguments, this code might cause an error. One solution is to assign a double to var1, but this may not be possible or desirable. In such a case set the CoerceNumericToType flag to mwTypeDouble, causing the data converter to convert all numeric input to double.
3 Programming with Excel Builder Components Sub foo(y1 foo(y1 As As Variant, Variant, y2 y2 As As Variant) Variant) Sub Dim aClass aClass As As mycomponent.myclass mycomponent.myclass Dim Dim ytemp ytemp As As MWArg MWArg Dim Dimtoday todayAs AsDate Date Dim OnError ErrorGoto GotoHandle_Error Handle_Error On today==Now Now today SetaClass aClass==New Newmycomponent.myclass mycomponent.myclass Set Sety1 y1==New NewMWArg MWArg Set y1.MWFlags.DataConversionFlags.OutputAsDate==True True y1.MWFlags.
4 Usage Examples Magic Square Examples . . . . . . . . . . . Creating the Project . . . . . . . . . . . . . Building the Project . . . . . . . . . . . . . Adding the Excel Builder COM Function to Excel . Illustration 1. Output Magic Square Results to Excel Illustration 2. Transpose the Output . . . . . . . Illustration 3. Resize the Output . . . . . . . . Inspecting the Visual Basic Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4 Usage Examples Magic Square Examples The M-file mymagic takes a single input, an integer, and creates a magic square of that size. The Excel file mymagic.xls uses this function in three different ways: • The first illustration calls the function mymagic with a value of 4. The function returns a magic square of size 4 and populates a range of Excel cells with that magic square. • The second illustration uses the transpose flag to transpose a magic square of size 4.
Magic Square Examples Creating the Project Figure 4-1: Empty New Project Settings Dialog Box From the MATLAB command prompt change directories to \work. Enter the command mxltool to start the MATLAB Excel Builder graphical user interface. From the File menu select New Project. This opens the New Project Settings dialog. On the New Project Settings dialog, enter the settings as listed below. • In the Component name text block enter the component name xlmagic.
4 Usage Examples • Select C as the code to compile in. • Leave all Compiler options unselected. The New Project Settings dialog now looks like Figure 4-2. Default directory name depends upon the location of MATLAB on your machine. You can choose an alternative directory if you do not want to accept the default. Figure 4-2: New Project Settings with Entries • Click OK to create the xlmagic project. Summary of Project Settings Component name: xlmagic Class name: xlmagic Project version: 1.
Magic Square Examples Build debug version = No Show verbose output = No Building the Project • From the Excel Builder graphical user interface click Add File … . • Select the file mymagic.m from the directory \work\xlmagic and click Open. • Click Build or select Excel/COM Files from the Build menu. Adding the Excel Builder COM Function to Excel • Start Excel on your system. • Open the file \work\xlmagic\mymagic.xls.
4 Usage Examples Illustration 2. Transpose the Output Reopen the Macro dialog, select the mymagic_transpose macro and click the Run button. This procedure returns a magic square of size 4 transposed, beginning in cell B14. Figure 4-4: Transposed Magic Square Illustration 3. Resize the Output Reopen the Macro dialog, select the mymagic_resize macro, and click Run. This procedure returns a magic square of size 4 beginning in cell B32.
Magic Square Examples Inspecting the Visual Basic Code On the Excel main window select Visual Basic Editor from the Tools -> Macro menu. From the Visual Basic Editor, in the Project - VBAProject window, double-click to expand the project VBAProject (mymagic.xls). Expand the Modules folder and double-click on the Module1 module. This opens the VB Code window with the code for this project.
4 Usage Examples Using Multiple Files and Variable Arguments The M-file, myplot, takes a single integer input and plots a line from 1 to that number. The M-file, mysum, takes an input of varargin of type integer, adds all the numbers, and returns the result. The M-file, myprimes, takes a single integer input n and returns all the prime numbers less than or equal to n. The Microsoft Excel file, mymulti.xls, demonstrates these functions in a multiple of ways.
Using Multiple Files and Variable Arguments Figure 4-7: Empty New Project Settings Dialog On the New Project Settings dialog, enter the settings as listed below. • In the Component name text block enter the component name xlmulti. Press the Tab key to move to the Class name text block. • This automatically fills in the Class name field with the name xlmulti. Leave this text in the Class name field. • The version has a default of 1.0. Leave this version as is.
4 Usage Examples The New Project Settings dialog now looks like Figure 4-8. Default directory name depends upon the location of MATLAB on your machine. You can choose an alternative directory if you do not want to accept the default. Figure 4-8: New Project Settings with Entries • Click OK to create the xlmulti project. Summary of Project Settings Component name: xlmulti Class name: xlmulti Project version: 1.
Using Multiple Files and Variable Arguments Building the Project • From the Excel Builder graphical user interface click Add File … . • Select the file myplot.m from the directory \work\xlmulti and click Open. • Repeat the above steps to add the files myprimes.m and mysum.m. • Click Build or select Excel/COM Files from the Build menu. Adding the Excel Builder COM Functions to Excel • Start Excel on your system. • Open the file \work\xlmulti\mymulti.xls.
4 Usage Examples Figure 4-9: mymulti.
Using Multiple Files and Variable Arguments Illustration 4: Calling myplot This illustration calls the function myplot with a value of 4. To execute the function, make A7 the active cell. Press F2 and then Enter. Figure 4-10: Calling myplot with a Value of 4 This procedure plots a line from 1 to 4 in a MATLAB figure window. This graphic can be manipulated as if it were called from MATLAB directly. The calling cell contains 0 because the function does not return a value.
4 Usage Examples Figure 4-11: myplot Output Illustration 5: Calling mysum Four Different Ways This illustration calls the function mysum in four different ways. The first (cell A14) takes the values 1 through 10, adds them, and returns the result of 55. The second (cell A19) takes a range object that is a range of cells with the values 1 through 10, adds them, and returns the result of 55. The third (cell A24) takes several range objects, adds them, and returns the result of 120.
Using Multiple Files and Variable Arguments Figure 4-12: Four Different Calls to mysum This illustration runs when the Excel file is opened. To reactivate the illustration, make the appropriate cell active. Then press F2 followed by Enter. Illustration 6: myprimes Macro In this illustration the macro myprimes calls the function myprimes.m with an initial value of 10 in cell A42. The function returns all the prime numbers less than 10 to cells B42 through E42.
4 Usage Examples Figure 4-14: myprimes Output for Value of 10 This function automatically resizes if the returned output is larger than the output range specified. Change the value in cell A42 to a number larger than 10. Then rerun the macro. The output returns all prime numbers less than the number you entered in cell A42. Figure 4-15: myprimes Output for Value > 10 Inspecting the Visual Basic Code From Excel select Visual Basic Editor from the Tools -> Macro menu.
Using Multiple Files and Variable Arguments Figure 4-16: Visual Basic Code for mymulti.
4 Usage Examples Spectral Analysis Example This example illustrates the creation of a comprehensive Excel add-in to perform spectral analysis. It requires knowledge of Visual Basic forms and controls, as well as Excel workbook events. See the VBA documentation for a complete discussion of these topics. The example creates an Excel add-in that performs an FFT on an input data set located in a designated worksheet range.
Spectral Analysis Example powerspect = []; return; end if (interval <= 0) error('Sampling interval must be greater then zero'); return; end fftdata = fft(data); freq = (0:length(fftdata)-1)/(length(fftdata)*interval); powerspect = abs(fftdata)/(sqrt(length(fftdata))); plotfft.
4 Usage Examples See “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 project. 4 Save the project. Make note of the project directory because you will refer to it later when you save your add-in. 5 Click Build to create the component. Integrating the Component with Visual Basic for Applications Having built your component, you can implement the necessary VBA code to integrate it into Excel.
Spectral Analysis Example Figure 4-17: VBA Project: Insert->Module 3 Enter the following code in the FourierMain module: ' ' FourierMain - Main module stores global state of controls ' and provides initialization code ' Public theFourier As Fourier.
4 Usage Examples Private Sub LoadFourier() 'Initializes globals and Loads the Spectral Analysis form Dim MainForm As frmFourier On Error GoTo Handle_Error Call InitApp Set MainForm = New frmFourier Call MainForm.Show Exit Sub Handle_Error: MsgBox (Err.Description) End Sub Private Sub InitApp() 'Initializes classes and libraries. Executes once 'for a given session of Excel If bInitialized Then Exit Sub On Error GoTo Handle_Error If theUtil Is Nothing Then Set theUtil = New MWUtil Call theUtil.
Spectral Analysis Example Figure 4-18: Creating the Visual Basic Form 3 Now add a series of controls to the blank form to complete the dialog, as summarized in the following table. Control Type Control Name Properties Purpose Frame Frame1 Caption = Input Data Groups all input controls. Label Label1 Caption = Input Data: Labels the RefEdit for input data.
4 Usage Examples Control Type Control Name Properties RefEdit refedtInput Label Label2 Caption = Sampling Interval Labels the TextBox for sampling interval. CheckBox chkPlot Caption = Plot time domain Plots input data and power spectral density. Selects range for input data. Signal and Power Spectral Density Frame Frame2 Caption = Output Data Groups all output controls. Label Label3 Caption = Frequency: Labels the RefEdit for frequency output.
Spectral Analysis Example Control Type Control Name Properties Purpose CommandButton btnOK Caption = OK Executes the function and dismisses the dialog Default = True CommandButton btnCancel Caption = Cancel Cancel = True Dismisses the dialog without executing the function. Figure 4-19, Layout of Controls on Main Form, on page 4-26 shows the controls layout on the form.
4 Usage Examples frmFourier Label1 Frame1 refedtInput Label2 edtSample chkPlot Label3 Frame2 refedFreq Label4 refedtReal Label5 refedtImag Label6 refedtPowSpect btnOK btnCancel Figure 4-19: Layout of Controls on Main Form When the form and controls are complete, right-click on the form and select View Code from the pop-up menu. The following code listing shows the code to implement. Note that this code references the control and variable names listed above.
Spectral Analysis Example ' 'frmFourier Event handlers ' Private Sub UserForm_Activate() 'UserForm Activate event handler. This function gets called before 'showing the form, and initializes all controls with values stored 'in global variables. On Error GoTo Handle_Error If theFourier Is Nothing Or theFFTData Is Nothing Then Exit Sub 'Initialize controls with current state If Not InputData Is Nothing Then refedtInput.Text = InputData.Address End If edtSample.
4 Usage Examples 'Process inputs Set R = Range(refedtInput.Text) If Err <> 0 Then MsgBox ("Invalid range entered for Input Data") Exit Sub End If Set InputData = R Interval = CDbl(edtSample.Text) If Err <> 0 Or Interval <= 0 Then MsgBox ("Sampling interval must be greater than zero") Exit Sub End If 'Process Outputs Set R = Range(refedtFreq.Text) If Err = 0 Then Set Frequency = R End If Set R = Range(refedtReal.Text) If Err = 0 Then theFFTData.Real = R End If Set R = Range(refedtImag.
Spectral Analysis Example Adding The Spectral Analysis Menu Item to Excel The last step in the integration process adds a menu item to Excel so that you can invoke the tool from Excel’s Tools menu. To do this you add event handlers for the workbook’s AddinInstall and AddinUninstall events that install and uninstall menu items. The menu item calls the LoadFourier function in the FourierMain module.
4 Usage Examples Private Sub Workbook_AddinInstall() 'Called when Addin is installed Call AddFourierMenuItem End Sub Private Sub Workbook_AddinUninstall() 'Called when Addin is uninstalled Call RemoveFourierMenuItem End Sub Private Sub AddFourierMenuItem() Dim ToolsMenu As CommandBarPopup Dim NewMenuItem As CommandBarButton 'Remove if already exists Call RemoveFourierMenuItem 'Find Tools menu Set ToolsMenu = Application.CommandBars(1).
Spectral Analysis Example 2 When the Workbook Properties dialog appears, select the Summary tab and enter Spectral Analysis as the workbook title. 3 Click OK to save the edits. 4 Select File->Save As from the Excel main menu. 5 When the Save As dialog appears, select Microsoft Excel Add-In (*.xla) as the file type, and browse to \distrib. 6 Enter Fourier.xla as the file name and click Save to save the add-in.
4 Usage Examples 15 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 points into column B. Creating the Data Follow these steps to create the data. 1 Enter 0 for cell A1 in the current worksheet. 2 Click on cell A2 and type the formula "= A1 + 0.01". 3 Click and hold on the lower right hand corner of cell A2 and drag the formula down the column to cell A1001.
Spectral Analysis Example Figure 4-21: Worksheet with Inputs and Outputs for Test Problem The power spectral density reveals the two signals at 15 and 40 Hz.
4 Usage Examples Package the Add-in As a final step, package the add-in, the COM component, and all supporting libraries into a self-extracting executable. This package can now be installed onto other computers that need to use the Spectral Analysis add-in. To package the add-in, follow these steps. 1 Return to mxltool. If mxltool has been dismissed, start it again and reload the Fourier project. 2 Select Component->Package Component. This command creates the Fourier.exe self-extracting executable.
5 Function Wizard Introduction . . . . . . . . . . Installing the Function Wizard Add-in Starting the Function Wizard . . . Function Viewer . . . . . . . . . Component Browser . . . . . . . Function Properties . . . . . . . Argument Properties . . . . . . . Function Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5 Function Wizard Introduction The Function Wizard enables you to pass Microsoft Excel (Excel 200 or later) worksheet values to a compiled MATLAB model and to return model output to a cell or range of cells in the worksheet. The Function Wizard provides an intuitive interface to Excel worksheets. Knowledge of Visual Basic for Applications (VBA) programming is not required. The Function Wizard reflects any changes that you make in the worksheets, such as range selections.
Introduction To start the Function Wizard, click on Tools -> MATLAB Functions on the Excel menu bar. The starting point of the Function Wizard, called the Function Viewer, now displays. Function Viewer Check to activate function. Clear to deactivate. Deactivated functions not executed when Execute button clicked. Duplicate and optionally move function Add new function Delete selected function. Rename selected function. Edit current function. Select worksheet ranges for inputs and outputs.
5 Function Wizard The Function Viewer controls the execution of worksheet functions. Use the Function Viewer to organize the list of all currently loaded MATLAB Excel Builder functions. Using the Function Viewer The Function Viewer displays the names of all loaded functions. You can edit this name to provide a more descriptive identifier. A check box for each entry denotes the active/inactive state of each function. Inactive functions are not executed when you click the Execute button.
Introduction Component Browser The Component Browser lists all MATLAB Excel Builder components currently installed on the system. When you click the New button on the Function Viewer, this dialog box displays. Add selected functions to list. Remove selected functions from list. Functions to be loaded Load selected functions and return to Function Viewer. Return to Function Viewer without loading selected functions. Invoke help. The Component Browser lists each component by name and version.
5 Function Wizard Function Properties This group of dialog boxes sets properties and values for the inputs and outputs. You can map inputs and outputs to ranges in your worksheet. You can also rename a function with any of these dialog boxes. When you click the Edit button on the Function Viewer, the dialog box below displays. Editing Required Inputs MATLAB calling convention Click to edit outputs. Type new function name to rename function. Enable editing of input arguments.
Introduction Editing Function Arguments. Function arguments may be either required arguments or varargin/varargout arguments: • Required arguments appear first on the left or right sides of a MATLAB function and are not named varargin or varargout. • varargin/varargout arguments always appear as the last input or output. They allow you to specify a variable number of arguments. To edit required arguments, select the argument in the list and click the Properties button.
5 Function Wizard Editing varargin Inputs Click to edit outputs. Set argument properties. Invoke help. Add new varargin argument to list. 5-8 Delete selected varargin argument. Return to Function Viewer.
Introduction Editing Required Outputs Click to edit inputs. Click to edit varargout outputs, if present. Select arguments from list for editing. Set output argument properties. Invoke help. Return to Function Viewer. • The Add and Delete buttons become active when you select varargout Arguments. • Select the Inputs tab to switch to editing inputs.
5 Function Wizard Editing varargout Outputs Click to edit inputs. Click to edit required output arguments. Set output argument properties. Invoke help. Add new varargout argument to list. 5-10 Delete selected varargout argument. Return to Function Viewer.
Introduction Argument Properties The Argument Properties and related dialog boxes allow you to select worksheet ranges or optionally enter a specific value for an input argument. Input Argument Range Set worksheet range for current argument. Recalculate current function when any cell in the current argument changes. Click here to set single value for current argument. Save and return to Function Viewer.
5 Function Wizard Input Argument Value Enter value for current argument. Save and return to Function Viewer. Output Argument Properties Select worksheet range. Coerce output values to Excel dates. Invoke help. Automatically resize output range to fit data. 5-12 Transpose Return to output array. Function Viewer.
Introduction Function Utilities Rename Function Use this dialog box to rename a function. When you click the Rename button on the Function Viewer, this dialog box displays. Enter new name for selected function. Save new name and return to Function Viewer. Return to Function Viewer without changing name.
5 Function Wizard Copy Function Use the Copy Function dialog box to make copies of the current function. The Standard Copy tab creates a specified number of copies of the function while copying any argument/range values you have set. The Advanced tab creates a rectangular array of copies of the current function in the current worksheet, and optionally copies the cell contents of ranges referenced by the function’s arguments.
Introduction Move Use the Move Function dialog box to move the currently selected function to a new position in the current worksheet. When you set the row and column increments, the move process automatically updates cell references by these values. You can also optionally move the cell contents of any ranges referenced by the function. Move contents of Move function and return to all worksheet cells referenced Function Viewer. by any argument of the current function.
5 Function Wizard 5-16
6 Function Reference This section provides detailed descriptions of the MATLAB Excel Builder functions.
componentinfo Purpose 6componentinfo Query system registry Syntax Info = componentinfo(ComponentName, MajorRevision, MinorRevision) Arguments ComponentName (Optional) A MATLAB string providing the name of a MATLAB Excel Builder component. Names are case sensitive. If this argument is not supplied, the function returns information on all installed components. MajorRevision (Optional) Component major revision number.
componentinfo With a component name and major revision supplied, the function returns information for revision 1.0 of mycomponent. Example 2. Info = componentinfo('mycomponent') With a component name but no major revision supplied, the function returns information for all revisions of mycomponent. Example 3. Info = componentinfo Without any arguments supplied, the function returns information for all installed components.
mxltool Purpose 6mxltool Graphical user interface to MATLAB Excel Builder Syntax mxltool Description mxltool displays the graphical user interface (GUI) for MATLAB Excel Builder.
A Producing a COM Object from MATLAB Capabilities . . . . . . . . . . . . . . . . . . . . A-2 Calling Conventions . . Producing a COM Class . . IDL Mapping . . . . . . Visual Basic Mapping . . . MATLAB Compiler Output . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-7 . A-8 . A-8 . A-9 .
A Producing a COM Object from MATLAB Capabilities MATLAB Excel Builder enables you to pass Microsoft Excel worksheet values to a compiled MATLAB model via Visual Basic for Applications (VBA) and to return model output to a cell or range of cells in the worksheet. Each MATLAB Excel Builder component is built as a stand-alone COM object. (COM is an acronym for Component Object Model, Microsoft’s binary standard for object interoperability.
Capabilities M-files mycomponent_1_0.dll IUnknown foo.m bar.m MATLAB Compiler Imyclass IDispatch myclass object User-supplied Information: Class name: myclass Component name: mycomponent Version number: 1.0 ISupportErrorInfo Figure A-1: Creating a Stand-Alone COM Object with the MATLAB Compiler The process of creating a MATLAB Excel Builder component is completely automatic. The user supplies a list of M-files to process and a some additional information, i.e.
A Producing a COM Object from MATLAB MATLAB M-files foo.m, bar.m MATLAB Compiler mcc -B cexel:mycomponent,myclass,1.0 foo.m bar.m Step 1. Code Generation mycomponent_com.hpp, mycomponent_com.cpp foo.h, foo.c bar.h, bar.c mycomponent_dll.cpp mycomponent.bas mycomponent.def Step 2. Create Interface Definitions Step 3. C++ Compilation mycomponent_idl.idl mycomponent.rc IDL Compiler mycomponent_idl.h, mycomponent_idl_i.c C++ Compiler mycomponent_idl.tlb mclcomclass.h Object Files Step 4.
Capabilities Step 1. Code Generation The first step in the build process generates all source code and other supporting files needed to create the component. The compiler first produces .c and .h files (foo.h, foo.c, bar.h, and bar.c), representing the C-language translation of the M-code in the original M-files (foo.m and bar.m). It also creates the main source file (mycomponent_dll.cpp) containing the implementation of each exported function of the DLL.
A Producing a COM Object from MATLAB Step 4. Linking and Resource Binding The fourth step produces the finished DLL for the component. This step invokes the linker on the object files generated in step 3 and the necessary MATLAB libraries to produce a DLL component (mycomponent_1_0.dll). The resource compiler is then invoked on the DLL, along with the resource script generated in step 1, to bind the type library file generated in step 2 into the completed DLL. Step 5.
Calling Conventions Calling Conventions This section describes the calling conventions for MATLAB Excel Builder components, including mappings from the original M-functions to Visual Basic. A function call originating from an Excel worksheet is routed from a Visual Basic function into a compiled M-function, as shown in Figure A-3. Excel Application sub foo(y1,y2,x1,x2) . Visual Basic function/subroutine COM Class.
A Producing a COM Object from MATLAB Producing a COM Class Producing a COM class requires the generation of a class definition file in Interface Description Language (IDL) as well as the associated C++ class definition/implementation files. (See the Microsoft COM documentation for a complete discussion of IDL and C++ coding rules for building COM objects.) The builder automatically produces the necessary IDL and C/C++ code to build each COM class in the component.
Calling Conventions nargout parameter, the outputs are listed in the order they appear on the left side of the MATLAB function, and are tagged as [in,out], meaning that they are passed in both directions. The function inputs are listed next, appearing in the same order as they do on the right side of the original function. All inputs are tagged as [in] parameters. When present, the optional varargin/varargout parameters are always listed as the last input parameters and the last output parameters.
A Producing a COM Object from MATLAB MATLAB Compiler Output The MATLAB Excel Builder generates a default Visual Basic function wrapper for each class method with the following format: Function foo(Optional X1 As Variant, _ Optional X2 As Variant, _ . . Optional varargin1 As Variant, _ Optional varargin2 As Variant, _ . . Optional vararginN As Variant) _ As Variant Dim Y1, Y2, ..., varargout As Variant Dim varargin As Variant . (other declarations) . . (function body) . . foo = Y1 . .
Calling Conventions Variant to pass collectively the varargin1,...,vararginn parameters in the form of a Variant array.
A Producing a COM Object from MATLAB A-12
B Data Conversion Data Conversion Rules . . . . . . . . . . . . . . . B-2 Array Formatting Flags . . . . . . . . . . . . . . . . B-12 Data Conversion Flags . . . . . . . . . . . . . . . .
B Data Conversion Data Conversion Rules This section describes the data conversion rules for MATLAB Excel Builder components. Excel Builder components are dual interface COM objects that support COM Automation compatible data types. When a method is invoked on a Excel Builder component, the input parameters are converted to MATLAB internal array format and passed to the compiled MATLAB function.
Data Conversion Rules Table B-1: VARIANT Type Codes Supported (Continued) VARIANT Type Code (C/C++) C/C++ Type Variant Type Code (Visual Basic) Visual Basic Type Definition VT_UI2 unsigned short - - Unsigned two-byte integer VT_I4 long vbLong Long Signed four-byte integer VT_UI4 unsigned long - - Unsigned four-byte integer VT_R4 float vbSingle Single IEEE four-byte floating-point value VT_R8 double vbDouble Double IEEE eight-byte floating-point value VT_CY CY+ vbCurrency Cu
B Data Conversion Table B-1: VARIANT Type Codes Supported (Continued) VARIANT Type Code (C/C++) C/C++ Type Variant Type Code (Visual Basic) Visual Basic Type Definition VT_DECIMAL DECIMAL+ vbDecimal - 96-bit (12-byte) unsigned integer, scaled by a variable power of 10 VT_BOOL VARIANT_BOOL+ vbBoolean Boolean Two-byte Boolean value (0xFFFF = True; 0x0000 = False) VT_DISPATCH IDispatch* vbObject Object IDispatch* pointer to an object VT_VARIANT + VARIANT vbVariant Variant VARIANT (ca
Data Conversion Rules Table B-2: MATLAB to COM VARIANT Conversion Rules MATLAB Data Type VARIANT type for Scalar Data VARIANT type for Array Data cell A 1-by-1 cell array converts to a single VARIANT with a type conforming to the conversion rule for the MATLAB data type of the cell contents.
B Data Conversion Table B-2: MATLAB to COM VARIANT Conversion Rules (Continued) B-6 MATLAB Data Type VARIANT type for Scalar Data VARIANT type for Array Data Comments char A 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 a string of length Lin MATLAB. This case converts to a VARIANT of type VT_BSTR with a string length = L.
Data Conversion Rules Table B-2: MATLAB to COM VARIANT Conversion Rules (Continued) MATLAB Data Type VARIANT type for Scalar Data VARIANT type for Array Data Comments double A 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 of type VT_DISPATCH. A real multidimensional double matrix converts to a VARIANT of type VT_R8|VT_ARRAY. A complex multidimensional double matrix converts to a VARIANT of type VT_DISPATCH.
B Data Conversion Table B-2: MATLAB to COM VARIANT Conversion Rules (Continued) B-8 MATLAB Data Type VARIANT type for Scalar Data VARIANT type for Array Data Comments uint8 A 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 type VT_DISPATCH. A real multidimensional uint8 matrix converts to a VARIANT of type VT_UI1|VT_ARRAY.A complex multidimensional uint8 matrix converts to a VARIANT of type VT_DISPATCH.
Data Conversion Rules Table B-2: MATLAB to COM VARIANT Conversion Rules (Continued) MATLAB Data Type VARIANT type for Scalar Data VARIANT type for Array Data Comments int32 A 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 VT_DISPATCH. A multidimensional int32 matrix converts to a VARIANT of type VT_I4|VT_ARRAY. A complex multidimensional int32 matrix converts to a VARIANT of type VT_DISPATCH.
B Data Conversion Table B-3: COM VARIANT to MATLAB Conversion Rules B-10 VARIANT Type MATLAB Data Type (scalar or array data) Comments VT_EMPTY N/A Empty array created. VT_I1 int8 VT_UI1 uint8 VT_I2 int16 VT_UI2 uint16 VT_I4 int32 VT_UI4 uint32 VT_R4 single VT_R8 double VT_CY double VT_BSTR char VT_ERROR int32 A VARIANT of type VT_BSTR converts to a 1-by-L MATLAB char array, where L = the length of the string to be converted.
Data Conversion Rules Table B-3: COM VARIANT to MATLAB Conversion Rules (Continued) VARIANT Type MATLAB Data Type (scalar or array data) Comments VT_DATE double 1. VARIANT dates are stored as doubles starting at midnight Dec. 31, 1899. MATLAB dates are stored as doubles starting at 0/0/00 00:00:00. Therefore, a VARIANT date of 0.0 maps to a MATLAB numeric date of 693960.0. VARIANT dates are converted to MATLAB double types and incremented by 693960.0. 2.
B Data Conversion Table B-3: COM VARIANT to MATLAB Conversion Rules (Continued) VARIANT Type MATLAB Data Type (scalar or array data) Comments |VT_BYREF (varies) Pointers to any of the basic types are processed according to the rules for what they point to. The resulting MATLAB array contains a deep copy of the values. |VT_ARRAY (varies) Multidimensional VARIANT arrays convert to multidimensional MATLAB arrays, each element converted according to the rules for the basic types.
Data Conversion Rules Table B-4 shows the array formatting flags. Table B-4: Array Formatting Flags Flag Description InputArrayFormat Defines the array formatting rule used on input arrays. An input array is a VARIANT array, created by the client, sent as an input parameter to a method call on a compiled COM object. Valid values for this flag are mwArrayFormatAsIs, mwArrayFormatMatrix, and mwArrayFormatCell. mwArrayFormatAsIs passes the array unchanged.
B Data Conversion Table B-4: Array Formatting Flags (Continued) Flag Description AutoResizeOutput (Applies to Excel ranges only.) When the target output from a method call is a range of cells in an Excel worksheet and the output array size and shape is not known at the time of the call, set this flag to True to resize each Excel range to fit the output array. TransposeOutput Set this flag to True to transpose the output arguments.
Data Conversion Rules bias (693960) as well as coerced to COM dates. Set this flag to True to convert all output values of type Double. DateBias As Long This flag sets the date bias for performing COM to MATLAB numeric date conversions. The default value of this property is 693960, which represents the difference between the COM Date type and MATLAB numeric dates.
B Data Conversion B-16
C Registration and Versioning Component Registration . . . . . . . . . . . . . . C-2 Self-Registering Components . . . . . . . . . . . . . . C-2 Globally Unique Identifiers . . .describes . . . .the . registration . . . . . and . C-2 This section versioning of MATLAB Excel Builder components and how to retrieve information about any installed Versioning . . . . component . . . . . from . . the . . system . . . registry. . . . . . C-4 Obtaining Registry Information . . . . . . . . . . .
C Registration and Versioning Component Registration When the MATLAB Excel Builder creates a component, it automatically generates a binary file called a type library. As a final step of the build, this file is bound with the resulting DLL as a resource. Self-Registering Components MATLAB Excel Builder components are all self-registering. A self-registering component contains all the necessary code to add or remove a full description of itself to or from the system registry.
Component Registration Table C-1: Keys Key Definition HKEY_CLASSES_ROOT\CLSID Information about COM classes on the system. Each component creates a new key under HKEY_CLASSES_ROOT\CLSID for each of its COM classes. The key created has a value of the GUID that has been assigned the class and contains several subkeys with information about the class. HKEY_CLASSES_ROOT\Interface Information about COM interfaces on the system.
C Registration and Versioning Versioning MATLAB Excel Builder components support a simple versioning mechanism designed to make building and deploying multiple versions of the same component easy to implement. The version number of a component appears as part of the DLL name, as well as part of the version-dependent ID in the system registry. When a component is created, you can specify a version number (default = 1.0).
Obtaining Registry Information Obtaining Registry Information MATLAB Excel Builder includes the MATLAB function componentinfo to query the system registry for any installed Excel Builder components. The function can be executed inside MATLAB with the component name, major version number, and minor version number as arguments. It returns an array of structures with the requested information. Calling componentinfo with no arguments returns all Excel Builder components installed on the machine.
C Registration and Versioning Info.CoClasses ans = Name: 'myclass' CLSID: '{3A14AB35-44BE-11D5-B155-00D0B7BA7544}' ProgID: 'mycomponent.myclass.1_0' VerIndProgID: 'mycomponent.myclass' InprocServer32:'D:\Work\mycomponent\distrib\mycomponent_1_0.dll' Methods: [1x4 struct] Properties: {'m', 'n'} Events: [1x1 struct] Info.CoClasses.Events.M ans = function myevent(x, y) Info.CoClasses.Methods ans = 1x4 struct array with fields: IDL M C VB Info.CoClasses.Methods.
Obtaining Registry Information ans = function [varargout] = randvectors() ans = function [x] = getdates(n, inc) ans = function [p] = myprimes(n) The returned structure contains fields corresponding to the most important information from the registry and type library for the component. These fields are defined in Table C-2.
C Registration and Versioning Table C-2: Registry Information Returned by componentinfo (Continued) Field Description Interfaces An array of structures defining all interface definitions in the type library.
Obtaining Registry Information Table C-2: Registry Information Returned by componentinfo (Continued) Field Description CoClasses An array of structures defining all COM classes in the component.
C Registration and Versioning C-10
D Utility Library Utility Library Classes Class MWUtil . . . . . Class MWFlags . . . . Class MWStruct . . . . Class MWField . . . . Class MWComplex . . . Class MWSparse . . . . Class MWArg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . D-3 . D-3 . D-9 .D-16 .D-22 .D-23 .D-25 .D-28 Enumerations . . .
D Utility Library This section describes the MWComUtil library provided with the MATLAB Excel Builder. This library is freely distributable and includes several functions used in array processing, as well as type definitions used in data conversion. This library is contained in the file mwcomutil.dll. It must be registered once on each machine that uses Excel Builder components. Register the MWComUtil library at the DOS command prompt with the command mwregsvr mwcomutil.
Utility Library Classes Utility Library Classes The Excel Builder Utility Library provides several classes: • “Class MWUtil” on page D-3 • “Class MWFlags” on page D-9 • “Class MWStruct” on page D-16 • “Class MWField” on page D-22 • “Class MWComplex” on page D-23 • “Class MWSparse” on page D-25 • “Class MWArg” on page D-28 Class MWUtil The MWUtil class contains a set of static utility methods used in array processing and application initialization.
D Utility Library Return Value. None. Remarks. This function must be called once for each session of Excel that uses Excel Builder components. An error is generated if a method call is made to a member class of any Excel Builder component, and the library has not been initialized. Example. This Visual Basic sample initializes the MWComUtil library with the current instance of Excel.
Utility Library Classes Parameters. Argument Type Description pVarArg Variant Receives the resulting array [Var0], [Var1], Variant Optional list of Variants to pack into the array. From 0 to 32 arguments can be passed. Return Value. None. Remarks. This function always frees the contents of pVarArg before processing the list. Example.
D Utility Library Dim aClass As Object Dim aUtil As Object On Error Goto Handle_Error Set aClass = CreateObject("mycomponent.myclass.1_0") Set aUtil = CreateObject("MWComUtil.MWUtil") Call aUtil.MWPack(varargin,V0,V1,V2,V3,V4,V5,V6,V7,V8,V9) Call aClass.mysum(1, y, varargin) mysum = y Exit Function Handle_Error: mysum = Err.Description End Function Sub MWUnpack(VarArg, [nStartAt As Long], [bAutoResize As Boolean = False], [pVar0], [pVar1], ...
Utility Library Classes Argument Type Description bAutoResize Boolean Optional auto-resize flag. If this flag is True, any Excel range output arguments are resized to fit the dimensions of the Variant to be copied. The resizing process is applied relative to the upper left corner of the supplied range. Default = False. [pVar0],[pVar1], ... Variant Optional list of Variants to receive the array items contained in VarArg. From 0 to 32 arguments can be passed. Return Value. None. Remarks.
D Utility Library Dim Dim Dim Dim Dim Dim aUtil As Object v As Variant R1 As Range R2 As Range R3 As Range R4 As Range On Error GoTo Handle_Error Set aClass = CreateObject("mycomponent.myclass.1_0") Set aUtil = CreateObject("MWComUtil.MWUtil") Set R1 = Range("A1") Set R2 = Range("B1") Set R3 = Range("C1") Set R4 = Range("D1") Call aClass.randvectors(4, v) Call aUtil.MWUnpack(v,0,True,R1,R2,R3,R4) Exit Sub Handle_Error: MsgBox (Err.
Utility Library Classes method performs this transformation and additionally converts dates in string form to COM date types. Example. This example uses MWDate2VariantDate to process numeric dates returned from a method compiled from the following MATLAB function. function x = getdates(n, inc) y = now; for i=1:n x(i,1) = y + (i-1)*inc; end This function produces an n-length column vector of numeric values representing dates starting from the current date and time with each element incremented by inc days.
D Utility Library components contain a reference to an MWFlags object that can modify data conversion rules at the object level.
Utility Library Classes Table D-1: Array Formatting Rules for Input Arrays Value Behavior mwArrayFormatAsIs Converts arrays according to the default conversion rules listed in Table B-3, COM VARIANT to MATLAB Conversion Rules, on page B-10. mwArrayFormatMatrix Coerces all arrays into matrices.
D Utility Library Table D-2: Array Formatting Rules for Output Arrays Value Behavior mwArrayFormatAsIs Converts arrays according to the default conversion rules listed in Table B-2, MATLAB to COM VARIANT Conversion Rules, on page B-5. mwArrayFormatMatrix Coerces all arrays into matrices.
Utility Library Classes function returns outputs as row vectors, and you desire to place the data into columns. The default value for this flag is False. Property DataConversionFlags As MWDataConversionFlags The DataConversionFlags property controls how input variables are processed when type coercion is needed. The MWDataConversionFlags class is a noncreatable class accessed through an MWFlags class instance.
D Utility Library Example. This example uses data conversion flags to reshape the output from a method compiled from a MATLAB function that produces an output vector of unknown length. function p = myprimes(n) if length(n)~=1, error('N must be a scalar'); end if n < 2, p = zeros(1,0); return, end p = 1:2:n; q = length(p); p(1) = 2; for k = 3:2:sqrt(n) if p((k+1)/2) p(((k*k+1)/2):k:q) = 0; end end p = (p(p>0)); This function produces a row vector of all the prime numbers between 0 and n.
Utility Library Classes Sub GenPrimes(R As Range, n As Double) Dim aClass As mycomponent.myclass On Error GoTo Handle_Error Set aClass = New mycomponent.myclass aClass.MWFlags.ArrayFormatFlags.AutoResizeOutput = True aClass.MWFlags.ArrayFormatFlags.TransposeOutput = True Call aClass.myprimes(1, R, n) Exit Sub Handle_Error: MsgBox (Err.Description) End Sub PropertyOutputAsDate As Boolean. This property processes an output argument as a date.
D Utility Library Remarks. Clone allocates a new MWFlags object and creates a deep copy of the object’s contents. Call this function when a separate object is required instead of a shared copy of an existing object reference. Class MWStruct The MWStruct class passes or receives a Struct type to or from a compiled class method.
Utility Library Classes Example. The following Visual Basic code illustrates use of the Initialize method to dimension struct arrays. Sub foo () Dim x As MWStruct Dim y As MWStruct On Error Goto Handle_Error 'Create 1X1 struct arrays with no fields for x, and y Set x = new MWStruct Set y = new MWStruct 'Initialize x to be 2X2 with fields "red", "green", and "blue" Call x.Initialize(Array(2,2), Array("red", "green", "blue")) 'Initialize y to be 1X5 with fields "name" and "age" Call y.
D Utility Library Parameters. Argument Type Description i0,i1, …, i31 Variant Optional index arguments. Between 0 and 32 index arguments can be entered. To reference an element of the array, specify all indexes as well as the field name. Remarks. When accessing a named field through this property, you must supply all dimensions of the requested field as well as the field name.
Utility Library Classes • All indices and field name. This format accesses an array element of an multidimensional array by specifying n indices. These statements access all four of the elements of the array in the previous example: For I From 1 To 2 For J From 1 To 2 r(I, J) = x(I, J, "red") g(I, J) = x(I, J, "green") b(I, J) = x(I, J, "blue") Next Next • Array of indices and field name. This format accesses an array element by passing an array of indices and a field name.
D Utility Library Index1(1) Index1(2) Index2(1) Index2(2) x(Index1, = 1 = 1 = 3 = 2 Index2, 2, "red") = 0.5 The last statement resolves to: x(1, 1, 3, 2, 2, "red") = 0.5 • The field name must be the last index in the list. The following statement produces an error: y = x("blue", 1, 2) • Field names are case sensitive. Property NumberOfFields As Long The read-only NumberOfFields property returns the number of fields in the structure array.
Utility Library Classes On Error Goto Handle_Error ' ' Call a method that returns an MWStruct in x ' Dims = x.Dims FieldNames = x.FieldNames For I From 1 To Dims(1) For J From 1 To Dims(2) For K From 1 To x.NumberOfFields y = x(I,J,FieldNames(K)) ' Do something with y Next Next Next Exit Sub Handle_Error: MsgBox(Err.Description) End Sub Sub Clone(ppStruct As MWStruct) Creates a copy of an MWStruct object. Parameters.
D Utility Library Example. The following Visual Basic example illustrates the difference between assignment and Clone for MWStruct objects. Sub foo Dim Dim Dim () x1 As MWStruct x2 As MWStruct x3 As MWStruct On Error Goto Handle_Error Set x1 = new MWStruct x1("name") = "John Smith" x1("age") = 35 'Set reference of x1 to x2 Set x2 = x1 'Create new object for x3 and copy contents of x1 into it Call x1.Clone(x3) 'x2's "age" field is also modified 'x3's "age" field unchanged x1("age") = 50 . . .
Utility Library Classes Property Value As Variant Stores the field’s value (read/write). The Value property is the default property of the MWField class. The value of a field can be any type that is coercible to a Variant, as well as object types. Property MWFlags As MWFlags Stores a reference to an MWFlags object. This property sets or gets the array formatting and data conversion flags for a particular field. Each field in a structure has its own MWFlags property.
D Utility Library Property Real As Variant Stores the real part of a complex array (read/write). The Real property is the default property of the MWComplex class. The value of this property can be any type coercible to a Variant, as well as object types, with the restriction that the underlying array must resolve to a numeric matrix (no cell data allowed). Valid Visual Basic numeric types for complex arrays include Byte, Integer, Long, Single, Double, Currency, and Variant/vbDecimal.
Utility Library Classes MsgBox(Err.Description) End Sub Property MWFlags As MWFlags Stores a reference to an MWFlags object. This property sets or gets the array formatting and data conversion flags for a particular complex array. Each MWComplex object has its own MWFlags property. This property overrides the value of any flags set on the object whose method’s are called. Sub Clone(ppComplex As MWComplex) Creates a copy of an MWComplex object. Parameters.
D Utility Library Property NumRows As Long Stores the row dimension for the array. The value of NumRows must be nonnegative. If the value is zero, the row index is taken from the maximum of the values in the RowIndex array. Property NumColumns As Long Stores the column dimension for the array. The value of NumColumns must be nonnegative. If the value is zero, the row index is taken from the maximum of the values in the ColumnIndex array.
Utility Library Classes MWSparse object has its own MWFlags property. This property overrides the value of the any flags set on the object whose method’s are called. Sub Clone(ppSparse As MWSparse) Creates a copy of an MWSparse object. Parameters. Argument Type Description ppSparse MWSparse Reference to an uninitialized MWSparse object to receive the copy. Return Value. None. Remarks. Clone allocates a new MWSparse object and creates a deep copy of the object’s contents.
D Utility Library cols(K) vals(K) K = K + rows(K) cols(K) vals(K) K = K + rows(K) cols(K) vals(K) K = K + = = 1 = = = 1 = = = 1 I + 1 -1 I I 2 I + 1 I -1 Next rows(K) = 5 cols(K) = 5 vals(K) = 2 Set x = New MWSparse x.NumRows = 5 x.NumColumns = 5 x.RowIndex = rows x.ColumnIndex = cols x.Array = vals . . . Exit Sub Handle_Error: MsgBox (Err.Description) End Sub Class MWArg The MWArg class passes a generic argument into a compiled class method.
Utility Library Classes Property Value As Variant The Value property stores the actual argument to pass. Any type that can be passed to a compiled method is valid for this property. Property MWFlags As MWFlags Stores a reference to an MWFlags object. This property sets or gets the array formatting and data conversion flags for a particular argument. Each MWArg object has its own MWFlags property. This property overrides the value of the any flags set on the object whose method’s are called.
D Utility Library Enumerations The MATLAB Excel Builder Utility Library provides three enumerations (sets of constants): • “Enum mwArrayFormat” on page D-30 • “Enum mwDataType” on page D-30 • “Enum mwDateFormat” on page D-31 Enum mwArrayFormat The mwArrayFormat enumeration is a set of constants that denote an array formatting rule for data conversion. Table D-4 lists the members of this enumeration.
Enumerations Table D-5: mwDataType Values (Continued) Constant Numeric Value MATLAB Type mwTypeChar 4 char mwTypeDouble 6 double mwTypeSingle 7 single mwTypeInt8 8 int8 mwTypeUint8 9 uint8 mwTypeInt16 10 int16 mwTypeUint16 11 uint16 mwTypeInt32 12 int32 mwTypeUint32 13 uint32 Enum mwDateFormat The mwDateFormat enumeration is a set of constants that denote a formatting rule for dates. Table D-6 lists the members of this enumeration.
D Utility Library D-32
E Troubleshooting
E Troubleshooting This section provides a table showing errors you may encounter using MATLAB Excel Builder, probable causes for these errors, and suggested solutions. Table E-1: MATLAB Excel Builder Errors and Suggested Solutions Message Probable Cause Suggested Solution MBUILD.BAT: Error: The chosen compiler does not support building COM objects. The chosen compiler does not support building COM objects. Rerun mbuild -setup and choose a supported compiler. Error in component_name.class_name.
Table E-1: MATLAB Excel Builder Errors and Suggested Solutions (Continued) Message Probable Cause Suggested Solution LoadLibrary("component_name_1_ 0.dll") failed - The specified module could not be found. You may get this error message while registering the project DLL from the DOS prompt. This usually occurs if MATLAB is not on the system path. Place \bin\win32 on your path. Cannot recompile the M file xxxx because it is already in the library libmmfile.mlib.
E Troubleshooting Table E-3: Function Wizard Problems E-4 Problem Probable Cause Suggested Solution The Function Wizard Help does not display. The Function Wizard Help file (mlfunction.chm) is not in the same directory as the Function Wizard add-in (mlfunction.xla). Copy the Help file (mlfunction.chm) into the same directory as the add-in.
Index A array formatting flags 3-14 C capabilities Excel Builder viii class 1-2 class method calling 3-6 Class MWFlags D-9 Class MWUtil D-3 class name 1-2 COM defined 1-2 COM class producing A-8 COM VARIANT B-2 Compiler Output A-10 compilers x component information 2-7 component name 1-5 Component Object Model 1-2 componentinfo 5-2 CreateObject function 3-6 D data conversion flags 3-14 data conversion rules B-2 E Enumeration mwArrayFormat D-30 mwDataType D-30 mwDateFormat D-31 enumerations D-30 error p
Index I S IDL Mapping A-8 input command xi self-registering component C-2 subroutines 3-3 system requirements x L limitations xi T M troubleshooting E-2 type library C-2 typographical conventions (table) xiii mbuild x mccsavepath x methods 1-2 missing parameter D-4 MWFlags class D-9 mwregsvr utility C-2 MWUtil class D-3 mxltool 5-4 purpose 2-2 N New operator 3-6 P project 1-2 creating 1-3 settings 2-6 project version 1-5 R required arguments 5-7 requirements system x restrictions xi I-2 U unr
