VBA vs Python in Excel – What You Need to Know

As a Microsoft Excel trainer, I speak regularly with professionals seeking to level-up their data analysis skills. Some of these professionals are looking to secure formal data roles (e.g., data analyst/scientist), but most are looking to have more impact at work in their current role by improving their data analysis skills. One of the most common questions I receive from these professionals is whether learning Visual Basic for Applications (VBA) or Python in Excel is the best use of their limited time. Since you’re reading this blog, I will assume you have the same question. 

Let’s dig into it. First, here’s how to use this blog post most effectively:

• If you’re new to VBA, keep reading.
• If you’re familiar with VBA, feel free to skip ahead to Understanding Python in Excel.

Understanding VBA

VBA was added to Microsoft Excel back in 1993 and was an immediate hit, making it simple to automate manual steps in common business processes. VBA essentially turned Excel into an application development platform with the ability to streamline repetitive tasks. It’s no surprise why VBA code in Excel became a go-to tool of organizations both large and small. 

Even today, many business processes are not automated using IT systems – they still have one or more manual steps. It’s common to use Microsoft Excel as the “glue” between IT systems. For example, data is recorded and managed in Excel and then periodically loaded into an IT system. It’s also common to see entire business processes designed around Microsoft Excel workbooks because of the low barrier to entry.     

An Excel Automation Example

To develop an intuition about Excel automation, I will offer you a contrived example – automating the process of adding a new worksheet to an Excel workbook.

Let’s say that our business process needs the following to happen within in Excel:

• New worksheets need to be added to the end of the worksheet list.
• New worksheets should be renamed.

VBA can help make sure that the above happens by writing code that responds to users performing the processes within Excel. Here’s how.

As a VBA coder, you can provide programming instructions within Excel that are followed whenever users add a worksheet. Here’s the code:

Fig 1 illustrates how code can automate the business process by making sure new worksheets are added to the end of the list and popping a message box to the user reminding them to rename the worksheet.

Let’s see how it works. First, a user clicks the plus symbol in Excel to create a new worksheet:

Behind the scenes, the VBA code is executed, and the new worksheet is added to the end of the list:

Lastly, the message box to the user:

While this is a contrived example, it does demonstrate the power of VBA. Just about anything you can think of in Microsoft Excel can be automated using VBA.

It’s About Extending Excel

The power of VBA doesn’t stop with automation. You can also use VBA to extend Excel’s functionality. For example, creating custom functions:

While the VBA code of Fig 5 is another contrived example, it demonstrates the power of VBA to extend Excel’s functionality.

The CalculateProfit() custom VBA function is now seamlessly available to Excel users:

VBA is a general-purpose programming language. You can use VBA code to extend Excel and accomplish just about anything you can think of.

Understanding Python in Excel

Enter Python in Excel. The easiest way to understand Python in Excel is to compare it to VBA. 

As an aside, this is also a great way to understand Python in Excel compared to other programming languages available within Microsoft Excel, including M (Power Query) and Data Analysis Expressions (DAX).

It Runs in the Cloud

VBA code is embedded within Excel workbooks and runs locally on a user’s laptop. By way of comparison, Python in Excel is designed to run in the Microsoft Azure cloud, as illustrated in Figure 7.

While the Python code, data, and results are stored within the Excel workbook, the processing does not happen locally.

This is a good thing. Here’s why:

• As an Excel user, you do not need to worry about installing Python on your laptop and all the complexity that goes along with it.
• You can share the results of your Python in Excel code with other Excel users, and they don’t need a local Python installation – or access to Python in Excel at all!
• Python in Excel makes it easy for your IT department to say, “Yes, you can have it.”

For so many professionals wanting to make a bigger impact applying their data analysis skills, that last bullet can’t be underestimated.

Note: If you are a programmer who greatly prefers or needs to work locally, check out the Anaconda Code functionality, an offering within the Anaconda Toolbox add-in (currently in beta). 

Which leads me to the next difference between VBA and Python in Excel.

It’s Secure

VBA is a proprietary solution provided by Microsoft. Microsoft built VBA and they know everything about its internal workings – including VBA security.

Python is a lot more complicated. As an open-source programming language, ensuring that Python in Excel is secure for Microsoft’s many customers is a big challenge.

As detailed in this Microsoft article, a lot of thought has gone into Python in Excel’s security. Here’s a short summary of what’s in the article:

• Python in Excel code runs in an isolated environment (i.e., a secure container).
• Anaconda provides a curated set of secured libraries.
• Python in Excel cannot access files on your computer.
• Python in Excel does not have network/Internet access.
• Python in Excel cannot interact with Microsoft Excel.

Running Python in Excel in the Azure cloud allows for a controlled, secure environment. 

Microsoft Azure is used by many of Microsoft’s customers who are hyper-focused on security (e.g., large corporations, governments, hospitals).  This is even more supporting evidence for your IT department to say “Yes” to Python in Excel, especially now that Python in Excel has entered general availability.

In comparing VBA to Python in Excel, the inability for Python in Excel to interact with Microsoft Excel (e.g., change formatting or functions) is of particular importance.

It’s About Analytics and Data Science

By design, Python in Excel cannot do something that VBA was specifically designed to do – interact with Microsoft Excel.

At a high level, you can only do two things with Python in Excel:

• Push data into Python in Excel via the new xl() function.
• Get data out of Python in Excel (including images).

So why would you want to use Python in Excel? Consult Figure 8, which highlights some of the most popular use cases of Python and their related packages:

Python is the de facto standard for real-world analytics and data science. 

Python in Excel is all about providing millions of professionals like you access to analytics that are just not possible with out-of-the-box Microsoft Excel.

For this reason, I believe that Python in Excel is the most significant Excel feature ever. 

Descriptive Analytics Is Not Enough

There are 700+ million Excel users worldwide. Only a small percentage of them really need to automate Excel or perform “real” analytics, or what we data nerds call “descriptive analytics.” I’m talking about analyzing data far beyond what’s possible with PivotTables. Most Excel users don’t really analyze data; most use Excel primarily for reporting. 

I need to be crystal clear on this point. To truly level-up your data analysis skills, you must have descriptive analytics. Leaders need to know what happened last week/month/quarter/year in order to drive business goals. This is why so much time is devoted to reporting.

As my favorite data author wrote, “Description is not analysis.” This is the value of Python in Excel.

When to Learn Python in Excel

If you answer “Yes!” to all the following questions, then learning Python in Excel is right for you:

• Am I constantly asking myself, “Why is this happening?” but I’m unsatisfied with the “answers” that others or I come up with?
• Do I want to differentiate myself from my peers with my data analysis skills?
• Would new and powerful insights delight my leaders and stakeholders?
• Is your long-term career goal to secure a formal data role (e.g., data analyst/scientist)?

One last thing I need to mention. Easily 99+% of the Python in Excel code you write is the exact same code used for analytics regardless of the Python tool. Learning Python in Excel is the first step to expanding your data analysis skills across a whole range of tools. To start, you can do real-world analytics and data science using Python in Excel.

What’s Next?

If learning Python in Excel is right for you, check out the following Anaconda blog series to help you jumpstart your skills with this powerful new Excel feature:

• Python for Excel Analysts
• Visual Data Analysis with Python in Excel

Stay tuned for more content from Anaconda on how to use Python in Excel to analyze real-world data, craft valuable insights, and have more impact at work. Check out Anaconda’s Python in Excel blogs, resources, and learning courses.

Until next time, stay healthy and happy data sleuthing!

________________________________________________________________________________

Dave Langer is a Microsoft MVP, LinkedIn Top Voice, and YouTuber who has helped thousands of professionals build real-world data science skills. Before founding Dave on Data, Dave delivered insights that drove business strategy at Schedulicity, Data Science Dojo, and Microsoft.