Daily Dose of Excel

I imagine this is the way it is for most people:

Vast number of ideas of what I’d like to do
    A few get implemented as add-ins, utilities, tutorials, what have you
        Even fewer find their way to the website
            And, it takes even longer to find a proper location for very new topics.

I spent the better part of today adding one new tutorial, one new template, and hooking up several old pages that were already part of the www.tushar-mehta.com website but were never part of the site navigation structure. In the process, I added a new high level category called “Templates.” The following introduces each item.

Enhanced Find: This is a source form utility that I wrote for my own use. I imagine it would have been much simpler if Microsoft had added the UI ‘Find All’ feature to the Excel object model. But, it didn’t, and, in any case, the utility does a lot more than just ‘find all.’ To those who will want to tell me I should have used the TreeView control in the userform used to display the result: I did. {grin} Then, I decided it would be prudent to forgo it since I don’t know how that control finds its way onto a particular machine.

Chart Image to Data: I wrote this utility to help out someone who wanted to convert an image of a graph into the associated data points. No, it doesn’t use artificial intelligence, or OCR, or some fancy heuristic to figure out the data. {grin} Instead, it relies on the user first calibrating the image and then clicking on various points on a series to convert the click location into a data value.

The templates hierarchy: Several templates that I created for various reasons have languished on the website with no good place to put them. So, I finally broke down and added a high level category, Templates. As usual, whenever a file moves from one directory to another, I leave — or at least try to leave — a stub in the old location pointing to the new one.

Solver template to find a subset of entries that add up to a given total: One would expect that when someone makes a payment against a list of different charges (invoices), they would list which charges invoices are being paid. Yet, every so often we find a request for help from someone who has a total and now wants to find a possible subset of pending payments that total the payment amount. This template contains the necessary Solver set up for the task. Obviously, this is not an easy task and the ability to find an acceptable solution is limited both by the requirements and limitations placed on Solver and of the algorithm used by Solver.

A calendar template: Even if I say so myself, I think this is worth checking out. It’s a template that lets one generate a calendar for any year from 1900 to 3000 with the starting-day-of-week set to any weekday. The result can be in one of 2 formats — one month-per-page or 12 months-per-page. The design is clean and simple. And, best of all, no programming!

Compare sizes of TVs, computer monitors, and broadcasts: Over the past month or so, I replaced my dying TV with a HD TV and added a widescreen computer monitor. In the process, I spent a fair amount of time understanding what’s what with widescreen devices and HD broadcasts. This template addresses one specific aspect of that research: comparing the sizes of various devices. One thing I realized very quickly in my research was that a widescreen device would have to be somewhat larger than its standard counterpart if it was to have at least the same height. I did most of my work with algebra. But, then decided I’d share some of it in the form of a template. While creating the template I also realized it could be used to explain the bands that appear when one views a broadcast in one format (say widescreen) on an incompatible TV (i.e., a standard size TV). I also realized that the work in the template would form a good tutorial for data validation.

Data Validation I: There’s a whole bunch of stuff I have on data validation that I’d like to share. Given the big weakness in Excel’s native data validation (copy+paste into a cell to wipe out the validation criterion in it!), I prefer alternatives. This tutorial documents some simple ways to validate data without Excel’s Data Validation or VBA. There’s a whole bunch more I will share in the weeks/months to come.

Userform coding interface: I started work on a VBA chapter — and it is far from complete — that addresses two favorite topics of mine: (1) The structure of the interface between the code that manages a userform and the code that does the actual task of a utility, and (2) The RefEdit control including a workaround that lets one simulate its functionality in a modeless userform, together with a class module that lets one “drop” the solution into any userform.

I’m having a small problem with the New Workbook Taskpane in Excel 2003 (11.6355.6360). I was investigating a problem dealing with application level events and book.xlt when I “discovered” that creating a new workbook from the task pane does not do what I expect. I created a template file named book.xlt and saved it in my xlstart directory. When I press Ctrl+N, a new workbook based on book.xlt was created. When I choose File > New, I get:

I dutifully choose Blank workbook, and I get a new workbook but it’s not based on book.xlt. What the heck is the point of book.xlt if File > New doesn’t use it? You could make the argument that choosing Blank Workbook gives you a blank workbook, but it seems to defeat the purpose. Am I doing something wrong here?

When you select New from the File menu, the New dialog box is displayed. Usually, the New dialog will have some tabs that organize the templates that are available. This example has the General, Spreadsheet Solutions, and Office 97 Templates tabs.

You can add your own tabs to this dialog by adding subfolders to the Templates folder. One way to find your templates folder is to type ?Application.TemplatesPath in the Immediate Window of the VBE.

If you have more than one version of Excel on your computer, Excel will show the contents all your Templates folders in the New dialog, even those from previous versions. Note that in my Templates folder that there is no Spreadsheet Solutions folder. It does exist, however, in the Templates folder of my Excel97 installation.

I’ve added a new folder called Dicks Templates. This folder will show up as a tab on the New dialog, but only if there is at least one Excel template in it. Once I put a template in it, the New dialog looks like this:

Someone sent me an email asking what this add-in was, but my reply bounced, so I’m posting it here. This file is from the Analysis Toolpack (I mean Toolpak). It holds all the code for the ATP functions like NETWORKDAYS. If you see it in your Project Explorer in the VBE, but don’t want it there, you can uninstall the add-in using Tools > Addins