MrExcel.com - Your One Stop for Excel Tips & Solutions

VBA Insert Picture Bug

June 06, 2018 - by

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

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2214: Really Annoying VBA Bug When You're Trying to Insert a Picture.

Alright. So, hey, this started happening in Excel 2010. I just got burnt by it again recently.

So I'm going to insert a picture in this workbook, but I want to record that action so I can automatically do it. View, Macros, Recorder Macro, HowToInsertAPicture. Perfect. And I'm just going to insert a picture here: Illustrations, Pictures, let's choose one of our rocket photos and Insert. Alright, stop recording. Beautiful. Now, I'm going to get rid of that. I want to take a look at the macros, so Alt+F8, HowToInsertAPicture, Edit, and it says is this: ActiveSheet.Pictures.Insert and then the path to the picture. Alright. Yeah, that sounds good. And, in fact, we should be able to run this. So Alt+8, HowToInsertAPicture, and Run, and we get the picture-- that's beautiful. Until I save this workbook and let you download it or send it to someone else, and then the picture doesn't show up at all-- all I get is a red X saying, Hey, we can't find the picture anymore. Like what do you mean you can't find the picture? I asked you to insert a picture, not a link to the picture. But starting in Excel 2010, this recorded code is actually inserting a link to the picture. And if I open this workbook somewhere on a computer that doesn't have access to this drive and that picture: red X. Super annoying.

Alright. So, for some reason, in Excel 2010, the new thing to do, is instead of ActiveSheet.Pictures.Insert you do ActiveSheet.Shapes.AddPicture. Alright. And we can still specify a file name, but then these extra arguments that we have: LinkToFile=msoFalse-- in other words, don't create the stupid red link-- and then, SaveWithDocument:=msoTrue-- which means, actually put the darn picture in there and they can specify where it's supposed to be-- the left, the top…

Now, how do we figure out the height and the width? Alright. Well, we want to resize this proportionally, right? So I'm going to hold down the Shift key, like, get this back to less than one screen full of data, maybe like that right there. So that's my goal. I want to insert the picture and have a beam that size with that selected. I'll come back to VBA Alt+F11, Ctrl+G for the immediate window, and I'll ask for: ? selection.width-- so that's question mark, space, selection dot width and a question mark, selection dot height (? Selection.height). Alright, and that tells me about 140 and 195-- so the width, 140, and 195, like that. Get rid of the immediate window, and then here we'll delete this and run the code, and it actually inserted it. It's the right size, it'll be able to be open when you download this, or I download this, if you don't have access to the original picture.

I get it, things change, they had to change the code. But the fact they didn't update the macro recorder, and the macro recorder was giving us the bad code, that doesn't work. That's super annoying.

Well, hey to learn more about macros check out this book, Excel 2016 VBA Macros, by Tracy Syrstad and myself. We actually have a version of this for every version going back to excel 2003. So, whatever you have, provided it's Windows, there's a version for you.

Alright, wrap-up today-- it's my problem. I recorded code to insert a picture and it's creating a link to the picture, so anyone else I send the workbook to can't see the picture. Instead, I'm using the Macro recorder that does ActiveSheets.Shape.Picture, use this new ActiveSheet.Shapes.AddPicture. Or we can specify LinkToFile, no; save with document, yes; and you'll be good to go.

Well, hey, thanks for watching, I'll see you next time for another netcast from MrExcel.

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