In this article, I am going to teach you a simple VBA code, which help you in inserting a picture in Excel Sheet. I will also discuss about difference between Inserting a picture in Excel and Embedding a picture in Excel Sheet using Excel VBA.
It is based on request from one of LEM reader who wants to know, How to insert a picture in excel sheet using VBA code It is a very simple one liner code to insert a picture in Excel using vba code.
Basically, there are two methods to insert a picture in Excel Sheet
Method 1. ActiveSheet.Pictures.Insert
Method 2. ActiveSheet.Shapes.AddPicture
VBA Code for Inserting Picture in Excel Sheet [Method 1]
Using .Pictures.Insert() method, you can insert a picture in Active sheet. Let see the Syntax of this method:
Syntax of .Pictures.Insert MethodActiveSheet.Pictures.Insert(‘Picture URL’)
This function requires only one parameter – Full path of the picture to be inserted in Excel Sheet. This is a mandatory parameter here.
Above statement will simply insert myPic.jpg picture in Active sheet in its original Size.
If you want to resize and locate the picture according to you, then use the following statement to resize the image and place it where ever you want in the excel sheet.
1. VBA Code to re-size (height and width) the inserted picture
Below code will set the height and width of the selected picture in worksheet which is inserted using VBA code:
With ActiveSheet.Pictures.Insert("Picture full path") .Left = 110 .Top = 220 .Width = 123 .Height = 134 End With
Explanation and issues with above Code
Left and Top will be set without any issue.
Later, Width of the image will be set to 123 as specified – Height of the image will be automatically set to a respective height to the width – because AspectRatio of the image is by default set to True
Similarly when control goes to the next statement then it will reset the height to 134 and since, aspect ratio is false, width will be adjusted to new respective value.
Challenge here is that you can NOT set AspectRatio flag of the picture while inserting it. (by above statement)
Therefore, be careful while resizing the picture while inserting it by using the above code
So what is the solution?
Here is the solution…
1. first add the picture in its own size.
2. Store the name of this image (uniquely generated one) in a variable. So that you can refer this picture uniquely later on
3. Using this variable, select that Shape and set the aspect ratio to false
4. Then set the height and width of the picture.
Here is the code now…
Dim nameOfPicture as String With ActiveSheet.Pictures.Insert("Picture file full path") .Left = ActiveSheet.Range("photograph").Left + 2 .Top = ActiveSheet.Range("photograph").Top + 2 nameOfPicture= .Name End With ActiveSheet.Pictures(profile).Select With Selection.ShapeRange .LockAspectRatio = msoFalse .Width = 123 .Height = 134 End With
2. VBA Code to set the location of the inserted Picture
Here you can either set fixed Left and Top value where you want to place your picture. In this case no matter what is the height and width of the cell in the worksheet, your picture will be always placed at a specific location. But suppose if you want – your picture should always be placed at a specific row and column then you can set the left and top values as follows:
With ActiveSheet.Pictures.Insert(<path of your picture in local drive>) .Left = ActiveSheet.Range("A1").Left .Top = ActiveSheet.Range("A1").Top .Placement = 1 End With
Now your selected picture will always be placed where Column A1 starts from left and Row 1 starts from top. It means even if you change height or width of the Range A1, your picture is always going to be in Range A1 only.
This method, simply links the image in to your Excel Sheet. It means, after inserting a picture, using this method, if you send it to another computer, picture will not be displayed and an Error message be displayed.
Therefore, this method is good only when you are going to use this excel sheet always in your own computer.
VBA Code for Embedding Picture in Excel Sheet [Method 2]
Using .Shapes.AddPicture() method, you can insert a picture in Active sheet. This method overcome the challenges of above method. This allows user to Embed the picture with the Excel Workbook itself. It means, even if you share the workbook to other computer… this picture will go with the document and you will be able to see it in other computer as well.
Syntax of .Shapes.AddPicture Method.Shapes.AddPicture( Filename , LinkToFile , SaveWithDocument , Left , Top , Width , Height )
Filename : (Mandatory) As the names suggests, this is the complete file path of the picture you want to embed to your Excel Sheet
LinkToFile : (Mandatory) MsoTriState- True or False – To set whether you want to create a link to the file?
SaveWithDocument : (Mandatory) MsoTriState – True or False – This is the flag which needs to be set to TRUE to embed the picture with Excel Sheet.
Left : (Mandatory)The position of the upper-left corner of the picture with respect to the upper-left corner of the document.
Top : (Mandatory) The position (in points) of the upper-left corner of the picture with respect to the top of the document.
Width : (Mandatory) The width of the picture you want to set. To keep the picture in its original width provide -1
Height : (Mandatory) The Height of the picture you want to set. To keep the picture in its original Height provide -1
Following VBA code will Embed this picture with the Excel file and it will display in any computer you sent it.
ActiveSheet.Shapes.AddPicture _ Filename:="full path of your file with extension", _ linktofile:=msoFalse, savewithdocument:=msoCTrue, _ Left:=50, Top:=50, Width:=250, Height:=250
Therefore .Shapes.AddPicture Method can insert a picture with and without links just simply by passing some flags.
For your practice I have created an Excel workbook which you can download and play around.