English Ordinal Indicators in Excel using VBA

English Ordinal Indicators in Excel using VBA
I often need to determine the ordinal indicator (st/nd/rd/th) associated with a number, especially when I am working with dates. In this post, we will learn to write a simple user defined function in VBA, which will identify the ordinal indicator of an integer. Since the logic is quite straightforward, let us see how we can use the different control structures available in VBA.

Remove Filters in an Excel Sheet using VBA

Remove Filters in an Excel Sheet using VBA
Has your data import procedure ever failed due to users applying filters to the data? This happens quite often, especially when you have to distribute multiple files, which you intend to stack later. Copying filtered data copies only works on the visible cells; and you end up loosing out the hidden rows if the user applied a filter before saving the file. Let us take a look at how to remove filters from an Excel sheet from VBA, so you can copy the whole dataset.

Removing non-printing characters from text in Excel using VBA

Removing non-printing characters from text in Excel using VBA

Have you ever encountered a perfectly constructed MATCH or a VLOOKUP function failing for no apparent reason? Have you had VBA tell you that a file does not exist even though it does? You may have non-printing characters to blame. There are notorious space-like characters that are invisible to the naked eye, which sneak in when you are importing data from Web Pages, Word Documents or PDFs. In this post we will learn more about these characters and how to remove them from your data.

Is the WorksheetFunction property necessary in Excel VBA?

Is the WorksheetFunction property necessary in Excel VBA?

Excel’s Application object’s WorksheetFunction property is a container for Microsoft Excel worksheet function. This property returns a WorksheetFunction object that allows VBA access to the rich set of functions that are built into Excel. While VBA has some generic functions of its own, Excel’s set is much bigger, and more suited for (you guessed it) Excel. As soon as you type in the dot after WorksheetFunction, you will see a list of Excel functions that you can use in your VBA code. But do you really need the WorksheetFunction object?

Fit Worksheet to Window in Excel

Fit Worksheet to Window in Excel

Have you ever felt Excel needed a Fit-Document-to-Window-Width feature like the millions of PDF Readers out there? Wouldn’t it be great if you could automatically fit your document to your current window size? The irony is, Excel already has all it needs to deliver that feature to you. Since spreadsheets come in various shapes and sizes, you might want to fit the width, or height, or the entire sheet to your screen. In this post, we will take a look at a routine that I wrote that you can use to fit any sheet to your window. And for you lazy bunch, I have an add-in that will let you call these macros from the Excel Ribbon.

Duplicate a Sheet and retain a reference to it in Excel VBA

Duplicate a Sheet and retain a reference to it in Excel VBA

I recently worked on a project where my client had a Master template-sheet that needed to be duplicated and populated repeatedly. I needed to duplicate a sheet, and retain a reference to it, so I can rename it, reposition it, and pass it to another sub that populates it with data. I assumed there must be a very straight forward way to that, but was utterly disappointed when I found out there wasn’t.

When do I use the Sheets Collection in Excel VBA?

When do I use the Sheets Collection in Excel VBA?

There are two collections in Excel-VBA that lets the user access sheets in a workbook: the Sheets collection and the Worksheets collection. You can use these collections interchangeably in most situations, but they were each created for a specific purpose. Read on if you’d like to know more about why Microsoft created those two collections and their purpose.

Magically Select All The Used Areas In A Sheet using VBA

Magically Select All The Used Areas In A Sheet using VBA

I recently published a post about automatically formatting a table in Excel using VBA. That got me thinking, how awesome it would be, if we could format all the tables in a sheet, with a single click. For that idea to work, we need to get all the used areas in a worksheet; and then use the Areas Collection to loop through the tables. We can access the Areas Collection through the Areas property of the Range object.