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:
In Excel we often like to Count Things. Sometimes those things are Cells with Text, Formulas or Formatting. Other times we want to Count Blank or Non-Blank Cells… and so on. Today I will teach you All About Counting Things in Excel. Excel Count Functions are an obvious option to go with, but there are also wonders you can do with just 1 line of VBA Code.
If want to see a Cool Summary of all your Excel Workbook Statistics scroll down to the last section.
Excel Count Functions
First let us start with Basic Excel Count Functions:
Excel COUNT function
Counts Number of Cells with Numbers
Excel COUNTA function
Counts Number of Non-Blank Cells
Excel COUNTBLANK function
Counts Number of Blank Cells
Excel COUNTIF function
Counts Number of Cells that Fulfill an If Condition
Excel DCOUNT function
Counts Number of Cells that Fulfill MULTIPLE Specified Conditions
VBA Count Functions
VBA Count Cells in Range
To simply count the number of VBA Cells in an Excel Range use the Count Range property.
The SpecialCells Range property allows you to find a subset of certain types of cells. Here are all the available types:
Any type of Cells
Cells with Validation Criteria
Cells with Comments
Cells with Constants (Non-Formula & Non-Blank)
Cells with Formulas (beginning with a =)
Get Last Cell in the UsedRange
Cells with Common Format Conditions
Cells with Common Validation Criteria
All Visible Cells
Excel Function Usage Statistics
I saved best for last. What if you wanted to do a statistic on the Excel Functions used in your Excel Workbook? Excel doesn’t provide you with a neat statistics windows like Word for words and sentences. Fortunately, I developed a neat VBA Macro that Counts every Function used in all Excel Formulas and presents a neat report as a result.
The Code below will generate Excel Function Usage Statistics to a new Worksheet.
How to use it? Simply run the CreateStats Sub procedure:
Or go to the DEVELOPER Ribbon Tab, select Macros and Run the CreateStats procedure on the list.
Say we have an example Excel Workbook with various Formulas. The CreateStats procedure will produce the following statistics:
An Excel UserForm
Want a neat UserForm report like the one below?
Instead of using the CreateStats function you can alternatively use my CreateStatsUserForm function (using the StatsForm – see Download section below), to instead admire a neat statistics UserForm without having to manage any additional ad-hoc Worksheets.
You can use Microsoft Query in Excel to retrieve data from an Excel Workbook as well as External Data Sources using SQL SELECT Statements. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel
A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!
You can extract data from:
Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook
Access – you can extract data from Access Database files
MS SQL Server – you can extract data from Microsoft SQL Server Tables
CSV and Text – you can upload CSV or tabular Text files
In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.
I will extract data from an External Excel file called Data Source.xlsx situated in C:\.
The below process shows how you can create a query for your current or an external Excel Workbook. However, the process for creating a MS Query for Access, SQL and Text (CSV) files is very similar
Open the MS Query (from Other Sources) wizard
Go to the DATA Ribbon Tab and click From Other Sources. Select the last option – From Microsoft Query.
Select the Data Source
Next we need to specify the Data Source for our Microsoft Query. Select Excel Files to proceed.
Select Excel Source File
Now we need to select the Excel file that will be the source for our Microsoft Query. In my example I will select my current Workbook, the same from which I am creating my MS Query.
Select Columns for your MS Query
The Wizard now asks you to select Columns for your MS Query. If you plan to modify the MS Query manually later simply click OK. Otherwise select your Columns.
Return Query or Edit Query
Now you have two options:
Return Data to Microsoft Excel this will return your query results to Excel and complete the Wizard
View data or edit query in Microsoft Query this will open the Microsoft Query window and allow you to modify you Microsoft Query
Optional: Edit Query
If you select the View data or edit query in Microsoft Query option you can now open the SQL Edit Query window by hitting the SQL button. When you are done hit the return button (the one with the open door).
Lastly, when you are done click OK on the Import Data window to complete running the query.
MS Query Trick
The above process is long and cumbersome. How about doing it quicker?
Just use my VBA Code Snippet:
Lastly, I would like to tackle the question of Why use MS Queries when I have Power Query? Microsoft has done a good job of understanding that users need a tool to transform data, but often don’t have the knowledge to use the SQL Language to create SELECT queries. That is why they created the Power Query AddIn (as part of Power BI Suite).
MS Query Pros:Power Query is an awesome tool, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:
Microsoft Queries are more efficient when you know SQL. While you can click your way through to Transform Data via Power Query someone who knows SQL will likely be much quicker in writing a suitable SELECT query
You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query
MS Query Cons:Microsoft Query falls short of the Power Query AddIn in some other aspects however:
Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations
In short I encourage learning Power Query if you don’t feel comfortable around SQL. If you are advanced in SQL I think you will find using good ole Microsoft Queries more convenient. I would compare this to the Age-Old discussion between Command Line devs vs GUI devs…
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:
Some VBA Macros run for ages – be it minutes or even hours. If we optimized our VBA Macro to the fullest there is often not much more you can do to change that. Usually what is irritating is waiting for VBA Macro code completion. Sitting by your desk is a waste of time – why not tell Excel/Access to let you know when the code has completed? Today we will explore such tools as the VBA Status Bar, VBA Progress Bar, Sound Notifications in VBA and Sending Email Alerts from VBA.
VBA Progress Bar
Progress Bars are ubiquitous anywhere anything takes more than a couple of seconds to complete. So why doesn’t VBA have a native Progress Bar – beats me.
The VBA Status Bar is a panel that appears at the bottom of your Excel (or Access) Workbook. It is basically a Text Box to which you can display any non-blocking (non-modal) Message to your users.
Showing a Message in the VBA Status Bar
To show a message in the VBA Status Bar we need to Enable it using Application.DisplayStatusBar:
Application.StatusBar="My Message in the Status Bar"
Hiding the VBA Status Bar
The VBA Status Bar when displayed, will remain displayed until ordered otherwise. It is best to hide and clean it after code execution completion:
Application.StatusBar=vbNullString'Clean the Status Bar message
Application.DisplayStatusBar=False'Hide the Status Bar altogether
Sound Notifications in VBA
What if you are away from your Screen, getting a Coffee or simply chatting with a colleague or getting on with our tasks stressed that we might miss that moment when our VBA Macro completes its task. Well why not introduce a Sound Notification? Let Excel or Access run a Sound to inform you that your Visual Basic for Applications macro has finally completed.
Playing a Sound in VBA
Playing a Sound in VBA is easy. Just add the below code snippet to a VBA Module: