Daily Dose of Excel

I updated the installation instructions for Office VBA add-ins to include Office 2010, Office 2007, and Office 2003. The updated material is at http://www.tushar-mehta.com/excel/software/installation/index.htm.

Tushar Mehta

There has been many an occasion when I have wanted programmatic access to the maximum or minimum or smallest value of a data type. Some programming languages have built-in support through names like MaxInt. VBA, unfortunately, is not one of them.

I decided to “translate” the documentation defining the different data types into code. The functions Max{datatype}, Min{datatype}, and Smallest{datatype} return the appropriate value. The Max and Min functions should be self-evident. The Smallest function returns the smallest non-zero value supported by the data type. Since this is simply 1 for all data types associated with integers (Byte, Integer, Long, LongPtr, LongLong, and Currency), Smallest is defined only for data types associated with real numbers (Single, Double, and Decimal).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1003%20MinMaxVals.shtml

Tushar Mehta

I came across a line of code that formatted a date in VBA like this: Format(aDate,”short date”). And I didn’t get it. So with a little investigation, it turns out that “short date” (and “medium date” and “long date”) relate to the specified system methods of expressing dates found in the regional settings. So I wrote this little bit of code:

This is what I got:
06/05/2012
5-Jun-12
Tuesday, June 5, 2012

On both my XP box and my Mac, except that on the Mac, “long date” maps to Full. Trouble is, on neither machine do I have a “medium” setting, or a setting that maps to “medium date”. So. it looks like “medium date” is XL internal.

I’m not sure what great good these choices do. It would appear to be full proof to just specify the format. This capability does not extend to the spreadsheet TEXT() function.

Here’s a page that covers it: http://www.techonthenet.com/excel/formulas/format_date.php
You can also do the same with Time.

…mrt
©¿©¬

By default, when the user selects a cell, Excel highlights the row and column by changing the color of the associated row and column headers. This tip shares multiple ways to highlight the row and column in more obvious ways as well as a way to highlight the cell in a specific column in the same row.

The emphasis is on the use of conditional formatting to accomplish the task. The minimal VBA code required to make it work is the same single executable statement for all of the different highlighting options!

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0121%20highlight%20row%20and%20col%20of%20selected%20cell.shtml

Tushar Mehta

The other day a reader asked about how to get a font list to a combo box so his users could have a choice to specify. Borrowing shamelessly from John Walkenbach’s Tip 79, this is one way to do it. I use this approach when I take a spreadsheet to a web table and want to specify the font.

From top to bottom, the form has:

• A label, named lblFontcboOverLabel
• A label, named lblFontcbo
• A combo box, named cboFontOther
• A frame, named Frame1
• An option button, named btnAllFonts
• An option button, named btnMonoFonts

This is the code behind the form:

It has the “Get Property” at the top. I screen the installed fonts to filter the Asian fonts from my list. Remove the “Like” operator if you need them. I capture the the installed fonts as a global string variable in a comma-delimited list. The list of monospaced fonts came from here, and represents the fonts installed as a minimum on ⅓ of all Windows, Mac, and Unix computers. They’re listed in percentage order irrespective of machine and not by installation count.

As I use the full form, the Over Label lies atop the combo box. But if you want to see more than the font’s name in its own font, modify the combo box change to something like this:

…mrt
©¿©¬

Writing for Wikipedia is a bit like writing in your third language—you know there are words for it, but your guides are from your second language—Wiki pour Les Nuls or maybe Wiki para Maniquíes. It’s a different dialect of HTML. Having done it for several years, Wikipedia’s goal seems to be a reduction in keystrokes to be saved on its servers. For example, to italicize, rather than using the i- or em- tags, you use paired apostrophes (not double-quotes) to lead and close, saving at least three key strokes. To embolden, you use three apostrophes to lead and close, saving at least one stroke over the b- or strong- tags. And bold italics is thus five apostrophes leading, five apostrophes closing, saving at least four strokes. An unordered list’s members are just lines started with an asterisk, and an ordered list’s members are lines started with a pound sign. This economy follows into Wiki tables, where pipes (“|”) and curly braces (“{}”)are used for all the tags. There is a good summary of the table markup differences here. This little table:

 
In Wiki is this:

as opposed to this:

If you have a table like this is in your spreadsheet:

 
The MakeWikiTable() macro will make a table like this for Wikipedia:

And this table G33:H38

 
where H34: = Text(J34, "0.00") & K34 is aligned right and filled down, looks like this:

Tapping into the class = “wikitable”, MakeWikiTable() can make any combination of sortable, collapsible, and collapsed tables. It invokes the class if the upper left table cell is not empty, and thus your (literally) first column is data vice row headers. There is a longstanding bug in the wikitable class that disables sorting if there is too much adornment given the table, no matter if the table is set as “sortable” or not. The combinations look like this in Wikipedia:

All these examples are available here. You’ll see the footnotes you created in Excel are at the bottom of the Wiki page. It looks different at Wiki Commons, but the code still works. Examples of a tables I made there with MakeWikiTable() are these. You’ll see it uses a sideways arrow instead of text for hide and show.

This is the MakeWikiTable code:

Note that the Caption is at Offset(1,0) from the your selected table. Wikipedia’s examples all use muted grays for the headers. The first two of the following are the provided colors, the third, aka “silver” is what I used above.

The macro should be invoked from a form instead of daisy-chained message boxes. I got lazy. The MakeFracs() function is here. If you want to put something in the upper-left cell, and still get row headers, put it in after you paste your table into Wikipedia.

… mrt
©¿©¬

HTML5 provides for fractional representation of halves, thirds, fourths, fifths, sixths, no sevenths, and eighths. Excel has a fractional number format. This post is about bringing these concepts together for exporting an Excel table into Wiki or HTML designs. The basic representations are:

 
The format for the name is &fracnd; where n is the numerator and d is the denominator. Thus ½ is a half, and ⅞ is seven-eighths. The HTML code representations for these are:

 
In theory (more on “in practice” later) every representation in a row is equivalent. This is our test table to export to Wiki or HTML format:

 
Cell D1: =CHAR(32)&ROW()&”/”&COLUMN()-3, then fill down and right. The right hand column has a few test cases. I used that formulaic construction to keep Excel from doing the divisions. The Excel fractional format is “# ?/?” for single digit denominators. The intermediary space is important. It indicates that a fraction may follow, just as a forward slash indicates a fraction may be present. And a format of /?? is a fraction not translatable into HTML5. Turning these patterns into VBA, this is my MakeFracs() function. It checks that there is a slash, then that there is not a slash–digit–digit pattern, and finally that there is a “space–digits 1 through 7–slash–digits 2, 3, 4, 5, 6, 8″ pattern to screen out sevenths and ninths. If all of those pass, it substitutes in the &fracnd; formulation for the fraction.

 
At least that’s all I wanted it to do. In practice, Wikipedia and WordPress seem to be not fully onboard with HTML5 and do not handle all fifteen &fracnd; formats (I confirmed Firefox does). That added “j-loop” in the middle translates the &fracnd;’s into Dec code. This works fine, though it’s a step back from HTML5. Option is given to use Hex if desired. Your Excel table then looks like this:

 
Where N1: =MakeFracs(D1) filled down and right. Arranged that way you can see the HTML5 design thoughts. The very ugly website would look like this:

 
Frankly, I’m not sure that this is an improvement. You’ll come across Wikipedia editors, however, who are convinced of it. I thought about adding a trailing space, as in MakeFracs = VBA.Replace(Arg, sOUT, sIN & Chr(32)), but for every time I wanted to, I thought of an example where I didn’t, and the logic got very convoluted. Better I decided to put the space in the table where wanted and not in the function. My HTML tablemaker is here, but it’s being overcome by the hard steady march of technology. Wiki and CSS tablemakers are coming up. I used MakeFracs() in the above. No fractions were harmed in the making of this post.

…mrt
©¿©¬

To test for empty cells, use the IsEmpty function. IsEmpty takes one argument, a variable, and returns True if that variable contains nothing (Technically, I think it returns True if the variable is uninitialized). While it’s true that you can pass variables to IsEmpty, you can also pass object’s properties, specifically the Value property of the Range object.

Many times you will see programmers test for a zero length string, like this

If Range(“A10″).Value = “” Then

In 99% of the cases, that will work. However, if the cell contains a single quote and nothing else, then it will contain a zero length string, but will not really be empty. To test for true emptiness, use IsEmpty on the Value property.

Update: A newsgroup post by Otto Moehrbach prompted me to look a little deeper into how IsEmpty works. I wanted to determine why a cell with a formula that returns an empty string behaves differently than an empty cell. I set up a watch for A1 and B1 to see what was going on. A1 contains the formula =”" and B1 contains nothing at all.

A1 is a Variant/String and B1 is a Variant/Empty which obviously accounts for the difference. There’s nothing too shocking here, it’s just an interesting glimpse into the inner workings of VBA. Although the “interesting” part is debatable.