Why all professionals should learn VBA scripting

Why should you learn Visual Basic For Applications?

In 2023, just about everyone in the professional world uses Microsoft Office in some way shape or form. It's definitely useful, there's no doubt there.

The functionality within the office suite is innovating, becoming more useful over time.

But did you know that all microsoft office applications have Visual Basic for Applications (VBA)?

VBA is a programming language used to automate tasks, processes, and procedures within all of the MS office products.

Here's 8 reasons (in no particular order) why I believe it's useful for professionals (engineers in particular) to learn how to use VBA:

Reason #1 : Creation of custom functions

One of the most useful things you can start off doing with VBA is creating your own custom functions in Excel.

Do you have to work on certain types of data and you just wished you had a quicker way of getting through it?

Does having to add the value of one cell with another and then the next thing divided by the next, multiplied by a constant get a bit repetitive?

There's obviously a lot within Excel's built in functionality that can support the user on doing these tasks quicker but VBA also always you to create custom functions making this very easy to work around.

This is great for scientists and engineers who have to work with complex formulas for their jobs - as an example.

Reason #2: Automate repetitive and mundane tasks

Automation is what VBA is about and where it becomes really useful.

Do you need to write / create thank you letters for each of your staff members? and do the letters in such a way that they don't look like they have been created from a template?

Do you need to rename a bunch of files in a folder using a new naming convention, or using the names in an excel table?

What about importing a bunch of images into comments or cells in an excel document?

These are just a couple of simple and easy automation tasks that can be accomplished with VBA.

Reason #3: "Talk" to other applications

As mentioned, the great thing about VBA is that it is built into each of the Microsoft Office applications.

You can use a table of data in excel to create a long form document in Word, or populate a PowerPoint slide deck.

Scripts can be created to retrieve data from a database, online or on a local server using SQL.

If you're a mechanical or industrial, or design engineer, the same applies to SolidWorks. You can keep stuff that you don't want the rest of your team playing around with such as file naming conventions, and other process knowledge stored on a database, and have it be retrieved by SolidWorks when it's needed.

Reason #4: Scrubbing data

Do you have to work with huge amounts of data in your work?

Are you using data from an acquisition system, or off a machine of some kind?

The great thing is if you know the exact steps needed to follow in order to clean that data, excel VBA will do it for you with ease.

Imagine having 6000 rows of data and having to remove any rows that containing a number less than or equal to 0?

VBA will do this for you in seconds. It will be able to give you feedback on exactly how many rows it removed, so you can then determine how many errors were in your data. This could then be used to benchmark in process improvements in the future.

Reason #5: Checking the integrity of your data

As well as scrubbing data you can also check how good the data set actually is against some pre-defined criteria of your choosing.

Is the data starting to deviate from where it should be?

Does the data follow a trend?

Another example would be if there are 6000 rows of data and theres the same value repeated on 6 consecutive rows, that could be an indication that something is wrong with your data acquisition system. Excel can be programmed to highlight, or correct this data as needed, and again could keep track of these errors or deviations to help the your organization track improvements in the acquisition methodology.

Reason #6: Dassault Systemes also included VBA in SolidWorks

If you're a an engineer, or use SolidWorks in your work, it's a good thing to know that Dassault Systemes has also included VBA built into their flagship software platform.

How does this benefit parametric 3D modelling ?

In many ways.

Firstly, because it's in Microsoft Office you can get SolidWorks to talk to them.

Create word documents with drawing / model views, Excel documents or tables with part details, automate some of the task of creating specification documents, shop drawings, lay flat profiles for laser cutting and so forth.

You can also generate a part barcode for the parts you have just designed and have them printed out at the click of a button.

You can also develop a part / file naming convention for your design office and have all the Design Engineers use this convention.

There's a lot that can be done in this space, thats for sure!

Reason #7: It's a legitimate programming language

VBA is easier to learn compared to other programming languages because it has direct application to what you're working on. You can see the results of your code on your Excel data or word document in real time. We all know it's easier to learn something by doing than it is to memorize theory and information alone.

Visual basic for applications has been around for a long time. It is not a popular programming language, but it's a great gateway drug into the world of computer programming as all the concepts learned in your VBA endeavors can be transferred into learning another language as the concept and control structures are basically the same across all languages, it's the differences in syntax that you mostly have to learn as you pick up new programming language from there-on.

Learning VBA will not only make turn you into an MS Office power user but also prepare you to learn other languages that may be helpful to you in your career.

Reason #8: Future proofing

ChatGPT, Google Bard, you name it, it's the wild-west, there's so much AI stuff floating around and it's is here to stay.

It will make our lives better.

It's ongoing development will also bring about drastic change to the way we do work.

I believe we will need to understand how to code to be able to leverage AI to it's full capacity as part of our jobs in the future.

I say this because AI at this point relies on being prompted the right way in order to provide a thorough and useful answer.

A well written script will allow the user to work through large amounts of data, and therefore query ChatGPT or such like in a very short amount of time leading to higher productivity.

Final thoughts

I hope you found this useful, and this article inspired you to start learning.

There's a ton of information out there to get you going on VBA.

YouTube, StackOverflow, and millions of websites. That's how I learned.

I mostly learned by doing and having juicy problems to solve or projects to work on over the years.

Lots of people share their code online too.

There's nothing stopping you from getting started!