The essence of VBA

Excel VBA
last updated:10-18-2019

About this site

Some of this topic was processed by machine translation. Original

Extensive technical information related to Excel.
"General Excel" is a basic application of Excel operations and functions.
“Macro VBA” offers a wealth of information from introductory and basic to advanced information.

Part of the developed software (Excel and VB.NET) is released as a free share.
Please feel free to download and use it.

*This site is link free..

Update information and news for the entire site

Creating an English table of contents for "Introduction to Excel Macro VBA"
Introduction to Excel VBA
The English page for each explanation will be considered in the future.
Changed the top menu of the PC version to drop-down format.
The menu structure of the macro VBA has also changed slightly.
Installed a VBA code copy button.
The character code of the site has been changed to UTF-8.
The VBA code used at this time is also posted.
Batch conversion of Shift_JIS text files to UTF-8
The entire site has been renewed.
The width has been expanded and the text has been made a little larger.
The entire site is HTTPS (always SSL).
Response speed is improved by HTTP / 2 protocol.
If bookmarked, change to HTTPS.

Excel new arrival article NEW

67. General practice question 7 | VBA basics(10/18/2019)

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.
66. Copy / Move / Delete (Copy / Move / Delete) | VBA basics(10/18/2019)
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.
65. Insert sheet, rename (Add, Name) | VBA basics(10/18/2019)
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.
64. Close / Save Book (Close, Save, SaveAs) | VBA basics(10/18/2019)
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.
63. Open a book (Open) | VBA basics(10/18/2019)
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.
62. "On Error Resume Next" and the Err object | VBA basics(9/22/2019)
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.
61. "On Error GoTo" and "Exit Sub" | VBA basics(9/22/2019)
_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.
60. Error processing (On Error) | VBA basics(9/22/2019)
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
59. Collection processing (For Each) | VBA basics(9/22/2019)
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.
58. What is a collection? | VBA basics(9/22/2019)
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 …
57. Application properties (Speed up VBA, stop warnings, etc.) | VBA basics(9/22/2019)
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.
56. Range object (Range and Cells) | VBA basics(9/21/2019)
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.
55. Worksheet object | VBA basics(9/21/2019)
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.
54. Window object | VBA basics(9/21/2019)
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.
43. General practice question 5 | VBA basics(9/21/2019)
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”.
52. Object variables and Set statements | VBA basics(9/18/2019)
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.
51. With statement | VBA basics(9/18/2019)
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.
50. General practice question 6 | VBA basics(9/15/2019)
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
49. Like operator and wildcard | VBA basics(9/15/2019)
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).
48. VBA function (Other, Fix, Int, Rnd, Round, IsEmpty) | VBA basics(9/15/2019)
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.
47. VBA function (String operation, Replace, InStr, StrConv) | VBA basics(9/15/2019)
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
46. VBA function (date, DateAdd) | VBA basics(9/15/2019)
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.
45. VBA function (Format) | VBA basics(9/15/2019)
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.
44. About VBA functions | VBA basics(9/15/2019)
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.
43. General practice question 5 | VBA basics(9/4/2019)
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”.
42. What is copying a cells? | VBA basics(9/4/2019)
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.
41. Copy cells and paste values (PasteSpecial) | VBA basics(9/4/2019)
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…
40. Copy, cut and paste of cells | VBA basics(9/4/2019)
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.
39. Clear the cells | VBA basics(9/3/2019)
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.
38. Set formula in cells | VBA basics(9/3/2019)
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.
37. Specifying a book sheet | VBA basics(9/3/2019)
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.
36. General practice question 4 | VBA basics(9/1/2019)
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…
35. Format Cells (ruled line, border) | VBA basics(9/1/2019)
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.
34. Format Cells (fill, interior) | VBA basics(9/1/2019)
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.
33. Format Cells (Font) | VBA basics(9/1/2019)
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.
32. Format Cells (Alignment) | VBA basics(9/1/2019)
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.
31. Format Cells (display format, NumberFormatLocal) | VBA basics(9/1/2019)
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
30. General practice question 3 | VBA basics(8/30/2019)
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.
29. Delete / Insert cell / row / column (Delete, Insert) | VBA basics(8/30/2019)
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
28. Select cell / row / column (Select, Activate) | VBA basics(8/30/2019)
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.
27. Select book / sheet (Select, Activate) | VBA basics(8/30/2019)
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.
26. General practice question 2 | VBA basics(8/29/2019)
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.
25. About named arguments | VBA basics(8/29/2019)
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.
24. Input box (InputBox function) | VBA basics(8/28/2019)
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.
23. Message box (MsgBox function) | VBA basics(8/28/2019)
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.
22. Conditional branch (Select Case) | VBA basics(8/25/2019)
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.
21. Conditional branch (ElseIf) | VBA basics(8/24/2019)
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.
20. Conditional branch (If) | VBA basics(8/24/2019)
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
19. General practice question 1 | VBA basics(8/17/2019)
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
18. Get last row (End, Rows.Count) | VBA basics(8/16/2019)
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.

Excel basic operations / functions to macro VBA

Macro VBA content is especially useful for beginners to advanced users.

★How to learn the basics of Excel★

★List of all contents of Excel in general★

★How to learn the basics of Excel macro VBA★

★List of all contents of Excel VBA★

Google spreadsheet, Google Apps Script

I didn't use it very much,
While studying, I will describe what I learned and tried.
We will post comparisons with Excel everywhere.

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.

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