It is not often that we have to control pictures or images within Excel. This post serves as a reference to cover most of the scenarios we’re likely to encounter when we do need to.
Adapting the code to your needs
It is unlikely that any of the code 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 size of the original image.
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
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
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
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 proportions of the image.
Sub ResizeImage() 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 ResizeImage() 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 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