In this post I will explain how to set location, height and width of an inserted image. So this is the image I’m going to insert to the Excel sheet.
And this is the “Details” tab of the “Properties” window of that image. As you can see height and width of the image are 1000 and 1500 pixels respectively.
So if we need we can easily insert the image using following code.
Sub InsertPicture_Example1()
Dim AddresPath As String
AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"
Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)
End Sub
|
Then picture will be inserted at active cell like this.
But sometimes we need to insert the image to a specific location of the sheet. And we may need to change the width and height to suit with the available space in the Excel sheet. So we can use following properties to change the location and size of the image to suit with our requirements.
LockAspectRatio
Height
Width
Top
Left
LockAspectRatio Controls the width: height ratio of the inserted image. So if it is true, width: height ratio of the inserted image will be equals to the width: height ratio of the original image. If it is false, ratio of the inserted image will be different. Height and Width defines the height and width of the inserted image respectively. Top define vertical location of the top left corner of the image. And Left defines horizontal location of the top left corner of the image. We can give the location using row numbers and column numbers.
So following code will insert the image to D2 cell. And height will be 200 pixels. As LockAspectRatio set to true, excel will automatically calculate the width to comply with original image.
Sub InsertPicture_Example2()
Dim AddresPath As String
AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"
Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)
'Set the location, width and height
With myPicture
.ShapeRange.LockAspectRatio = msoTrue
.Height = 200
.Top = Rows(2).Top
.Left = Columns(4).Left
End With
End Sub
|
Image will be inserted like this
And here is our next example. In this example, image will be inserted to D2 cell of the active sheet. But width: height ratio will not equal to the ratio of the original image because we have set LockAspectRatio to false. And we have given specific height and width.
Sub InsertPicture_Example3()
Dim AddresPath As String
AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"
Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)
'Set the location, width and height
With myPicture
.ShapeRange.LockAspectRatio = msoFalse
.Height = 200
.Width = 450
.Top = Rows(2).Top
.Left = Columns(4).Left
End With
End Sub
|
So the image will be inserted like this
Here is another example. Actually there's a mistake in this code. But I’m putting it here to show you how Excel application works if we use properties incorrectly. In this code we have set LockAspectRatio to true but after that we have given both height and width values.
Sub InsertPicture_Example4()
Dim AddresPath As String
AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"
Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)
'Set the location, width and height
With myPicture
.ShapeRange.LockAspectRatio = msoTrue
.Height = 200
.Width = 450
.Top = Rows(2).Top
.Left = Columns(4).Left
End With
End Sub
|
So when the code is executed image size will be altered keeping the original width: height ratio. You will see how it works if you use debug -> step into method.
At the end image will be inserted with width of 450 pixels. And height will be altered to comply with original ratio. So the image will inserted like this.
In above examples we gave height and width in pixels. But sometimes we need to assign the width and height in centimeters. We can use Application.CentimetersToPoints to do that. So the following code will insert the image to D2 cell of the active sheet. Height of the image will be 5cm. And width of the image will be 7.1cm
Sub InsertPicture_Example5()
Dim AddresPath As String
AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"
Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)
'Set the location, width and height
With myPicture
.ShapeRange.LockAspectRatio = msoFalse
.Height = Application.CentimetersToPoints(5)
.Width = Application.CentimetersToPoints(7.1)
.Top = Rows(2).Top
.Left = Columns(4).Left
End With
End Sub
|
So if you print the Excel sheet you will notice that image will printed in given size.
