How to get started with Excel VBA
List of frequently used codes

You don't need any other software to write VBA. Write it in VBE（Visual Basic Editor） in Excel and execute it as is. First, prepare the following only once.

1. Show the developer tab

Open "File" → "Options" → "Customize Ribbon", check "Developer" in the list of "Main tabs" on the right, and press "OK". A “Developer” tab will appear on the ribbon.

2. Open VBE

Click "Visual Basic" on the "Developer" tab.Alt+F11But I can open it. Pressing it again will return you to the Excel screen.

3. Add standard module

Select "Insert" → "Standard Module" from the VBE menu. "Module1" will be created in the project on the left, and you will be able to write code on the right. Ordinary macros are written in this standard module.

4. Turn on "Force variable declaration"

Check "Force variable declaration" in VBE's "Tools" → "Options" → "Edit" tab. The following line is automatically inserted at the beginning of the newly created module, and an error message will be displayed to let you know if you have mistyped a variable name.

Option Explicit   ' Place this at the top of a module to require variable declarations with Dim.

5. Write and execute

After writing the code, click inside Sub ~ End Sub and then press "▶" (Sub/Run User Form) on the toolbar, orF5Press From the Excel screen, click "Macros" (Alt+F8) to select a name and execute it.

If you want to check intermediate values, go to VBE's "View" → "Immediate Window" (Ctrl+G) and write "Debug.Print variable name" in the code, the value will appear there.

6. Save as Macros-enabled workbook (.xlsm)

For the workbook in which you wrote VBA, select "Excel Macros-enabled workbook (*.xlsm)" in "Save As". If you save it as a regular .xlsx file, the code you wrote will be lost.

When you open the workbook, if you receive a message saying "Security Warning: Macros have been disabled," click "Enable Content" only if the workbook is one you created or you trust. Macros may be blocked in files saved from email or the Internet. In that case, right-click the file → select "Properties" and check "Allow".

Changes made using a Macros cannot be undone using Ctrl+Z.When trying it out, use a copy of the workbook or practice data.

For Mac, display the developer tab by selecting "Excel" menu → "Preferences" → "Ribbon and Toolbar", and open VBE in "Visual Basic" on the developer tab.

Practice using the browserPractice from displaying the Developer tab to running code →

If you can read this much, you will be able to read many of the recorded macros and codes created by AI. Detailed instructions for each are introduced in the sections below.

The Macros starts with "Sub Macros name()" and ends with "End Sub". The commands written during this time will be executed in order from the top. Japanese can also be used in Macros names.

Sub SayHello()
    ' This line is commented (not executed)
    MsgBox "Hello"
End Sub

「'” (single quote) to the end of the line is a comment. Writing down what you're doing will help you read it later or pass it on to someone else.

Use "Call" to call other macros.

Sub RunAll()
    Call CheckSales     ' call another Macros
    Call SayHello
End Sub

Variables are boxes that store values that are being calculated or values that are used many times. Prepare it with "Dim variable name As type" and enter the value with "=".

Sub VariablesExample()
    Dim total As Long        ' integer
    Dim customer As String   ' string
    Dim price As Double      ' numbers with decimals
    Dim today As Date        ' Date
    Dim finished As Boolean  ' True or False

    total = 12
    customer = "Komorebi Shop"
    price = 1200.5
    today = Date
    finished = False

    Range("A1").Value = customer & "：" & total & "matter"
End Sub

When storing "things" (objects) such as sheets or cells in variables, add "Set" at the beginning. If you forget to attach it, an error will occur, which is where I often stumble.

Dim ws As Worksheet
Set ws = Worksheets("tally")    ' Insert sheets and cells with Set
ws.Range("A1").Value = "Total sales"

Dim target As Range
Set target = ws.Range("A2:C10")
target.ClearContents

For values that do not change during the process, such as the consumption tax rate, if you use "Const" to make it a constant, you only need to change one place when changing it.

Const TAX_RATE As Double = 0.1   ' A value that does not change during the process

Range("C2").Value = Range("B2").Value * (1 + TAX_RATE)

The most common thing to write in VBA is specifying cells. 「Range("A1")" is the cell address, and "Cells (row, column)" is specified by number.Cells, which can be specified by number, is useful when shifting rows one by one during repetition.

Range("A1").Value = 100                     ' A1
Range("A1:C3").Value = 0                    ' Grouped into A1-C3
Range("A:A").Font.Bold = True               ' Entire column A
Range("2:2").Font.Bold = True               ' entire second row
Cells(2, 3).Value = "C2"                     ' 2nd row/3rd column (C2)
Range(Cells(1, 1), Cells(5, 3)).Select      ' A1〜C5
Worksheets("tally").Range("A1").Value = "Total" ' Specify sheet

If you do not write a sheet name, the sheet that is currently open (active sheet) will be targeted. When operating another sheet, clickWorksheets("sheet name").” in front.

find the last row

For tables where the number of rows changes every month, check how much data there is before processing. This is a way of writing to find the row number of the first cell that contains data, starting from the bottom cell in column A and working upwards.

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row   ' Search from the bottom of column A to the top

Range("A2:A" & lastRow).Font.Bold = True     ' From the bottom of the heading to the last line

Entire table/shift/expand

Range("A1").CurrentRegion.Select       ' Entire table connected to A1
Range("A1").Offset(1, 0).Value = "bottom"    ' 1 line below (A2)
Range("A1").Offset(0, 2).Value = "right"    ' 2nd row right (C1)
Range("A1").Resize(3, 2).Select         ' 3 rows x 2 columns from A1 (A1:B3)

Cell values are read and written using ".Value". For the formula, enter the same string as when entering it in Excel in ".Formula".

Range("D2").Formula = "=B2*C2"                  ' enter the formula
Range("A2:D10").ClearContents                   ' Delete only the value (the format remains)
Range("A1").Font.Bold = True                    ' Bold
Range("A1").Interior.Color = RGB(255, 242, 204) ' fill color
Range("D2:D10").NumberFormat = "#,##0"          ' 3-digit separator
Range("A1:D10").Copy Destination:=Worksheets("reserve").Range("A1")  ' copy and paste

Use "If ~ Then" to separate processing based on conditions. Don't forget the "End If" at the end.Use "ElseIf" to divide the condition into multiple conditions, and "Else" when none of the conditions apply.

If Range("B2").Value >= 80 Then
    Range("C2").Value = "Passed"
ElseIf Range("B2").Value >= 60 Then
    Range("C2").Value = "Reconfirmation"
Else
    Range("C2").Value = "Fail"
End If

If Range("A2").Value = "" Then
    MsgBox "A2 is blank"
End If

' And (both), Or (either), <> (not equal)
If Range("B2").Value >= 60 And Range("C2").Value <> "Absence" Then
    Range("D2").Value = "OK"
End If

When dividing into multiple ways based on one value, "Select Case" is easier to read.

Select Case Range("B2").Value
    Case "Tokyo", "Yokohama"
        Range("C2").Value = "Kanto"
    Case "Osaka", "Kyoto"
        Range("C2").Value = "Kansai"
    Case Else
        Range("C2").Value = "Other"
End Select

Performing the same processing for each row is where VBA shows its most power."For ~ Next" repeats while incrementing variable i by 1.

Dim i As Long
For i = 2 To 10
    Cells(i, 4).Value = Cells(i, 2).Value * Cells(i, 3).Value
Next i

When processing cells in a range or sheets in a workbook one by one, use "For Each."

Dim cell As Range
For Each cell In Range("A2:A10")
    If cell.Value = "" Then
        cell.Interior.Color = RGB(255, 199, 206)   ' fill in the blanks with red
    End If
Next cell

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets   ' all sheets
    ws.Range("A1").Font.Bold = True
Next ws

If the ending row is not determined, use "Do While" to repeat as long as the conditions are met.If you forget "r = r + 1", you will not be able to stop.When it doesn't stop,EscOrCtrl+BreakYou can interrupt it with .

Dim r As Long
r = 2
Do While Cells(r, 1).Value <> ""   ' Until column A is blank
    Cells(r, 5).Value = "Confirmed"
    r = r + 1
Loop

For i = 2 To 100
    If Cells(i, 1).Value = "Total" Then Exit For   ' exit midway
Next i

When we talk about "functions" in VBA, there are three types:

Create your own function (Function)

If you create it using "Function", it will become a function that returns the calculated value. If you put a value in the function name, that is the result.If you write it in a standard module, you can enter "=Amount including tax (A2)" in a cell and use it in the same way as a worksheet function.

Function PriceWithTax(price As Double) As Double
    PriceWithTax = price * 1.1     ' The value you put in the function name becomes the result.
End Function

Sub UseFunction()
    Range("B2").Value = PriceWithTax(Range("A2").Value)
End Sub

Using worksheet functions with VBA

Functions used in cells, such as SUM and COUNTIF, can be called with "WorksheetFunction."

Range("C11").Value = WorksheetFunction.Sum(Range("C2:C10"))
Range("C12").Value = WorksheetFunction.Average(Range("C2:C10"))
Range("C13").Value = WorksheetFunction.CountIf(Range("B2:B10"), "Tokyo")

VBA functions

VBA also provides functions that handle dates and strings.

Range("A1").Value = Format(Date, "yyyy-mm-dd")   ' today's date in text
Range("A2").Value = Now                           ' current date and time
Range("A3").Value = Len("Komorebi Shop")             ' Number of characters → 13
Range("A4").Value = Left("2026-10-06", 4)         ' 4 characters from the left → 2026
Range("A5").Value = Replace("Tokyo Branch", "Branch", "") ' Replace → Tokyo
Range("A6").Value = Trim("  Tokyo  ")               ' Erase the spaces before and after

Used to notify the end of processing or to confirm before Run. If you use "InputBox", you can input the month, person in charge, etc. each time you run the program.

MsgBox "It's over"

If MsgBox("Do you want to run it?", vbYesNo) = vbNo Then Exit Sub

Dim answer As String
answer = InputBox("How many months?")
Range("A1").Value = answer & "Monthly"

Worksheets("Sales").Activate                    ' switch sheets
Worksheets("Sales").Copy After:=Worksheets(Worksheets.Count)  ' copy sheet
ActiveSheet.Name = "Oct"                        ' Change sheet name
Worksheets.Add After:=Worksheets(Worksheets.Count)          ' add sheet

Dim book As Workbook
Set book = Workbooks.Open(ThisWorkbook.Path & "\Tokyo_October.xlsx")
book.Close SaveChanges:=False                    ' Close without saving
ThisWorkbook.Save                                ' Save this workbook

If there is a lot of processing to be done, you can stop updating the screen and it will finish faster. You can write the process when an error occurs using "On Error GoTo".

Sub RunFaster()
    Application.ScreenUpdating = False   ' Stop screen updates
    ' ...Process that takes time...
    Application.ScreenUpdating = True    ' return to last
End Sub

Sub HandleErrors()
    On Error GoTo ErrHandler
    Worksheets("tally").Range("A1").Value = "OK"
    Exit Sub
ErrHandler:
    MsgBox "I got an error:" & Err.Description
End Sub

Combining the code so far, we get this: In the "Sales" sheet, in a table with product name in column A, quantity in column B, and unit price in column C, calculate the amount in column D, and color the rows of 100,000 yen or more green.

Sub CheckSales()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Set ws = Worksheets("Sales")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        ' Amount = Quantity × Unit Price
        ws.Cells(i, 4).Value = ws.Cells(i, 2).Value * ws.Cells(i, 3).Value
        ' If it's over 100,000 yen, it's green.
        If ws.Cells(i, 4).Value >= 100000 Then
            ws.Cells(i, 4).Interior.Color = RGB(198, 239, 206)
        Else
            ws.Cells(i, 4).Interior.ColorIndex = xlNone
        End If
    Next i

    MsgBox lastRow - 1 & "calculated the row"
End Sub

Contains variables (Dim), sheet specification (Set), final row, repetition (For), and conditional branching (If). If you can read "what is being done in which cell" row by row, you can change the number of rows and conditions to suit your table.

Even when you have AI create code, if you can read this shape, you can check for yourself which column is being calculated and whether the conditions are met.

We recommend checking the code list to see how to write it and then trying it out using the teaching material file. This step-by-step introductory book allows you to practice everything from recording macros to variables, repetition, and conditional branching with connected examples.

For those who feel stuck doing it alone, or those who would like to discuss areas of their work that can be automated, taking a course taught by an instructor is also an option.

How to choose a study method is also introduced in How to study macros and VBA.