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.

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 inContinue 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.Continue reading “Is the WorksheetFunction property necessary in Excel VBA?”

Concatenate Unique Cells, with Text Formatting


A fellow struggler requested me to help him with a slightly beefed up Range Concatenate function. He needed to combine a bunch of numbers in a range; there were multiple entries of the same number, and he wanted a unique list; finally he needed to format the numbers.

Clean a string for Range Names – Regular Expressions


I am a big fan of using named  ranges in my VBA code. It makes referring to cells in formulas easy, and I can also helps in auditing the code. For instance, I can simply search for “RangeName” (including the quotes)  to see if that particular range name is being used in my code.

Introduction to Regular Expressions in VBA


Regular-Expressions (RegExp) is something I bumped into when my string manipulation requirements jumped to an advanced level. Before I started using this powerhouse, all my string manipulation functions involved maneuvering through a dozen for-loops; and tackling hundreds of Left-Right-Mid-InStr functions. RegExp is a pattern matching system that allows you to perform advanced string manipulations very easily. ItContinue reading “Introduction to Regular Expressions in VBA”

Reversing a string in Excel


In my many years of rummaging through the internet for help, I have seen countless posts where troubled help seekers are told that VBA does not have a built in function to reverse a string. Amature know-alls suggest a makeshift solution using a for-loop; and everyone is happy. Contrary to popular belief, Visual Basics doesContinue reading “Reversing a string in Excel”

Concatenate Range and Retain Formatting


In one of my previous posts, I wrote a function to concatenate the values stored in all the cells of a specified range. That function holds good, if you have a text stored in all the cells, and you just want to club them together. It fails if you have cells containing numeric values: dates,Continue reading “Concatenate Range and Retain Formatting”

An upgrade to the Text() function in Excel


Many of us are familiar with the Text() function. It comes in handy when you set up excel to draft reports for you. It converts a numeric value stored in a cell to a string based on  the format specified by the user. The only thing I hate about that function is that you haveContinue reading “An upgrade to the Text() function in Excel”