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?”

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”

Do You Hate The Numbers Stored As Text Error?

Convert To Numbers

How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your lookups and Match functions, and arithmetic calculations. Excel has a Convert to Number functionality to help with this situation, but it could be a lot better. You have to deal with your columns one at a time, sometimes one cell at a time. Also, I noticed that if the dataset is huge, excel takes a lot of time to push through; occasionally, it is so slow that you can see the cells getting updated one by one.

Continue reading “Do You Hate The Numbers Stored As Text Error?”

Get Only The Used Areas In A Range

Used Areas

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.

Continue reading “Get Only The Used Areas In A Range”

Format Tables in Excel using VBA

Format Tables

Excel Tables make analyzing data, a breeze. It surprises me that it is not used as often as it should. It automatically “includes” new data you add to your spreadsheets, it automatically drags down formulas for you, it automatically formats the tables for you. In addition to that, you can use structured references that make your formulas tractable without having to name each range. You can also link an Excel Table to your PowerPivot Model. For a comprehensive, yet concise list of stuff excel tables can do, I recommend reading through this page.

Continue reading “Format Tables in Excel using VBA”

To Err is Excel, Handle your Errors with grace


Error handling is an important aspect of programming in VBA, especially if you are writing macros for other users. Unfortunately, many users ignore it completely. Visual Basics is an amazing programming language, but it lags far behind in the error handling department. All we have is the On Error”, “Goto” and the “Resume” statements. These statements allow only a few error handling structures, and each of the structures has its own set of expert proponents. In this post, I am going to share with you, a little block of code that I use to handle errors in all my spreadsheet applications; and hopefully offer a fresh perspective.

Continue reading “To Err is Excel, Handle your Errors with grace”

Progress Bar for all your excel Applications

Progress Bar

Excel is versatile by itself and VBA makes it even better by allowing us to do our own thing. Most of us use VBA to automate tasks of varying complexity – some macros are executed in a flash, but others take hours to run. While there are users who are happy with just a Msgbox “This thing is DONE!”, there are others who’d like to let the user know more about what is happening.

Continue reading “Progress Bar for all your excel Applications”

Mix And Match – Get all possible permutations of an unlimited number of lists in Excel

Mix N Match

This post is a little fun, not much of a Struggle I guess. In excel, we use permutations more often than we realize. Say you have like twenty performance metrics and you’d like to monitor them for each month, imagine how much copy pasting and concatenating would need being done to get your range names in place?

Continue reading “Mix And Match – Get all possible permutations of an unlimited number of lists in Excel”