Did you know you can use Excel to Image snapshots of your Excel spreadsheets ? Sure you can. What is more you can use this feature directly from VBA to achieve some impressive feats. Today we will learn how to use the Excel Camera Tool!
Excel Camera Tool
Let us start with exploring how to use the built-in Excel Camera Tool. The Excel Camera Tool is a button that let’s you create an image snapshot of any region of your Excel spreadsheet.
To use the Excel Camera Tool all you need to do is:
Select an Excel Range
Click on the Camera Tool Icon
Click on any place in your Excel Worksheet
Add Excel Camera Tool Icon
Adding the Excel Camera Tool to your Quick Access Toolbar is easy. Just follow the steps below:
Select the Customize Quick Access Toolbar icon
Click on the arrow show the Quick Access Toolbar menu.
Next click More commands.
Browser Commands not in the toolbar
From the Choose commands from: menu select Commands not in the toolbar.
Now you should see an item called Camera in the list of Commands.
Add the Camera Tool
Now select Camera and hit the Add button to add the Camera tool to your Quick Access Toolbar.
Now you should see the Excel Camera Tool Icon in your Quick Access Toolbar: .
Use Excel Camera Tool in VBA
The Camera Tool is very useful in your everyday work when you need to copy part of your Workbook as an Image i.e. send an image snapshot of an Excel Worksheet or Chart. Fortunately there is also an easy way to use it in VBA:
Today something much lighter than usual, more to do with presenting data than just crunching it – How to Create an Excel Pictograph? Or, in other simpler words, How to Create an Excel Chart with Images!
If you want to get Your Message across use Pictures instead. Today I will show you a simple, but effective Excel Trick you can use to make the most out of your Data Visualizations – to transform Dull Charts into Impressive Infographics.
How to Create an Excel Pictograph?
Let’s start with the Basics. An Excel Pictograph is a Chart that replaces standard Bars, Pies and other Graphic Elements with Pictures. Creating Excel Pictographs is incredibly easy, hence making it an incredibly Powerful Excel Tip / Trick.
Create an Excel Chart
To create an Excel Pictograph we need to have a Chart to work on first. If you are new to Excel Charts feel free to listen to Chandoo’s Excel Chart Tutorial here. I created a simple Chart below:
Find an Image Icon for the Chart
The Chart above is Boring. Isn’t it? Just Raw Data. It takes a moment to take it in. Now let’s find an interesting image to replace the Boring Barchart! Here are some great resources I use to find Image Icons:
IconFinder a great database of Free and Premium Image Icons. I really appreciate the large amount of free images they have you don’t need to link back to
Be sure to find an Icon that will suitably visual the data you are trying to exemplify.
Paste the Image to your Excel Spreadsheet
Now copy your image to your Excel Worksheet. To do that go to the INSERT Ribbon Excel tab. Select Pictures if you have the Image on your workstation, or Online Pictures if you need to download the Image from the Web. Follow the steps and hit Insert.
Copy & Paste the Image onto your Chart
This is the important part:
Copy the Image by clicking on it and hitting CTRL+C
Select the Data Visual select your Charts Bars, Pie slices or any other Data Visual Element which you want to replace with an Image
Paste the Image by hitting CTRL+V
Configure the Image
The Chart is starting to look good. But often stretching the Image distorts it. We need to replace the Stretch with either the Stack or Stack and Scale with options:
Go to Data Series options by right clicking on the Bars and clicking Format Data Series...
Go to Fill Options and select Stretch, Stack or Stack and Stretch with. That last options requires you specify the Units/Pictures ration. In our case 1000$ should equal to 1 picture, so I input 1000
Too make the Image look even better, you may also want to go to SERIES OPTIONS and adjust the Gap Width! The Final Effect looks more like this:
Images are what enriches our content, visualizing data enables us to compare results, notice patterns and provide insights. Most Excel files are bereft of any images, presenting raw data supported by boring charts. Dashboard often could use a couple of images here and there to visual a metric or trend. Today shortly on how to dynamically create, embed and delete images from your Excel Workbooks by using only VBA.
We will start by introducing the Shapes AddPicture function:
This will load the image from the URL. Can take a couple of seconds to complete. Awesome right?
Now with this you can do cool things. Why not upload your project status/dashboard on a webpage and simply refresh it using VBA?
Deleting / Replacing images using VBA
Often you will want to replace an existing image or remove one. Not as obvious as it seems, you can’t replace an existing image – instead need to delete and recreate using a new picture.
Delete an image from VBA
Just deleting an image can be done like this – based on it’s index:
Or like this if you need to obtain the Shape by name:
Replacing images using VBA
Now let’s see what’s the correct way to replace an image (of similar sizes and location):
fileName = "C:\NewImage.bmp"
'Collect the location and size of the image
Dim shHeight As Long, shWidth As Long, shTop As Long, shLeft As Long
Dim s as Shape, ws as Worksheet
Set ws = ActiveSheet
Set s = ws.Shapes("Picture 1")
shHeight = ws.Height: shWidth = ws.Width: shTop = ws.Top: shLeft = ws.Left
'Delete the image
'Recreate the image using same location and size
ws.Shapes.AddPicture(fileName, msoCTrue, msoCTrue, shLeft, shTop, shWidth, shHeight)