Modeling for application: developing industrial strength simulation models using Visual Basic for Applications (VBA)

ABSTRACT

Since 1984 the author has developed simulation models that use input data from spreadsheets. These original applications used a standalone Basic program to convert Lotus 123® data into Siman Experiment Frames. While this process has evolved overtime, it did not reach a truly viable level until Arena® 3.0 introduced Visual Basic® for Applications (VBA) by Microsoft®. This advanced tutorial demonstrates the basic concepts developed by the author to transfer data between Excel® and Arena. The same techniques can be used to communicate simulation data with a wide range of VBA supported tools, such as Access®, AutoCAD®, and Visio®. Arena permits the model developer to use VBA as the model file is loaded, executed, or terminated or as entities flow through the Arena model modules. This tutorial focuses on the design of Excel workbooks for simulation applications and the transfer of data to/from Arena using VBA.

REFERENCES

• 1: Ken Getz , Michael Gilbert , Mike Gilbert, VBA Developer's Handbook, SYBEX Inc., Alameda, CA, 2001
• 2: Paul Lomax, VB and VBA in a nutshell: the language, O'Reilly & Associates, Inc., Sebastopol, CA, 1998
• 3: Marvin Seppanen, Designing Simulation Models to Use Visual Basic® for Applications (VBA), Proceedings of the 12th European Simulation Multiconference on Simulation - Past, Present and Future, p.100-104, June 16-19, 1998
• 4: Seppanen, M. S., 1996. A database approach to simulation modeling. In Proceedings of the 1996 European Simulation Symposium, 332-335 (Budapest, Hungary, June 2-6). SCS.
• 5: Marvin S. Seppanen, Developing industrial strength simulation models, Proceedings of the 27th conference on Winter simulation, p.936-939, December 03-06, 1995, Arlington, Virginia, United States [doi>10.1145/224401.224754]
• 6: Systems Modeling Corporation, 1999. Arena online help and smart models, Sewickley, PA.
• 7: Systems Modeling Corporation, 1999. Arena user's guide, Sewickley, PA.
• 8: Eric Wells, Developing Microsoft Excel 95 solutions with Visual Basic for applications (2nd ed.): development strategies using the Microsoft Excel for Windows 95 object model and VBA, Microsoft Press, Redmond, WA, 1995