Insert (and then reference) a picture into Excel using VBA

Question

I'm trying to insert a picture into a worksheet using VBA, that I can later reference by name (to for example delete, or hide).

I can insert the image using code similar to the below:

 ActiveSheet.Shapes.AddPicture Filename:="image.jpg", linktofile:=msoFalse, _
        savewithdocument:=msoCTrue, Left:=10, Top:=20, width:=100, Height:=50

However, I'm struggling to assign the picture to a shape (or other object) so that I can name it and later refer to it. e.g.

Dim shp As Shape
set shp = ActiveSheet.Shapes.AddPicture Filename:="image.jpg", linktofile:=msoFalse, _
        savewithdocument:=msoCTrue, Left:=10, Top:=20, width:=100, Height:=50
shp.name = "myPicture"

... some code ...

ActiveSheet.Shapes("myPicture").Delete ' or similar code to later delete the image

However, I'm getting a syntax error on the set shp = ... line

I've also tried with: Dim shp As Excel.Shape, Dim shp As Object as per the comments in SO post: VBA to insert embeded picture excel but still get the syntax errors.

Where am I going wrong?

Rory Rory · Accepted Answer · 2014-08-20T09:21:51

You need parentheses when returning a value from a method:

set shp = ActiveSheet.Shapes.AddPicture(Filename:="image.jpg", linktofile:=msoFalse, _
        savewithdocument:=msoCTrue, Left:=10, Top:=20, width:=100, Height:=50)

Insert (and then reference) a picture into Excel using VBA

1 Answers