VBA not dead, yet.

VBA, from what I see, is still used here and there.

Before Power Query had come to Excel I used mainly VBA macros to perform data cleansing and transformation. Hours of coding…

Now I can’t believe I had to fight with IT department to get Power Query addin installed (when it was a standalone add-in. Now it is a built-in part of modern Excel).

Long story short. Now it is Power Query that helps me with data transformation, cleansing, and consolidation.

I built several dozens of workbooks that transform and consolidate data from various sources.

Once extraction and transformation problem is solved comes another thing – how to automate update of all those workbooks? Obviously, I don’t want to open each manually and hit Refresh button every day.

Using VBA, I developed a solution that helps me to update Excel workbooks automatically on schedule – check https://excel.city/excel-automation/ if interested how I do this.

Why not to schedule in Power BI?

“What the hell?” – one may ask: “Create Power BI datasets, schedule refresh in Power BI service, use Gateway when needed, and voila”.

That’s in theory.

Firstly, we need to keep in mind that some organizations prefer to store everything within corporate network only.

Secondly, Excel still can handle more scenarios than Power BI. Or maybe not more, but many scenarios, that Power BI can’t perform. Due to Excel’s extensibility with add-ins and embedded programming language – VBA (Visual Basic for Applications).

Thirdly, far not everything is stored in the databases and accessible from Power Query – the main engine used to pull data to Power BI data models.

Excel add-ins and VBA help to fill that gap quite often for me.

As an example, in Excel I can dynamically change “Named Range” parameters for Power Query queries using formulas or via VBA.

Or, let’s say, you need to pull data sitting in SAP and for some reason not available in corporate BI system – here comes in handy SAP GUI scripting, which can be executed in Excel via VBA.

In the end, through many years, BI solutions come and go. Excel stays!

And if you want Excel to become even better, post your ideas or vote for existing ones at http://excel.uservoice.com.

Notable Ideas

Dear reader, using an opportunity, I’d like to ask you to take a look at the following ideas for VBA improvement.

Vote if feel it will be useful for you too.

• Improve Workbook.Query object – add necessary properties – vote here

• RefreshAll method has to be improved – vote here

• SaveCopyAs for some reason doesn’t save file to OneDrive and SharePoint – vote here

• Authentication to SharePoint should not be a problem in VBA – vote here

VBA Trends on Google

What do we know about popularity of Excel, VBA and Access around the globe – let’s ask Google.

Google Trends worldwide for VBA – despite high interest it is gradually declining

Considering popularity of idea to introduce Python to Excel it is probably worth to learn Python. However, at the time of writing, Excel has only VBA as embedded scripting language.

VBA in United States – slightly declining from 2016

Excel and Access Trends

Excel and Access are probably two the most important “containers” of VBA code. So I decided to check what’s happening with interest to these Microsoft products

Microsoft Excel in United States – steady since 2016

However, Worldwide it is gradually declining

Interest to Microsoft Access in United States is decreasing over time

Very similar – Microsoft Access Worldwide

If you are still using Microsoft Access there must be a good reason for that.

Afterword

Clearly, VBA is not the hottest topic nowadays and it definitely requires modernization to stay competitive. However, even in current form it is still widely used.

To be honest, maybe simpleness of built-in IDE (not so many bells and whistles as in VS Code / Visual Studio) makes it easier to understand and accept by a common folk.

Does it worth to invest time in learning VBA, Excel or Access?

This I can’t tell you. All depends on your situation.

However, automation of tedious tasks in Excel has never been a bad skill.