If You Love Excel – Here is Why You Need to Learn VBA

You are good with Excel. You love it. It gets the job done. You even heard about Macros, or VBA, but pushed it aside. It is not for you, you said to yourself. It is too complicated, you thought. I am here to tell you: it is exactly what you need to do next. Here is why.

The plain motivation at sight

Would you skip learning how to drive a car had I told you it can get you to your destination hundreds and thousand times faster than your bicycle?! I don’t think so.

Operating a vehicle may sound difficult, if not frightening, if all you’re used to is riding your bike. Still – you would do what it takes to drive a car.

As an Excel professional, you understand numbers and formulas. So, let me frame it to you in a simple formula:

The straightforward motivation to learn VBA (or Macros – more about that later) is that you can achieve more with less. How much more? Tens and thousands of times more! In how much less time? Tens and thousands of times faster!

Only for that reason – you should want to embrace VBA and start today.

As simple as that.

But there’s more. Much more…

How can VBA help you with your Excel work

Automation

The immediate benefit that most people will tell you about is automation.

I’ll be careful to say that 99.8% of the tasks you normally do with Excel – can be automated. I’m not counting your thinking about how to phrase a sentence and the like, of course.

What are the most common tasks you do with Excel? Maybe:

  • Open a text file with data from another system and import it into your own model
  • Prepare tables and charts and embed them in a PowerPoint presentation
  • Consolidate data from multiple Worksheets/Workbooks into a summary ledger or report
  • Compare lists for any subsequent action
  • Copy data from a Website for further processing in Excel, on a regular basis
  • Copy data from a Google Sheet into Excel, or the other way around
  • Send emails with reports you prepared in Excel

I can go on and on and on, but I guess you get the idea.

With a click of a button, or triggered automatically by some other event, all these tasks can be done automatically in a snap. Really.

I challenge you to challenge me with a repetitive task you are doing with Excel that cannot be automated. Let me know: [email protected].

Beyond automation

I know most people stop at automation, and I will shortly explain why. I don’t.

I want you to think about any Desktop application, running on a Windows machine or a Mac.

It can be a software to manage your contacts, your booking, your customers (CRM), your invoicing, your lab protocols, your music, your production line, anything.

Now, describe what it does and what are the benefits of using it. Why not just manage whatever it is in a Word document, or even as a table in Excel? Why go through the trouble to buy, install, learn how to operate and backup data using a software someone else had designed and prepared for you?

Typically, most will offer the following benefits:

  • The data is organized in a way that makes it efficient to find and cross-match data
  • The user experience of updating data in the system is inviting, appealing and keeps me on track with what I am allowed and not allowed to do
  • I can produce informative reports with a click of a button
  • I get a visually attractive summary of what I need to know/do
  • I am compliant with regulation and legal requirements
  • The data is backed-up to the cloud automatically for me

You may have a couple of additional gains to add to the list – please do.

Now, here is the kicker: any of these software applications can be programmed to achieve the same benefits with Excel.

You heard me right.

I will put it differently. The person who wrote that application, chose to write it using a technology of choice. There are many technologies for writing computer programs. Excel VBA is one of them.

He could have chosen Excel VBA, but for reasons I explored in another post, he chose something else. That’s it.

What does this tell you?

It tells you that you can write computer applications with Excel VBA.

I’m talking about real-life, mission-critical business applications, not just a recipe directory.

To make the point, consider some of the business applications I delivered to customers running on Excel VBA (some also use an external Database):

  • Complete training, certification and competency management for a company employing hundreds of employees
  • Manufacturing software from receiving the order, down to the production line, including real-time production reporting and QC
  • Banking solution to manage house loans (mortgage) and investments to fund those loans
  • Presales management of software solutions
  • Complete sales management of a company employing 15 salespersons, including pipeline and dashboards

From now on, when colleagues talk to you about automation with VBA, tell them: automation is just the beginning. I can produce business applications with VBA, too!

What is the difference between VBA and Macros?

For this, we need to head back a couple of decades before Excel came to be.

The first popular electronic spreadsheet software was called Lotus 1-2-3.

For efficient use, they associated a letter with each menu-item (or action), so that you could hold the “Alt” key while running a series of keys on the keyboard to quickly invoke actions. For example, to center a label in a range, you would use the following menu entries: Range -> Label -> Center.

To speed things up, you could hold “Alt” and tap on the RLC keys in that order – and voila!

Lotus 1-2-3. Photo credit: zdnet.com

By the way, this is still supported today in Excel – you have keyboard shortcuts for most menu items. Hold the “Alt” key for a while and see what comes up on the Ribbon menu. Alt+FO will File->Open for you.

Later, to allow automation, Lotus introduced Macros. A macro was simply a sequence of “Alt” keystrokes, stored in a cell, with the forward slash replacing the “Alt” key. For example, we could store /RLC in a cell, and even associate a keyboard shortcut to run this macro, and this would be run for us every time we trigger that Macro.

Other symbols were added to simulate non-menu actions, such as pressing the Enter key (Tilde sign), arrows ({RIGHT} to hit the right arrow once) etc.

The macro would run down the cells until an empty cell is found, such that complex automation could be programmed with such a macro.

By the way, how do I remember all these prehistoric examples? I don’t. I just grabbed a book I co-authored 20 years ago in which I taught how to right Lotus 1-2-3 Macros!

As Lotus 1-2-3 (and later its other contesters: Quatro Pro and Microsoft Excel) became widely used and popular, the term “Macro” stuck and it became synonym with Spreadsheet automation.

Fast forward to Microsoft Office suite of applications, Microsoft decided to build on top of its popular programming language VB (Visual Basic) a flavor that will be used by its Office suite of application: VBA – Visual Basic for Applications.

This turned Excel (and the other Office application) almost instantly into a programming capable platform.

VBA is a full-fledged programming language, so much so, that it is the language used in MS-Access, an Office component designed for business applications development.

VBA can also call other, external functions and services, either provided out-of-the-box with the Windows operating system, or even 3rd-party libraries. This is how you can engage many non-Excel services in your VBA program, such as working with the file system, the Internet, and your computer settings.

Why most people stop at automation when they think about Excel VBA?

I hope now you can appreciate how irrelevant the term Macro is nowadays, and what a disservice the concept of Automation does to Excel.

For reasons beyond the scope of this Blog post, Microsoft never positioned VBA as a strategic technology with the developers community.

VBA was intended for their Office applications, and that is the boundaries it was kept by, despite its growing capabilities of yet another computing language.

Since Microsoft never educated the knowledge worker (to whom Office was targeted) about computer programming, this huge crowed was taught father to son about Macros and automation.

They are told Macros are now written with this beast called VBA, but they have no idea they are actually learning to use a rich computer programming language (and they usually don’t learn proper programming, as a result).

How big is the jump? What does it take to start enjoying the VBA benefits?

The beauty with VBA is that it is not all or nothing. You even have a “Macro recorder” to produce your first VBA code out of your regular Excel activities, without you typing a single VBA command. By the way, when a programmer says “code”, he usually refers to the programming language statements, or the “program”. The program is the “code” given to the machine to crunch.

On the flip side, since most consider VBA to be just automation script, they skip learning computer programming, thus acquiring limited skill to do just… automation, and mostly, not producing a particularly good code.

With all modesty, I have not yet found a single Excel VBA course that actually teaches computer programming, besides the online program I offer. If you found one – please let me know so that I can amend this statement.

Regardless, taking any rout of choice, I can say that with confidence: As you have enough technical attitude to have learned Excel, its formulas, logic and working with Excel models – you have enough to be successful as a VBA programmer.

You just need to make a choice – and as I said in the opening: it’s a no-brainer that you should!

I suggest you plan to conquer VBA and you will not believe how you have worked all those years without it.

While you are making your own plans to become a VBA programmer, be a sport and send this to all of your Excel lovers colleagues and friends – they will thank you!

WANT TO LEARN PROGRAMMING WITH EXCEL VBA?

Join today to the Excel VBA Inner Circle with Mor Sagmon.

Get a weekly lesson, weekly live Q&A, monthly deep-dive workshop, monthly challenge and other activities.
 
Click the button below to learn more and join.
Yes, I want to become a top Excel VBA Expert
Close

50% Complete

Two Step

Once you submit your details, you'll receive an email with a confirmation link. That's it! you're subscribed!