Main Content

Integrate Components Using VBA

When to Use Formula Functions or Subroutines

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 returns one or no outputs.

You access a subroutine as a general macro. Use a subroutine procedure when the original MATLAB function returns an array of values or multiple outputs, because you need to map these outputs into multiple cells/ranges in the worksheet.

When you create a component, MATLAB Compiler™ produces a VBA module (.bas file). This file contains simple call wrappers, each implemented as a function procedure for each method of the class. For more information, see How Excel Add-In Compilation Works.

Initialize MATLAB Compiler Libraries with Microsoft Excel

Before you use any MATLAB Compiler component, initialize the supporting libraries with the current instance of Microsoft® Excel®. Do this once for an Excel session that uses the MATLAB Compiler 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. For details, see Class MWUtil.

One way to add this initialization code into a VBA module is to provide a subroutine that does the initialization once, and simply exits for all subsequent calls. The following Microsoft Visual Basic® code sample initializes the libraries with the current instance of Excel. A global variable of type Object named MCLUtil holds an instance of the MWUtil class, and another global variable of type Boolean named bModuleInitialized stores the status of the initialization process. The private subroutine InitModule() creates an instance of the MWComUtil class and calls the MWInitApplication method with an argument of Application. Once this function succeeds, all subsequent calls exit without reinitializing.

Dim MCLUtil As Object
Dim bModuleInitialized As Boolean

Private Sub InitModule()
   If Not bModuleInitialized Then
      On Error GoTo Handle_Error
      If MCLUtil Is Nothing Then
         Set MCLUtil = CreateObject("MWComUtil.MWUtil")
      End If
      Call MCLUtil.MWInitApplication(Application)
      bModuleInitialized = True
      Exit Sub
Handle_Error:
      bModuleInitialized = False
   End If
End Sub

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 Compiler components can include a call to InitModule at the beginning to ensure that the initialization always gets performed as needed.

Create 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

This method uses the Microsoft Visual Basic application programming interface (API) CreateObject function to create an instance of the class. Microsoft refers to calling CreateObject as late binding and using new as early binding.

To use this method, declare a variable of type Object using Dim to hold a reference to the class instance and call CreateObject using the class programmatic identifier (ProgID) as an argument, as shown in the next example:

Function foo(x1 As Variant, x2 As Variant) As Variant
       Dim aClass As Object
	 
       On Error Goto Handle_Error
       Set aClass = CreateObject("mycomponent.myclass.1_0") 
       ' (call some methods on aClass)
       Exit Function
Handle_Error:
   foo = Err.Description
End Function

New Operator

This method uses the Visual Basic New operator on a variable explicitly dimensioned as the class to be created. Before using this method, you must reference the type library containing the class in the current VBA project. Do this by selecting the Tools menu from the Visual Basic Editor, and then selecting References to display the Available References list. From this list, select the necessary type library.

The following example illustrates using the New operator to create a class instance. It assumes that you have selected mycomponent 1.0 Type Library from the Available References list before calling this function.

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 can be dimensioned as simply myclass. The full declaration in the form <component-name>.<class-name> guards against name collisions that can occur if other libraries in the current project contain types named myclass.

Using both CreateObject and New produce a dimensioned class instance. The first method does not require a reference to the type library in the VBA project; the second results in faster code execution. The second method has the added advantage of enabling the Auto-List-Members and Auto-Quick-Info capabilities of the Microsoft Visual Basic editor to work with your classes. The default function wrappers created with each built component all use the first method for object creation.

In the previous two examples, the class instance used to make the method call was a local variable of the procedure. This creates and destroys a new class instance for each call. An alternative approach is to declare one single module-scoped class instance that is reused by all function calls, as in the initialization code of the previous example.

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.Description
End Function

How MATLAB Runtime Is Shared Among Classes

MATLAB Compiler creates a single MATLAB Runtime instance when the first Microsoft COM class is instantiated in an application. This MATLAB Runtime is reused and shared among all subsequent class instances within the component, resulting in more efficient memory usage and eliminating the MATLAB Runtime startup cost in each subsequent class instantiation.

All class instances share a single MATLAB workspace and share global variables in the MATLAB files used to build the component. This makes properties of a COM class behave as static properties instead of instance-wise properties.

Call 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 Compiler applies a standard mapping from the original MATLAB function syntax to the method's argument list. For a detailed description of the mapping from MATLAB functions to COM class method calls, see Reference Utility Classes.

When a method has output arguments, the first argument is always nargout, which is of type Long. This input parameter passes the normal MATLAB nargout parameter to the compiled function and specifies how many outputs are requested. Methods that do not have output arguments do not pass a nargout argument. Following nargout are the output parameters listed in the same order as they appear on the left side of the original MATLAB function. Next come the input parameters listed in the same order as they appear on the right side of the original MATLAB function. All input and output arguments are typed as Variant, the default Visual Basic data type.

The Variant type can hold any of the basic VBA types, arrays of any type, and object references. For a detailed description of how to convert Variant types of any basic type to and from MATLAB data types, see Data Conversion Rules. In general, you can supply any Visual Basic type as an argument to a class method, with the exception of Visual Basic UDTs. You can also pass Microsoft Excel Range objects directly as input and output arguments.

When you pass a simple Variant type as an output parameter, the called method allocates the received data and frees the original contents of the Variant. In this case it is sufficient to dimension each output argument as a single Variant. When an object type (like an Excel Range) is passed as an output parameter, the object reference is passed in both directions, and the object's Value property receives the data.

The following examples illustrate the process of passing input and output parameters from VBA to the MATLAB Compiler component class methods.

The first example is a formula function that takes two inputs and returns one output. This function dispatches the call to a class method that corresponds to a MATLAB function of the form function y = foo(x1,x2).

Function foo(x1 As Variant, x2 As Variant) As Variant
   Dim aClass As Object
   Dim y As Variant
   
   On Error Goto Handle_Error
   Set aClass = New mycomponent.myclass
   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.foo(1,Rout,Rin1,Rin2)
   Exit Sub
Handle_Error:
   MsgBox(Err.Description)
End Sub

Program with Variable Arguments

Process 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.

The following example creates a varargin array to call a method resulting from a MATLAB function of the form y = foo(varargin):

Function foo(x1 As Variant, x2 As Variant, x3 As Variant, _ 
             x4 As Variant, x5 As Variant) As Variant 
   Dim aClass As Object 
   Dim v As Variant 
   Dim y As Variant 
   Dim MCLUtil As Object 
    
   On Error GoTo Handle_Error 
   set aClass = CreateObject("mycomponent.myclass.1_0") 
   Set MCLUtil = CreateObject("MWComUtil.MWUtil") 
   Call MCLUtil.MWPack(v, x1, x2, x3, x4, x5) 
   Call aClass.foo(1, y, v) 
   foo = y 
   Exit Function 
Handle_Error: 
   foo = Err.Description 
End Function

The MWUtil class included in the MWComUtil utility library provides the MWPack helper function to create varargin parameters. See Class MWUtil for more details.

The next example processes a varargout parameter into three separate Excel Ranges. This function uses the MWUnpack function in the utility library. The MATLAB function used is varargout = foo(x1,x2).

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.Description)
End Sub

Pass an Empty varargin from Microsoft Visual Basic Code

In MATLAB, varargin inputs to functions are optional, and may be present or omitted from the function call. However, from Microsoft Visual Basic, function signatures are more strict—if varargin is present among the MATLAB function inputs, the VBA call must include varargin, even if you want it to be empty. To pass in an empty varargin, pass the Null variant, which is converted to an empty MATLAB cell array when passed.

Pass an Empty varargin from VBA Code.  The following example illustrates how to pass the null variant in order to pass an empty varargin:

Function foo(x1 As Variant, x2 As Variant, x3 As Variant, _ 
             x4 As Variant, x5 As Variant) As Variant 
   Dim aClass As Object 
   Dim v(1 To 5) As Variant 
   Dim y As Variant 
    
   On Error Goto Handle_Error 
   v(1) = x1 
   v(2) = x2 
   v(3) = x3 
   v(4) = x4 
   v(5) = x5 
   aClass = CreateObject("mycomponent.myclass.1_0") 

   'Call aClass.foo(1,y,v) 
   Call aClass.foo(1,y,Null) 

   foo = y 
   Exit Function 
Handle_Error: 
   foo = Err.Description 
End Function

For More Information

For more information about working with variable-length arguments, see Create Macro Using Multiple MATLAB Functions.

Modify Flags

Each MATLAB Compiler 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.

The data conversion flags change selected behaviors of the data conversion process from Variants to MATLAB types and vice versa. By default, the MATLAB Compiler components allow setting data conversion flags at the class level through the MWFlags class property. This holds true for all Visual Basic types, with the exception of the MATLAB Compiler MWStruct, MWField, MWComplex, MWSparse, and MWArg types. Each of these types exposes its own MWFlags property and ignores the properties of the class whose method is being called. The MWArg class is supplied specifically for the case when a particular argument needs different settings from the default class properties.

This section provides a general discussion of how to set these flags and what they do. For a detailed discussion of the MWFlags type, as well as additional code samples, see Class MWFlags (MATLAB Compiler SDK).

Array Formatting Flags

Array formatting flags guide the data conversion to produce either a MATLAB cell array or matrix from general Variant data on input or to produce an array of Variants or a single Variant containing an array of a basic type on output.

The following examples assume that you have referenced the MWComUtil library in the current project by selecting Tools > References and selecting MWComUtil 7.5 Type Library from the list:

Sub foo( )
   Dim aClass As mycomponent.myclass
   Dim var1(1 To 2, 1 To 2), var2 As Variant
   Dim x(1 To 2, 1 To 2) As Double
   Dim y1,y2 As Variant
   
   On Error Goto Handle_Error
   var1(1,1) = 11#
   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.Description)
End Sub

In addition, these examples assume you have referenced the COM object (DLL file) created with MATLAB Compiler (mycomponent) as mentioned in New Operator.

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.

In MATLAB Compiler, when using the default settings, both of these arrays will be converted to 2-by-2 arrays of doubles. This does not follow the general convention listed in COM VARIANT to the MATLAB Conversion Rules. According to these rules, var1 converts to a 2-by-2 cell array with each cell occupied by a 1-by-1 double, and var2 converts directly to a 2-by-2 double matrix.

The two arrays both convert to double matrices because the default value for the InputArrayFormat flag is mwArrayFormatMatrix. The InputArrayFormat flag controls how arrays of these two types are handled. This default is used because array data originating from Excel ranges is always in the form of an array of Variants (like var1 of the previous example), and MATLAB functions most often deal with matrix arguments.

But what if you want a cell array? In this case, you set the InputArrayFormat flag to mwArrayFormatCell. Do this by adding the following line after creating the class and before the method call:

aClass.MWFlags.ArrayFormatFlags.InputArrayFormat = 
mwArrayFormatCell

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. 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.

Tip

If your MATLAB function is specifically returning a row vector, for example, ensure you assign a similar row vector of cells in Excel.

You may prefer worksheet columns from row vector output. This example auto-resizes and transposes an output range:

Sub foo(Rout As Range, Rin As Range )
   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.foo(1,Rout,Rin)
   Exit Sub
Handle_Error:
   MsgBox(Err.Description)
End Sub

Data Conversion Flags

Data conversion flags deal with type conversions of individual array elements. The two data conversion flags, CoerceNumericToType and InputDateFormat, govern how numeric and date types are converted from VBA to MATLAB. Consider the example:

Sub foo( )
   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. 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. In the previous example, place the following line after creating the class and before calling the methods:

aClass.MWFlags.DataConversionFlags.CoerceNumericToType = 
mwTypeDouble

The InputDateFormat flag controls how the VBA Date type is converted. This example sends the current date and time as an input argument and converts it to a string:

Sub foo( )
   Dim aClass As mycomponent.myclass
   Dim today As Date
   Dim y As Variant
   
   On Error Goto Handle_Error
   today = Now
   Set aClass = New mycomponent.myclass
   aClass. MWFlags.DataConversionFlags.InputDateFormat = 
mwDateFormatString
   Call aClass.foo(1,y,today)
   Exit Sub
Handle_Error:
   MsgBox(Err.Description)
End Sub

Handle 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. Microsoft Visual Basic provides an exception handling capability through the On Error Goto <label> statement, in which the program execution jumps to <label> when an error occurs. (<label> must be located in the same procedure as the On Error Goto statement). All errors are handled this way, including errors within the original MATLAB code. An exception creates a Visual Basic ErrObject object in the current context in a variable called Err. (See the Visual Basic for Applications documentation for a detailed discussion on VBA error handling.) All of the examples in this section illustrate the typical error trapping logic used in function call wrappers for MATLAB Compiler components.