Excel-VBA automation

What is it and what are the benefits for your company

A very recurring image when looking at computer screens in any company is an Excel sheet. Virtually everyone uses or has used Excel! Despite the large amount of specialized software available, Excel is a tool that we all already know when we start our working lives. Excel has become universal and it is understandable that we use it frequently in our work.

Opportunity to Improve

So far so good, however, the fact that each employee of a company carries out their work or part of their administrative tasks in private Excel files stored on their machines has its risks and disadvantages, it is in any case something that could be greatly improved. But anyway, if something works, we are going to keep doing it, right? A dangerous idea that is often thought of is "if it ain't broke, don't fix it." Many companies have ended badly because they didn't "fix" what wasn't "broken." Just because certain processes have worked well and proven to be successful doesn't mean they shouldn't be improved. However, breaking this paradigm has its price, keeping up with technology is difficult, often not even the largest companies can keep up.

An Option to Consider

That is when the alternative could be, paradoxically, to continue using what we already have but making the most of it. Excel is a ubiquitous and cool tool, but most people only use the basic functions of Excel or Office in general. A few have more advanced skills like PivotTables or Macros, but hardly anyone takes full advantage of the fact that MS Office comes with a built-in programming language.

Automation and Familiarity

A VBA (Visual Basic for Applications) application has the power to bring automation to the most basic tasks of managing your business, while remaining familiar and easy to use for users. There is practically no limit to what can be done with VBA, in theory a complete ERP could be created from scratch. However, the idea that at Micronics.MX we propose is to take advantage of what has been created in a natural way in your company. This is to reduce time and costs, and to generate a tool with which people are already familiar from its conception and development.

Time Optimization

Surely your company already has all this:

• Forms that require completion and printing or mailing.
• Spreadsheets with formulas and data that need to be captured to later generate graphs, reports or indicators.
• Hundreds or thousands of Excel workbooks with daily or weekly information on the operation.
• And a long list of etceteras involving Excel files and a lot of hours invested.

Much of the capture in the previous points consists of transcribing information that already existed elsewhere. In other words, it is actually a transfer of data, whether it comes from another file, other software or some sensor or any other hardware device. It is also common for a good part of the day to be spent executing mechanical and / or repetitive tasks. Optimizing the time spent on all of this is perhaps the most immediate and direct benefit of VBA, since basically all repetitive tasks and all data movements can be automated.

Unified Database

But that's not all, imagine now that all that ecosystem of files you work with could be linked to a database on a SQL server. In this way, the information would seem to flow from one file to another, while in reality it would be stored in an orderly way in one place, safe and backed up, instead of being dispersed on the machines of all the company's employees. Some of the advantages of this are:

• Ensure the integrity and continuity of the information.
• Allow several people to work simultaneously on similar files, without the risk of generating two different data streams.
• In addition to eliminating countless hours of copy-paste, the errors that this entails are also eliminated.
• Enable reports and indicators that involve information from more than one file, user or process.

Additional functions

There are many other functions that could be added to a set of Excel files with VBA, it would be impossible to list them all, these are some:

• Printing or mailing with just one button or automatically.
• Acquisition of information from other sources, such as other systems (ERP or eCommerce for example), files with other formats or web pages.
• Interaction with other programs so that they also perform tasks.
• Execution of automatic tasks triggered by company processes or always carried out at a certain time
• Automatic and real-time generation of reports

Learning online

Another advantage of Excel-VBA is that it is very easy to find high-quality material to learn online. If you want to learn, we recommend this course from the University of Colorado at Boulder.

Specialized Program - Excel / VBA for Creative Problem Solving

Optimize Your Excel Spreadsheets With VBA. Learn how to automate your Excel spreadsheets with Visual Basic for Applications

Do you think that it is likely that some of your business processes or activities of your collaborators can be optimized with Excel-VBA ?, Write us an email to info@micronics.mx and we help you identify the tasks that can be automated and we make a proposal to carry it out.

Share if you like it