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.
