Download this Paper Open PDF in Browser

Implementing Large-Scale Optimization Models in Excel Using VBA

Interfaces, Vol. 37, pp. 370-382, July-August 2007

24 Pages Posted: 31 Jan 2008  

Larry J. LeBlanc

Vanderbilt University - Operations Management

Michael R. Galbreth

University of South Carolina - Moore School of Business

Abstract

We discuss the importance of spreadsheets for optimization modeling, including a description of their limitations for large-scale problems. We then describe efficient ways to overcome these limits. Our approach makes use of Excel's standard functionality but augments Excel with its programming language, Visual Basic for Applications (VBA), where necessary. We show how using VBA within Excel to generate and solve large linear programs (LPs) overcomes many of the problems inherent in purely spreadsheet-based models and greatly increases model usability. The techniques described were instrumental in our successful development of a large-scale procurement/distribution LP that resulted in savings of approximately $1,000,000 in the first year, with even greater annual savings expected in the future.

Keywords: OR/MS implementation, spreadsheet optimization, visual basic for applications

Suggested Citation

LeBlanc, Larry J. and Galbreth, Michael R., Implementing Large-Scale Optimization Models in Excel Using VBA. Interfaces, Vol. 37, pp. 370-382, July-August 2007. Available at SSRN: https://ssrn.com/abstract=1088420

Larry LeBlanc (Contact Author)

Vanderbilt University - Operations Management ( email )

Nashville, TN 37203
United States

Michael R. Galbreth

University of South Carolina - Moore School of Business ( email )

1014 Greene Street
Columbia, SC 29208
United States

Paper statistics

Downloads
807
Rank
23,919
Abstract Views
3,258