® ® MATLAB Builder for Excel The Language of Technical Computing Computation Visualization Programming User’s Guide Version 1
How to Contact The MathWorks Web Newsgroup www.mathworks.com/contact_TS.html Technical Support www.mathworks.com comp.soft-sys.matlab suggest@mathworks.com bugs@mathworks.com doc@mathworks.com service@mathworks.com info@mathworks.com 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.
Revision History December 2001 July 2002 June 2004 Online only First printing Online only August 2004 October 2004 September 2005 March 2006 September 2006 Online Online Online Online Online only only only only only New for Version 1.0 Revised for Version 1.1 (Release 13) Revised for Version 1.2 (Release 14) Name changed from MATLAB Excel Builder Revised for Version 1.2.1 (Release 14+) Revised for Version 1.2.2 (Release 14SP1) Revised for Version 1.2.5 (Release 14SP3) Revised for Version 1.2.
Contents Getting Started 1 What Is MATLAB Builder for Excel? . . . . . . . . . . . . . . . . . Component Naming Conventions . . . . . . . . . . . . . . . . . . . . . 1-2 1-2 Building a Deployable Application . . . . . . . . . . . . . . . . . . Creating and Building a Component . . . . . . . . . . . . . . . . . . Using the mcc Command to Build a Component . . . . . . . . . Testing the Component . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Deploying the Component . . . . . . . . . . . . . . .
Handling Errors During a Method Call . . . . . . . . . . . . . . 2-13 Modifying Flags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Array Formatting Flags . . . . . . . . . . . . . . . . . . . . . . . . . . . . Data Conversion Flags . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-14 2-14 2-16 Usage Examples 3 Magic Square Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating the Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Installing the Function Wizard Add-In . . . . . . . . . . . . . . . 4-3 Starting the Function Wizard . . . . . . . . . . . . . . . . . . . . . . . 4-4 Understanding the Function Viewer . . . . . . . . . . . . . . . . Using the Function Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . Loading and Executing Functions . . . . . . . . . . . . . . . . . . . . 4-5 4-5 4-5 Component Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-7 Function Properties . . . . . . . . . . .
Component Registration . . . . . . . . . . . . . . . . . . . . . . . . . . . Obtaining Registry Information . . . . . . . . . . . . . . . . . . . . . . Self-Registering Components . . . . . . . . . . . . . . . . . . . . . . . . Globally Unique Identifier (GUID) . . . . . . . . . . . . . . . . . . . Versioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A-5 A-5 A-7 A-8 A-9 Calling Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Class MWSparse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Class MWArg . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C-27 C-30 Enumerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Enum mwArrayFormat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Enum mwDataType . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Enum mwDateFormat . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
x Contents
1 Getting Started What Is MATLAB Builder for Excel? (p. 1-2) Brief description of the product Building a Deployable Application (p.
1 Getting Started What Is MATLAB Builder for Excel? MATLAB® Builder for Excel® (also called Excel Builder) is an extension to the MATLAB Compiler. You use Excel Builder to package MATLAB functions so that Microsoft Excel users can access them from Excel. Excel Builder converts MATLAB M-functions to methods of a class that you define. From this class, Excel Builder creates components.
What Is MATLAB Builder for Excel? name and class name, and which methods (MATLAB functions) go into a particular class, are purely 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 describe what the class does. Organizing related functions into classes in this way reduces the amount of code to rebuild and redeploy when one function is changed.
1 Getting Started Building a Deployable Application Using MATLAB Builder for Excel to create a deployable application requires the following steps: • “Creating and Building a Component” on page 1-4 • “Using the mcc Command to Build a Component” on page 1-6 • “Testing the Component” on page 1-7 • “Deploying the Component” on page 1-8 • “Packaging and Distributing the Component” on page 1-8 Creating and Building a Component To use MATLAB Builder for Excel to build a component, follow this procedure: 1 If y
Building a Deployable Application 3 Create a new project by clicking the New Project button in the toolbar. 4 Add files that you want to encapsulate by dragging them to the Deployment Tool, or by selecting them and clicking the Add Files button toolbar. in the Note The name of any file added to the project cannot duplicate the name of any function existing in the library of precompiled functions. 5 Set properties for building and packaging.
1 Getting Started 6 Add classes (optional). 7 Save the project by clicking the Save button in the toolbar. 8 Build the component. Click the Build button in the toolbar to start the build process. Excel Builder copies intermediate source files to project_directory\src and output files necessary for deployment (a DLL and a VBA file (.bas) to project_directory\distrib. The Output pane shows the output of the build process and informs you of any problems encountered.
Building a Deployable Application not specify the class name (), mcc uses the component name as the default. If you do not specify a version number, mcc uses the latest version built or 1.0, if there is no previous version. The following example shows the mcc command used to create a COM component called mycomponent containing single COM class named myclass with methods foo and bar, and a version of 1.0. The -T option tells mcc to create a DLL. mcc -W 'excel:mycomponent,myclass,1.
1 Getting Started Formula functions of this type are most useful to access a function of one or more inputs that returns a single scalar value. When you require multiple outputs or outputs representing ranges of data, you need a more general Visual Basic subroutine. For details about integrating Excel Builder components into Microsoft Excel via Visual Basic for Applications, see Chapter 2, “Programming with MATLAB Builder for Excel”.
Building a Deployable Application reopening the project in Deployment Tool and clicking the Package button in the toolbar. Repeat this distribution process on each target machine. MATLAB Builder for Excel creates a self-extracting executable containing the following files. File Description .ctf Component Technology File archive; platform-dependent file that must correspond to the end user’s platform .dll Compiled component _install.
1 1-10 Getting Started
2 Programming with MATLAB Builder for Excel Overview of the Integration Process (p. 2-2) Provides information on integrating MATLAB Builder for Excel components into Excel using the VBA programming environment When to Use a Formula Function or a Subroutine (p. 2-3) Discusses the two basic procedure types: functions and subroutines Initializing MATLAB Builder for Excel Libraries with Excel (p.
2 Programming with MATLAB Builder for Excel Overview of the Integration Process Each MATLAB Builder for Excel component is built as a COM object that you can access from Microsoft Excel through Visual Basic for Applications (VBA). This topic provides general information on how to integrate 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 discuss how to program in Visual Basic.
When to Use a Formula Function or a Subroutine When to Use a Formula Function or a Subroutine 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. Use function procedures when the original MATLAB function takes one or more inputs and returns one scalar output. You access a subroutine as a general macro.
2 Programming with MATLAB Builder for Excel Initializing MATLAB Builder for Excel Libraries with Excel Before you use any MATLAB Builder for Excel component, initialize the supporting libraries with the current instance of Excel. Do this once for an Excel session that uses Excel Builder components. To do this initialization, call the utility library function MWInitApplication, which is a member of the MWUtil class. This class is part of the MWComUtil library.
Initializing MATLAB Builder for Excel 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 Excel Builder components can include a call to InitModule at the beginning to ensure that the initialization always gets performed as needed.
2 Programming with MATLAB Builder for Excel 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 • 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 Function foo(x1 As Variant, x2 As Variant) As Variant 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 .
2 Programming with MATLAB Builder for Excel Handle_Error: foo = Err.Description End Function How the MCR Is Shared Among Classes MATLAB Builder for Excel creates a single MCR when the first COM class is instantiated in an application. This MCR is reused and shared among all subsequent class instances within the component, resulting in more efficient memory usage and eliminating the MCR startup cost in each subsequent class instantiation.
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 Builder for Excel applies a standard mapping from the original MATLAB function syntax to the method’s argument list. See “Calling Conventions” on page A-11 for a detailed description of the mapping from MATLAB functions to COM class method calls.
2 Programming with MATLAB Builder for Excel Function foo(x1 As Variant, x2 As Variant) As Variant Dim aClass As Object Dim y As Variant On Error Goto Handle_Error 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.
Processing varargin and varargout Arguments Processing varargin and varargout Arguments When varargin and/or varargout are present in the MATLAB function that you are using for the Excel component, 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.
2 Programming with MATLAB Builder for Excel Sub foo(Rout1 As Range, Rout2 As Range, Rout3 As Range, _ Rin1 As Range, Rin2 As Range) Dim aClass As Object 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
2 Programming with MATLAB Builder for Excel Modifying Flags Each MATLAB Builder for Excel 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. Array formatting flags affect the transformation of arrays, whereas data conversion flags deal with type conversions of individual array elements.
Modifying Flags var1(1,2) = 12# var1(2,1) = 21# var1(2,2) = 22# x(1,1) = 11 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.
2 Programming with MATLAB Builder for Excel Setting this flag presents all array input to the compiled MATLAB function as cell arrays. Similarly, you can manipulate the format of output arguments using the OutputArrayFormat flag. You can also modify array output with the AutoResizeOutput and TransposeOutput flags. AutoResizeOutput is used for Excel Range objects passed directly as output parameters. When this flag is set, the target range automatically resizes to fit the resulting array.
Modifying Flags Dim aClass As mycomponent.myclass Dim var1, var2 As Variant Dim y As Variant On Error Goto Handle_Error var1 = 1 var2 = 2# Set aClass = New mycomponent.myclass Call aClass.foo(1,y,var1,var2) Exit Sub 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.
2 Programming with MATLAB Builder for Excel mwDateFormatString Call aClass.foo(1,y,today) Exit Sub Handle_Error: MsgBox(Err.Description) End Sub The next example uses an MWArg object to modify the conversion flags for one argument in a method call. In this case the first output argument (y1) is coerced to a Date, and the second output argument (y2) uses the current default conversion flags supplied by aClass. Sub foo(y1 As Variant, y2 As Variant) Dim aClass As mycomponent.
3 Usage Examples Magic Square Examples (p. 3-2) Creates a magic square from a single input integer Multiple Files and Variable Arguments Example (p. 3-6) Plots a line from 1 to an input number Spectral Analysis Example (p. 3-13) Creates a comprehensive Excel add-in to perform spectral analysis Note You might also find usage examples on http://www.mathworks.com/matlabcentral/.
3 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 Setting Value Project name xlmagic Class name xlmagicclass Project directory The name of your work directory followed by the component name. Show verbose output Selected 5 Locate your work directory and navigate to the \work\xlmagic directory and add the mymagic.m file to the project. 6 Build the component by clicking the Build button in the Deployment Tool toolbar.
3 Usage Examples Illustration 2. Transpose the Output Reopen the Macro dialog box, select the mymagic_transpose macro and click Run. This procedure returns a magic square of size 4 transposed, beginning in cell B14. Illustration 3. Resize the Output Reopen the Macro dialog box, select the mymagic_resize macro, and click Run. This procedure returns a magic square of size 4 beginning in cell B32. Change the value of 4 in cell A32 to a higher value and rerun this macro.
Magic Square Examples Inspecting the Visual Basic Code 1 From the Excel main window, click Tools > Macro > Visual Basic Editor. 2 When the Visual Basic Editor opens, in the Project - VBAProject window, double-click to expand VBAProject (mymagic.xls) 3 Expand the Modules folder and double-click the Module1 module. This opens the VB Code window with the code for this project.
3 Usage Examples Multiple Files and Variable Arguments Example 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 several ways.
Multiple Files and Variable Arguments Example Setting Value Project name xmulti Class name xmulticlass Project directory The name of your work directory followed by the component name. . Show verbose output Selected 5 Locate your work directory and navigate to the xmulti directory, which contains the M-files for myplot, myprimes, and mysum functions. Add these files to the project. 6 Build the component by clicking the Build icon in the Deployment Tool toolbar.
3 Usage Examples 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.
Multiple Files and Variable Arguments Example This procedure plots a line from 1 through 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. 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.
3 Usage Examples • 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. This illustration demonstrates that the ranges do not need to be the same size and that all the cells do not need a value. • The fourth (cell A30) takes a combination of a range object and explicitly stated values, adds them, and returns the result of 16.
Multiple Files and Variable Arguments Example To execute the macro, from the main Excel window (not the Visual Basic Editor), open the Macro dialog box, by pressing the Alt and F8 keys simultaneously, or by clicking Tools > Macro > Macros. Select myprimes from the list and click Run. 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.
3 Usage Examples Inspecting the Visual Basic Code 1 On the Excel main window, click Tools > Macro > Visual Basic Editor. 2 On the Visual Basic Editor, in the Project - VBA Project window, double-click to expand VBAProject (mymulti.xls) 3 Expand the Modules folder and double-click the Module1 module. This opens the VB Code window with the code for this project.
Spectral Analysis Example 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 a fast Fourier transform (FFT) on an input data set located in a designated worksheet range.
3 Usage Examples computefft.m: function [fftdata, freq, powerspect] = computefft(data, interval) if (isempty(data)) fftdata = []; freq = []; 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.
Spectral Analysis Example 2 Create a project with the following settings. Setting Value Component name Fourier Class name Fourier Project directory The name of your work directory followed by the component name. Show verbose output Selected 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.
3 Usage Examples Creating the Main VB Code Module for the Application. The add-in requires some initialization code and some global variables to hold the application’s state between function invocations. To achieve this, implement a Visual Basic code module to manage these tasks, as follows: 1 Right-click the VBAProject item in the project window and click Insert > Module. A new module appears under Modules in the VBA Project. 2 In the module’s property page, set the Name property to FourierMain.
Spectral Analysis Example Public theFourier As Fourier.
3 Usage Examples MsgBox (Err.Description) End Sub Creating the Visual Basic Form The next step in the integration process develops a user interface for your add-in using the Visual Basic Editor. Follow these steps to create a new user form and populate it with the necessary controls: 1 Right-click VBAProject in the VBA project window and click Insert > UserForm. A new form appears under Forms in the VBA project window.
Spectral Analysis Example 3 Add a series of controls to the blank form to complete the dialog box, as summarized in the following table: Controls Needed for Spectral Analysis Example Control Type Control Name Properties Purpose CheckBox chkPlot Caption = Plots input data and power spectral density.
3 Usage Examples Controls Needed for Spectral Analysis Example (Continued) Control Type Control Name Properties Purpose Label Label3 Caption = Labels the RefEdit for frequency output. Frequency: Label Label4 Caption = FFT Real Part: Label Label5 Caption = FFT - Imaginary Part: Label Label6 Caption = Power Spectral Density 3-20 Labels the RefEdit for real part of FFT. Labels the RefEdit for imaginary part of FFT. Labels the RefEdit for power spectral density.
Spectral Analysis Example Controls Needed for Spectral Analysis Example (Continued) Control Type Control Name Properties Purpose RefEdit refedtImag N/A Selects output range for imaginary part of FFT of input data. RefEdit refedtPowSpect N/A Selects output range for power spectral density of input data. The following figure shows the controls layout on the form: 4 When the form and controls are complete, right-click the form and click View Code.
3 Usage Examples for any of the controls or any global variable, change this code to reflect those differences. ' '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.
Spectral Analysis Example 'Cancel button click event handler. Exits form without computing fft 'or updating variables. Unload Me End Sub Private Sub btnOK_Click() 'OK button click event handler. Updates state of all variables from controls 'and executes the computefft or plotfft method. Dim R As Range If theFourier Is Nothing Or theFFTData Is Nothing Then GoTo Exit_Form On Error Resume Next 'Process inputs Set R = Range(refedtInput.
3 Usage Examples bPlot = chkPlot.Value 'Compute the fft and optionally plot power spectral density If bPlot Then Call theFourier.plotfft(3, theFFTData, Frequency, PowerSpect, _ InputData, Interval) Else Call theFourier.computefft(3, theFFTData, Frequency, PowerSpect, _ InputData, Interval) End If GoTo Exit_Form Handle_Error: MsgBox (Err.
Spectral Analysis Example 2 Place the following code into ThisWorkbook.
3 Usage Examples Call RemoveFourierMenuItem 'Find Tools menu Set ToolsMenu = Application.CommandBars(1).FindControl(ID:=30007) If ToolsMenu Is Nothing Then Exit Sub 'Add Spectral Analysis menu item Set NewMenuItem = ToolsMenu.Controls.Add(Type:=msoControlButton) NewMenuItem.Caption = "Spectral Analysis..." NewMenuItem.
Spectral Analysis Example Testing the Add-In Before distributing the add-in, test it with a sample problem. Spectral analysis is commonly used to find the frequency components of a signal buried in a noisy time domain signal. In this example you will create a data representation of a signal containing two distinct components and add to it a random component.
3 Usage Examples 3 Click and hold the lower-right corner of cell A2 and drag the formula down the column to cell A1001. This procedure fills the range A1:A1001 with the interval 0 to 10 incremented by 0.01. 4 Click cell B1 and type the following formula: "= SIN(2*PI()*15*A1) + SIN(2*PI()*40*A1) + RAND()" Repeat the drag procedure to copy this formula to all cells in the range B1:B1001.
Spectral Analysis Example The power spectral density reveals the two signals at 15 and 40 Hz. Packaging and Distributing 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 be installed onto other computers that need to use the Spectral Analysis add-in. To package and distribute the add-in, follow these steps: 1 Reopen project in the Deployment Tool, if it is not already open.
3 Usage Examples MATLAB Builder for Excel creates the Fourier_pkg.exe self-extracting executable. 3 To install this add-in onto another computer, copy the Fourier_pkg.exe package to that machine, run it from a command prompt, and follow the instructions.
4 Function Wizard Overview of the Function Wizard (p. 4-2) Describes the purpose and use of the Function Wizard Installing the Function Wizard Add-In (p. 4-3) How to install the Add-In Starting the Function Wizard (p. 4-4) How to open the Function Viewer Understanding the Function Viewer (p. 4-5) How to load and execute functions Component Browser (p. 4-7) How to view components currently installed Function Properties (p. 4-8) How to edit inputs and outputs to functions Argument Properties (p.
4 Function Wizard Overview of the Function Wizard The Function Wizard enables you to pass Microsoft Excel (Excel 2000 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.
Installing the Function Wizard Add-In Installing the Function Wizard Add-In The Function Wizard GUI is contained in an Excel add-in (mlfunction.xla) residing in the \toolbox\matlabxl\matlabxl directory. You must install this add-in before using the Function Wizard. Follow these steps to install the add-in: 1 Select Tools > Add-Ins from the Excel main menu. 2 If the Function Wizard was previously installed, a reference to MATLAB Function Wizard appears in the list. Select the item and click OK.
4 Function Wizard Starting the Function Wizard To start the Function Wizard, click Tools > MATLAB Functions from the Excel menu bar.
Understanding the Function Viewer Understanding the Function Viewer The Function Viewer controls the execution of worksheet functions. Use the Function Viewer to organize the list of all currently loaded 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.
4 Function Wizard 3 Click Execute on the Function Viewer. When you click Execute, functions execute in the order displayed in the list.
Component Browser Component Browser The Component Browser lists all Excel Builder components currently installed on the system. When you click New on the Function Viewer, this dialog box appears: The Component Browser lists each component by name and version. Expanding a component reveals the class name at the next level. You can also expand the class to reveal the MATLAB functions that make up the class methods. Select the desired method and click Add to add a function.
4 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 Edit on the Function Viewer, the Function Properties dialog box appears, as shown: The Add and Delete buttons become active when you click varargin Arguments. Click the Outputs tab to switch to editing outputs.
Function Properties • 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 let you specify a variable number of arguments. Editing Required Arguments To edit required arguments, select the argument from the list and click Properties. Before you can edit varargin/varargout arguments, you must first explicitly add them using Add.
4 Function Wizard Editing Required Outputs When you are editing required output arguments, the Function Properties dialog box appears as shown: The Add and Delete buttons become active when you click varargout Arguments. Click the Inputs tab to switch to editing inputs.
Function Properties Editing varargout Outputs When you are editing varargout outputs, the Function Properties dialog box appears as shown: 4-11
4 Function Wizard 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. These dialog boxes are as follows: • “Input Argument Properties Dialog Box” on page 4-12 • “Output Argument Properties Dialog Box” on page 4-13 Input Argument Properties Dialog Box Here is an example of the Argument Properties dialog box for input arguments. In this example, the input arguments have a range of A1 to A10.
Argument Properties Output Argument Properties Dialog Box Here is an example of the Argument Properties dialog box for output arguments. In this example, the output argument is A12. From this dialog box you can • From the Range list, select the worksheet range to be used as the output argument. • Select Auto resize to tell Excel Builder to adjust the output range to fit the output array.
4 Function Wizard Function Utilities Excel Builder supports several function utilities, which you use via the following dialog boxes: • “Rename Function Dialog Box” on page 4-14 • “Copy Function Dialog Box” on page 4-14 • “Move Function Dialog Box” on page 4-15 Rename Function Dialog Box Use the Rename Function dialog box to rename a function. To open this dialog box, click Rename on the Function Viewer.
Function Utilities • 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. When you set the number of rows and columns and the row/column increments, the copy process automatically updates cell references by the specified increment amounts. - Positive increments move rows down and columns to the right. Negative increments move rows up and columns to the left.
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 optionally move the cell contents of any ranges referenced by the function.
5 Functions — Alphabetical List componentinfo deploytool
componentinfo Purpose Query system registry about component created with MATLAB Builder for Excel Syntax info info info info Arguments Description = = = = componentinfo componentinfo(component_name) componentinfo(component_name, major_revision_number) componentinfo(component_name, major_revision_number, minor_revision_number) component_name MATLAB string providing the name of a MATLAB Builder for Excel component. Names are case sensitive.
componentinfo The return value is an array of structures representing all the registry and type information needed to load and use the component. When you supply a component name, major_revision_number and minor_revision_number are interpreted as shown below. Value Information Returned >0 Information on a specific major and minor revision 0 Information on the most recent revision. When omitted, minor_revision_number is assumed to be equal to 0.
componentinfo Field Description Interfaces An array of structures defining all interface definitions in the type library. Each structure contains two fields: • Name - Interface name • IID - Interface GUID CoClasses An array of structures defining all COM classes in the component.
componentinfo • C - An array of C-Language function prototypes. • VB Examples See Also - An array of VBA function prototypes Function Call Returns Info = componentinfo Information for all installed components. Info = componentinfo('mycomponent') Information for all revisions of mycomponent. Info = componentinfo('mycomponent',1,0) Information for revision 1.0 of mycomponent.
deploytool Purpose Open GUI for MATLAB Builder for Excel and MATLAB Compiler Syntax deploytool Description The deploytool command displays the Deployment Tool dialog box, which is the graphical user interface (GUI) for MATLAB Builder for Excel and for MATLAB Compiler.
A Producing a COM Object from MATLAB Overview of Internal Processes (p. A-2) Provides a high-level description of internal processes Component Registration (p. A-5) Describes the registration process for MATLAB Builder for Excel components Calling Conventions (p.
A Producing a COM Object from MATLAB Overview of Internal Processes MATLAB Builder for Excel lets you pass Microsoft Excel worksheet values to a compiled MATLAB model via VBA, and return model output to a cell or range of cells in the worksheet. Each Excel Builder component is built as a stand-alone COM object. Each MATLAB function included in a given component appears as a method of the created COM class. The resulting call syntax from VB is systematically mapped to the syntax of the original MATLAB.
Overview of Internal Processes The process of creating an Excel Builder component is completely automatic from a user point of view. You specify a list of M-files to process and a few additional pieces of information, such as the component name, the class names, and the version number.
A Producing a COM Object from MATLAB interface definition in the IDL file. The interface GUID file contains the definitions of the GUIDs from all interfaces in the IDL file. The component type library file contains a binary representation of all types and objects exposed by the component. C++ Compilation The third step compiles all C/C++ source files generated in steps 1 and 2 into object code. One additional file containing a set of C++ template classes (mclcomclass.h) is included at this point.
Component Registration Component Registration When 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. When programming with Excel components you might need details about a component. You can use componentinfo, which is a MATLAB function, to query the system registry for details about any installed Excel Builder component.
A Producing a COM Object from MATLAB ans = Name: 'Imyclass' IID: '{3A14AB36-44BE-11D5-B155-00D0B7BA7544}' 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.
Component Registration ans = function [varargout] = randvectors() ans = function [x] = getdates(n, inc) ans = function [p] = myprimes(n) Self-Registering Components 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. The mwregsvr utility, distributed with the MCR, registers self-registering DLLs. For example, to register a component called mycomponent_1_0.
A Producing a COM Object from MATLAB Note The mwregsvr utility invokes a process that is similar to regsvr32.exe, except that mwregsvr does not require interaction with a user at the console. The regsvr32.exe process belongs to the Windows OS and is used to register dynamic link libraries and ActiveX controls in the registry. This program is important for the stable and secure running of your computer and should not be terminated. You can use regsvr32.
Component Registration (Continued) Key Definition HKEY_CLASSES_ROOT\Interface Information about COM interfaces on the system. Each component creates a new key under HKEY_CLASSES_ROOT\Interface for each interface it defines. This key has the value of the GUID assigned to the interface and contains subkeys with information about the interface. HKEY_CLASSES_ROOT\TypeLib Information about type libraries on the system.
A Producing a COM Object from MATLAB When a component is created, you can specify a version number (default = 1.0). During the development of a specific version of a component, the version number should be kept constant. When this is done, the MATLAB Compiler, in certain cases, reuses type library, class, and interface GUIDs for each subsequent build of the component.
Calling Conventions Calling Conventions This section describes the calling conventions for MATLAB Builder for Excel components, including mappings from the original M-functions to VBA. A function call originating from an Excel worksheet is routed from a VBA function into a compiled M-function. 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.
A Producing a COM Object from MATLAB . . . [in] VARIANT varargin); This IDL function definition is generated by producing a function with the same name as the original M-function and an argument list containing all inputs and outputs of the original plus one additional parameter, nargout. (nargout is not produced if you compile an M-function containing no outputs.) When present, the nargout parameter is an [in] parameter of type long. It is always the first argument in the list.
Calling Conventions varargin As Variant) (See “Programming with COM Components Created by MATLAB Builder for .NET” in the MATLAB Builder for .NET documentation for mappings to other languages, such as C++.) Visual Basic provides native support for COM VARIANTs with the Variant type, as well as implicit conversions for all Visual Basic basic types to and from Variants. In general, arrays/scalars of any Visual Basic basic type, as well as arrays/scalars of Variant types, can be passed as arguments.
A Producing a COM Object from MATLAB (function body) . . . foo = Y1 . . . (error handling code) . . . End Function By default, the generated formula function contains an argument list with all the inputs to the method call and a return value corresponding to the first output parameter. The argument list includes each explicit input parameter. If the optional varargin parameter is present in the original MATLAB function, additional arguments varargin1, varargin2,...
B Data Conversion Data Conversion Rules (p. B-2) Describes the process of converting data between MATLAB and COM variants Array Formatting Flags (p. B-12) Describes the flags that control the formatting of data Data Conversion Flags (p.
B Data Conversion Data Conversion Rules This topic describes the data conversion rules for MATLAB Builder for Excel components. These components are dual interface COM objects that support data types compatible with Automation. Note Automation (formerly called OLE Automation) is a technology that allows software packages to expose their unique features to scripting tools and other applications.
Data Conversion Rules VARIANT Type Codes Supported Variant VARIANT Type Code (C/C++) C/C++ Type VT_EMPTY Type Code (Visual Basic) Visual Basic Type Definition Uninitialized vbEmpty VARIANT Signed one-byte character VT_I1 char VT_UI1 unsigned char vbByte Byte Unsigned one-byte character VT_I2 short vbInteger Integer Signed two-byte integer VT_UI2 unsigned short — — Unsigned two-byte integer VT_I4 long vbLong Long Signed four-byte integer VT_UI4 unsigned long — — Unsigned
B Data Conversion VARIANT Type Codes Supported (Continued) Type Code (Visual Basic) Visual Basic Type vbDate Date Eight-byte floating point value representing date and time — — Signed integer; equivalent to type Variant VARIANT Type Code (C/C++) C/C++ Type VT_DATE DATE VT_INT int + Definition int VT_UINT unsigned int — — Unsigned integer; equivalent to type unsigned int + vbDecimal — 96-bit (12-byte) unsigned integer, scaled by a variable power of 10 VARIANT_BOOL+ vbBoolean Bo
Data Conversion Rules VARIANT Type Codes Supported (Continued) Variant VARIANT Type Code (C/C++) C/C++ Type Type Code (Visual Basic) |VT_BYREF — — Visual Basic Type — Definition Bitwise combine VT_BYREF with any basic type to declare as a reference to a value + Denotes Windows-specific type. Not part of standard C/C++. The following table lists the rules for converting from MATLAB to COM.
B Data Conversion MATLAB to COM VARIANT Conversion Rules (Continued) B-6 MATLAB Data Type VARIANT Type for VARIANT Type for Scalar Data 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 MATLAB to COM VARIANT Conversion Rules (Continued) MATLAB Data Type VARIANT Type for VARIANT Type for Scalar Data 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 MATLAB to COM VARIANT Conversion Rules (Continued) B-8 MATLAB Data Type VARIANT Type for VARIANT Type for Scalar Data 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 MATLAB to COM VARIANT Conversion Rules (Continued) MATLAB Data Type VARIANT Type for VARIANT Type for Scalar Data 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 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 VT_DATE double 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 COM VARIANT to MATLAB Conversion Rules (Continued) VARIANT Type MATLAB Data Type (scalar or array data) VT_INT int32 VT_UINT uint32 VT_DECIMAL double VT_BOOL logical VT_DISPATCH (varies) Comments IDispatch* pointers are treated within the context of what they point to. Objects must be supported types with known data extraction and conversion rules, or expose a generic Value property that points to a single VARIANT type.
B Data Conversion Array Formatting Flags Excel Builder components have flags that control how array data is formatted in both directions. Generally, you should develop client code that matches the intended inputs and outputs of the MATLAB functions with the corresponding methods on the compiled COM objects, in accordance with the rules listed in MATLAB to COM VARIANT Conversion Rules on page B-5 and COM VARIANT to MATLAB Conversion Rules on page B-10.
Array Formatting Flags Array Formatting Flags (Continued) Flag Description InputArrayIndFlag Sets the input array indirection level used with the InputArrayFormat flag (applicable only to nested arrays, i.e., VARIANT arrays of VARIANTs, which themselves are arrays). The default value for this flag is zero, which applies the InputArrayFormat flag to the outermost array. When this flag is greater than zero, e.g., equal to N, the formatting rule attempts to apply itself to the Nth level of nesting.
B Data Conversion Data Conversion Flags Excel Builder components contain the following flags to control the conversion of certain VARIANT types to MATLAB types: • “CoerceNumericToType” on page B-14 • “InputDateFormat” on page B-15 • “OutputAsDate As Boolean” on page B-16 • “DateBias As Long” on page B-16 CoerceNumericToType This flag tells the data converter to convert all numeric VARIANT data to one specific MATLAB type.
Data Conversion Flags VT_UINT VT_ERROR VT_BOOL VT_DATE Valid values for this flag are mwTypeDefault mwTypeChar mwTypeDouble mwTypeSingle mwTypeLogical mwTypeInt8 mwTypeUint8 mwTypeInt16 mwTypeUint16 mwTypeInt32 mwTypeUint32 The default for this flag, mwTypeDefault, converts numeric data according to the rules listed in “Data Conversion Rules” on page B-2. InputDateFormat This flag tells the data converter how to convert VARIANT dates to MATLAB dates.
B Data Conversion the rule listed in VARIANT Type Codes Supported on page B-3. The mwDateFormatString flag converts a VARIANT date to its string representation. This flag only affects VARIANT type code VT_DATE. OutputAsDate As Boolean This flag instructs the data converter to process an output argument as a date.
C Utility Library Referencing Utility Classes (p. C-2) How to reference the classes in your programming environment. Utility Library Classes (p. C-3) Describes the classes provided in the Utility library. Enumerations (p. C-32) Describes the sets of constants provided with the library.
C Utility Library Referencing Utility Classes This section describes the MWComUtil library provided with MATLAB Builder for Excel. 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.
Utility Library Classes Utility Library Classes The MATLAB Builder for Excel Utility library provides these classes: • “Class MWUtil” on page C-3 • “Class MWFlags” on page C-10 • “Class MWStruct” on page C-16 • “Class MWField” on page C-24 • “Class MWComplex” on page C-25 • “Class MWSparse” on page C-27 • “Class MWArg” on page C-30 Class MWUtil The MWUtil class contains a set of static utility methods used in array processing and application initialization.
C Utility Library Parameters. Argument Type Description pApp Object A valid reference to the current Excel application 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 Sub MWPack(pVarArg, [Var0], [Var1], ... ,[Var31]) Packs a variable length list of Variant arguments into a single Variant array. This function is typically used for creating a varargin cell from a list of separate inputs. Each input in the list is added to the array only if it is nonempty and nonmissing. (In Visual Basic, a missing parameter is denoted by a Variant type of vbError with a value of &H80020004.) Parameters.
C Utility Library Dim Dim Dim Dim Optional Optional Optional Optional Optional Optional Optional Optional y As Variant varargin As Variant aClass As Object aUtil As Object V2 V3 V4 V5 V6 V7 V8 V9 As As As As As As As As Variant, Variant, Variant, Variant, Variant, Variant, Variant, Variant) _ _ _ _ _ _ _ As Variant On Error Goto Handle_Error Set aClass = CreateObject("mycomponent.myclass.1_0") Set aUtil = CreateObject("MWComUtil.MWUtil") Call aUtil.
Utility Library Classes Argument Type Description nStartAt Long Optional starting index (zero-based) in the array to begin processing. Default = 0. 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], ...
C Utility Library This function produces a sequence of nargout random column vectors, with the length of the ith vector equal to i. Assume that this function is included in a class named myclass that is included in a component named mycomponent with a version of 1.0. The Visual Basic subroutine takes no arguments and places the results into Excel columns starting at A1, B1, C1, and D1. If an error occurs, a message box displays the error text.
Utility Library Classes Parameters. Argument Type Description pVar Variant Variant to be converted Return Value. None. Remarks. MATLAB handles dates as double-precision floating-point numbers with 0.0 representing 0/0/00 00:00:00 (see “Data Conversion Rules” on page B-2 for more information on conversion between MATLAB and COM date values).
C Utility Library Set aUtil = CreateObject("MWComUtil.MWUtil") Call aClass.getdates(1, R, R.Rows.Count, inc) Call aUtil.MWDate2VariantDate(R) Exit Sub Handle_Error: MsgBox (Err.Description) End Sub Class MWFlags The MWFlags class contains a set of array formatting and data conversion flags (see “Data Conversion Rules” on page B-2 for more information on conversion between MATLAB and COM Automation types).
Utility Library Classes Property InputArrayFormat As mwArrayFormat. This property of type mwArrayFormat controls the formatting of arrays passed as input parameters to Excel Builder class methods. The default value is mwArrayFormatMatrix. The behaviors indicated by this flag are listed in the following table. Array Formatting Rules for Input Arrays Value Behavior mwArrayFormatAsIs Converts arrays according to the default conversion rules listed in “Data Conversion Rules” on page B-2.
C Utility Library Property OutputArrayFormat As mwArrayFormat. This property of type mwArrayFormat controls the formatting of arrays passed as output parameters to Excel Builder class methods. The default value is mwArrayFormatAsIs. The behaviors indicated by this flag are listed in the following table. Array Formatting Rules for Output Arrays Value Behavior mwArrayFormatAsIs Converts arrays according to the default conversion rules listed in MATLAB to COM VARIANT Conversion Rules on page B-5.
Utility Library Classes Property AutoResizeOutput As Boolean. This flag 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, setting this flag to True instructs the data conversion code to resize each Excel range to fit the output array. Resizing is applied relative to the upper left corner of each supplied range. The default value for this flag is False.
C Utility Library Conversion Rules for Input Dates Value Behavior mwDateFormatNumeric Convert dates to numeric values as indicated by the rule listed in COM VARIANT to MATLAB Conversion Rules on page B-10. mwDateFormatString Convert input dates to strings. Example.
Utility Library Classes explicit type declaration for the aClass variable. As with previous examples, this function assumes that MWInitApplication has been previously called. 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.
C Utility Library Parameters. Argument Type Description ppFlags MWFlags Reference to an uninitialized MWFlags object that receives the copy Return Value. None 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 Parameters. Argument Type Description varDims Variant Optional array of dimensions varFieldNames Variant Optional array of field names Return Value. None. Remarks. When created, an MWStruct object has a dimensionality of 1-by-1 and no fields. The Initialize method dimensions the array and adds a set of named fields to each element. Each time you call Initialize on the same object, it is redimensioned.
C Utility Library Call y.Initialize(, Array("name", "age", "salary")) Exit Sub Handle_Error: MsgBox(Err.Description) End Sub Property Item([i0], [i1], ..., [i31]) As MWField The Item property is the default property of the MWStruct class. This property is used to set and get the value of a field at a particular index in the structure array. Parameters. Argument Type Description i0,i1, ..., i31 Variant Optional index arguments. 0 to 32 index arguments can be entered.
Utility Library Classes Field name only This format may be used only in the case of a 1-by-1 structure array and returns the named field’s value. For example: x("red") = 0.2 x("green") = 0.4 x("blue") = 0.6 In this example, the name of the Item property was neglected. This is possible since the Item property is the default property of the MWStruct class. In this case the two statements are equivalent: x.Item("red") = 0.2 x("red") = 0.
C Utility Library All indices and field name This format accesses an array element of a 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.
Utility Library Classes Dim Index1(1 To 2) As Integer Dim Index2(1 To 2) As Integer 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.
C Utility Library Example. The next Visual Basic code sample illustrates how to access a two-dimensional structure array’s fields when the field names and dimension sizes are not known in advance: Sub foo () Dim x As MWStruct Dim Dims as Variant Dim FieldNames As Variant 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)) ' ...
Utility Library Classes Parameters. Argument Type Description ppStruct MWStruct Reference to an uninitialized MWStruct object to receive the copy Return Value. None Remarks. Clone allocates a new MWStruct 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. Example.
C Utility Library End Sub Class MWField The MWField class holds a single field reference in an MWStruct object. This class is noncreatable and contains these properties/methods: • “Property Name As String” on page C-24 • “Property Value As Variant” on page C-24 • “Property MWFlags As MWFlags” on page C-24 • “Sub Clone(ppField As MWField)” on page C-24 Property Name As String The name of the field (read only). Property Value As Variant Stores the field’s value (read/write).
Utility Library Classes Parameters. Argument Type Description ppField MWField Reference to an uninitialized MWField object to receive the copy Return Value. None. Remarks. Clone allocates a new MWField 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 MWComplex The MWComplex class passes or receives a complex numeric array into or from a compiled class method.
C Utility Library and type of the Real property’s array, an error results when the object is used in a method call. Example. The following Visual Basic code creates a complex array with the following entries: x = [ 1+i 1+2i 2+i 2+2i ] Sub foo() Dim x As MWComplex Dim rval(1 To 2, 1 To 2) As Double Dim ival(1 To 2, 1 To 2) As Double On Error Goto Handle_Error For I = 1 To 2 For J = 1 To 2 rval(I,J) = I ival(I,J) = J Next Next Set x = new MWComplex x.Real = rval x.Imag = ival . . .
Utility Library Classes Sub Clone(ppComplex As MWComplex) Creates a copy of an MWComplex object. Parameters. Argument Type Description ppComplex MWComplex Reference to an uninitialized MWComplex object to receive the copy Return Value. None Remarks. Clone allocates a new MWComplex 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.
C Utility Library Property NumColumns As Long Stores the column dimension for the array. The value of NumColumns must be nonnegative. If the value is 0, the row index is taken from the maximum of the values in the ColumnIndex array. Property RowIndex As Variant Stores the array of row indices of the nonzero elements of the array.
Utility Library Classes 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. Call this function when a separate object is required instead of a shared copy of an existing object reference. Example.
C Utility Library vals(K) K = K + rows(K) cols(K) vals(K) K = K + rows(K) cols(K) vals(K) K = K + = 1 = = = 1 = = = 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 any flags set on the object whose methods are called.
C Utility Library Enumerations The MATLAB Builder for Excel Utility library provides three enumerations (sets of constants): • “Enum mwArrayFormat” on page C-32 • “Enum mwDataType” on page C-32 • “Enum mwDateFormat” on page C-33 Enum mwArrayFormat The mwArrayFormat enumeration is a set of constants that denote an array formatting rule for data conversion. The following table lists the members of this enumeration.
Enumerations mwDataType Values (Continued) Constant Numeric Value MATLAB Type 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. The following table lists the members of this enumeration.
C Utility Library C-34
D Troubleshooting
D Troubleshooting This appendix provides a table showing errors you may encounter using MATLAB Builder for Excel, probable causes for these errors, and suggested solutions. Note MATLAB Builder for Excel uses the MATLAB Compiler to generate components. This means that you might see diagnostic messages from the MATLAB Compiler. See the MATLAB Compiler documentation for more information about those messages. Excel Builder Errors and Suggested Solutions Message Probable Cause Suggested Solution MBUILD.
Troubleshooting Excel Builder Errors and Suggested Solutions (Continued) Message Probable Cause Suggested Solution LoadLibrary ("component_name.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. See Required Locations to Develop and Use Components on page D-4. Cannot recompile the M file xxxx because it is already in the library libmmfile.mlib.
D Troubleshooting Required Locations to Develop and Use Components Component Development Machine MCR Make sure that Verify that matlabroot\bin\win32 mcr_root\ver\runtime\win32 appears on your system path ahead of any other MATLAB installations. (matlabroot is your root MATLAB directory.) appears on your system path. (mcr_root is your root MCR directory.) CTF D-4 Target Machine Verify that the CTF file is in the same directory as your program’s executable file.
Troubleshooting Excel Errors and Suggested Solutions Message Probable Cause Suggested Solution The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros. Note: Wording may vary depending upon the version of Excel you are running. The macro security for Excel is set to High. Medium on the Security Set Excel macro security to Level tab. Select Tools > Macro > Security.
D D-6 Troubleshooting
E Examples Use this list to find examples in the documentation.
E Examples Calling a MATLAB Function from Excel “Magic Square Examples” on page 3-2 Using Multiple Files and Variable Arguments “Multiple Files and Variable Arguments Example” on page 3-6 Creating a Comprehensive Excel Add-In: Spectral Analysis “Spectral Analysis Example” on page 3-13 Querying the Registry “Obtaining Registry Information” on page A-5 E-2
Index A F array formatting flags 2-14 flags array formatting 2-14 data conversion 2-14 function wizard argument properties 4-12 component browser 4-5 function properties 4-8 function utilities 4-14 function viewer 4-5 purpose 4-2 functions 2-3 Index C capabilities A-2 class 1-2 class method calling 2-6 Class MWFlags C-10 Class MWUtil C-3 class name 1-2 COM defined 1-2 COM class producing A-11 COM VARIANT B-2 command line interface 1-6 Compiler Output A-13 Component Object Model 1-2 componentinfo functi
Index R U required arguments 4-8 unregistering components A-7 utility library C-3 S self-registering component A-7 subroutines 2-3 T troubleshooting Excel Builder D-2 type library A-5 Index-2 V varargin/varargout 4-9 VARIANT variable B-2 version number A-9 versioning rules A-10 Visual Basic Mapping A-12
