All posts by AnalystCave

Tom. Excel / VBA / C# enthusiast and hobbist. Collecting and sharing my knowledge and experience with beginner/advanced analysts and VBA developers. My posts are written with one thing in mind: teaching analysts how to do things properly.
vba vlookup


1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

The VLOOKUP function is probably one of the most dreaded functions by beginner Excel users and also one of the most appreciated by the more advanced. VLOOKUP is used to lookup a row in a certain table, based on a value and return a corresponding value in a certain column with that row.

VLOOKUP is easily used in Excel. Today, however, we will explore how to do lookup operations in VBA (VBA VLOOKUP) or how to VLOOKUP in VBA if you prefer.

VBA VLOOKUP WorksheetFunction

First let us explore the simple approach of using the VBA VLOOKUP WorksheetFunction.

For this example table:
vba vlookup table
A simple VLOOKUP operation for say to lookup Roberts age might look like this:
VLOOKUP in Visual Basic equivalent
What about the same in Visual Basic for Applications (VBA)? The below is the equivalent to the VLOOKUP operation in Excel. We can use the WorksheetFunction object to run a VLOOKUP operation as such:

The result:
vba vlookup result

WorksheetFunction Vlookup function


The syntax for the Vlookup function in VBA is:


The lookup value – the value which is searched for within the first column of the provided Table_array parameter

A VBA Range variable. A table of at least 2 columns or more. The first column is the lookup column.

The number of the column in the Table_array table VBA Range from which the matching value must be returned.

An optional parameter. A logical parameter (True or False) that specifies whether you want the VLookup method to find an exact match or an approximate match. True – approximate match, False – exact match.

VBA VLOOKUP Dictionary

Another approach is to use the VBA Dictionary Object. For the same table as above we can use the following code:

What happens above? First we loop through all the rows and create a VBA Dictionary object with a Key-Value pair of all rows. The Dictionary contains only the lookup and matching columns. Next we lookup our desired Key and return the matching Value.

The result:
vba vlookup result
Right. So if both ways provide the same result what is the benefit of building a Dictionary upfront? Well, imagine wanting to lookup multiple values within your VBA code. In the first approach you would have to reintroduce the entire arguments of the Vlookup function. In the second you can run additional simple calls:

The Dictionary approach will in this case prove MUCH FASTER. This is because the Dictionary is built once and then just reused to lookup a certain key as opposed to doing the entire lookup operation on the entire table as a Vlookup operation would do.

If you want to learn more on the most effective way to lookup values in Excel instead, read my article on VLOOKUP vs INDEX MATCH vs SQL vs VBA.

excel vba camera tool

Excel Camera Tool – create an Image snapshot in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

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

excel camera tool
Using the 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:

  1. Select an Excel Range

  2. Click on the Camera Tool Icon

  3. 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.
add excel camera tool
Next click More commands.

Browser Commands not in the toolbar

From the Choose commands from: menu select Commands not in the toolbar.
add excel camera tool
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.
add excel camera tool
Now you should see the Excel Camera Tool Icon in your Quick Access Toolbar:
excel camera tool.

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:

An example of how the macro works below:
excel camera vba
Nice huh? Well this is the beginning of things you can do. There are lot of cool ideas you can use this feature to:

  • Send image snapshot of Excel Worksheet via Email
  • Save Excel Workbook as static image – replace each Worksheet with a static Image to protect your formulas and calculations
excel count formula

Excel Count Cells with Text and Formula – Excel Stats

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

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

COUNT Function

Excel COUNTA function

Counts Number of Non-Blank Cells

COUNTA Function

Excel COUNTBLANK function

Counts Number of Blank Cells


Excel COUNTIF function

Counts Number of Cells that Fulfill an If Condition

COUNTIF Function

Excel DCOUNT function

Counts Number of Cells that Fulfill MULTIPLE Specified Conditions

DCOUNT Excel Function

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.

To learn more about the VBA Range read my VBA Range Tutorial

VBA Count Cells with Numbers

To Count Cells with Numbers (equivalent to Excel COUNT Function) use the WorksheetFunctions.Count function:

VBA Count Non-Blank Cells

To Count Non-Blank Cells (equivalent to Excel COUNTA Function) use the WorksheetFunctions.CountA function:

VBA Count Blank Cells

To Count Blank Cells (equivalent to Excel COUNTBLANK Function) use the WorksheetFunctions.CountBlank function or the SpecialCells Range property:


If SpecialCells returns no cells then the Count property will throw an error

VBA Count If Cells

To Count If Cells (equivalent to Excel COUNTIF Function) on certain conditions use the WorksheetFunctions.CountIf function:

VBA Count Cells with Formulas

To Count Cells with Formulas use the SpecialCells Range property:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count Cells with Constants (Non-Formulas & Non-Blank)

To Count Cells with Constants use the SpecialCells Range property:

If SpecialCells returns no cells then the Count property will throw an error

VBA Count Visible / Invisible Cells

To Count Cells with Constants use the SpecialCells Range property:
Count Visible Cells:

Count Invisible Cells:

VBA Count Other Types of Cells

The SpecialCells Range property allows you to find a subset of certain types of cells. Here are all the available types:

Constant Description
xlCellTypeAllFormatConditions Any type of Cells
xlCellTypeAllValidation Cells with Validation Criteria
xlCellTypeBlanks Blank Cells
xlCellTypeComments Cells with Comments
xlCellTypeConstants Cells with Constants (Non-Formula & Non-Blank)
xlCellTypeFormulas Cells with Formulas (beginning with a =)
xlCellTypeLastCell Get Last Cell in the UsedRange
xlCellTypeSameFormatConditions Cells with Common Format Conditions
xlCellTypeSameValidation Cells with Common Validation Criteria
xlCellTypeVisible 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

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.

An Example

Example Excel WorkbookSay we have an example Excel Workbook with various Formulas. The CreateStats procedure will produce the following statistics:

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

An Excel UserForm

Want a neat UserForm report like the one below?

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

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.

Download Excel Stats

You can download the Excel Functions Usage Statistics Modules below:

How to create an MS Query in Excel

How to create a Microsoft Query in Excel (Excel Query)

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 out of 5)

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

How to Create a Microsoft Query

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

Create a Microsoft  Query (QueryTable)Go to the DATA Ribbon Tab and click From Other Sources. Select the last option – From Microsoft Query.

Select the Data Source

Create a Microsoft Query (QueryTable)Next we need to specify the Data Source for our Microsoft Query. Select Excel Files to proceed.

Select Excel Source File

querytableCreate a Microsoft Query (QueryTable)3Now 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

Create a Microsoft Query (QueryTable)3The 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

Create a Microsoft Query (QueryTable)3Now you have two options:

  1. Return Data to Microsoft Excel this will return your query results to Excel and complete the Wizard

  2. 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

Create a Microsoft Query (QueryTable)3If 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).

Import Data

Create a Microsoft Query (QueryTable)3Lastly, 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?
create ms query
Just use my VBA Code Snippet:

Just create a New VBA Module and paste the code above. You can run it hitting the CTRL+SHIFT+S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.

Power Query vs Microsoft Query

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

excel pictograph featured

Excel Pictograph – Charts with Pictures

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Today something much lighter than usual, more to do with presenting data than just crunching itHow to Create an Excel Pictograph? Or, in other simpler words, How to Create an Excel Chart with Images!

Images are Powerful Stuff! Our Brain processes Images 60 Thousand Times Faster than Text! For us Consultants, Marketers and Office Clerks this is important to understand, although often neglected. Hence the term Death by PowerPoint, known also as Death By Slideas in overwhelming your audience with an abundance of text and information squeezed into a single slide.

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?

example excel pictographLet’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:

Simple Boring Excel Chart
Simple Boring Excel Chart

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:

  1. 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

  2. Icon Archive a clipart image database

  3. Icon Finder yet another icon database

Be sure to find an Icon that will suitably visual the data you are trying to exemplify.

Paste the Image to your Excel Spreadsheet

past image to excelNow 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:
past image to excel chart

  1. Copy the Image by clicking on it and hitting CTRL+C

  2. Select the Data Visual select your Charts Bars, Pie slices or any other Data Visual Element which you want to replace with an Image

  3. 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:
excel pictograph configure image settings

  1. Go to Data Series options by right clicking on the Bars and clicking Format Data Series...

  2. 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:
final excel pictograph