Power BI or Excel VBA?

What is the purpose and difference between Power BI and Excel VBA * When to use what * Or maybe you can use both? * Here is my take on the role Power BI and Excel VBA can play in your setting.

Lucien, one of the professional Excel VBA experts following my Blog and courses approached me this week with this question.

He noted a shift from Excel to Power BI in his workplace and asked about my point of view on the two.

Lucien mentioned that he is more of an “Excel/VBA person” and that the Power BI reports seem slow and not interactive.

What a great question to call for a Blog post!

What is Power BI

Power BI is a configurator tool and open platform in the analytics and reporting domain.

It allows for “templating” dashboards and reports that source data from multiple and different data sources.

It is accessible from a Desktop application, a Cloud service, or a Mobile app.

Being focused on visualization of data, Power BI offers a rich and beautiful visualizations “templates” for presenting your data.

It also requires no programming expertise or any coding at all for the most part. You can configure beautiful visualizations over your data relatively fast and easy. The result will be beautiful!

As with most data visualization tools, the basic steps to follow include pointing at your data sources, modeling the structure, selecting, arranging data and the visualization template to use. Aggregation and custom calculated data fields are also part of the deal. Finally, you can publish and share the result with others.

Advanced users may be able to leverage languages such as DAX, M and SQL for sourcing data and manipulating the data.

AI algorithms may also be leveraged in projecting data sets before visualizing them.

However, it is important to remember that Power BI is all about presentation of data – not generation of data. It is a pure analytics tool, not an operational software.

If you want to take operational action based on what you see in a Power BI report or dashboard – you cannot click on it to open the software screen to update the underlying data source in context. The boundaries of Power BI as an analytics tool are clear cut.

The Power BI “magic” comes at a price, as it adds a sophisticated layer of functionality between the data and the resulting reports / dashboards. It is not up to you to optimize the way data is fetched, joined, arranged and staged for presentation, and it can become quite heavy.

What is Excel VBA

VBA is a programming language.

VBA offers access to the complete MS-Office suite of applications’ Object Model. This makes it easy and intuitive to manipulate and query MS-Office objects.

Excel objects examples are: Worksheets, Cells, Ranges, Charts, etc.

Outlook objects may be: Message, Meeting, Task, Attachment, etc.

PowerPoint objects include: Slide, Shape, TextBox, etc.

VBA can be used with any MS-Office application installed on your Desktop (on your machine) and cannot be run in the Cloud or on your Mobile device.

Being a programming language, VBA can be used to write (almost) any software you may need. This includes operational and analytics solutions.

From VBA you can also leverage thousands of code libraries readily available to extend your program’s reach and functionality. This include Microsoft DLL’s that are shipped with MS-Windows and third-party packages.

From VBA you can access the Internet using standard HTTP requests and write complex integrations across applications and web-services.

With VBA you can design beautiful and highly functional user interface for your business applications.

With VBA you have full control and flexibility on how efficient you fetch and manipulate data, you can customize visualization element to be exactly as you want and of course, cover a complete business application, end-to-end, with data entry, validation, data storage, transactions, reporting and visualization.

I guess the point I’m trying to make is that once you think of VBA as yet another programming language, such as Java, C#, Python, .NET and the like – you realize that you are only limited by your imagination, business requirements and programming capacity.

(You may be interested in reading another Blog post of mine: VBA as a Programming Language: Pros and Cons.

When to use Excel VBA and when to use Power BI

I trust the above definitions already clarifies some of the considerations in choosing between the two.

For anything that is not pure analytics and reporting based on existing data – Power BI cannot be used.

If you’re aiming to present data accumulated by other systems (Excel included), possibly make them accessible to others also in the Cloud or on Mobile – consider using Power BI. You will enjoy highly slick and professional visualizations out-of-the-box without programming a line of code.

If you are developing a hybrid solution that entails generating the data as well as presenting it – you can consider using Excel VBA with or without Power BI as the visualization cap.

As Excel data can serve as a data source to a Power BI model, you can implement the business logic and automation with VBA and have Power BI present the data with not much effort.

One way or another, remember the core difference between the two:

Power BI is a great data visualization tool.

VBA is a programming language.

Writing this Blog post I can’t escape recalling another Blog post I wrote a while back you may be interest in: Dashboards as a Trigger and Entry Point to Operational Transactions.

Please share this post with your colleagues so that our community of experts grow on.