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

Export Multiple Sheets to a Single PDF File – Excel VBA

This tutorial will demonstrate how to export multiple sheets of a workbook into a single PDF file using Excel VBA. The code can be seen below and adapted for your own use.

I have previously written a tutorial on exporting all the sheets of a workbook to PDF using VBA. This macro will allow you to be more specific as to what sheets are exported. It will also export to a single PDF file, rather than a separate file for each sheet. Continue reading

Loop Through Subfolders using FileSystemObject

A common Excel VBA requirement is to work with different files and folders of a directory. It may be that you need to search and open a file, list the names of files from a folder, print all the files from a folder, or even delete files.

The different tasks you may need to perform are endless, but fortunately Excel VBA provides an easy way to access and perform operations on the files and folders of a directory. This easy way is known as FileSystemObject.

To use FileSystemObject, otherwise known as FSO, you first need to create an instance of FileSystemObject. You can then access some of the objects from its model such as File and Folder to perform different tasks. Continue reading

Email Workbook as an Attachment – Excel VBA Macro

The functionality in Excel to send a workbook via email as an attachment is fantastic. However if you find yourself using this feature often, you may have a desire to automate or speed up the process.

I have been asked this many times in my Excel VBA classes so thought it would be beneficial to share some code to accomplish this.

This Excel VBA Macro will attach the current workbook as an attachment to an email and send it. It will add some text to the subject line and body of the email also.

This code can be adapted to your own needs. I will explain how to do this and the lines you will need to change. There are also a few things to look out for that may cause you problems.

This macro should work for any email service. I have used it with my GMail and my Yahoo accounts successfully. The code may just need some tweaking to work for your own situation. Continue reading

Pick Names at Random from a List – Excel VBA

In this blog post we will explore how to pick names at random from a list using Excel VBA.

Suppose we were picking the names for a draw or competition and needed to generate a list of maybe 3, 5 or any number of names from a list.

These names must be selected at random and be unique. You cannot select the same name more than once.

The macro code shown below can be copied and pasted into a VBA module and adapted for your use. Comments have been used to identify the key and more complex parts of the macro.

This macro uses an array variable to store the names being randomly selected from the list. Every time a name is selected, it is checked against this array to ensure that it has not already been selected. If it has, then another name is randomly selected. Continue reading

Export All Sheets to PDF – Excel VBA

This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of a workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.

By creating a macro this seemingly repetitive task can be performed at the click of a button. Continue reading

Add a Hyperlink to a Userform – Excel VBA

If you have ever tried to add a hyperlink to a userform, you would have noticed that there is no hyperlink button on the toolbox.

You may not be able to insert a hyperlink like you would on an Excel spreadsheet, but you can create a link by inserting a label and applying the OnClick event.

Adding a Hyperlink to a Userform

In this example we will add a hyperlink so users may email an enquiries department from the userform. Continue reading