Why not Automate your Daily Business Processes using your Existing Toolkits – Excel and Visual Basic (VBA)?

by | Jul 26, 2021 | Blog

You probably already have Excel and VBA in everyday use, let’s look and some of their advantages and disadvantages:

Excel is a great tool for simple processes, reporting, and if set up properly, for ensuring data integrity:

  • Excel is prevalent in most businesses, it is used daily for a wide range of functions (e.g., tracking inventory, collecting data, preparing reports, sales orders, purchase orders, analysis etc) and is widely adopted.
  • An Excel spreadsheet on a server can be accessed by multiple users – in such instances, particularly if the data could be considered “mission critical” it should be subject to some stringent user controls.
  • If properly set up, restricted user access to controls and logic is necessary to assist with data integrity.

Using VBA within Excel for automation and reporting:

  • VBA which is already a part of Excel, is a flexible solution to automate processes (e.g., tedious line by line or cell by cell manual data entry, collecting data from multiple sources and rearranging it into the required format for output, checking and analysing data for producing reports – all of which are error prone if performed manually). The automation routine to be performed on the data can be developed and customised to suit specific business needs – and is easily changeable as needs dictate. 
  • Inexpensive and easy to maintain/tweak– It is included with Excel.
  • Properly developed, the process automation will have an interface which is simple to use, error resistant and very user friendly with the user being able to simply “button click” in Excel.
  • All team members on the server or with access to the spreadsheet can make changes -shareable with other team members.

Let’s look at some examples of a business’s daily process that could be automated and streamlined using Excel & VBA to free up resources and prevent having to employ another system!  -see if you recognise any?

  1. Manual Data Entry (tedious and error prone)– could it be automated?
  1. 1 Aggregating Sales Orders

Sales Orders can come in multiple formats e.g., exported directly from a sales platform, received via email, or using an Excel sales order form.

Due to time constraints of receiving a sales order and to turnaround purchase orders (POs) to suppliers- all sales orders need to be integrated into the required format reflecting: item ID, price, Quantity, supplier etc.

  • An efficient way this can be done is to automate the integration process of these multiple sales order forms by using VBA and a point and click interface i.e., the format exported from the sales platform is automatically combining all sales orders reducing manual error.
  1. 2 Creating Individual Purchase Orders to send to multiple suppliers from a single data set.

You receive multiple Sales Orders from your clients for various products from various suppliers – you want to process each order in turn to create a single PO to a single supplier – a laborious and error prone task. Why not automatically create standardized individual supplier purchase order from a dataset (e.g., spreadsheet) of sales orders using VBA. This automated process could be further enhanced by including data integrity checks to ensure accuracy of information and that PO value totals are accurate in the individual purchase orders to the total to then be sent to suppliers.  

  1. 3 Automate the flow of data from one platform to ensure compatibility for importing into another platform.

Whether a platform is an in-house developed process or is a third party provided application your data is extracted from one platform in a certain format and may not be 100% compatible with the input format of another platform. (Where platforms aren’t connected such as uploading product inventory recorded in excel to a sales ordering platform, Account receivable information to provide to outsourced accounting company, manual inventory stock take to load into your inventory management system).

To manually manipulate the formats to make them compatible will typically require a trained person for an extended period, a robust process and to be error free.  Difficult to achieve – and the impact of errors on process time and the business needs to be considered – e.g., a new staff member is given the task.

What are your options – The best way is to automate – e.g., using Excel templates and/or VBA automation ensuring an almost instantaneous error free delivery, requiring minimal operator skills and resource commitment – resulting is greater efficiency and cost saving. ONE CLICK solution

2. Data Integrity for Strategic Processes and Reporting.

  • Strategic data using a Master Excel Spreadsheet: Many businesses use Excel to track inventory – it is readily available, relatively easy and can be set up to allow everyone to access the master inventory Excel workbook. With everyone accessing the same Excel workbook there can be pitfalls e.g., include overwriting pivotal information, duplicating codes/names, other human errors within the data- – and if the workbook is strategic to the business this can become mission critical at times. Clearly then, the utilization of the features of Excel such as access controls, terminology, discipline and functionally are important to prevent errors as well as to ensure data integrity.
    • As an example: – to confirm and flag if a product code is unique, simply set up a code provider (A data entry drop down feature) in Excel to provide unique codes for selection and reduce typing errors.
  • Using Excel to flag issues and data errors: If reports and/or data sets in Excel are properly set up, they can include “conditional logic” (this is where what is expected is not the actual case, so a warning message is triggered) to flag errors and issues such as:
    • Data Errors – incorrect postcodes used for a state, missing data, number inserted instead of text etc
    • Issues – e.g., a traffic light system to flag issues or overdues (e.g., payments, orders, deliveries if shipping method will meet required delivery date, flag costs as green if the cost is 10% less than last year, etc.)
  • Using VBA to automate manual data entry processes and producing reports reduces errors and allows data integrity checks to be put in place e.g., check totals of all individual workbooks add to the total of the aggregate data set.

3. Streamline Daily/Weekly Reporting

  • Most businesses use a daily or weekly reporting system as a status check, a nice to have would be if everyone could run their own reports when they wanted them – avoiding the need to ask the ‘guru’ and to then wait in line. This can be made achievable by setting up the right tools and automated reporting from your data.
    • Excel: For example, using Excel (tools such as pivot tables and formulas) can streamline and provide greater functionality to reports (set up for an individual’s requirements)
    • A further sophistication would be instead of (or as well as) generating multiple reports for multiple report requirements they could be condensed into a single report – which holds all the required data but then has the extra capability of refining the data view allowing it to have drill down and filtering capability making it much more useful.
    • VBA: a powerful feature of VBA is that “in the right hands” it can also be used to set up to customised /automated reporting – e.g. from a data set (Excel) and then into a pre designed template for Power Point or Excel based reports allowing reports to be more efficiently prepared and distributed without the need to have a high level of skills in this area.

What is the magic behind being able to achieve this?

  • Templates – Excel:

A properly set up data process will have collection/ input templates in Excel to ensure that the data is stored into required formats. This will typically have a data input section (with checks) and “behind the scenes” an automated process set up to transform the data into an output page ready to export and load into platforms or other reports. It should also identify and then add missing data from the original data set (e.g., Excel standard behind the scenes functions includes: HLOOKUPS, VLOOKUPS, conditional formatting, IF statements, filter formula).

  • Automation- VBA:

In legacy situations (we have always done it this way) where there are one or numerous files that need a dedicated resource to manipulate/check/edit all the data in all the files, VBA can be programmed to automate the process as well as including data integrity checks.

VBA can be programmed to manipulate numerous files at once into a report (numerical and/or graphic) with a combined view or can be separated into individual reports as required – see above.

Ensuring data integrity – Setting up Data Integrity in Excel

Data is critical, maximum efficiency, useability, trust, and visibility is only achievable if maximum data integrity is achieved so if follows that the ad-hoc support of a data expert is not a discretionary requirement. A data expert can quickly assess current data systems, jointly develop a vision of what is required and then implement and test the new process always being available to support as required.

  • Data Consistency
    • Format standardization – data validation formulas, conditional formatting.
    • Interface controls – editing restrictions (e.g., using drop down selections) and view only access to certain cells.
  • Breadth of data
    • Identifying if all data points having been captured from all sources – identify missing/incorrect values – data logic, conditional formatting, countif, sumif, filter function.
jQuery(function ($) { //open toggle on button click $('a.open-toggle').on('click', function(event){ $('#toggle3.et_pb_toggle_2 .et_pb_toggle_title').click(); }); });