How to Insert a Picture Into a Cell Using VBA in Excel

Visual Basic for Application, VBA for short, is a form of Visual Basic 6 integrated into Microsoft Office programs. Through coding, VBA allows you to automate tasks in Office programs, including Excel. In some cases, you can even add new features to Excel using VBA.

Although you need to work with code to use VBA, that doesn’t mean that VBA is all letters and numbers. With VBA in Excel, you can create a macro that allows you to insert pictures into a cell or a range of cells. Read on to find out all about it!

How to Insert a Picture Into a Cell Using VBA in Excel

In order to create a macro to insert pictures into Excel cells using VBA, you actually don’t need any advanced Visual Basic knowledge. All you need to do is enable Developer tools, create the macro and paste the right code.

However, if you’re interested in learning VBA and writing your own code someday, we’ve broken down the VBA code in the third section. Of course, you can also insert pictures in Excel without using VBA. But this article is about getting it done through VBA. Let’s get down to business!

1. Enabling Developer Tools

In order to use VBA in Excel, you need to enable Developer tools in Excel. This will enable the Developer tab in the ribbon, which is disabled by default.

1. Open Excel.
2. Go to the File menu.
3. Click on Options at the bottom of the screen. This will open the Excel Options window.
4. In Excel Options, go to the Customize Ribbon tab.
5. Under Main Tabs, check Developer.

Now Developer tools, including access to VBA, are enabled for you. You don’t need to do this every time you want to use VBA in Excel. Developer tools will stay enabled until you disable them.

2. Creating the Macro and Inserting the Code

Now it’s time to get down to creating the macro. Alternatively, you could also create a button using VBA for this task, but we’re going to stick with macros.

1. In Excel, go to the Developer tab.
2. In the Code section, select Macros.
3. In the new window, enter a name for your macro under Macro name. We’re going to use insertPhotoMacro.
4. Click Create.

Once you click Create, the VBA window will open up and display the code for your macro. Right now, the code will consist of two lines: A Sub to initiate the macro, and an End Sub to end it.

Let’s add some code to this macro. Add the following code between the two lines:

Dim photoNameAndPath As Variant
Dim photo As Picture
photoNameAndPath = Application.GetOpenFilename(Title:="Select Photo to Insert")
If photoNameAndPath = False Then Exit Sub
Set photo = ActiveSheet.Pictures.Insert(photoNameAndPath)
With photo
.Left = ActiveSheet.Range("A1").Left
.Top = ActiveSheet.Range("A1").Top
.Width = ActiveSheet.Range("A1").Width
.Height = ActiveSheet.Range("A1").Height
.Placement = 1
End With

Your final code should be something like the below:

You don’t need to worry about saving your progress. Every change you make in VBA is instantly saved.

Now it’s time to see the code at work.

1. Close the VBA window.
2. Go to the Developer tab in Excel.
3. Select Macros from the Code section.
4. Highlight the macro you just created.
5. Click Run.

Now a prompt will pop open asking you to locate the image file you want to insert. Select your image and then click Open. You should now see your photo in the A1 cell!

Notice that the inserted picture is shrunk to fit cell A1. You can alter this, and also change the code to insert the picture in other cells or even a range of cells. In the next section, we’re going to break down the code and explain the parameters at work.

3. Breaking Down the Code

To make this VBA code work for you the way you want, you need to understand it. Once you do, you can alter the code to insert photos in any cell in any size. We’re going to go through the code bit by bit to make it easier to understand.

Sub insertPhotoMacro()
Dim photoNameAndPath As Variant
Dim photo As Picture
photoNameAndPath = Application.GetOpenFilename(Title:="Select Photo to Insert")
If photoNameAndPath = False Then Exit Sub
Set photo = ActiveSheet.Pictures.Insert(photoNameAndPath)
With photo
.Left = ActiveSheet.Range("A1").Left
.Top = ActiveSheet.Range("A1").Top
.Width = ActiveSheet.Range("A1").Width
.Height = ActiveSheet.Range("A1").Height
.Placement = 1
End With
End Sub

Once the code starts, we use the Dim statement to determine the variable’s type. We’ve got two variables here: photoNameAndPath and photo itself. We’ve determined the former as a Variant and the latter as a Photo.

From there, the photoNameAndPath variable runs and it opens an application to get the whereabouts of the picture file. This is done through Application.GetOpenFileName. The Title parameter is optional, and the content in it is displayed as the window name.

Using If photoNameAndPath = False Then Exit Sub, we specify that if an invalid or blank address is given, the process should be terminated. However, if a proper file is fed, then Set photo = ActiveSheet.Pictures.Insert(photoNameAndPath) indicates that the picture should be set as the photo variable we defined earlier, and it should be inserted into the active spreadsheet.

Finally using With photo and the five lines following it, we elaborate on the image’s positioning. .Left and .Top indicate the start locations, while .Width and .Height indicate the end locations. If you’re looking to insert the image in other cells, or in a range, then these are the lines you should alter.

.Placement indicates whether the picture should be sized with the cells or inserted in free form. Setting it to 1 will size it with the cells.

Ultimately, we use End With and then End Sub to close the macro. Note that you can change the photoNameAndPath and photo variables to whatever other name you like. Just remember to keep the names consistent throughout the code.

Get More Done in Excel With VBA

Excel is indeed a wonderful tool for organizing and analyzing data, but that doesn’t mean that Excel is inept when it comes to graphics. Though photos and bitmaps aren’t Excel’s strongest suit, Excel is still perfectly capable of handling them.

Although you can insert pictures in Excel using the interface as you would in other Office apps, you can also do it using VBA. With VBA, you can automate this task and even couple it with other tasks to run simultaneously. The possibilities with VBA in Excel are endless.