Databases Reference
In-Depth Information
9
Analyses Via External
Applications
9.1
INTRODUCTION
Although Excel has a number of built-in functions, the reality is that its ability to perform com-
putations of any degree of sophistication internally is somewhat limited. There are many possible
solutions to Excel's limitations. Custom functionality can be written by the user utilizing the VBA
macro programming language. Custom software libraries or Excel Add-Ins can be purchased that
will perform the desired operations within Excel. The last and most flexible option is to harness
the engine of another computational tool the user is intimate with and have it return its computations
to Excel. In this section the reader will be shown how to utilize the computational power of Matlab
and Origin from within the Excel environment. The techniques employed here could be modified
and used with other computational tools such as SAS, Mathematica, JMP, or any other tool that
accepts COM, OLE, ActiveX, or DDE Commands.
9.2
SETTING UP A MATLAB ACTIVEX SERVER FROM EXCEL
The first step toward harnessing the power of the Matlab computational engine (or any other
software tool for that matter) is to establish a reference from Excel to the tool to be controlled.
The software that executes the function calls (in this case Excel) is referred to as the
The
software that performs the operations and returns the results (in this case Matlab) is referred to as
the
client.
. Client server communication is accomplished by means of an Object variable. An object
variable should be declared at the top of the module that will contain the ActiveX server control
subroutines from Excel.
server
'Declare a Matlab Object Variable to reference Matlab from Excel
Dim MatLab As Object
This object variable can be utilized to create a reference to Matlab from Excel. A very practical
way to think about this is that the object variable creates a “tunnel” between Excel (the client
application) and Matlab (the server application). Simply declaring the object variable at the top of
the module does not establish the connection (or tunnel) between Excel and Matlab. This must be
done via the Set command.
'Initiate a reference to Matlab
Set MatLab = CreateObject("Matlab.Application")
As soon as this Set command is executed, an instance of Matlab is started (just like running
the program from the start menu), and a connection is established between Excel and Matlab via
the MatLab Object variable. With the establishment of this connection, a number of COM server
functions can be invoked using the Matlab Object variable (Table 9.1).
Equally important as establishing the connection is the termination of a connection. Leaving
a connection to the server open after the client no longer requires its use is not only bad form but
wasteful of system resources as well. Connections are terminated via the
command. In
addition to terminating the connection, the object reference should be released. This can be easily
done by setting the Matlab object variable equal to nothing.
Quit
313
