One Button to Rule Them All!
How to make Excel do everything you want, even start your car, with the click of one button.
Most people that use Excel daily, do repetitive tasks that could be automated. While you can write down your work flow, and use Excel Macros to automate each task, the real power is when you lash these tasks together. Then your whole day is condensed one button. Sounds impossible? Maybe not.
You may have heard that Excel can bundle tasks into things called “Macros.” These are recorded actions. What most people don’t know, is that all these actions can be bundled into a single button stroke.
People who use Excel for most of their day, fall into two types. Runners and Interrupters.
Runners tend to process data with Excel. They import a file, move some columns around. Delete some rows. And generally massage the data. When these tasks are always the same, and follow some rules, Excel Marcos can be used to automate each task. Then all the Macros can be put together. The result is that Runners can automate their whole day. Or at least the data processing part.
Many of my clients do this — they are Runners — processing data all day.
For example, I have a debt collection client. All day they receive data from hospitals, medical clinics and doctor’s offices of people who owe money. These files need to be converted into a standard format to feed into their collection software. Getting data from so many sources, then converting them, was a time-consuming task.
Working with them, I began to see patterns in how the data came in. Every file has a debtor phone number, a debtor name, an account number. Once the pattern was known, a Macro could import all the files received, and process them.
The final Excel sheet had just one button. And that one button would open all the files in a folder, identify which company the file came from, and grab the right data. The end result, was all the files in the target directory were processes — while the employee sat back and watched.
Well actually, the employee did other things while Excel processed the data in the background.
And that’s my point. Runners are people where the Excel file is automated to the point where the button just says “RUN.” Once pressed, Excel runs without user interaction.
But not everyone works that way. Sometimes humans need to interpret that data. I have an insurance company like that. Excel can process the data to a certain point, then it needs to stop for a human to eyeball the data file.
Some people have a workflow where the data processing is “interrupted.”
In this case, automation still works, but the button needs to be pressed more than once. Take our insurance client. They press the button, and a data file is imported. Then the automation stops while they look over the data file. When they press the button again, it automates step two. The user looks over the data after that step. If all looks good, they press the same button again, and automation continues.
Normally this kind of automation has an “Abort” button to clear the tasks and start over if the data file is unusable. Even with interruptions, automation still saves the user gobs of time, and reduces errors. Get the rules right, and let Excel do the work.
Here is how the One Button works — to rule them all.
Let’s say we recorded three Macros. We name them Macro1, Macro2 and so on. I know — really creative right! Now we add a button to our Excel sheet. And that button points to a Macro called “Button Rule them all.” The code looks like this:
Sub Button_Rule_them_all _Click()
The above code works for Runners, and is slightly more complicated for Interrupters. We just need to break after each call with a statement that checks an internal variable to see which automation tasks are left to be done.
So there you have it. How to automate your workflow. And yes, I was kidding about starting your car. Excel can only do that with some robotics, and your remote car starter … but that’s for another article.
If you want to know more,