Naming Macros Archives - Struggling To Excel

Magically Format Tables in Excel using VBA

By Ejaz June 2, 2014Macros5 Comments

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.

Initialize Local Range Names in VBA Quicker

By Ejaz February 9, 2014Applications18 Comments

I always use locally named range in all my spreadsheets, in fact I wrote a post about it earlier. I extend my love for named ranges even while writing VBA code for spreadsheet applications. Using a pure Offset function based code, or a Cell Reference based code, in my opinion, is not the best way to go. Having named ranges in worksheets, and updating them to include more data before processing is the best way to go.

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

By Ejaz January 21, 2014Macros, Tips and Tricks3 Comments

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 the productivity of the worker.
helping the ‘new guy’ figure out the sheet in no time.
increasing the visibility of errors, consequently reducing the need for rework.