The Future of Excel Automation in the Age of Python and AI

The evolution of Excel

I like bookstores.

Those of you who are reading this have probably already guessed, but I often go to bookstores.

I also frequently use Amazon and Mercari.

Just browsing around a bookstore allows you to comprehensively gather the latest information of the times.

It's said that bookstores are struggling due to the rise of Amazon and other online retailers, but I love them.

If I've browsed through a book in a bookstore and the price is significantly different from what I'd find on Mercari, I sometimes feel bad about it and end up buying a new copy at the bookstore.

Power Query is here!

One day, I saw a huge stack of books on "Excel Power Queries" at a large bookstore.

I had noticed that Power Query had been subtly added to some of the tabs in my company's Excel spreadsheets, but I hadn't touched anything about it.

It was around that time that I was asked by my company to find a way to improve the efficiency of the data aggregation work for a banking contract with an independent administrative agency.

"Hmm, I wonder. Will it be useful?"

However, I purchased it because I wanted to absorb the changes in the new Excel.

Power Query seems to be becoming an essential skill for accounting positions. On the other hand, many companies still primarily use VBA for administrative roles. However, as I'll mention below, Python is also great, not just VBA! 😊

As I read about it and tried it out on my home PC, I got the impression that data aggregation and other calculations will no longer be based on VBA, and that practical work will be based on "Power Query, a standard feature."

However, if you want to delve a little deeper and customize it to your liking, you will need to learn DAX functions and the M language, which are new functions and languages.

In that sense, I felt that "the learning cost isn't all that different from learning Excel functions and VBA."

Microsoft is shifting to a cloud-first approach (becoming a Microsoft that relies on Teams/SharePoint/OneDrive).

• Microsoft 365

• Teams

• SharePoint

• OneDrive

It seems that this is already becoming quite familiar to Windows users.

To be honest, as someone who was obsessed with Excel VBA, I felt that this tool would bring about a bigger change in the world than Power Query.

It was a group. I felt that society is changing, including with the spread of social media. I think it's becoming a world of "sharing."

I did.

This book was excellent because it carefully explains the importance of standard features in various apps and how to configure them. I still keep it at work and refer to it regularly.

It's convenient when you're not quite sure about something 😊

This book was excellent because it carefully explained the importance of standard features in various apps and how to configure them.

The advent of Office Script and the Web era

The arrival of Office Script

This really impressed upon me how much Office products are changing.

Excel will no longer be limited to VBA within a company's security environment; security will be ensured on Microsoft 365, and Excel files will be accessible anytime, anywhere, from any tool (including mobile devices) via the web. On top of that, Office Script will emerge as a language for manipulating and customizing such Excel files, similar to VBA.

I found it quite shocking.

However, later on, I had the opportunity to experiment with TypeScript, the language that forms the basis of Office Script, using TypeScript Playground within the company.

TypeScript is apparently a JavaScript-based language (which makes sense since it's a language used to "run" things on the front end).

"Oh, I've seen similar descriptions before," I thought.

From what I've seen on various websites, Office Scripts seems to be gaining popularity in the Microsoft 365 environment. However, compared to VBA, which has a history of several decades, there is less learning know-how available, and it seems there is still room for improvement, especially for companies that already have existing macro assets.

This was quite a shock to me, but I thought it was a really good reform based on the excellent idea of making it usable anytime, by anyone, and from any tool (including mobile), and it opens up possibilities for web developers to get involved.

Towards Excel that can be used anytime, by anyone, and from any tool (including mobile devices).

At this time, I was really glad that I had studied web front-end technologies like HTML, CSS, and JavaScript using the Web Creator Proficiency Certification Exam Expert and Mana's books. I still have a lot to learn, but it makes it easier to adapt to Office Script.

👉 [Part 1 of a 4-part series: Website Creation - Scratch vs. No-Code]

The impact of Python in Excel

When Guido van Rossum, the developer of the Python language, joined Microsoft in 2020, discussions about replacing Excel VBA with Python, and the question of whether VBA would become obsolete, suddenly increased on VBA blogs.

Now that I use Python with VS Code, I've come to think of it as "nothing special," but back then, when I knew nothing, there were times when I felt like it was like "the arrival of a black ship?"

Then, around 2023, I cautiously tried out Python in Excel, which was still in "public review" and available for free.

"Oh, Python works. Wow, that's amazing," I honestly thought.

There are many similar books available now.

I also want to continue honing my skills.

K's thoughts on the current and future position of VBA

VBA, Python, and AI should coexist, not be in conflict.

I believe VBA, Python, and AI will coexist, not be in conflict.

- I think Python makes cross-application development easy and has an amazing abundance of libraries.

However, once data is entered into an VBAOffice product, it becomes convenient and highly stable to automate the process using VBA.

Since the editor is included with Excel, it's also convenient because no environment settings are required.

And above all, Excel is widely used all over the world. Outlook VBA and PowerPoint VBA are also convenient.

It's also easy to collaborate with others.

AI is convenient.

Many people may think that VBA can be written by AI and then adjusted by themselves.

I believe that's a certain truth for anyone who can read VBA to a certain extent.

However, the number of people who can work with systems built using the COBOL language is decreasing, and demand is expected to decline in the long term. Nevertheless, I hear that there are many job openings for COBOL engineers targeting middle-aged and senior individuals in their 40s to 60s, and experienced engineers are highly valued.

VBA is a language with a long history that has influenced many people around the world. I believe that it may attract significant attention again in the future, in addition to its current use.

Things I think are necessary for future business improvements

on second thoughts

- A perspective that allows you to identify what users want and which parts can be automated.

- Perhaps it's about having the flexibility to try out any tool or language without being too averse to it?

To achieve this, a certain level of foundational knowledge is necessary in every field.

I, too, would like to continue my daily efforts to improve myself at a leisurely pace.