VBA basics
Introduction to Excel VBA

Excel macro basics and applications, introduction to Excel VBA
last updated:10-18-2019

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 programming language is VBA (Visual Basic for Applications).
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 macros. * The image will be Excel2019 The preparation operation for using macro VBA is different for each version.
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 automatically. Of course it is recorded in VBA the macro language.
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 VBE for short. How to display _Visual Basic Editor_ Excel2007 Excel2010 or later _Development_-) _Visual Basic_ on the ribbon E…
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_ The following screen will appear.
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 function. Various processing contents are written between Sub and End Sub.
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 the time. You may have seen a macro run automatically by clicking a button.
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 now I can see that _Good morning!_ If you want to put characters in a cell Range(_cell address_) = _letters_ The cell address is the column sy…
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 Rectangular cell range entire row entire column special cell range Let's see how to specify these.
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 _B5_.
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 actual macro VBA description it is necessary to use Range and Cells properly.
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, etc. (ie data). It is well explained, but this is a good idea first.
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 and constants are those whose values do not change. A constant is a meaningful name used in place of a number or string.
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 this time Macro VBA when _abc123_ is inserted in cell C1.
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 same as the worksheet operator.
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, First of all, the VBA syntax for iteration to remember is For Next.
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 to specify the number of repetitions in advance Do ~ Loop is not a repeat count but a repeat condition.
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, that is, it handles vertical and horizontal tables.
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 table above calculate the unit price in column A x the quantity in column B

20.Conditional branch (If)
You may want to process only under certain conditions or you may want to change the processing contents depending on the conditions. The If statement is used when you want to branch processing according to a condition. In many cases of automation with VBA macros
21.Conditional branch (ElseIf)
In the previous IF statement, there were two choices: true or false. This time, we will write in the case of multiple-conditional branching with an If statement that requires more branches. The statement is an If statement.
22.Conditional branch (Select Case)
There is another way to write multi-limb conditional branches other than the previous ElseIf. That is Select Case. Rather than ElseIf, this Select Case is more suitable for multiple-limb conditional branching.
23.Message box (MsgBox function)
Displays a dialog box (small screen) that displays a message. Users can select (“Yes”, “No”, “OK”, “CANCEL”, etc.) and receive the selection results. To display a message box with macro VBA, use the MsgBox function.
24.Input box (InputBox function)
Displays a message and text box in a dialog box You can get the string entered by the user. In the beginning or middle of macro VBA you may want to change the process according to user input. To achieve this the InputBox function is used in macro VBA.
25.About named arguments
An argument is a specification of a value passed when calling a function or method. There is a named argument as a description method when passing this argument. In the macro VBA description,: = is used.
26.General practice question 2
Macro VBA exercises Use the table below for message box and numeric aggregation issues. Input numerical value with InputBox function Find the sum of the numbers in column A that are greater than or equal to the specified value and display it with the MsgBox function.

27.Select book / sheet (Select, Activate)
Excel handles multiple books, Each book has multiple sheets. Therefore, it is necessary to handle multiple books and multiple sheets even in macro VBA. In VBA so far, the active sheet of the active book has been targeted.
28.Select cell / row / column (Select, Activate)
This section describes the description for selecting cells rows and columns in macro VBA. First before explaining cell selection It is necessary to describe the selected cell and the active cell. Selected cell and active cell In this state The selected cells are 10 cells from B2 to C6.
29.Delete / Insert cell / row / column (Delete, Insert)
Macro VBA is explained about deletion / insertion of single cell or multiple cells and deletion / insertion of row / column. Deleting / inserting entire rows / columns by specifying a single cell or multiple cells Deletion / insertion by specifying a row / column will result in the same thing
30.General practice question 3
Macro VBA exercises The problem is shown in the table below. Problems with deleting / inserting rows / columns. ⇒ From the table on the left Delete even-numbered rows insert a blank row in the first row a blank row in column A Use the table on the right.

31.Format Cells (display format, NumberFormatLocal)
By specifying the display format of the cell you can make various appearances even if the value in the cell is the same value. You can keep the cell value as it is and make it look just like the user. Even with the same number 12345 12345 12345 $12
32.Format Cells (Alignment)
Macro VBA can be used to specify the position (vertical position, horizontal position) for displaying the value in the cell. It is the content specified by _Cell Formatting_ → _Placement_. Specifying placement in macro VBA Range.
33.Format Cells (Font)
This is the macro VBA specification method for the typeface of the character displayed in the cell. The font of the cell (Range object) is the Font property. The Font property returns a Font object. I think it's hard to understand.
34.Format Cells (fill, interior)
The most effective way to make a cell stand out would be to fill it with a color This is a macro VBA explanation when filling a cell. The fill (pattern) of the cell (Range object) becomes the Interior property. The Interior property returns an Interior object.
35.Format Cells (ruled line, border)
Sometimes it is necessary to draw a ruled line with macro VBA. If the data range of the sheet is changed by a macro it will be necessary to redraw the ruled lines. When searching the net etc. the macro that draws ruled lines seems to be written in various ways.
36.General practice question 4
Macro VBA exercises Write a formatting macro for the table below. Please format the table above and complete the table below. Tips!When formatting cells be sure to do the same settings all at once. Thinking time Thinking time starts The sinking time ends Go to Macro VBA Practice Question Answer Now…

37.Specifying a book sheet
So far only the active sheet of the active book has been dealt with in the introduction to macro VBA. The active sheet of the active book is the selected sheet of the book displayed in the foreground Usually it is a sheet that is manually operated.
38.Set formula in cells
Macro VBA for setting a formula (function) in a cell. In macros, I do n’t think there are many formulas for cells. It's not that you never use it. However, there are several types of properties in setting up this formula, which is quite troublesome.
39.Clear the cells
How to write macro VBA to clear cells, Even if it is cleared, the VBA code differs depending on what is to be cleared (value, color, comment, etc.). Specifically, the properties used depend on what is cleared in the cell (value, color, comment, etc.
40.Copy, cut and paste of cells
In macro VBA it becomes the explanation of macro VBA when copying or cutting a cell and pasting it to another cell. By hand Copy (Ctrl + C) or cut (Ctrl + X) a cell and paste it into another cell (ESC or Enter after Ctrl + V) This is a macro VBA that performs the same operation.
41.Copy cells and paste values (PasteSpecial)
Although it was titled Paste Value there are various ways to specify not only the value but also “Paste by selecting format”. This is the macro VBA code for copying a cell and “paste specially” in other cells. Copying a cell value or format to another cell is a standard and essential technique in M…
42.What is copying a cells?
What does it mean to copy a cell ... You can write more practical macro VBA code by digging into the macro VBA of copying cells a bit. What does copy mean in a word? What is copying a cell? A cell is a Range object The object itself cannot be copied.
43.General practice question 5
Macro VBA exercises The problem with the table below is the so-called hundred calculation. One hundred squares calculation: Please multiply the intersection cell. Please fill in the formula in “Sheet1”.

44.About VBA functions
Just as there are functions in the worksheet macro VBA also has dedicated functions This macro VBA-dedicated function is called a VBA function. What is a function? The functions (commands) that many people need in common are provided as a programming language.
45.VBA function (Format)
The VBA Format function is a function that returns a string converted to the specified format. The function is almost the same as the TEXT function of the worksheet function. The Format function is a very frequently used function and is an important and essential learning function in macro VBA.
46.VBA function (date, DateAdd)
Date and time are very special among data types and the use of functions is essential. This section explains the list of VBA functions related to dates and the DateAdd function. For other functions other than the DateAdd function refer to the individual pages from the links in the list.
47.VBA function (String operation, Replace, InStr, StrConv)
String manipulation is essential for programming with macro VBA Data shaping data cleansing data cleaning An essential part of VBA that does this is the string manipulation function. Here is a list of VBA functions related to string manipulation and This section explains the Replace function
48.VBA function (Other, Fix, Int, Rnd, Round, IsEmpty)
Among the VBA functions, this is a list of functions that should be kept in macro VBA, except for date functions and string manipulation functions. VBA functions related to mathematics, data type confirmation, and data type conversion.
49.Like operator and wildcard
The Like operator is used when performing character string comparison by partial matching or pattern matching. The Like operator performs pattern matching between two strings and returns whether the rule matches or not as True (match) or False (not match).
50.General exercise 6
Macro VBA exercises 1st question In cell A1 20120203 And this In cell B2 Date (2012/2/3) Please output as and input. 2nd question In cell A1 Japan Taro The space between first and last names is a single-byte space. this Cell B1 in Japan In cell C1

51.With statement
By using the With statement,You can write properties and methods without writing the object name again for the object specified as With. For example, in writing Once you have written the subject, you can then omit the subject.
52.Object variables and Set statements
In the description of variable data types, Object Do you remember that there was? Variables that contain objects, not numbers or letters, are object variables. There are various things to say about objects. Books, sheets and cells are all objects.
53.Workbook object
The Workbook object is the workbook itself. One workbook is one Excel file. When handling multiple books with Macro VBA, or when reading or saving a book, You have to write VBA with full awareness of the Workbook object.
54.Window object
Window objects can change the way books and sheets are viewed. It becomes an object when handling what is expressed as “window” in Excel with macro VBA. In the Excel ribbon, words such as “new window” and “window frame” are used.
55.Worksheet object
The Worksheet object is the worksheet itself. Since it is an Excel macro VBA the worksheet must be handled firmly. A collection of WorkSheet objects becomes a WorkSheets collection. That means A WorkSheet object is a member of the WorkSheets collection.
56.Range object (Range and Cells)
The root of Excel is a cell, the cell is a Range object, Without understanding this Range object, macros cannot be understood. It is no exaggeration to say that you have an understanding of the Range object as a test sample for improving macro VBA.
57.Application properties (Speed up VBA, stop warnings, etc.)
Application is an object that represents the entire Excel. In other words think of it as Excel itself. Here are some of the properties. The Application properties introduced here are just a few. Application is an object that represents the whole Excel.
58.What is a collection?
A collection of multiple objects of the same type is called a _collection_. Collections are also a type of object. For example _Workbooks collection_ is a collection of multiple Workbook objects _Worksheets collection_ is a collection of multiple Worksheet objects Whereas object names are singular …
59.Collection processing (For Each)
For Each iterates over each element of the collection. Because a collection is a collection of objects For Each Used to retrieve and process individual objects from the collection. The loop ends when all elements of the collection have been processed.
60.Error processing (On Error)
If a macro is being executed an error message may be displayed and macro execution may stop. If the macro stops in error the purpose of automation will not be achieved so it should not be stopped. If you can write VBA so that there are no errors you can't go beyond that
61."On Error GoTo" and "Exit Sub"
_On Error GoTo line_ This statement Moves control to the specified line label when a runtime error occurs. Macro VBA stops when an error occurs. If VBA cannot be executed, it will stop at the VBA code where the error occurred.
62."On Error Resume Next" and the Err object
The _On Error Resume Next_ statement is Even if a runtime error occurs the macro VBA is not interrupted and execution continues from the statement following the statement where the error occurred. If VBA cannot be executed it will stop at the VBA code where the error occurred.
63.Open a book (Open)
There are many ways to open other books, but this is quite different. There are text files such as CSV that can be opened in Excel, Here we will explain only for Excel files. Please refer to another article for text files and CSV.
64.Close / Save Book (Close, Save, SaveAs)
Describes macro VBA when closing or saving a workbook. You can specify whether to save or not when closing. Also, When saving a book, the VBA description will differ depending on whether it is overwritten or in a separate file.
65.Insert sheet, rename (Add, Name)
This is an explanation for inserting a sheet (adding a new sheet) or changing the name with Macro VBA. Inserting a sheet will often change the sheet name, Let's remember this series of VBA, insert sheet → change sheet name.
66.Copy / Move / Delete (Copy / Move / Delete)
Instructions for copying moving and deleting sheets. In macro VBA it is often used to copy a template sheet and delete unnecessary sheets. To copy / move a sheet Worksheet.Copy method Worksheet.Move method Use these methods of the WorkSheet object.
67.General practice question 7
Macro VBA exercises It's a problem dealing with books and sheets. First look at the following. In the _Book List_ sheet, the A column contains the path and the B column contains the book name.

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

100.InputBox method (input box)
101.Mid statement
102.Intersect method
103.Union method
104.GetPhonetic method and SetPhonetic method (phonetic)
105.Call statement
106.Function procedure
107.Procedure arguments

108.Variable scope (Scope, Private, Public)

This is essential for automating operations with VBA.
Most of the things you want to automate can be done so far.

Let's work on the exercises as a review.

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