January 2014 - Struggling To Excel

Excel Report Generator – Fill Reports with Records, Print and Save Automatically

By Ejaz January 31, 2014Applications, Macros3 Comments

Many small-scale businesses do not use database management systems to generate reports. Most of them stick to spreadsheet packages. Unfortunately Spreadsheet softwares are not equipped with advanced query and report generating features. However, some Excel users struggle with Reports that they update, save and print manually. It is a very tedious job, but VBA can make it better. I have created a spreadsheet application you may used to fill a template/report with different “Records“, save each in a separate workbook, and print automatically.

Range Navigator – Smarter Navigation for Excel

By Ejaz January 25, 2014Applications1 Comment

My previous Excel Navigation add-in fetched a few fans; I got a lot of positive feedback on LinkedIn. I designed it to help people working on enormous spreadsheets. I then realized that big workbooks usually host many sheets. I received a few requests to add worksheet selection functionality into the form. Nothing makes me happier than to help my fellow strugglers, so I worked on an extension.

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.

Navigate in Style! – An Excel VBA Add-in to help get around in a sheet

By Ejaz January 13, 2014Applications4 Comments

Have you ever been frustrated about having to scroll through endless rows and columns in an Excel Spreadsheet? I have! With all the amazing touch screen devices flooding the market, having to use the scroll bar seems a bit archaic. I longed for a better solution and transformed that yearning into a fun little project. I drew inspiration from the navigator panes in graphics design suits and strategy games.

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

By Ejaz January 10, 2014Tips 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.

List Files in a Folder and Rename them – Batch Renamer

By Ejaz January 1, 2014Applications67 Comments

Some of the tasks I had to do at work involved running a couple of “master” spreadsheet models every month for new market conditions, for a lot of clients. Running the models took ages, but the part I loathed the most was, I had to rename each workbook individually. I figured, my time is far too valuable and created an Excel Spreadsheet Application to rename the files for me.