Introduction

The Solver Add-in (from FrontLine Systems) that comes installed with Microsoft Excel is a powerful tool for linear and non-linear spreadsheet model optimization. However, automating the Solver via VBA can be awkward due to Solver's cryptic "functional" design, and the requirement that the Add-in must be installed (activated) before a VBA reference can be made to it (see Peltier Tech for details).

This repo offers a solution to automating Solver via VBA by means of an ActiveX DLL referenced from within your VBA projects. The DLL, compiled in twinBASIC, can either be installed/registered, or be called without registration if the use of IntelliSense and the Object Browser are not important.

This unique solution controls Solver by communicating directly with the SOLVER32.DLL, thus circumventing the SOLVER Add-in, and eliminating having to ensure that the Add-in has been loaded into Excel. Another advantage is that we can rewrite the user-DLL interface to add improvements such as a more flexible and easier to understand Object Model, an enhanced Solver callback protocol, and an event-model for monitoring optimization progress.

Features

• OOP design, making it easier to understand and code with
• Unique implementation that communicates directly with SOLVER32.DLL, bypassing SOLVER Add-in
• Capability to save intermediate trial solutions, as opposed to just one BEST solution (often more than one exists!)
• Enhanced Solver callback protocol
• Alternative event-based model for monitoring solution progress versus using the callback
• Other miscellaneous enhancements
• Help documentation is available in the SolverWrapper Wiki

Be aware that one disadvantage of marshalling communication directly with the Solver DLL (as opposed to the Solver Add-in) is that Solver Report creation is lost. This is because those reports were created by the Add-in, not the DLL.

Next Topic

The ActiveX DLL