Tag Archives: VBA

excel vba evaluate

Excel VBA Evaluate – Tips and Tricks to use Application.Evaluate

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

Some call it the most powerful command in Excel VBA, while others struggle with simple VBA macros oblivious of its existence – today we with explore the VBA Evaluate function or more precisely the VBA Application.Evaluate function.

We will start with the basics and then move on to more elaborate examples and uses of the VBA Evaluate function.

VBA Evaluate

The Excel VBA Evaluate function converts a Microsoft Excel Name to an object or a value.

Syntax

The syntax for the Evaluate function in VBA is:

Parameters

Name
A formula or the Name of the object you want to evaluate. The length of the Name must be less than or equal to 255 characters.

Examples

Example 1: Simple formula evaluation

Example 2: Using Excel cells in formula evaluation

What out for the first example above! As it might evaluate differently depending on the Activeworksheet!

Example 3: Using functions in formula evaluation

vlookup table
This example is going to be a bit more complex for several reasons I will explain after the example.

Consider the table on the right I will use to run a simple VLOOKUP function. Let us pull up the value associated with the lookup name “Andrew”.

Now there are several things you need to learn from the example above:

  • Escaping strings enclosed within double apostrophes with another set of apostrophes “” – otherwise expect errors
  • Use a comma (,) to separate all function arguments REGARDLESS of your default system list separator as VBA assumes the comma by default
  • You don’t need an equals sign before your formula. The Excel VBA Evaluate functions assumes this by default

Now that we know the basics of the Excel VBA Evaluate function lets us explores some useful tips:

Tip 1: Use brackets instead

Did you know you can encapsulate Excel formulas in VBA in brackets to achieve the same results are embracing them within the Evaluate function? See example on the right. The Evaluate function can be easily swapped with square brackets to achieve same resutl.

Tip 2: Using brackets instead of Range

You can easily replace a VBA Range with square brackets as well:

Combining Tip 1 and 2 you can seriously shorten your VBA code:

Tip 3: Simple array definition

You can also define elements in a VBA Array in just a single line without needing to resort to the VBA Substring function like so:

Summary

The VBA Evaluate function is a very powerful tool if you want to easily reference cells in your workbook in your VBA Macros. There are however many pitfalls in using this function as well as its square brackets equivalent. Here are some:

  • Prone to errors – typical error includes forgetting that without preceding cells with the worksheet name the statement with always reference the Activesheet
  • Slightly slower – if you intend to run your code multiple times you might see a slight performance disadvantage. Usually you won’t notice this at all

In summary I personally think it make sense to know the VBA Application.Evaluate function as sometimes quick and dirty is all you need. However, I would avoid using it in complex VBA macros as it is easy to make a mistake, this comes especially as I am not a fan of embedding static cell references e.g. Range(“A1”) vs Cells(1,1). What is your take?

excel vba colorindex

Excel VBA ColorIndex vs VBA Color property

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

There are two ways of setting colors of Cell Backgrounds, Fonts or Borders in Excel. One of them is the VBA ColorIndex property and the other of them is the VBA Color property. But what is the difference between the two, and which is better! So let us explore…
vba colorindex

The VBA ColorIndex property

The VBA ColorIndex property can be any value between 1 and 56, and represents a color defined within your currently selected color theme with the provided index.

VBA ColorIndex table
VBA ColorIndex table
As you can see in the image on the left this represents a variety of colors. The downside of using the ColorIndex is that it is dependent on the actually selected color theme and you are limited to selecting colors from the 56 color palette.

If you want to print a similar ColorIndex table simply use the code below in any selected worksheet:

VBA ColorIndex example

VBA ColorIndex usage examples
VBA ColorIndex usage examples
On the left you can see several examples of setting the VBA ColorIndex to different Excel Range properties such as Interior (background), Font and Borders.

The VBA Color property

The VBA Color property is a more convenient way of defining the color of a Cell Interior, Border or Font. The Excel VBA Color property accepts Long values representing a RGB color. The easiest way to set this property is to use the VBA RGB function:

Example: Setting cell background to red

Example: Setting cell font to blue

Example: Setting cell borders to yellow

Summary

Using colors is easily achieved in Excel VBA. The Excel VBA ColorIndex property limits you however to 56 theme or default Excel colors and is rarely used in practice. The Excel VBA Color on the other side allows you to set any color to any Excel Cell property using the VBA RGB function.

vba vlookup

VBA VLOOKUP – Using VLOOKUP in VBA

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

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

Syntax

The syntax for the Vlookup function in VBA is:

Parameters

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

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

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

Range_lookup
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)
Loading...

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)
Loading...

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

COUNTBLANK Function

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:
CountA:

SpecialCells:

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: