While it is not often that we have to control pictures or images within Excel, based on the number of Google searches for this topic, it’s clearly something people want to know more about. This post serves as a reference to cover most of the scenarios we’re likely to encounter to copy, insert, move, delete and control pictures.
This code can be used in conjunction with my post about using a user defined function to insert and change pictures based on a cell value, which can be found here: https://exceloffthegrid.com/automatically-change-picture/
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0042 VBA copy insert, move, delete pictures.zip
Adapting the code to your needs
It is unlikely that any of the codes will meet your exact requirements. Every code snippet uses variables to hold either the image, the worksheet, a range or an object. By changing those variables, the code can easily be changed and combined with other code snippets to meet your specific requirements.
Insert an image into a worksheet
The following code will insert an image into the active cell of the active worksheet, keeping the original image’s size.
Sub InsertImage() Dim ws As Worksheet Dim imagePath As String Dim imgLeft As Double Dim imgTop As Double Set ws = ActiveSheet imagePath = "C:\Users\marks\Documents\myImage.png" imgLeft = ActiveCell.Left imgTop = ActiveCell.Top 'Width & Height = -1 means keep original size ws.Shapes.AddPicture _ fileName:=imagePath, _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=imgLeft, _ Top:=imgTop, _ Width:=-1, _ Height:=-1 End Sub
Depending on our needs, it may be better to create an image straight into an object variable. Then we can refer to the image by using the variable and do not need to know the name of the image. The following code is an example of this technique.
Sub InsertImageToDeclaredVariable() Dim myImage As Shape Dim ws As Worksheet Dim imagePath As String Dim imgLeft As Double Dim imgTop As Double Set ws = ActiveSheet imagePath = "C:\Users\marks\Documents\myImage.png" imgLeft = ActiveCell.Left imgTop = ActiveCell.Top Set myImage = ws.Shapes.AddPicture( _ Filename:=imagePath, _ LinkToFile:=msoFalse, _ SaveWithDocument:=msoTrue, _ Left:=imgLeft, _ Top:=imgTop, _ Width:=-1, _ Height:=-1) 'Use the variable for the created image MsgBox myImage.Name End Sub
The code below will display the name of the last inserted image.
The message box is to illustrate that the code works. Once we have captured the shape as an object in the real world, we would perform other actions on the shape.
Sub GetNameOfLastInsertedImage() Dim myImage As Shape Set myImage = ActiveSheet.Shapes(ActiveSheet.Shapes.Count) MsgBox myImage.Name End Sub
The code below renames an existing image.
Sub RenameImage() Dim myImage As Shape Dim ws As Worksheet Set ws = ActiveSheet Set myImage = ws.Shapes("Picture 2") myImage.Name = "New Image Name" End Sub
Get image properties
The following code demonstrates how to retrieve common image properties
Sub GetImageProperties() Dim myImage As Shape Dim ws As Worksheet Set ws = ActiveSheet Set myImage = ws.Shapes("Picture 1") MsgBox "Top: " & myImage.Top & vbNewLine & _ "Left: " & myImage.Left & vbNewLine & _ "Width: " & myImage.Width & vbNewLine & _ "Height: " & myImage.Height & vbNewLine & _ "Z-Order: " & myImage.ZOrderPosition & vbNewLine & _ "Name: " & myImage.Name & vbNewLine & _ "Top Left Cell: " & myImage.TopLeftCell & vbNewLine End Sub
Delete an image
The following code will delete an image called Picture 1 from the active worksheet.
Sub DeleteImage() Dim myImage As Shape Dim ws As Worksheet Set ws = ActiveSheet Set myImage = ws.Shapes("Picture 1") myImage.Delete End Sub
Make images invisible
Images can be made invisible. They still exist and are part of the workbook, but they are not visible to the user.
Sub MakeImageInvisible() Dim myImage As Shape Dim ws As Worksheet Set ws = ActiveSheet Set myImage = ws.Shapes("Picture 1") myImage.Visible = msoFalse 'Make the image visible again 'myImage.Visible = msoTrue End Sub
Loop through all images on a worksheet
The following code will loop through all the images on the active sheet.
Sub LoopThroughImagesOnWs() Dim shp As Shape Dim ws As Worksheet Set ws = ActiveSheet For Each shp In ws.Shapes If shp.Type = msoPicture Then 'Do something to the image 'Example, show message box MsgBox shp.Name & " is a picture" End If Next shp End Sub
Delete an image
The code below will delete a specific named picture.
Sub DeletePicture() Dim myImage As Shape Set myImage = ActiveSheet.Shapes("Picture 1") myImage.Delete End Sub
Confirm if the selected object is a picture
The code below will check if a specific object is a Picture.
Sub CheckIfSelectionIsPicture() Dim thing As Object Set thing = Selection If TypeName(thing) = "Picture" Then MsgBox "Selection is a picture" Else MsgBox "Selection is NOT a picture" End If End Sub
Images can be linked to cells or named ranges. This makes the image dynamic; when the contents of the cells change, so does the picture.
Sub MakeImageLinkedPicture() Dim ws As Worksheet Set ws = ActiveSheet ws.Pictures("Picture 1").Formula = "=A1:D10" End Sub
Image placement and locking options
Image behavior can be controlled using the placement option.
Sub ImagePlacementAndLockingOptions() Dim myImage As Shape Dim ws As Worksheet Set ws = ActiveSheet Set myImage = ws.Shapes("Picture 1") 'Image placement options myImage.Placement = xlFreeFloating 'The other placement options are: 'xlMoveAndSize 'xlMove 'Locking images (prevent editing image when worksheet protected) myImage.Locked = True 'The other placement options are: 'myImage.Locked = False End Sub
The following code rotates the image by a specific amount
Sub RotateImageIncremental() Dim myImage As Shape Dim rotationValue As Integer Set myImage = ActiveSheet.Shapes("Picture 1") rotationValue = 45 'Rotate the image by the amount specified by the rotationValue myImage.IncrementRotation (rotationValue) End Sub
The following code rotates the image to a specific amount.
Sub RotateImageAbsolute() Dim myImage As Shape Dim rotationValue As Integer Set myImage = ActiveSheet.Shapes("Picture 2") rotationValue = 90 'Rotate the image to the amount specified by the rotationValue myImage.rotation = rotationValue End Sub
Set image position to the center of a cell
An image is positioned based on the top and left of that image. The following code will set the position so that it appears centered within a specific cell.
Sub CenterInCell() Dim myImage As Shape Dim cellLocation As Range Set myImage = ActiveSheet.Shapes("Picture 1") Set cellLocation = ActiveSheet.Range("B4") myImage.Top = cellLocation.Top + (cellLocation.Height / 2) - (myImage.Height / 2) myImage.Left = cellLocation.Left + (cellLocation.Width / 2) - (myImage.Width / 2) End Sub
Do you know the fastest way to learn foreign languages? It is to read, write, speak, and think in that language as often as possible. Apart from speaking, programming languages are no different. The more you immerse yourself in that language, the faster you will pick it up.
Therefore, what most people like you need is lots of examples that you can practice. That is why the 100 Excel VBA Macros eBook exists. It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers. The book contains:
- 100 example codes to practice reading and writing macros that will embed the language into your thinking.
- An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
- Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
- Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.
Flipping an image horizontally or vertically
Flip the image horizontally:
Sub FlipImageHorizontal() Dim myImage As Shape Set myImage = ActiveSheet.Shapes("Picture 1") myImage.Flip msoFlipHorizontal End Sub
Flip the image vertically:
Sub FlipImageVertical() Dim myImage As Shape Set myImage = ActiveSheet.Shapes("Picture 1") myImage.Flip msoFlipVertical End Sub
Resize an image
The code below locks the aspect ratio; therefore, resizing the width or height will maintain the image’s proportions.
Sub ResizeImageLockAspectRatio() Dim myImage As Shape Dim imageWidth As Double Set myImage = ActiveSheet.Shapes("Picture 1") imageWidth = 100 myImage.LockAspectRatio = msoTrue myImage.Width = imageWidth End Sub
When setting the aspect ratio to msoFalse, the height and width operate independently.
Sub ResizeImageHeightOrWidth() Dim myImage As Shape Dim imageWidth As Double Dim imageHeight as Double Set myImage = ActiveSheet.Shapes("Picture 1") imageWidth = 100 imageHeight = 50 myImage.LockAspectRatio = msoFalse myImage.Width = imageWidth myImage.Height = imageHeight End Sub
The following code positions an image and stretches it to perfectly cover a specified range.
Sub StretchImageToCoverCells() Dim myImage As Shape Dim ws As Worksheet Dim rng As Range Set ws = ActiveSheet Set myImage = ws.Shapes("Picture 1") Set rng = ws.Range("A2:D10") myImage.LockAspectRatio = msoFalse myImage.Left = rng.Left myImage.Top = rng.Top myImage.Width = rng.Width myImage.Height = rng.Height End Sub
The code below crops an image based on the distance from the top, left, bottom or right.
Sub CropImage() Dim myImage As Shape Set myImage = ActiveSheet.Shapes("Picture1") myImage.PictureFormat.CropLeft = 50 myImage.PictureFormat.CropTop = 50 myImage.PictureFormat.CropRight = 50 myImage.PictureFormat.CropBottom = 50 End Sub
The image can be moved forward or backward within the stack of objects (known as the Z-Order).
Sub ChangeZOrderRelative() Dim myImage As Shape Set myImage = ActiveSheet.Shapes("Picture 1") myImage.ZOrder msoBringForward 'Alternative send backward 'myImage.ZOrder msoSendBackward End Sub
The Z-Order position cannot be set directly. First, send the image to the back, then move the image forward with a loop. Continue looping until the image reaches the correct Z-Order Position.
Sub ChangeZOrderAbsolute() Dim myImage As Shape Dim imageWidth As Double Dim imageZPosition As Integer Set myImage = ActiveSheet.Shapes("Picture 1") imageZPosition = 3 'Force z-order to zero then bring forward myImage.ZOrder msoSendToBack Do While myImage.zOrderPosition < imageZPosition myImage.ZOrder msoBringForward Loop End Sub
Set the background image
The background image appears behind the cells in the spreadsheet.
Sub SetImageBackground() Dim ws As Worksheet Dim imgPath As String Set ws = ActiveSheet imgPath = "C:\Users\marks\Documents\myImage.png" ws.SetBackgroundPicture fileName:=imgPath 'Remove the background image 'ws.SetBackgroundPicture fileName:=" End Sub
Save picture from Excel
If we have a picture in an Excel workbook, there is no straightforward way to save it to disk as a picture. A common workaround is to set the picture as the background of a chart area, then export the chart as an image.
Sub SavePictureFromExcel() Dim myPic As Shape Dim tempChartObj As ChartObject Dim savePath As String Set myPic = ActiveSheet.Shapes("Picture 1") Set tempChartObj = ActiveSheet.ChartObjects.Add(0, 0, myPic.Width, myPic.Height) savePath = "C:\Users\marks\Downloads\mySavedPic.jpg" 'Copy picture into chart, then export chart myPic.Copy tempChartObj.Chart.ChartArea.Select tempChartObj.Chart.Paste tempChartObj.Chart.Export savePath tempChartObj.Delete End Sub
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: