How to conduct a simple operating system development with Excel
Many employees in companies use Excel to get things done. You collect those Excel worksheets, connect them by VBA, then you can make a simple operating system. This post introduces how to conduct this kind of development project.
(Duration: 3:51)
＜＜ Related Posts ＞＞
- Synergy Effect of Lean Six Sigma and Excel/Excel VBA
- 【VSM Case Study】Value Stream Mapping, Common Issues from SIPOC Analysis and VSM
How to make simple operating system program with Excel
Hi, this is Mike Negami, Lean Sigma Black Belt.
I talked about the synergy effect of Lean Sigma and Excel, and the simple operating system in Excel in the previous post. If you haven’t read it, please click the link below and read it.
＜＜ Related Posts ＞＞
Here is my basic development flow of a simple operating system program.
1) Collect best practices of the target process by the Lean Sigma method and complete designing the optimized target process.
2) Collect the Excel worksheets currently used in the process and improve and finalize each user interface.
3) Complete a simple operating system program using Excel functions and VBA.
I’ve just completed my personal project, so I’ll explain each step by using the project as a case study.
System Development Using Excel Case Study
1) Complete designing the optimized target process.
The first step will be finished when you complete a Value Stream Map of the new process. Here is our project’s VSM.
2) Improve and finalize each user interface.
In the 2nd step, collect all the Excel worksheets that they currently use for the target process and interview those operators about what they’re trying to achieve with each sheet. Even at this point you can improve each worksheet by considering the interview results and the new process design.
In my project, they look like this. Then, you should show the results to the managers and skilled workers and obtain approval. This is an important step.
3) Complete a simple operating system program using Excel functions and VBA.
In the final step, by using VBA, I make the process flow smooth and minimize operators’ operational burden. Let’s see how it actually works.
This program is an OS for the art production process flow. Here are step-by-step instructions. The operators work by following the instructions.
For example, since you enter a new production schedule first, you click this button, and you can enter the project name. Then, a new line is added. You type necessary information in the light blue cells.
Let’s say that this schedule was canceled. Select that line and click on this button, you are prompted to confirm deletion and click “Yes” to delete that line. Also, click this button, which brings you to the previous worksheet.
As you saw, you can program Excel to make it work for you any way you want. Typically, you click a button or double click a cell, then your program starts.
In addition, like this, you can make customized screen any way you want to for a better user experience.
We call codes for sentences of a program. I didn’t show VBA codes today, but I will introduce them someday in the future. I hope that that gets you to try mastering VBA.
However, if some of you may have thought that today’s contents were too difficult, I’m planning to make articles about useful tips for worksheets and Excel functions, first.
