Excel VBA: Process Modeling (2019)

Course details

Excel power users: Learn how to use Excel and Visual Basic for Applications (VBA) to create and run simulations of business processes for customer flow, queuing, and manufacturing. VBA exponentially extends the power of Excel, allowing you to control process flow and logic and add custom classes and variables. In this course, Curt Frye demonstrates how to build simulations of increasing complexity, which model the flow of customers through a multi-station process. Learn how to calculate arrival times, add customers, increase individual station capacity, and enable conditional station paths. At the end of the course, Curt shows how to convert output to an Excel table and analyze the results of your simulation.

Learning objectives
Creating a class module in VBA
Defining class properties
Creating collections
Describing process flow and programming goals
Creating loops
Increasing capacity of a model
Running simulations
Analyzing simulation results

Concepts

Introduction

Build effective simulations using Excel VBA
What you should know

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

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

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

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

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

Allow Conditional Processing

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

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