Excel VBA Evaluate Function – How to use this Secret VBA Function

The Evaluate function is a hidden secret that few Excel VBA users know. In this blog post we will explore what it does, and why you will be happy to know it.

The Excel VBA Evaluate Function converts a Microsoft Excel name to an object or a value.

The syntax for the Evaluate function looks like this;

Evaluate(Name)

The name can be a formula or the name of an object that you want to evaluate. This name must not exceed 255 characters.

What does this mean? And why is this useful?

Well let’s have a look at some typical uses of the Evaluate Function. Continue reading

6 Ways to Speed up your Excel Macros

Creating macros in Excel is a fantastic way to automate Excel processes and save loads of time. However as your macros get bigger, you may need to increase the speed that they execute.

This blog post looks at 6 ways that you can speed up your macros. You really need to know this stuff to create efficient macros in Excel.

If you are a user of Excel VBA already, then you may know all, or most of these. Please feel free to comment with the techniques that you use.

Watch the Video


Continue reading

Excel VBA: Select Case Statement with Examples

The Select Case statement can offer a neater and more concise alternative to nested If Then Else statements in Excel VBA.

So when you are performing complex logical tests, the Select Case statement can break it down into more logical chunks of code. This then makes it easier to write, read and understand what the code does, which everyone will appreciate.

In this tutorial we will look at a few examples to show the different ways that the Select Case statement can be written. Continue reading

Select/Deselect all Checkboxes with a Single Checkbox

Add a single checkbox to select, or deselect, all of the checkboxes on a worksheet with one click.

If you have a large list of checkboxes, selecting all of the boxes, or removing the checks from all the boxes can be very time consuming.

This blog post will provide the code to select/deselect all the checkboxes on a worksheet using a single checkbox, and explain how it works.

Watch the Video

Continue reading

Automatically Refresh PivotTable – Excel VBA

Excel PivotTables do not refresh automatically when the data source is updated.

Now, you can refresh all of the PivotTables in your workbook with just 2 or 3 clicks. But this is not something you need to do when you work with formulas, charts or Conditional Formatting. So you might forget, or just find it irritating to do it regularly.

This tutorial walks you through the VBA code to refresh the PivotTable automatically.

New to VBA and want to learn quickly? Sign up to our online Excel VBA course.

Watch the Video

Continue reading

Excel VBA Progress Bar on Userform

In this blog post we look at how to create a custom progress bar on a userform using Excel VBA, so that we can see the progress of our macros as they are running.

The progress bar will look like the one below. The fonts and colours used can be customised to your own liking.

Create a progress bar in Excel VBA

There is a video at the bottom of this tutorial if you prefer to learn that way. Otherwise lets crack on and create this progress bar.

Download the completed file from this tutorial. Continue reading

5 Ways to Use the Immediate Window – Excel VBA

The Immediate Window in Excel VBA is a very useful tool to help us step through, check and debug our code. In this blog post we explore 5 ways you could use the Immediate Window.

It is called the Immediate Window because you can ask questions about your code, run statements and get immediate results. This can help us to understand what our macro is doing, and why and where it may be going wrong.

Watch the video tutorial below, or continue reading the full tutorial.

Continue reading

Use Own Icon for Macro Button on Ribbon

In this blog post we look at how you can use your own icon or image for the button of your macro on the Ribbon. This could be an image that you have created, or one that you downloaded from a website.

By using our own icons we can create customised workbooks and not be constrained by the standard Microsoft icon set provided.

In this example I have a macro that exports all the worksheets of a workbook to PDF, and I want to assign it to the icon shown below. This icon was downloaded from flaticon.com, a fantastic resource for icons that you can use for your macros.
Custom icon for macro button Continue reading

Excel VBA: 6 Ways to Find the Last Row of a List

A very common task in Excel VBA is to find the last row of a list. This can be for many reasons such as to make a chart or PivotTable ranges dynamic, or to append fresh data to the bottom of a list.

This blog post uncovers 6 different ways you can find the last row. In these examples the number of the last row is assigned to a variable for use within your procedures.

Are you new to Excel VBA? Learn Excel VBA quickly with this online course.

Find the last row using Excel VBA

6 Ways to Find the Last Row of a List – Excel VBA

For these examples the following variables have been declared and the sht variable activated.

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

Continue reading

Count Cells by Colour – Excel VBA Function

If you have ever tried to count cells by colour in Excel, you may have noticed that Excel does not contain a function to accomplish this.

We can find out how many cells by filtering the list by colour, and maybe use the AGGREGATE function to return the count. This is an option. But having to filter the list each time may not be good enough.

Because functions such as COUNTIF cannot count by cell colour, we will need to create our own custom function (also known as User Defined Functions or UDF’s) to get the job done. Continue reading