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:
CreateObjectfunctionNewoperator
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 FunctionFor 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.
You can also select a web site from the following list
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
Asia Pacific
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)