Category: VBA Code Snippet

VBA in Excel does not restrict us to just a single application. VBA gives us access to the windows environment too. With this we can perform a lot of common file-based actions. One of the most common is to rename a file. In this post, we will look at 5 examples of renaming files with Read More

In VBA, there are several ways to run a macro from a macro, and even run a macro from another workbook. On forums, these methods are used interchangeably depending on the commenter’s personal preferences. This can lead to confusion for anybody trying to learn VBA. Depending on the method for running/calling the macro, the syntax is slightly Read More

Protecting and unprotecting sheets is a common action for an Excel user. There is nothing worse than when somebody, who doesn’t know what they’re doing, overtypes essential formulas and cell values. It’s even worse when that person happens to be us; all it takes is one accidental keypress, and suddenly the entire worksheet is filled with Read More

Since Excel 2010, it has been possible to save Excel as PDF. The PDF format was then and continues to be, one of the most common file formats for distributing documents. The code examples below provide the VBA macros to automate the creation of PDFs from Excel using the ExportAsFixedFormat method. This means you do not Read More

This post looks at how to loop through all items in a Data Validation list with VBA. This was inspired by a question from a reader. He wanted to save a PDF for each item in a Data Validation list. However, once we master the looping process, we are not restricted to PDFs; we can Read More

Automating processes with VBA can involve copying, moving, deleting and managing lots of files.  Thankfully, VBA has many built-in functions to undertake these tasks for us.  These files do not necessarily need to be Excel workbooks; they can be any file type. Download the example file I recommend you download the example file for this Read More

While it is not often that we have to control pictures or images within Excel, based on the number of Google searches for this topic, it’s clearly something people want to know more about. This post serves as a reference to cover most of the scenarios we’re likely to encounter to copy, insert, move, delete Read More

Listing filenames in a worksheet, printing all the files to PDF, or making changes to every file in a folder, there are many reasons why we may want to loop through files in a folder.  VBA provides us with a few ways to achieve it (1) Dir function (2) File System Object. Let’s briefly look Read More