VBA Code To Save As Picture in Excel

By Chris Newman • Updated: 09/02/20 • 6 min read

Not As Easy As You’d Think…

One would think that saving something as a picture file (png, jpg, etc…) inside Excel with VBA would be a pretty straightforward exercise. Unfortunately, the “Save as Picture” functionality only exists for saving images of Charts (called exporting). This leaves saving pictures of shapes, icons, and cell ranges void of any VBA automation.

However, with a little bit of creativity, we can utilize the chart object to store our desired image and use the chart object’s Export function to create the image file that we can manually generate by right-clicking and selecting Save as Picture…

In this article I’ll walk you through the basic concept of what the VBA code is doing and also go through two VBA macro examples pertaining to saving shapes/icons and cell ranges in various file formats.

Methodology

Not many people know this but Charts can actually store shapes/objects inside of them. For example, if you have ever copied a textbox and pasted it while a chart is selected, you’ll notice the textbox object appears to be grouped with the chart. Meaning, if you move the chart to a different location, the textbox would move right along with it. What is actually occurring is the textbox is getting embedded within the chart and is essentially now a component of the chart object (think legend, chart title, chart series, axis, etc…).

Notice that the chart object is selected while selecting the textbox? This indicates the textbox has been embedded into the chart object.

The fact that charts in Excel can absorb other objects is what we will be utilizing to workaround the fact that shapes and other objects cannot be saved as a picture in VBA.

Before we dive into the VBA code, the concept the macro will be utilizing will be to isolate the object you want to save as a picture file and also generating a blank/empty chart object.

With those two objects in place, the VBA code will simply paste the desired object into the Chart Object (kind of like converting it into a chart!).

With this concept in mind, let’s take a look at the VBA code I’ve proposed to get this job done!

Save Shape As A PNG File [VBA Code]

Hopefully the following VBA macro is easily to follow along, but I’ll outline essentially the steps it is doing so you can customize it accordingly if needed.

Determine if a shape is selected, if not, don’t proceed. Store the selected shape to a variable (ActiveShape)
Generate a Chart Object that is the exact same size as the selected shape
Remove the chart’s Fill and Border to make the background transparent
Copy the selected shape and Paste it into the Chart Object
Export the Chart Object as a PNG file and save to the user’s desktop
Delete the chart so it appears like nothing has occurred to the user

Sub SaveShapeAsPicture()
'PURPOSE: Save a selected shape/icon as a PNG file to computer's desktop
'SOURCE: www.thespreadsheetguru.com

Dim cht As ChartObject
Dim ActiveShape As Shape
Dim UserSelection As Variant

'Ensure a Shape is selected
  On Error GoTo NoShapeSelected
    Set UserSelection = ActiveWindow.Selection
    Set ActiveShape = ActiveSheet.Shapes(UserSelection.Name)
  On Error GoTo 0

'Create a temporary chart object (same size as shape)
  Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=ActiveShape.Width, _
    Top:=ActiveCell.Top, _
    Height:=ActiveShape.Height)

'Format temporary chart to have a transparent background
  cht.ShapeRange.Fill.Visible = msoFalse
  cht.ShapeRange.Line.Visible = msoFalse
    
'Copy/Paste Shape inside temporary chart
  ActiveShape.Copy
  cht.Activate
  ActiveChart.Paste
   
'Save chart to User's Desktop as PNG File
  cht.Chart.Export Environ("USERPROFILE") & "\Desktop\" & ActiveShape.Name & ".png"

'Delete temporary Chart
  cht.Delete

'Re-Select Shape (appears like nothing happened!)
  ActiveShape.Select

Exit Sub

'ERROR HANDLERS
NoShapeSelected:
  MsgBox "You do not have a single shape selected!"
  Exit Sub

End Sub

Save Range As A JPG File [VBA Code]

Hopefully the following VBA macro is easily to follow along, but I’ll outline essentially the steps it is doing so you can customize it accordingly if needed.

Determine if a cell range is selected, if not, don’t proceed.
Copy/Paste the range as a picture and store the picture to a variable (ActiveShape)
Generate a Chart Object that is the exact same size as the selected shape
Remove the chart’s Fill and Border to make the background transparent
Copy the selected shape and Paste it into the Chart Object
Export the Chart Object as a PNG file and save to the user’s desktop
Delete the generated chart & picture so it appears like nothing has occurred to the user

Sub SaveRangeAsPicture()
'PURPOSE: Save a selected cell range as a JPG file to computer's desktop
'SOURCE: www.thespreadsheetguru.com

Dim cht As ChartObject
Dim ActiveShape As Shape

'Confirm if a Cell Range is currently selected
  If TypeName(Selection) <> "Range" Then
    MsgBox "You do not have a single shape selected!"
    Exit Sub
  End If

'Copy/Paste Cell Range as a Picture
  Selection.Copy
  ActiveSheet.Pictures.Paste(link:=False).Select
  Set ActiveShape = ActiveSheet.Shapes(ActiveWindow.Selection.Name)
  
'Create a temporary chart object (same size as shape)
  Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=ActiveCell.Left, _
    Width:=ActiveShape.Width, _
    Top:=ActiveCell.Top, _
    Height:=ActiveShape.Height)

'Format temporary chart to have a transparent background
  cht.ShapeRange.Fill.Visible = msoFalse
  cht.ShapeRange.Line.Visible = msoFalse
    
'Copy/Paste Shape inside temporary chart
  ActiveShape.Copy
  cht.Activate
  ActiveChart.Paste
   
'Save chart to User's Desktop as PNG File
  cht.Chart.Export Environ("USERPROFILE") & "\Desktop\" & ActiveShape.Name & ".jpg"

'Delete temporary Chart
  cht.Delete
  ActiveShape.Delete

'Re-Select Shape (appears like nothing happened!)
  ActiveShape.Select

End Sub

I Hope This Helped!

Hopefully, I was able to explain how you can use VBA to create image files in Excel. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

Ask If User Wants To Save File Before Executing VBA Macro

What This VBA Code Does In Excel and Microsoft Word you cannot undo a macro once it has run. Sometimes...

VBA Code To Save As A New Version If File Already Exists

Version Control Keeping track of different versions of a file has always been a pain for me. I am grateful...

The VBA Guide To Sending Excel Attachments Through Outlook

Quickly Emailing Your Spreadsheets I absolutely love using the Email As Attachment functionality provided by Excel. It's great for quickly...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.