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?”
Category Archives: Tips and Tricks
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 andContinue reading “Fit Worksheet to Window in Excel”
How To Quickly Get A List All DAX Formulas From Your Excel 2010 PowerPivot Data Model
In this post, I describe a quick way to list all the DAX formulas in your PowerPivot for Excel 2010 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 theirContinue reading “When do I use the Sheets Collection in Excel VBA?”
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 cellContinue reading “How to deal with the Numbers Stored As Text Error in Excel?”
Local trumps Global – Local Named Ranges make the spreadsheet more tractable
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 reducing the time spent on testing and scrutinizing the sheet. improving theContinue reading “Local trumps Global – Local Named Ranges make the spreadsheet more tractable”
Codename to the Rescue! – The best way to refer to a Worksheet whilst developing a Spreadsheet Application
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
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,Continue reading “Excel Worksheet Functions and VBA Functions”
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”
