Why I Learned Excel VBA | From Office Work to Business Process Improvement

A complete read-through of the perfect reverse-lookup guide to Excel VBA.

I bought "Excel VBA Reverse Dictionary Perfect" (by Toru Tanaka) at a convenience store, as my next purchase. I picked it up thinking that since it's a reverse dictionary, it might be immediately useful.

Although I wasn't in a position to use it immediately, I was amazed at the possibilities of Excel. It made me wonder what I had even known about Excel up until then... and at the same time, I was excited by its potential.

https://amzn.asia/d/0gzBdjWv

Looking back, I found your YouTube channel and website to be very easy to understand, and I watched them many times.

I can strongly feel your unwavering love for Excel, even as you get older. I enjoyed learning from your engaging and lighthearted style. Thank you.

This book has gone through several printings, and it seems that it's now available in a white book format.

I've included an Amazon link, so please check it out if you're interested.

The phrase "Office workers who can use VBA are highly valued"

I found this phrase while reading a blog.

I can't remember whose blog it was, but it was one of the words that strongly encouraged me.

Challenging the Odyssey VBA Expert Exam

The official Excel VBA textbook was written by Mr. Toru Tanaka, the author of the "Excel VBA Reverse Dictionary Perfect."

I was engrossed in reading it on my commute to and from work. It wasn't just because I wanted to pass the certification exam; I found it interesting because it offered a systematic way to learn.

The VBA Expert textbook is from the previous version of the exam.

Thanks to my intense focus, I was able to pass both the Basic and Standard exams after only one month of studying (though I think this is because I'm not very familiar with Access; I struggled quite a bit with Access VBA).

This is a text by Toru Tanaka.

(Please note that a textbook corresponding to the newly revised exam is now available.)

This is Muto's textbook.

(Please note that a textbook corresponding to the newly revised exam is now available.)

It felt like I had colleagues I had developed myself.

By the time I passed Odyssey's Excel VBA Basic and Excel VBA Standard exams, I had fallen in love with VBA. I began applying it to my own work (at that time, I was in charge of compiling large amounts of individual sales data for certain products), automating tasks one after another.

A huge amount of work suddenly became accurate and much easier for me. I remember being incredibly happy, as if I had truly gained "colleagues who I developed myself."

I became engrossed not only in automating my own work, but also in automating the work of others.

- Excel file for reserving the numerous reception rooms

- Excel files containing roster information for some departments (though this would be around 200-300 people) (allowing users to create, update, and delete entries themselves).

- A file that generates a large number of documents, such as invoices, based on a database.

I created many files, both large and small.

Hearing others say things like, "It's become easier," and "I'm no longer making mistakes," further motivated me.

Things I learned from working with Java/C#

Around that time, I came across a post on a VBA blog that said, "An engineer told me that VBA is like a toy."

The statement "Office workers who can use VBA are highly valued" was the complete opposite of what I expected. Having been impressed by VBA, I thought, "What?! Calling it a toy is an exaggeration." So, wondering if that was really true, I read through one book each on Java and C#, which I had heard were highly versatile languages.

"Wow, it's quite similar to VBA."

I was actually surprised to find that the content—if-else statements, for loops, variables, constants, event handling, error handling, etc.—is the same as VBA. There are some minor syntax differences (like Select case 👉 switch statement), but they are basically the same.

Certainly, compared to large-scale programming involving intricately interwoven blocks of code spanning thousands of lines, it might seem like "just a toy," but at least in the world of office work, it's a language that's been reliably useful and valued for about 20 years with its fundamental aspects remaining largely unchanged. I even felt a little relieved. I decided to continue studying it.

The benefits and limitations of learning VBA in depth and broadly

• Perfect VBA (Class Modules - a book by Nobuyuki Takahashi)

Good things

The words clearly written at the beginning of this book are truly convincing.

"VBA is easy for beginners, but its difficulty level increases rapidly from the intermediate level onwards. That's the characteristic it has." "The entry point is easy, but the further you go, the steeper the path becomes." I was moved by these words, which directly expressed how I felt at the time. And I also realized how much I have grown.

I found this to be a valuable and insightful book that provides clear guidelines on how to write universally applicable code in VBA, a language with a high degree of flexibility.

limit

Because of this, I became very conscious of how to avoid the "Excel legacy problem" (a phenomenon in which Excel files and macros (VBA) created and grown bloated by on-site work lead to the personalization of tasks and the black boxing of data management, posing a serious risk to management and security).

• Practical Excel VBA Techniques (A book by Haruhiko Sawauchi)

Good things

I initially started reading this book, along with Perfect VBA, mainly to understand class modules, but I was so impressed by the versatility and depth of each chapter that I ended up reading it four or five times.

limit

The three main elements of object-oriented programming

・Encapsulation:

I was able to grasp the general idea of using class modules for encapsulation, which I found somewhat complex as part of "object-oriented programming." I understood it as creating a "blueprint" for a program and then "materializing" it in various situations by instantiating it in different contexts. At the same time, I was relieved to hear that there is no VBA that absolutely cannot be achieved without class modules.

Other

・Inheritance・Polymorphism

Apparently, other languages besides VBA can do this, but VBA is not strictly an object-oriented language, and inheritance and polymorphism can only be used pseudo-(implemented in a modified form - not inherently in the language). Even now, I can't say that I fully understand inheritance and polymorphism.

• Whale Flying Machine Sensei

  

   Kujira-Hikoutukue：https://amzn.asia/d/0bLs069K

This book, which begins with the striking words, "It all starts with ALT+F11" (opening the VBE, the VBA editor), taught me about the vast possibilities of Excel VBA.

Automate mouse movements using SendKeys with the Windows API. Potential connectivity with SQL Lite.

It is possible to automatically retrieve necessary information from websites (for example, today's Nikkei Stock Average and exchange rates), automatically input it into Excel, perform calculations, and instantly generate answers and directions that align with your own ideas (crawling and scraping).

Especially during the Internet Explorer era, there were many specialized books published on the subject, and I bought and read some of them myself.

 Kujira-Hikoutukue's book also taught me that it's possible to control Google Chrome using something called SeleniumBasic.

Professor Atsushi Omura's book on the Windows API further expanded upon the knowledge I had gained from Professor  Kujira-Hikoutukue.

limit

Professor  Kujira-Hikoutukue has written so many books on Python, and every time I read them, I'm reminded that he's a true programmer. Regarding extensibility, I've come to realize that VBA ultimately has its limitations.

The good and bad things about learning VBA

Good things

What I'm truly glad about since starting programming, including VBA, is the joy I get from seeing people happy with the things I create. In a world where error-free work is often expected and considered the "norm," and where many jobs focus on bringing things from a negative to zero, I'm incredibly fortunate to have found work that allows me to create something from nothing.

This point remains one of the fundamental reasons why my interest in IT never wanes.

limit

As Kujira-Hikoutukue said, "There's no need to abandon VBA," and at the same time, "There are many situations where Excel is more convenient." However, Python is convenient, and considering its extensibility and compatibility with AI, I don't think it's a good idea to stick solely to VBA.