Excel VBA: Process Modeling

Learn how to use Excel and VBA for business process modeling. Find out how to create and run simulations for customer flow, queuing, and manufacturing.

Introduction

• Build effective simulations using Excel VBA
• What you should know

1. Define Necessary VBA Constructs

• Create a class module
• Define class properties
• Create an instance of a class
• Create collections
• Delete objects to free up memory

2. Develop Process Control Structures

• Define process flow and programming goals
• Declare variables used in the simulation
• Define the Customers class and add a customer
• Define the Stations class and add a station
• Add code to calculate and write out times
• Delete unneeded objects
• Run the simulation
• Adapt the code for multiple runs

3. Add a Ticker to Track Time

• Define process flow and programming goals
• Create an outline of If and For Next statements
• Add instructions within the framework
• Add code to write out simulation results
• Run the simulation

4. Add Customers during the Simulation

• Define process flow and programming goals
• Use the Poisson distribution to calculate arrival times
• Add customers to the simulation
• Add code to write out simulation results
• Run the simulation

5. Modify the Simulation

• Define process flow and programming goals
• Add capacity to each station
• Run the simulation with added station capacity
• Add more stations to the simulation
• Run the simulation with additional stations
• Add rework loops to stations
• Run the simulation with rework loops

6. Allow Conditional Processing

• Define process flow and programming goals
• Enable conditional station paths
• Run the simulation

7. Analyze Simulation Results

• Read the simulation results as written
• Manually reset the results cells
• Summarize results using the Total row
• Sort and filter Excel table values
• Troubleshoot errors in your code

Conclusion

• Next steps