Excel VBA: Process Modeling

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 multistation process.

Learning objectives
Critically assess and describe the process flow and programming goals for various simulations.
Develop and implement class modules, including creating instances, defining properties, and managing object memory.
Analyze and join multiple data sources to identify and remove duplicate records, and implement data filters.
Design and code custom simulations, including adding customers, defining stations, and incorporating advanced features like rework loops and conditional processing.
Evaluate simulation results and troubleshoot errors, including summarizing and sorting data within Excel.

Concepts

0. Introduction

01 - Analyze your business processes using Excel VBA
02 - Gain insight into your business through simulation

1. Defining Necessary VBA Constructs

03 - Create a class module
04 - Define class properties
05 - Create an instance of a class
06 - Create collections
07 - Delete objects to free up memory

2. Developing Process Control Structures

08 - Describe process flow and programming goals
09 - Declare variables used in the simulation
10 - Define the Customers class and add a customer
11 - Define the Stations class and add stations
12 - Add code to calculate and write out times
13 - Delete unneeded objects
14 - Run the simulation
15 - Adapt the code for multiple runs

3. Adding a Ticker to Track Time

16 - Define process flow and programming goals
17 - Create an outline of IF and For Next statements
18 - Add instructions within the framework
19 - Add code to write out simulation results
20 - Run the simulation

4. Adding Customers during the Simulation

21 - Describe process flow and programming goals
22 - Use the Poisson distribution to calculate arrival times
23 - Add customers to the simulation
24 - Add code to write out simulation results
25 - Run the simulation

5. Modifying the Simulation

26 - Describe process flow and programming goals
27 - Add capacity to each station
28 - Run the simulation with added station capacity
29 - Add more stations to the simulation
30 - Run the simulation with additional stations
31 - Add rework loops to stations
32 - Run the simulation with rework loops

6. Allowing Conditional Processing

33 - Describe process flow and programming goals
34 - Allow conditional station paths
35 - Run the simulation
36 - Allow other employees to help the process
37 - Run the simulation with helpers
38 - Add time for customers to complete the process

7. Analyzing Simulation Results

39 - Read the simulation results as written
40 - Manually reset the results cells
41 - Summarize results using the Total row
42 - Sort and filter Excel table values
43 - Troubleshoot errors in your code

Conclusion

44 - Further resources