Excel VBA Course(1): Overview of Excel VBA

This is the first article of the previous Excel VBA lecture preview.

Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)

---

1. Excel VBA Overview

A function written in the VBA language similar to VB is called a Macro.

1.1. VBA, Macro concept

VBA (Visual Basic for Application) is a built-in MS Office product (Excel, PowerPoint, Word, Access, Visio, etc.) development language(and development environment).

Macro means:

1. automate repetitive tasks function
2. Procedure (processing procedure, function) written in VBA (development language)

Strictly speaking, the functions written in the VBA language are Macros, so they have different meanings, but it is safe to say that VBA and Macros have the same meaning. VBA is the best tool to maximize the use of Excel, and it is the best tool that can greatly help improve productivity (quantity and quality of output compared to investment time).

VBA is an interpreted language, not a compiled language, and there is no need to install separate development tools (Visual Studio, Eclipse, Delphi, etc.). (Already included in MS Office products) It has the advantage of being simple to develop and deploy.

There is little language difference between VB and VBA, but VBA does not support multi-threading and has limitations that cannot create independent executable files (.exe, .dll, etc.).

1.2. Experience, knowledge, and mindset required to approach Excel VBA

Many people use Excel, but not many people use Excel VBA. Many people do not even know the existence of Excel VBA, and there are many people who know that it is helpful for their work but do not touch it.

As you get started with Excel VBA, keep the following in mind:

• In order to use Excel VBA well, you need to use Excel well.
• If you have experience developing applications (C/S, Web, shell script, etc.), you can learn the language quickly.
• An understanding of Visual Basic language syntax is best.
• You need to understand the Excel Object Model.
• Imagination (Is this possible? Wouldn't this help my work? etc.) is required.
• You need search power (good use of Google).
• It is good to approach with the mindset of assembling (multiple components, Mash-up of services, etc.).
• What is most important is the will and desire to solve it yourself.

1.3. What can you do with Excel VBA?

It is possible to expand almost infinitely, such as all the functions that Excel can do, new functions combined with each function, and extension functions using external reference objects.

• Automate repetitive tasks in Excel
• Create and use user-defined functions in addition to the built-in functions
• Creating, merging, splitting many excel files
• Record multi-step actions that are frequently used repeatedly, make them into one function, and assign hotkeys for easy use
• Extend the functionality of Excel itself by creating Excel Add-Ins.
• CRUD processing in connection with database (Excel data upload & download, current status report/chart creation, etc.)
• Web connection (Naver dictionary search, batch registration application for meta standard terms, etc.)
• Mass processing in conjunction with other applications, automatic creation of reports/products
• Automated job processing to create script files such as UNiX shell, SQL, PL/SQL, etc.
• In addition, almost anything in terms of S/W is possible if there is imagination, search, and the will to do it.

1.4. A step-by-step approach to learning Excel VBA

It is necessary to learn by dividing it into the stage of learning the basics and the stage of using it.

It is recommended to learn the basic steps in the order of Step 1) Learning Object Model, Step 2) Learning VB Syntax, Step 3) Handling Object Model with VB. One day at the earliest, three days at the latest is sufficient to learn the concept, and one to two weeks or more are required to use it to some extent.

It is recommended to learn in the order of utilization steps: 4) Dealing with External Reference Objects, 5) Dealing with Module/UserForm, and 6) Dealing with Classes. External reference objects (eg, ADO for DB linkage, HttpRequest for web linkage, etc.) should be learned by type. Each object has a different purpose or method of use, so it is effective to learn the necessary objects at that time. In this utilization stage, the time required for learning and utilization may vary from person to person.

The following diagram shows the learning difficulty/productivity of each level of Excel VBA over time.

At least up to step 3, you can use it usefully for your work, and it can be helpful. Step 1) Learn Object Model or Step 2) Learn VB Syntax for a little while, and then give up. Don't give up and step 3) Let's make sure to deal with the Object Model with VB.

What should not be misunderstood here is that it is not possible to handle object models with VB only by learning all object models and all VB syntax. Rather, if you learn like that, you will quickly get bored and give up easily because it is not fun. If you come across something you don't know, you can search it on Google from time to time and move forward little by little.

* Reference: Getting started with VBA in Office (Microsoft Learn)

1.5. Factors that make Excel VBA difficult and how to solve it

• VB syntax is unfamiliar, I don't know, and it's difficult anyway.

VB syntax is so simple compared to C, C++ or Java that you can learn all the basics in an hour.
OOP is possible in VB, but compared to C++ and Java, there are many restrictions and it is as simple as that.

• I'm not familiar with the Excel Object Model.

You can learn Excel's basic Object Model in 5 minutes.
If you're not sure, you can use a macro recorder, or just Google it.

• I'm not sure how to implement the required functionality.

If you search on Google, you can find code that has already been implemented in most cases (90% and above).

• I don't know why I have to do this.

If you do it yourself, you can see that personal work productivity improves.
Projects/teams become more productive when more people can use VBA.

1.6. Do's and Don'ts of using Excel VBA

It will depend on each person's circumstances and circumstances, but in general, the following criteria are followed.

1.6.1. When using Excel VBA

• When it is expected that the time required to write VBA code will be significantly less than the time required for manual work.
• It is a one-time task, but when it is judged that it will be effective when writing VBA code
• When it is certain that an operation will be repeated more than once

1.6.1. When not to use Excel VBA

• When writing VBA code and failing to meet the schedule (in case the navel is bigger than the stomach)
• When it's a one-time task and manual work is faster

---

So far, we have looked at the overview of Excel VBA. Next, we will look at the basics of Excel VBA. If you have any questions, please leave them in the comments.

---

<< Related article list >>

• Start the Excel VBA course. (Lecture notice, feat. Why we recommend Excel VBA)
• Excel VBA Course(1): Overview of Excel VBA
• Excel VBA Course (2): Excel VBA Basics
• Excel VBA Course (3): Excel Object Model
• Excel VBA Course (4): Working with Excel Object Model
• Excel VBA Course (5): Excel File Extension, VBE, Font Settings
• Excel VBA Course (6): Excel VBA Language Basics - Variables
• Excel VBA Course (7): Excel VBA Language Basics - Syntax
• Excel VBA Course (8): Excel VBA Language Basics - Data Types, Data Structures
• Excel VBA Courses (9): Excel VBA How-To
• Excel VBA Course (10): Tools developed and used with Excel VBA
• Full Table of Contents for Excel VBA Courses