Category: Tips and Tricks

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 →

Advertisements
by Categories: Tips and TricksTags: , , , , , 2 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.

Continue reading →
by Categories: Tips and TricksTags: , , , , , , , , 6 Comments
Convert To Numbers

Do You Hate The Numbers Stored As Text Error?


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 →

by Categories: Macros, Tips and TricksTags: , , , , , 13 Comments
Prepare for the Show

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


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.

Continue reading →

by Categories: Macros, Tips and TricksTags: , , , 7 Comments
Create from Selection

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

  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.

Continue reading →

by Categories: Macros, Tips and TricksTags: , , , , , , , , , 6 Comments
Name Property

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.

Continue reading →

by Categories: Tips and TricksTags: , , , , , , 8 Comments
Struggling to Excel

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, 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.

Continue reading →

by Categories: Tips and TricksTags: , , 2 Comments
Regular Expression

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

Continue reading →

by Categories: Tips and Tricks, User Defined FunctionTags: , , , 14 Comments