Is the WorksheetFunction property necessary in Excel VBA?

February 9, 2018Posted inTips and Tricks, User Defined Function
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

October 21, 2017Posted inMacros, Tips and Tricks4 Comments
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.

When do I use the Sheets Collection in Excel VBA?

September 9, 2015Posted inTips and Tricks5 Comments
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.

How to deal with the Numbers Stored As Text Error in Excel?

August 11, 2014Posted inMacros, Tips and Tricks22 Comments
How to deal with the Numbers Stored As Text Error in Excel?

How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your LOOKUP 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.

Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications

April 25, 2014Posted inMacros, Tips and Tricks8 Comments
Prepare for the Show

Hiding a bunch of rows and columns in a sheet before showing it to your boss is inevitable.  I insert blank rows and columns around a table, so I can use the CurrentRegion property of the Range Object in my code. I add labels to all my named ranges in the sheet. I split out complex formulas into a couple of columns. Ultimately I end up with a lot of rows and columns to hide.  I desperately needed a framework to hide and un-hide rows and columns in all my sheets. I experimented with a lot of methods before settling down with one and I would definitely like to know if you have a better way to do it.

Local trumps Global – Local Named Ranges make the spreadsheet more tractable

January 21, 2014Posted inMacros, Tips and Tricks3 Comments
Create from Selection

Creating user friendly spreadsheets is not just a professional courtesy anymore. We have the obligation to help users decipher the spreadsheet we so hastily put together. Spending a little extra time setting up well designed spreadsheets help a firm reduce future costs by

  1. reducing the time spent on testing and scrutinizing the sheet.
  2. improving the productivity of the worker.
  3. helping the ‘new guy’ figure out the sheet in no time.
  4. increasing the visibility of errors, consequently reducing the need for rework.

Codename to the Rescue! – The best way to refer to a Worksheet whilst developing a Spreadsheet Application

January 10, 2014Posted inTips and Tricks7 Comments

Have you ever wondered why there are two properties called “Name” for a spreadsheet? Go ahead, open up the Visual Basics Editor; go to the Project Explorer Window, and select a sheet from the ‘Microsoft Excel Objects’ Node. You will notice that there is a ‘(Name)’ and a ‘Name’ property.

Excel Worksheet Functions and VBA Functions

December 8, 2013Posted inTips and Tricks2 Comments
Struggling to Excel

Many excel users might not be aware of the subtle distinction between Excel Worksheet functions and VBA functions. Majority of Excel’s capability is constructed using Visual Basics for Applications (VBA). VBA is a programming language that contains a fairly rich set of built in Functions; lets call these functions VBA Functions. Using the aforementioned VBA Functions, developers of Excel meticulously create hundreds of functions for their software, that could be keyed into the Formula Bar. These functions are called Excel Functions or Worksheet functions.

Introduction to Regular Expressions in VBA

December 8, 2013Posted inTips and Tricks, User Defined Function13 Comments
Regular Expression

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. It may take a while to get used to it, but once you get the hang of it, the possibilities are endless.