3

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?

Community
  • 1
  • 1
Jonny
  • 3,807
  • 8
  • 31
  • 48

1 Answers1

7

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)
Rory
  • 32,730
  • 5
  • 32
  • 35