Excel is one of the most frequently used digital platforms because of its wide-ranging capabilities, but it can take a bit of massaging and training to truly unlock its full potential and automate menial, repetitive tasks.
Visual Basic in Excel opens up a whole new world of automation.
Below, we’ll look at what VBA is and whether you should use it to automate Excel if you’re in retail or e-commerce.
What is VBA?
Visual Basic for Applications (VBA) is a Microsoft programming language that can be used to extend and improve the functionality of Microsoft products, including Excel, Word, PowerPoint, and Access.
In essence, it allows you to custom code specific add-ins, functions, and actions into your Excel worksheets.
The biggest benefit of VBA within Microsoft Excel is that it allows you to define and set your own automations.
By using VBA, you can automate repetitive tasks and the creation of unique forms, data reports, and dashboards.
VBA vs. Macros
The conversation here is less about VBA versus macros, but instead how they work together — since that ultimately is the relationship between the two.
Visual Basic is the programming language that sits behind all Microsoft applications. So, a macro is actually created, defined, and edited using VBA.
That makes VBA the key ingredient behind all of your Excel automations.
The possibilities of what you can do are close to unlimited, as long as you have the developer know-how to do so.
Let’s look at some common use cases as examples.
Common VBA functions
While there are plenty of very unique VBA automations you might identify over time, there are a number of common go-to automations that retail and e-commerce teams can use in Excel.
Excel VBA array function
The array function allows you to define any number of different elements that share the same intrinsic data type as related, or as one group/variable.
Arrays are most useful to manipulate data ranges and summarize large bulks of data more quickly.
For example, let’s say dairy products are one component of your inventory, but you have many different types of dairy products.
Using an array, you can establish “dairy” as the variable, whereas individual product names would fit in as elements within that variable — “organic 2% reduced fat milk,” for example, would sit underneath “dairy” as a sub-category.
So if you have, say, 28 different dairy products, instead of creating 28 different variables, you can create one variable comprised of 28 elements.
When building or automating formulas, you’ll be able to do so on a variable-level, without having to account for 28 separate elements. It makes data manipulation much easier and much cleaner.
Excel VBA remove duplicates function
The “remove duplicates” function within Excel is pretty self-explanatory. It helps you formulaically identify and remove duplicates from any specified range.
Using VBA, however, this process can be automated.
Perhaps you do regular inventory data uploads, and within that, certain parts of product names are going to be duplicated — for example, the brand name, color, or other specific descriptor of a product.
By creating automated duplicate removals via VBA, whenever you upload new inventory data, you won’t have to run a manual prompt to clean and consolidate your data.
Excel VBA VLookup function
The VLookup function allows you to search and identify a value from a specific column within a defined range, and return a corresponding value from a different column.
Using VBA, you can build VLookup functions into your worksheets to automate specific workflow steps.
A real-world example of using VBA VLookup in Excel would be to use it to fill in pricing data across invoicing and reporting — from individual orders, to full sales reports, profitability reports, and forecasting reports.
By building VLookups into your worksheets, you could automatically pull pricing data from an existing worksheets into new worksheets and reports.
Excel VBA else if function
In VBA, the "ElseIf" statement is used within conditional statements (like an "If" statement) to specify further conditions that should be tested when the original "If" condition is false.
Suppose you wanted to categorize purchases based on the geography of your customers.
Your initial “If” statement might say something like, “If location = New York Then,” in which case you’d follow that statement with a function or formula you want to be carried out.
The “ElseIf” statement would then follow that, working as a backdrop in the case that the original If statement is false (in this case, if the associated location on an order is not New York).
The ElseIf statement would essentially say, “if the location is not New York, then check if it’s [location B],” to which you’d then again define a function or formula you’d want to be carried out in the case that the second statement is true.
How to use VBA to automate Excel workflows
To automate Excel workflows using VBA, you need to write specific macros that perform the individual tasks or sequences of tasks needed to carry out your workflows.
In general, that requires you to utilize the VBA editor within the “Developer” tab in Excel, to insert a module, where you’d then input the code required to build and run specific macros.
Let’s look at some specific examples of how retail and e-commerce brands can use Visual Basic in Excel to get rid of inefficient Excel tasks.
Examples of VBA automation
1. Creating custom functions with VBA and automating simple data manipulations
Of course, it’s important to first understand the basics of VBA automation.
In the video below, you can see an example of how to create custom functions.
More specifically, the video shows two things:
- How to auto-apply discounts to individual customers based on their order quantity.
- How to auto-clear sales data periodically (based on your billing cycle).
These are both examples of how powerful VBA can be for invoice creation and automation.
If you’re on a weekly billing cycle, you can use VBA to clear old/unneeded transaction data each week.
When generating new invoices, based on quantity (and perhaps geography or other factors), you can automatically manipulate price totals based on the quantity and product type of each order.
2. Automating reporting processes with VBA
Another major timesaver is to automate reporting with VBA.
In this next video, you can see how to auto-generate a report based on a specific variable present in your data — that could be geography, product type, product name, or even individual items.
In this case, you’ll see how to do so by product name.
This is great for something like inventory on hand reporting. If you have 40-50 different product names, you’d have to filter and run through each to update your current on hand numbers.
With VBA, you can automate this process, so that, by simply running a macros, you’ll auto-generate as many on hand reports as you need, defined by as many variables as you need.
3. Automating email sending with VBA
Something else that is incredibly helpful for invoice automation and sending, is using VBA to automate email outreach to your customers.
You can see how to do that in the following video.
In the video, you can see how to use VBA to build a macro to build and personalize emails, add attachments, and auto-send emails without having to leave Excel.
So, after automating the creation of your invoice, you can also automate the sending of those invoices to your customers, or the re-sending of those invoices near or after their intended due date.
4. Creating a dashboard and automating transaction monitoring with VBA
VBA automations can also give you real-time visibility into your inventory and warehouse activity.
The video below shows how you can automate transaction monitoring and the creation of dashboards.
More specifically, the video shows how to automate the use of transaction forms to collect data that can then be used to automatically generate transaction dashboards and monitor activity.
This is also a tremendous help for inventory on hand reporting, as you can build live dashboards that get updated as transactions happen, instead of requiring more periodic (and more manual) audits.
Is VBA the right tool for your automation?
In the end, VBA is amazing for automating repetitive spreadsheet-based tasks and reporting.
The two major caveats, however, would be:
1. It requires coding knowledge. If your team does not have coding experience, VBA is likely going to be too challenging.
2. It’s not collaborative. While you can run macros when a file is shared, the code that’s been built to power the macro cannot be shared, nor can it be edited after the file is shared.
If either of these caveats are a detractor for you, you’d want to look for an alternative for your VBA automation.
Alternatives to VBA automation in Excel
Parabola works as a great alternative to VBA for retail and e-commerce companies looking to automate their Excel workflows.
Parabola allows you to set invoicing, reporting, and many other custom automations across your supply chain, no code required.
Setting up automations and workflows is made simple through customizable drag-and-drop modules.
As your business changes or grows, Parabola allows for easy and collaborative real-time workflow changes, with centralized access across teams. Your team can also build custom data visualizations and establish a single source of truth for your workflows.
Whatever the case, automating your Excel workflows is one of, if not the most immediate way to increase supply chain efficiency and set up your e-commerce engine to scale seamlessly.