VBA basics
Introduction to Excel VBA

Excel macro basics and applications, introduction to Excel VBA
last updated:2019-08-16

Introduction to Excel VBA

Some of this topic was processed by machine translation. Original

Excel VBA is a programming language used in the macro function that automates Excel operations.

VBA is short for "Microsoft Visual Basic Applications"

In this VBA introductory series As an introduction to macro VBA which is required in practice, we will explain VBA in general from basic to application.

Beginners and beginners should read from the beginning.
Even if there are parts that you don't understand, it is also important to move forward.
Going forward, you may be able to understand what you couldn't understand before.
By reviewing from time to time, you should be able to realize that your understanding has progressed.

For intermediate / advanced users, please refer to the required items from the table of contents.

Please refer to the following page for the learning order.

How to learn the basics of Excel Macro VBA

Anyway, if you want to learn quickly through the actual battle,
Excel macro re-introduction
You can also start here.

Basics of VBA introduction

First of all, from the basics as an introduction.
I tried to write in detail as easily as possible so that the beginner can understand the Excel macro VBA.

1.What is a macro? What is VBA?
What is an Excel macro? What is Excel VBA? _Macro_ automates Excel operations. The function to be automated is called _macro_. The contents of the macro are programs. The program…
2.First prepare to use Macro VBA
Let's make a macro right away. And before that, there are preparations, Add a _Development_ tab to the top ribbon, The _Development_ tab contains icons necessary for developing m…
3.Macro recording
In explaining the Excel macro, first of all, You must explain _Recording macros_. It's also called automatic macro recording, In short, the manual operation of Excel is recorded …
4.Where is the macro written (VBE startup)
So where do you write your macros? First of all, an edit screen equivalent to paper for writing macros is displayed. This macro editing screen is called Visual Basic Editor,
5.VBE Set Options (Option Explicit)
There are some options that should be set a little before you start writing macros. No, it's a VBE Set Options that must be set. Be sure to set it first. VBE _Tools_ → _Options_ …
6.Let's write it anyway (Sub, End Sub)
Let's write a macro anyway. sub practice1 And enter In this way, sub becomes Sub, and () follows. And End Sub is added below. This is a macro. It becomes a macro with one functio…
7.To run a macro (F5)
Let's run the last macro, “Practice 1”. There are two main ways to execute macros, Execute on VBE screen Execute on worksheet It can be executed either way for the convenience of…
8.What is putting a character in a cell (Range, Value)
Let's take a closer look at the macro that we ran last time. Why is the value in the cell now, How to read VBA, I will explain in a little more detail. Anyway,
9.How to specify cells in Range
How to use Range that came out until the last time Range (_A1_) It was a case of specifying one cell by writing like this. When specifying multiple cell ranges,
10.Specification method other than Range (Cells, Rows, Columns)
You can specify any cell and range by specifying Range. When used in a macro, it may be a little difficult to use. It is inconvenient to specify with characters such as _A1_ and …
11.How to use Range and Cells
In VBA, there are Range and Cells as a way to specify cells. Both Range and Cells specify cells with a Range object. Which should I use? How should we use them properly? In the a…
12.Variables and data types (Dim)
This variable is the first thing that macro VBA beginners will trip, A variable is a container that temporarily stores numeric values, character strings,
13.Constants and type declaration characters (Const)
Last time I explained variables, but if there are variables, there are also constants, of course, In other words, variables are those whose values change,
14.Character concatenation and continuation line
A character in one cell and a character in a cell are joined together and displayed in another cell. This is a common case and is often done. _Abc_ in cell A1 _123_ in cell B1 At…
15.Four arithmetic operations and annotations (comments)
Excel is a spreadsheet software, so if you can't calculate it, it won't be a story. The first is the arithmetic operation (addition / subtraction remainder) operator. This is the…
16.Iterative processing (For Next)
BA's For Next is a VBA syntax for repeating the same process. There are several ways to iterate over VBA, The most frequently used, the simplest and most convenient,
17.Iterative processing (Do Loop)
As a repetitive process, we performed For ~ Next, this time Do ~ Loop. The frequency of use is lower than that of For-Next, This is something that you must remember. For-Next was…
18.Get last row (End, Rows.Count)
Getting the last row of a table in an Excel worksheet is an essential technique for macro VBA. Necessity of obtaining the last row in Excel VBA Excel is a spreadsheet software,
19.General practice question 1
Macro VBA exercises As explained in the introduction to Macro VBA, For ~ Next Get last row Four arithmetic operations Use the above to complete the following exercises. In the ta…

20.Conditional branch (If)
21.Conditional branch (ElseIf)
22.Conditional branch (Select Case)
23.Message box (MsgBox)
24.Input box
25.About named arguments
26.General practice question 2

27.Select book / sheet (Select, Activate)
28.Select cell / row / column (Select, Activate)
29.Delete / Insert cell / row / column (Delete, Insert)
30.General practice question 3

31.Cell format (display format, NumberFormatLocal)
32.Cell format (Alignment)
33.Cell format (font, Font)
34.Cell format (fill, interior)
35.Cell format (ruled line, border)
36.General practice question 4

37.Specifying a book sheet
38.Set formula in cell (Formula)
39.Clear the cell (Clear)
40.Copy, cut and paste of cells (Copy, Cut, Paste)
41.Copy cells and paste values (PasteSpecial)
42.What is copying a cell?
43.General practice question 5

44.About VBA functions
45.VBA function (Format)
46.VBA function (date, DateAdd)
47.VBA function (String operation, Replace, InStr, StrConv)
48.VBA function (Other, Fix, Int, Rnd, Round, IsEmpty)

49.Like operator and wildcard
50.General exercise 6

51.With statement
52.Object variables and Set statements
53.Workbook object
54.Window object
55.Worksheet object
56.Range object (Range and Cells)
57.Application properties (such as macro speedup and warning stop)
58.What is a collection?
59.Collection processing (For Each)
60.Error processing (On Error)
61."On Error GoTo" and "Exit Sub"
62."On Error Resume Next" and the Err object
63.Open a book (Open)
64.Close / Save Book (Close, Save, SaveAs)
65.Insert sheet, rename (Add, Name)
66.Copy / Move / Delete (Copy / Move / Delete)
67.General practice question 7

Up to this point, the basics are the minimum for an introduction to VBA.
Basically, if you can learn so far, you should be able to write Excel VBA considerably.
Now let's review debugging to encourage future learning smoothly.
Coding and debugging
Using local windows

Basics of VBA programming

It is a good idea to look at the basics of VBA programming.
The following is a separate article from the VBA introductory series.

Understanding Excel functions and objects

From here, you will learn VBA knowledge to move from introductory / beginner to intermediate level.
Programming terminology such as objects, properties, and methods frequently appears.
Regardless of the definition of words, try to understand the whole picture of what each means.
In addition, it is not necessary to memorize VBA codes corresponding to various Excel functions.
It is important to look through it so that it can be examined immediately when needed.

68.Sheet protection, book protection (Protect)
69.Hide sheet (Visible)
70.Fixing the window frame (FreezePanes)
71.Print (PrintOut)
72.Print Preview (PrintPreview)
73.Page Setup (PageSetup)
74.Open dialog (GetOpenFilename)
75.Save As dialog (GetSaveAsFilename)
76.File dialog (FileDialog)
77.Built-in dialog (Dialogs, xlDialogPrint)
78.General practice question 8

79.File operation I (Dir)
80.File operation I (others)
81.General practice question 9

82.Range Resize property
83.Range Offset property
84.Range Address property
85.Handling merged cells
86.General practice question 10

87.WorksheetFunction (use worksheet function)
88.Sort (Sort)
90.Setting filter options (AdvancedFilter)
91.Conditional formatting (FormatCondition)

126.Input rules (Validation)-added later 繝サ繝サ繝サ Add at a later date
92.Name definitions
97.Shape Auto Shape (Shape)
98.Find method (Find, FindNext, FindPrevious)
99.Replace method
132.Other Excel functions (grouping, deleting duplicates, autofill, etc.) 繝サ繝サ繝サ Add at a later date

Application convenience methods and proseeder split

Arrays, file operations, VBA extensions

Additional articles on VBA introduction (events, registry, tables)

Once it was completed, there was content that was leaked so far.
So I added what I think should be listed here, not the other series.

124.Workbook event procedure
125.Worksheet event procedure
126.Validation rules (Validation)
127.Run other book macros (Run method)
128.Launch macro with shortcut (OnKey method)
129.Registry operations (SaveSetting, GetSetting, GetAllSettings, DeleteSetting)
132.Other Excel functions (grouping, duplicate deletion, autofill, etc.)

130.Overview of table operations (ListObject)
131.Table operation VBA code (ListObject)

This completes the "Introduction to Excel Macro VBA". (Although it will be added in the future if necessary)
It is difficult and not necessary to memorize everything.
If you have any questions about macro VBA development, you can search this page and review it.
You can easily remember what you have learned once.

Learning after getting started with VBA

If you want to review, check the exercises
For those who have been disappointed,

When you're done learning here, you can be a VBA expert.
If there is something you don't understand, you should be able to solve it yourself by checking below.

VBA reference

Excel user form basics

VBA technical explanation

BA sample collection

Introduction to VBA class

  • >
  • Introduction to Excel VBA

  • If you find this site useful, please share and bookmark.

    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.

    • >
    • Introduction to Excel VBA

    If you find this site useful, please share and bookmark.
    To the bottom of the text