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.
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.
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.
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")
[Read more…] about Excel VBA: 6 Ways to Find the Last Row in a ListIf 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.
[Read more…] about Count Cells by Colour – Excel VBA FunctionThis 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.
[Read more…] about Export Multiple Sheets to a Single PDF File – Excel VBA
