# TOMLAB  
# REGISTER (TOMLAB)
# LOGIN  
# myTOMLAB
TOMLAB LOGO

« 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:
  1. 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.

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

  3. These files were copied to a dedicated project directory for the new Excel example.

  4. The Excel Add-In was built using the MCC compiler.

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

  6. A problem example was set up in the Excel worksheet. A push button was added and assigned the solvelp macro.

  7. The workbook was saved as lpsolver.xls.

  8. The macro was run by clicking on the pushbutton. No error messages appeared, and the example was done.

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