« Previous « Start » Next »
8 Calling TOMLAB from Microsoft Excel
This section contains a simple description of how to call the TOMLAB
solvers from Microsoft Excel using the Matlab Builder for Excel.
Matlab Builder for Excel is an extension to the Matlab Compiler.
8.1 Creating a stand alone Excel Add-In
The first step is to make a selection of TOMLAB m-files and mex-files
needed in the stand alone program and to create a suitable entry
point. See the beginning of section
7.1. Do not
forget to include
tomlablic.dll
.
After all Matlab files have been put in a single directory, compile
them into an Excel Add-In using the following command:
> mcc -B cexcel:<addin_name>,<addin_name>,<version_number> <m-file(s)>
where
<addin_name>
is the name of the Add-In to create, and
<version_number>
is the version number of the Add-In, for
example:
1.0
.
(Notice: This is tested using MCC 4 only.)
This creates several new files, most of which are only used
temporarily during the compilation by the Matlab Compiler. The
important files are:
<addin_name>_<version_number>.dll
<addin_name>.ctf
<addin_name>.bas
The
.bas
-file is just an example of Excel Macro code in
Visual Basic. This should be imported to the Excel workbook from
where TOMLAB is called, but it often has to be modified in order to
be useable. It is good as a template to start from though.
The
.dll
- and
.ctf
-files are the needed runtime files.
They have to be in the same directory as the Excel workbook when
making the TOMLAB calls.
8.2 Calling the Add-In from Excel
To be able to call the Add-In, one must create a link between the
Add-In and Excel. This is done through the Visual Basic Editor in
Excel.
In Excel, start the Visual Basic Editor:
Tools -> Macro -> Visual Basic Editor
Then, in the Visual Basic Editor, import the
.bas
-file created during
the compilation by Matlab Builder for Excel:
File -> Import File...
All m-files mentioned in the compilation command corresponds to one
Visual Basic function. A function can be called from an Excel
worksheet by writing something like this in a cell:
=function_to_call(argument1, argument2, ...)
This could be suitable if the chosen entry point returns a scalar
value. The arguments may be ranges of cells, which is an analogy for
matrices in Matlab. The cell is then filled with the first element in
the first returned matrix of
function_to_call
. In most cases
this does not satisfy the needs, and one has to customize the Visual
Basic code.
One is often interested in the solution vector
x
, and perhaps
to call TOMLAB from Excel via a macro. Then the function should be
rewritten as a subroutine, setting the variables/matrices to be sent
to TOMLAB to ranges of the Excel worksheet. This includes both input
variables/matrices and output variables/matrices. When the
subroutine has been written it can be called from the menu:
Tools -> Macro -> Macros...
8.3 TOMLAB /CPLEX LP example
The TOMLAB distribution includes an example running TOMLAB /CPLEX for
an LP problem from an Excel workbook. It is available in the TOMLAB
distribution in the
/examples/sal/excel
directory.
In summary, the example was constructed like this:
- An m-file called solvelp.m was created to take simple
input in the form of matrices for c, A, x_L, x_U, b_L and
b_U. This m-file simply passes the matrices to cplex.m from
where the CPLEX solver is called.
- The profiler in Matlab was turned on and the
solvelp
function was called with some LP data. When CPLEX returned, the
profile report command displayed the m- and mex-files used during
the execution.
- These files were copied to a dedicated project directory for the
new Excel example.
- The Excel Add-In was built using the MCC compiler.
- Excel was started and the Visual Basic Editor was opened. The
example
.bas
-file was imported. The Visual Basic
solvelp
function was replaced by a solvelp
subroutine.
- A problem example was set up in the Excel worksheet. A push button
was added and assigned the solvelp macro.
- The workbook was saved as
lpsolver.xls
.
- The macro was run by clicking on the pushbutton. No error messages
appeared, and the example was done.
- In case an error message appears, it would likely be about
missing m-functions. The missing files would need to be added and
the project recompiled.
« Previous « Start » Next »