VBA not dead, yet.

excel access vba visual basic

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.

  • 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

Interest over time 
Nov 16, 20_ 
May 22. 2016 
Nov 26, 2017 
Jun 2, 2019

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

Interest over time 
Nov 16.20... 
May 22, 2016 
Nov 26.2017 
Jun 2, 2019

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

Iterest over time 
Nov 26.2017 
May 22,

However, Worldwide it is gradually declining

Interest over time 
Jun 2019 
Nov 26.2017 
May 22 2016

Interest to Microsoft Access in United States is decreasing over time

Interest over time 
Nov 16, 20... 
May 22. 2016 
Nov 26, 2017 
Jun 2, 2019

Very similar – Microsoft Access Worldwide

Interest over time 
Nov 16, 20_ 
May 22, 2016 
Nov 26, 2017 
Jun 2, 2019

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


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.

Relocation to Australia

As some of my readers know, I recently moved from Moscow to Sydney. Huge change in my life, and it required a huge effort from me. From March I was busy with English exam, paper work, visa, preparation of goods transfer, then with transfer itself, then with finding apartment, buying furniture, household goods, taking over things at work, diving into new area and responsibilities, enrollment to school etc.

Thanks to Matt Allington for warm welcome in one of my first days in Sydney. For me it was like “coffee with legend”. After years of reading Matt’s articles, I could met him in person. This is inspiring!

Year ago I couldn’t even imagine such turn in my life.

So, I was a bit busy and had no time to write new posts.

However, I’m still passionate about Power Query, M, Excel, VBA, and Power BI will join this list very soon, as I’m officially start to work with it.

What is interesting, I’m now in “No-SQL world”, which means that I have no SQL Server to store data.

And I’m happy, as it is a chance to build reporting solution with no such luxury as servers.

Folders and Files – stored on local drive / network drive / SharePoint – great data base! There are million reasons why not, but it is still great!

Cheap, portable, flexible, scalable to a certain level, and what is important – still a good source for Power Query, no matter if resulting model is in Excel or Power BI.

Another important element from my point of view – availability to “small people”, analysts or reporting specialists, accountants etc. – people with no admin rights, knowledge of SQL etc.

I call it “Self-Service BI for poor”. Even when company is rich enough, has expensive ERP, IT governed business intelligence etc., I’m pretty sure, such Excel-DIY solutions appear here and there, as far not every analyst has access to serious IT solutions, or IT is not fast enough.

I still keep in mind one of old posts from Chris Webb: Why Corporate BI and Self-Service BI Are Both Necessary.

To wrap up, in near future you may expect posts covering interesting (and not) problems related to the situation when your data base is set of files.

Stay tuned!