[How to Automatically Insert and Reset Images in Excel VBA]

2025年12月30日

Let’s create two macros using Excel VBA: one to automatically place images from a folder into specific cells, and another to delete all placed images with a single click. These codes will allow you to easily arrange images on an Excel sheet and quickly remove them when they are no longer needed.

Preparation

First, prepare a folder containing images. You will place the images from this folder onto the sheet.

1. Macro to Insert Images into Specified Cells

The following code reads images from a folder in order and places them into specified cell positions. It also allows you to select the image folder using a dialog box. Images are placed in the order of cells A1, D1, A6, D6, and so on, with their sizes adjusted automatically.

Sub InsertImagesInCells_Array()
    Dim ws As Worksheet
    Dim imgFolder As String
    Dim img As Picture
    Dim imgPath As String
    Dim imgWidth As Single
    Dim imgHeight As Single
    Dim imgFile As String
    Dim imgPositions As Variant
    Dim i As Integer
    Dim folderDialog As FileDialog

    ' ダイアログボックスでフォルダを選択
    Set folderDialog = Application.FileDialog(msoFileDialogFolderPicker)
    folderDialog.Title = "画像フォルダを選択してください"
    If folderDialog.Show <> -1 Then Exit Sub ' キャンセルした場合は終了
    imgFolder = folderDialog.SelectedItems(1) & "\" ' フォルダパスを取得

    ' トリミング後のサイズを指定
    imgWidth = 30   ' 幅を30ポイントに設定
    imgHeight = 30  ' 高さを30ポイントに設定

    ' 画像を貼り付けたいシートを指定
    Set ws = ActiveSheet ' アクティブなシートを使用

    ' 貼り付けるセルの位置をリストとして定義（最大20個）
    imgPositions = Array("A1", "D1", "A6", "D6", "A11", "D11", "A16", "D16", _
                         "A21", "D21", "A26", "D26", "A31", "D31", "A36", "D36", _
                         "A41", "D41", "A46", "D46")

    ' フォルダ内のファイルを順に読み込む
    imgFile = Dir(imgFolder & "*.jpg")  ' .jpg形式のファイルを対象に読み込み
    i = 0

    ' 画像ファイルがある限り、かつセル位置リストの範囲内で画像を貼り付け
    Do While imgFile <> "" And i <= UBound(imgPositions)
        imgPath = imgFolder & imgFile

        ' 画像を貼り付け
        Set img = ws.Pictures.Insert(imgPath)

        ' 画像の位置とサイズを調整
        With img
            .Left = ws.Range(imgPositions(i)).Left  ' 指定セルの左端に配置
            .Top = ws.Range(imgPositions(i)).Top    ' 指定セルの上端に配置
            .Width = imgWidth                       ' 幅を30ポイントに設定
            .Height = imgHeight                     ' 高さを30ポイントに設定
        End With

        ' 次の画像ファイルを取得し、インデックスを進める
        imgFile = Dir
        i = i + 1
    Loop

    ' 終了メッセージ
    MsgBox "画像の貼り付けが完了しました。", vbInformation
End Sub

Code Explanation

Folder Selection Dialog: Application.FileDialog(msoFileDialogFolderPicker) displays a dialog to select an image folder, using the folder chosen by the user.
Specifying Cell Positions: The imgPositions array defines the order of cells where images will be placed.
Image Size Adjustment: imgWidth and imgHeight adjust the image size to 30×30 points.

2. Macro to Delete All Placed Images

Next is a macro to delete all placed images with one click. The code below removes all images located in the current sheet.

Sub ResetImagesInSheet()
    Dim ws As Worksheet
    Dim img As Shape

    ' 画像を削除するシートを指定
    Set ws = ActiveSheet ' アクティブなシートを使用

    ' シート内の画像をすべて削除
    For Each img In ws.Shapes
        If img.Type = msoPicture Then
            img.Delete
        End If
    Next img

    MsgBox "シート内のすべての画像が削除されました。", vbInformation
End Sub

Code Explanation

Delete Images in Active Sheet: By specifying ActiveSheet, it only deletes images on the sheet currently being viewed.
Image Check: If img.Type = msoPicture ensures that only images are identified and deleted. Other shapes (such as buttons) will not be removed.

Summary

With these two macros, you can easily manage the placement and resetting of images on an Excel sheet. First, select an image folder and run “InsertImagesInCells_Array” to place the images on the sheet. Then, run “ResetImagesInSheet” to clear all the placed images.