How to Insert an Image into a Cell with Excel VBA (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can insert an image into a cell with Excel VBA.


Insert an Image into a Cell with Excel VBA (Quick View)

Sub Insert_Single_Image()

Image_Name = "Argentina"
Image_Location = "E:\ExcelDemy\National Flags"
Image_Format = ".png"

Cell_Reference = "C3"

Set Image = ActiveSheet.Pictures.Insert(Image_Location + "\" + Image_Name + Image_Format)

Image.Top = Range(Cell_Reference).Top
Image.Left = Range(Cell_Reference).Left
Image.ShapeRange.Height = 45
Image.ShapeRange.Width = 75

End Sub

VBA Code to Insert Image into a Cell with Excel VBA


How to Insert an Image into Cell with Excel VBA: Step-by-Step Analysis

Here we’ve got a data set with the names of some countries and some empty cells for their national flags.

Data Set to Insert an Image into a Cell with Excel VBA

Also, we have a folder on our computer in the location E:\ExcelDemy\National Flags where we have stored the images of the flags of all these countries.

Our objective today is to insert images from this folder into the respective cells of the worksheet.


1. Insert a Single Image into a Cell with Excel VBA

First of all, we’ll learn to insert a single image into a cell. We’ll insert the image of the flag of Argentina in cell C3 of the active worksheet.

I am showing you the step-by-step procedure to execute this.

⧪ Step 1: Inserting the Image Name, Image Location, Image Format, and the Cell Reference of the Image on the Worksheet

First of all, we’ve to enter the Image_Name (Argentina), Image_Location on the computer (E:\ExcelDemy\National Flags), Image Format (.png), and the Cell_Reference (C3) of the image on the worksheet in the code.

Image_Name = "Argentina"
Image_Location = "E:\ExcelDemy\National Flags"
Image_Format = ".png"

Cell_Reference = "C3"

⧪ Step 2: Declaring the Image

Then we have to declare the image.

Set Image = ActiveSheet.Pictures.Insert(Image_Location + "\" + Image_Name + Image_Format)

⧪ Step 3: Setting the Attributes of the Image

Finally, we’ll set the attributes of the image.

Image.Top = Range(Cell_Reference).Top
Image.Left = Range(Cell_Reference).Left
Image.ShapeRange.Height = 45
Image.ShapeRange.Width = 75

So, the complete VBA code will be:

â§­ VBA Code:

Sub Insert_Single_Image()

Image_Name = "Argentina"
Image_Location = "E:\ExcelDemy\National Flags"
Image_Format = ".png"

Cell_Reference = "C3"

Set Image = ActiveSheet.Pictures.Insert(Image_Location + "\" + Image_Name + Image_Format)

Image.Top = Range(Cell_Reference).Top
Image.Left = Range(Cell_Reference).Left
Image.ShapeRange.Height = 45
Image.ShapeRange.Width = 75

End Sub

VBA Code to Insert Image into a Cell with Excel VBA

â§­ Output:

Run the code (Don’t forget to change the Image_Name, Image_Location, Image_Format, and Cell_Reference of the image according to your need before running the code).

It’ll insert the image of the national flag of Argentina in cell C3 of the active worksheet.

Output to Insert an Image into a Cell with Excel VBA


2. Insert a Series of Images into a Range of Cells with Excel VBA

We’ve learned to enter a single image into a cell. This time we’ll learn to enter a series of images into a range of cells with Excel VBA.

Let’s enter the flags of the countries mentioned in the range B3:B8 into the range C3:C8.

I’m showing you the step-by-step procedure to accomplish this.

⧪ Step 1: Inserting the Image Names, Image Location, Image Format, and the Cell References of the Images on the Worksheet

First of all, we have to enter the Image_Names (Range B3:B8 on the worksheet), the Image_Location, Image_Format, and the Cell_References of the images on the worksheet (Range C3:C8).

Set Image_Names = Range("B3:B8")
Image_Location = "E:\ExcelDemy\National Flags"
Image_Format = ".png"

Set Cell_Reference = Range("C3:C8")

⧪ Step 2: Iterating through a For-Loop to Declare and Set Attributes of the Images

Next, we’ll iterate through a For-loop to declare the image and set the attributes of the image.

For i = 1 To Image_Names.Rows.Count
    For j = 1 To Image_Names.Columns.Count
        Set Image = ActiveSheet.Pictures.Insert(Image_Location + "\" + Image_Names.Cells(i, j) + Image_Format)
        Image.Top = Cell_Reference.Cells(i, j).Top
        Image.Left = Cell_Reference.Cells(i, j).Left
        Image.ShapeRange.Height = 45
        Image.ShapeRange.Width = 75
    Next j
Next i

Therefore, the complete VBA code will be:

â§­ VBA Code:

Sub Insert_Multiple_Images()

Set Image_Names = Range("B3:B8")
Image_Location = "E:\ExcelDemy\National Flags"
Image_Format = ".png"

Set Cell_Reference = Range("C3:C8")

For i = 1 To Image_Names.Rows.Count
    For j = 1 To Image_Names.Columns.Count
        Set Image = ActiveSheet.Pictures.Insert(Image_Location + "\" + Image_Names.Cells(i, j) + Image_Format)
        Image.Top = Cell_Reference.Cells(i, j).Top
        Image.Left = Cell_Reference.Cells(i, j).Left
        Image.ShapeRange.Height = 45
        Image.ShapeRange.Width = 75
    Next j
Next i

End Sub

VBA Code to Insert an Image into a Cell with Excel VBA

â§­ Output:

Run the code (Again don’t forget to change the Image_Names, Image_Location, Image_Format, and Cell References of the images according to your need before running the code).

It’ll insert the images of the national flags of all the countries mentioned in the range B3:B8 into the range C3:C8 of the active worksheet.


Things to Remember

While working with VBA in Excel, you needn’t save any file to run a VBA code. You can see the output of any code without saving the file. But in case you want to save the file for future use, you must save the file as Excel Macro-Enabled Workbook (*.xlsm).


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So, these are the ways to insert an image into a cell with VBA in Excel. Do you have any questions? Feel free to ask us. Keep learning new tricks in Excel!


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

2 Comments
  1. Reply
    Jan (Yan) Woellhaf Sep 7, 2022 at 4:03 AM

    When I use one of these methods to supposedly insert an image into a cell, it seems what is actually done is to position the image over the cell. It is not actually _in_ the cell. Thus, when the sheet is sorted, the image does not move as it would if the image were actually imbedded in the cell. Like a fill color would move with the cell, for example.

    • Hello, JAN (YAN) WOELLHAF!
      If those code does not work for you, try this one! Hope this will help you.

      Sub InsertPic()
      Dim path As String, photo As Picture, cell As Range
      path = “E:\test” & Range(“C3”).Value & “.png”
      Set cell = ActiveCell.MergeArea
      Set photo = ActiveSheet.Pictures.Insert(PicPath)
      With photo
      .ShapeRange.LockAspectRatio = msoFalse
      .Left = ImageCell.Left
      .Top = ImageCell.Top
      .Width = ImageCell.Width
      .Height = ImageCell.Height
      End With
      End Sub

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo