Excel VBA: Process Modeling

Excel VBA: Process Modeling
Excel VBA: Process Modeling

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 2h 27m | 334 MB
eLearning | Skill level: Advanced


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.

Topics include:

  • 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
+ Table of Contents

1 Build effective simulations using Excel VBA
2 What you should know
3 Create a class module
4 Define class properties
5 Create an instance of a class
6 Create collections
7 Delete objects to free up memory
8 Define process flow and programming goals
9 Declare variables used in the simulation
10 Define the Customers class and add a customer
11 Define the Stations class and add a station
12 Add code to calculate and write out times
13 Delete unneeded objects
14 Run the simulation
15 Adapt the code for multiple runs
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
21 Define 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
26 Define 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
33 Define process flow and programming goals
34 Enable conditional station paths
35 Run the simulation
36 Read the simulation results as written
37 Manually reset the results cells
38 Summarize results using the Total row
39 Sort and filter Excel table values
40 Troubleshoot errors in your code
41 Next steps