MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Insert Picture Bug


June 06, 2018 - by Bill Jelen

VBA Insert Picture Bug

I needed to have an Excel macro insert a picture in Excel. I turned on the macro recorder, inserted the picture. But when I used that recorded code, the picture will not appear on any other computer. Instead, I get a red X where the picture should be.

The code used to work in Excel 2007 or earlier. But something changed in Excel 2010. When you run code to ActiveSheet.Pictures.Insert, Excel is *not* inserting the picture. Instead, it is inserting a link to the picture.

Of course, when you run the code on your computer, it appears like everything worked. The picture appears.

Looks like the macro worked.
Looks like the macro worked.

But when someone opens the workbook on another PC, they get a red X and a message that the picture may have been moved or renamed.

Red X instead of the image
Red X instead of the image

Well, of course the image is not on my Manager's computer. I did not ask for Excel to create a link to the picture. I asked for Excel to insert the picture. But the recorded code is inserting a link to the picture.


The solution is to switch to different code. By using Shapes.AddPicture, you can specify LinkToFile:=msoFalse. Here is the code to use:

Sub HowToInsertPicture()
    ' This code fails in 2010 or newer
    ' ActiveSheet.Pictures.Insert( _
    '    "C:\Users\Bill\Desktop\PhotoPrint\All Rocket\aWalg180422-3.jpg").Select
        
    NewFN = "C:\Users\Bill\Desktop\PhotoPrint\All Rocket\aWalg180422-3.jpg"
    ActiveSheet.Shapes.AddPicture(Filename:=NewFN, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=1, Top:=1, Width:=140, Height:=195).Select
End Sub

Watch Video

Download Excel File

To download the excel file: vba-insert-picture-bug.xlsm

Many times, the macro recorder in Excel records the wrong code. With the fix above, you can successfully insert images using VBA.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Excel is a tool in service of the people who have to live with the consequences."

Title Photo: Brigitta Schneiter on Unsplash


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.