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.

A quick refresher on English Ordinal Indicators

The general rule is: numbers ending with 1, 2 and 3, get the ordinal indicators ‘st’, ‘nd’ and ‘rd’ respectively. The exceptions to the previous rule are numbers ending with 11, 12 and 13, which get the ‘th’ indicator.
The rest of the numbers get the ‘th’ indicator.

What are control Structures?

For those of you who are new to programming, in general, any code is executed one line after another. And control structures let you break that rule. They let us jump around unconditionally or conditionally. They even let us keep looping through lines. We will be focussing only on the control structures that let us jump around conditionally, which are generally referred to as Decision Structures.

The two popular decision structures available in VBA are:

  1. If… Then… Else
  2. Select… Case

Follow the links in the list for a detailed account on using these control structures. Although these links are related to VB.NET, and not VBA specifically, almost all the principles apply.

I highly recommend reading about the Select Case statement, as it allows you to implement complex decisions with minimal lines of code. I have used some of these principles in this post, and in the end, I will leave you with a weird way of using the Select Case statement, which I prefer over the If… Then… ElseIf… Else… structure.

Now let us take a look at how we can use the control structures to fetch the ordinal indicator of a number. Notice that I am using the Long data type to accept a number as it will let me work even with very large numbers. The integer data type would suffice for most use cases though.

If… Then… Else

Here is the UDF written using the If… Then… Else structure:

Function OrdinalIndicator(ByVal Number As Long) As String
 
If Number Mod 100 = 11 Or _
    Number Mod 100 = 12 Or _
    Number Mod 100 = 13 Then
    OrdinalIndicator = "th"
    ElseIf Number Mod 10 = 1 Then
        OrdinalIndicator = "st"
    ElseIf Number Mod 10 = 2 Then
        OrdinalIndicator = "st"
    ElseIf Number Mod 10 = 3 Then
        OrdinalIndicator = "rd"
    Else
        OrdinalIndicator = "th"
End If

OrdinalIndicator = Number & OrdinalIndicator

End Function

Select… Case

Here is the UDF written using the Select… Case Structure:

Function OrdinalIndicator(ByVal Number As Long) As String

Select Case Number Mod 100
    Case 11 To 13
        OrdinalIndicator = "th"
    Case Else
        Select Case Number Mod 10
            Case 1
                OrdinalIndicator = "st"
            Case 2
                OrdinalIndicator = "nd"
            Case 3
                OrdinalIndicator = "rd"
            Case Else
                OrdinalIndicator = "th"
        End Select
End Select

OrdinalIndicator = Number & OrdinalIndicator

End Function

Now that you have seen the regular implementation of the Select.. Case statement, let us look at the quirky way I promised earlier. You can pass TRUE as the test expression to the Select statement, and then use Boolean expression beside the Case statements to convert the Select… Case control structure into a compact If… Then… ElseIf… Else structure.

Let me show you what I mean:

Function OrdinalIndicator(ByVal Number As Long) As String

Select Case True
    Case Number Mod 100 = 11, Number Mod 100 = 12, _
         Number Mod 100 = 13
        OrdinalIndicator = "th"
    Case Number Mod 10 = 1
        OrdinalIndicator = "st"
    Case Number Mod 10 = 2
        OrdinalIndicator = "nd"
    Case Number Mod 10 = 3
        OrdinalIndicator = "rd"
    Case Else
        OrdinalIndicator = "th"
End Select

OrdinalIndicator = Number & OrdinalIndicator

End Function

Downloads

I hope that you learnt something new about control structures today. Click on the link below to download a workbook, which contains the three UDFs.

Advertisements

Remove Filters in an Excel Sheet using VBA


Has your data import procedure ever failed due to users applying filters on the data? This happens quite often, especially when you distribute multiple files, which you then stack. Copying filtered data copies only the filtered rows. You end up loosing the hidden rows if users filtered data before saving the workbook. Let us take a look at how to remove filters in an Excel sheet using VBA.

Before we dive into how to remove filters, let us briefly take a look at the types of filtering capability Excel offers. Filtering in Excel comes in two flavours:

Auto filter adds dropdown buttons to the columns, which offer you a variety of filtering and sorting options through an intuitive graphical interface. Advanced Filter on the other hand, lets you specify your filtering criteria in a range of cells.

Auto Filter is easy to spot visually; the dropdowns are hard to miss. However, Advanced Filter is a harder to spot. In both cases, if some of the Row Numbers to the left of a sheet appear blue instead of the regular black colour, filters have been applied on that sheet.

Mastering both these skills are essential for anybody who uses Excel at their workplace. There are a multitude of resources on the internet on these topics. I recommend you familiarise yourself with filtering data in Excel.

The ShowAllData method of the Worksheet object lets you remove any filters that were applied on the data. However, it throws a runtime error when filters have not been applied. One could simply supress the error and call it a day, but I’d loose sleep over resorting to such cowardice. Let us try to figure out a legitimate way of finding whether or not filters have been applied on a sheet.

The FilterMode property of the Worksheet object gets set to TRUE, when filters are applied either as Auto Filters or Advanced Filters. This is a Read-Only property. It can be used to check whether any filters are applied, before using the ShowAllData method. This will help us ensure a runtime error is not triggered when calling the ShowAllData method.

If you are interested in removing the Auto Filter Dropdown buttons as well, which is how I prefer it, you can use the AutoFilterMode property to turn it off. This property is not a Read-Only property, which means you can set it to TRUE to activate AutoFilters and vice versa.

Wrapping this all up, here is a Sub that I use very frequently, which you can use in your projects:

Sub RemoveFilters(ByRef WhichSheet As Worksheet)

'If data is filtered either using AutoFilters or Advanced Filters
'Show all the data
If WhichSheet.FilterMode Then WhichSheet.ShowAllData
'Hide the AutoFilter DropDown Buttons
If WhichSheet.AutoFilterMode Then WhichSheet.AutoFilterMode = False

End Sub

Just pass a Worksheet to the above sub to remove filters without encountering a runtime error.

Note that having Excel Tables, also known List Objects in VBA, in the worksheet could throw these properties off, especially when the active cell is in a Table. If you are using Excel Tables, and I highly recommend that you do, you can deal with filters applied to a table specifically. We will reserve that discussion for another day.


Downloads

Click the link below to download a workbook with the above code. It also has a cover macro on removing filters in the active sheet.

Remove Filters V1.01.xlsb
Remove Filters V1.01.xlsb

Further Reading

  • Advanced filters come in handy when you have to apply a rather complex filter criteria repeatedly. It is also easy to filter data using VBA if you create an appropriate criteria range. Coding the application of AutoFilters is rather cumbersome. Here is an introduction to using AutoFilters in Excel:

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.

Continue reading “Removing non-printing characters from text in Excel using 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?

Continue reading “Is the WorksheetFunction property necessary in Excel VBA?”

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.

Continue reading “Fit Worksheet to Window in Excel”

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.

Continue reading “Duplicate a Sheet and retain a reference to it in Excel VBA”

Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners


I have wasted many hours loading huge amounts of data into spreadsheet models, only to inadvertently click the ‘Reset’ button at the last moment, because it was the day my colleague decided to bump into my chair. Excel is not capable of undoing actions performed by a macro, making it very important to confirm whether a user meant to click a button, before performing certain operations. In this post, we will look at how to get the user’s permission before running a procedure, using the MsgBox function.

Continue reading “Are you Sure? Get user’s consent before running a Macro in Excel VBA for Beginners”

How To Quickly Get A List All DAX Formulas From Your Excel 2010 PowerPivot Data Model


It is good practice to keep track of all the formulas you write in a PowerPivot Data Model. I suppose you’d even be required to document the formulas at work, for the audit trail. I was working on a rather large data model, and was manually copy-pasting each and every DAX formula into an Excel sheet; and updated the sheet every time I tweaked a formula. It was frankly a very annoying necessity, and I wanted to remedy the situation. Microsoft introduced the Model Object in Excel 2013, allowing users to access and control PowerPivot using VBA, but there is no straightforward means to programmatically access the Excel 2010 add-in. In this post, I describe a quick way to list all the DAX formulas in your PowerPivot for Excel 2010 Data model.

Continue reading “How To Quickly Get A List All DAX Formulas From Your Excel 2010 PowerPivot Data Model”

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.

Continue reading “When do I use the Sheets Collection in Excel VBA?”

First Class Progress Bar for all your Macros


I believe I owe you an explanation; about this post’s title. At first, you might think I am a pompous prick, branding my work myself as First Class. This Progress Bar was my first project after I learned how to set up class modules: hence the name. The progress bar that I developed earlier, is one of my most visited posts, and I thought I have to reward my readers with a progress bar that is much easier to use.

Continue reading “First Class Progress Bar for all your Macros”