Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Insert an Image to Excel Worksheet using VBA

         Are you working with excel templates in your office life. Then you might able to automate some of your time consuming tasks with VBA. If you go through the posts of this blog you will find lot of resources to improve your workbooks. And if you are new to VBA start with this post.

Getting started with Visual Basic Editor in Excel

 In today's post I will explain how to insert images to your excel sheets and how to set their properties.

So let's begin with a simplest example. I have a worksheet with no data. Think I need to insert below image to this worksheet using a macro.

Actually we can do this with just one line of code.


Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg")

You should replace the image path of above code with relevant path of your image.
So here below is the result we get.

As you can see this is not in a very useful form. Because image is in it's original size. Also we should have the control to insert image where we need.

So we need to improve our code like below.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg")

'Setting picture properties
With myPicture
    .ShapeRange.LockAspectRatio = msoTrue
    .Height = 250 ' Set your own size
    .Top = Rows(2).Top
    .Left = Columns(2).Left
End With

Value of Left and Top define the place where this image will insert. Also image will be re-sized to the height we given in the code. Value of .ShapeRange.LockAspectRatio determine whether to keep aspect ratio or not.
Below is the result of above code.

And there might be situations, where we need to insert our image to a empty space located middle of some other content. In that case we can't keep our aspect ratio and we need to set height and width to match with that free space. But if the ratio of height and width we set have higher deviation from that of original image, this can have big impact on the appearance of the image. But small deviation won't be visible at all.

Below is the code where both height and width are defined.

Set myPicture = ActiveSheet.Pictures.Insert("C:\Users\Mel\Pictures\Photo-0625.jpg")

'Setting picture properties
With myPicture
    .ShapeRange.LockAspectRatio = msoFalse
    .Height = 250
    .Width = 300
    .Top = Rows(2).Top
    .Left = Columns(2).Left
End With

And here is the result.



In this post I explained how to insert only one image to a excel sheet. But if we need to insert one image we can do it manually. So this method is useful only if we have lot of images to insert to our worksheet/worksheets. So I will explain how to insert several images to a worksheet with a click of a button in another post.

Contact Form

Name

Email *

Message *

Popular Posts