Dear Friends,
 
This is not a big article. It is based on request from one of LEM reader who wants to know, How to insert a selected picture in excel using VBA code It is a very simple one liner code to insert a picture in Excel using vba code.

Insert Picture Using VBA Code

Insert Picture Using VBA Code


 

Syntax:

ActiveSheet.Pictures.Insert('full path of the picture from your local drive')

 
Above statement will simply insert selected picture in the excel in the selected cell or range in its original size. But there are more you can do as below:
 

1. You can re-size(height and width) the picture to display in excel worksheet

Below code will set the height and width of the selected picture in worksheet which is inserted using VBA code:
 

  '  First select the picture to re-size it. Be careful with
  '  the below statement. If it is omitted then any other image
  '  or shape will be resized and not the one which you wanted.
    ActiveSheet.Pictures(<name of the picture>).Select
    With Selection.ShapeRange
'       If lockAspectRatio is set to true then if you first set
'       the any one one of the dimension then other one will
'       be automatically set to maintain the aspect ratio. So
'       if you want to set height and width both the dimension
'       to a specific area then set it to msoFalse.
        .LockAspectRatio = msoFalse
        .Width = 123
        .Height = 134
    End With

2. You can also set a particular location where your picture should be placed in excel worksheet

Here you can either set a 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.

For your practice I have created an Excel workbook which you can download and play around.

Insert Picture Using VBA Code

Insert Picture Using VBA Code


 

Download Now