Daily Dose of Excel

I have a table of numbers and formulas for each row, column, and for the table as a whole. The table is part of a report - the output of the application. The user wants to exclude certain numbers from the calculations after reviewing them. These numbers would be outliers and would skew the results. The calculations are AVERAGE and STDEV functions. If a number is excluded, it needs to still be shown on the reports, but with a strikethrough format.

The obvious course is to modify the formula when the user has identified a cell to exclude. With formulas for every row, column, and for the whole table, that's a pretty big job. An easier way is to change the numbers to text. Both AVERAGE and STDEV ignore text, so this would have the effect of excluding the numbers from the formulas without having to change the formulas. I started with something like this:

This is in the worksheet's BeforeDoubleClick event. I use the strikethrough property to determine if the number has already been excluded. The user can double click the number to toggle between inclusion and exclusion. Excluded numbers have an apostrophe put in front of them (making them text) and the font is changed to strikethrough. Included numbers are changed back to a Double (using CDbl) and the strikethrough is removed.

Incidentally, not every number can be excluded. I've applied a particular style to those numbers that can be excluded and I limit the event like this:

A new wrinkle appeared. Now some of the numbers are actually formulas. That complicates the above code snippet a little.

Instead of putting an apostrophe in front of the value to make it text, I surround it with the TEXT function. This has the added benefit of keeping the same number format applied to the text as was applied to the number. When a number is excluded (the Else part), I start with "=TEXT(". Then I repeat the existing formula, removing the equal sign if there was one (Mid(.Formula, lStart, Len(.Formula))). The suffix to this string manipulation is the existing NumberFormat surrounded by double quotes.

When a number is included, the TEXT portion of the formula is removed. The Mid function starts at Len("=TEXT()"), which is a verbose way of saying 7. The length of Mid is the length of the formula, minus the length the text function (including parentheses, the comma that separates the number format argument, and the quotes that surround the number format), minus the length of the numberformat.

This has the strange side effect of converting a number like 3 into a formula like =3 when it's toggled. I can't think of any ill effects of that, but there may be.

Hi All,

If you do some VBA programming regularly, you'll be acquainted with the Object browser, which neatly shows you all objects, properties and methods belonging to any object in Excel (or any other library you have referenced in your VBA project).

Whilst this is a tremendous tool to look for information on just a couple of properties I find it not very handy when you e.g. need to get all of an objects properties into your VBA code.

So I thought I'd roll my own object browser, just for the fun of it (and because it proved useful, I share it here).

The tool uses the tlbinf32.dll present on (I assume) any current Windows installation to fetch data from typelibraries. I grabbed some code from various places (amongst which Chip Pearson's great site and some newsgroup messages like this one) and created a userform with a treeview control (because this is the type of control that can show hierarchical information so nicely).

Here is a screenshot of the tool:

Look here for a bit more information and the download link:
Object Lister

Regards,

Jan Karel Pieterse
JKP Application Development Services

I often use Excel's macro recorder to perform repetitive tasks.
It's usually a list of cells with a handful of exceptions. That is, I cant just run the macro from start to finish - I have to give each item a brief glance before the macro runs.
At the end of the macro, it's handy to position the selected cell as the start of the next item in the list.
I'll assign a shortcut key to the macro, such as ctrl+w, then use it on demand.
The process becomes simple: look at the item, is it ok? yes, press ctrl+w, next item, is it ok? yes, press ctrl+w... over and over.

You can reassign your shortcut keys from the Macros window:
From Excel's menu: Tools, Macro, Macros... (or hit Alt+F8)
Highlight a macro, then click Options.

More often than not, the macro recorder will give me a good first draft but I'll have to edit it some more from within the VB editor.
While coding the changes, I wondered where Excel stores the shortcut key.

Could it be that Excel recognises the code comment?

So I deleted the comments to be sure. No, it wasn't the comment.

It turns out that the shortcut key is stored in the Code Module, but it's hidden from sight.

Export the Code Module (right-click the Module, click Export File) then open it in Notepad.

You will notice a line that looks like this:
Attribute Macro1.VB_ProcData.VB_Invoke_Func = "w\n14"

JWalk wrote about a Keyless Entry Hack. "That shouldn't be too hard to duplicate", I thought. I get 3,133, but I have four duplicate numbers. Here's part of my immediate window.

How do I get rid of those duplicates?

As you may recall, I've written about registering User Defined Functions before.

The problem with the trick I used in that post, was (and this isn't trivial): it crashed Excel.

KeepITCool came up with yet another trick to make this work, which I have described in
this article.

Enjoy!

Regards,

Jan Karel Pieterse
JKP Application Development Services

One of my new year's resolutions was to write a procedure to ftp picture files to this blog to simplify the procedure. It took two days, but I finally got it done. I don't know if it's well done, but it works. I got a little help along the way, specifically:

Forestasia has code to determine the image size. Another resolution is to put height and width arguments in my img tags. I tried about 17 other things before finding this page, such as looking at the extended file properties like Walkenbach did with MP3s. I'm glad I found it, though, because I learned something about the GIF file format. Nice use of Get.

Speaking of that, I read the GIF89a file specification to see what else I could learn. I had a little trouble applying the spec to the bits (which I printed to a worksheet for inspection). For one, it was clear from Forestasia's code that the dimensions were stored in two bits; one with size mod 256 and the other with the number of full 256's, but I couldn't find that in the spec. I was going to make an all white GIF and an all black GIF and compare the bits, but I lost interest.

For the FTP stuff, I went to bygsoftware. I read about ftp subcommands in Windows help, but all my attempts to change bygsoftware's code failed. It ended up looking pretty much like they have it.

Finally, since I can never remember how to stuff text into the clipboard, I went back (as always) to Chip's Clipboard page. Forms 2.0. Oh yeah, now I remember.

The code is a bit long, but there's some good stuff in there.

What kind of dink would I be if I didn't actually have a picture on this post? Here's the first part of the 12,365 bits of GIF file on my computer. The bits actually start at 1 (if you're using the Get Statement), but the array they were stored in was zero based.