Website update

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.

Posted in Uncategorized

6 thoughts on “Website update

  1. Hi Tushar,
    The Chart Image to Data….reminded me of a file called Grabit….basically you could click on two points in a picture and key in kdistance in an input box and then click on any other 2 points and it would give you the distance…..I found it on a website…dont recollect where…

  2. Hi JKP: Thanks for the pointer to the FlexFind tool. It definitely seems to have its value. At the same time, I think the key value of my tool is the structured hierarchy of locating occurrences and the *post*-find features.

    Hi Sam: Grabit might have been the program I tried to find before I rolled my own but can’t be sure. In the version I put together I did consider using only two points to calibrate the image but decided that clicks on the axes (which meant requiring 4 clicks) would be easier for the user than hard-to-pinpoint locations like (Xmin, Ymin) and (Xmax, Ymax).

  3. Well, Flexfind takes you to the found objects too, but there is no multiselect.

    You can have a list created of all found stuff on a worksheet though.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *