Daily Dose of Excel

When you carol along with The Twelve Days of Christmas do you think of spreadsheets? I do, and that certainly says all you need to know about my musical ability. The folks at PNC Wealth Management do also. Annually they issue their Christmas Price Index, with the 2011 version here. Spreading the data out, The Twelve Days of Christmas looks like this:

PNC provides the Bundled Cost. We have to work backwards to find the Unit Cost. In other words, eight total maids-a-milking cost $58. The data sources are at the Wiki link. The poor dairy lasses are working minimum wage.

For DDoE, PNC’s table is a good way to review the concept of named ranges. Define Christmas as =Sheet1!$B$2:$M$14, _12th_Day as =Sheet1!$M$2:$M$14, Bundled_Cost as =Sheet1!$P$2:$P$14, Maids as =Sheet1!$B$6:$P$6, Quantity as =Sheet1!$N$2:$N$14, and Unit_Cost as =Sheet1!$O$2:$O$14.

The total number of gifts can be =SUM(Christmas) or =SUM(Quantity), being 376 (more on this later). The cost of the 12th Day of Christmas can be =SUM(Bundled_Cost) or = SUMPRODUCT(_12th_Day,Unit_Cost), being $24,263.18. The total expenditure of your True Love is =SUMPRODUCT(Quantity,Unit_Cost), or $101,119.84. A very generous person, your True Love.

We can use the Intersection Operator, a space, as =Maids Quantity, to find that we need 40 work hours from them. The value of named ranges is in the legibility it brings to your spreadsheet. You can expand this until you cover all days and all gifts. The work is in the defining of the names, the benefit is in the presentation.

PNC says the total gifts number 364. Surely a partridge and a pear tree count as two gifts in your household, no? Happy Holidays!

…mrt
©¿©¬

As you may have figured out, I’ve had some trouble with the site for a while. We lost the database server and had corrupted backups. About 98% of the posts were recovered and I’m not yet sure which are still missing. I know that at least some of the links to downloads are broken and it may be all of them. If you had a username and password, those will be changed. I’ll keep you posted on new login credentials.

Sorry for the inconvenience. I should be back up and posting soon.

How was that video? Pretty super-awesome, huh?

You can download VBHelpers.zip

It’s rough, to say the least, but if you want to mess around with it, have at it. Here’s some more information on what was happening in that video:

0:00 First I insert a class module. Inserting modules is one of those activities that bugs me. I do it enough that the extra few steps get on my nerves. Normally, I Alt+I+M/C/U. Then I F4 to open the properties box and change the name. The utility that I use now does a few things. It prompts for a module name. Based on the first letter of the module name, it creates the appropriate module.

M = Standard Module and adds a private constant to the module called msMODULE with the module’s name. This is for the error handling stuff that I copied out of Professional Excel Development

C = Class Module and adds the line Public ModNameID As Long The CreateParent code relies on the presence of this property.

U = Userform and adds nothing.

0:15 I add some more public variables to the class

0:34 The Convert Public to Property finds all the public variables in the class and converts them to private variables and Property Get and Let/Set statements. This is hardcoded to my personal preferences. Namely, all module level variables start with “m” and another prefix indicating the data type. If you don’t like those preferences, you won’t like this add-in.

0:40 Create Parent Class make a class module whose name is the plural of whatever class module is active. If you’re in CPerson, it create CPeople. CCar spawns CCars. And so on. It creates a text file in your My Documents folder and puts all the ATTRIBUTE goodness so you can use For Each and you can avoid using Item. It uses a collection to store the child instances of the class. People seem to prefer to dictionaries, but I think the extra reference dependency makes it not worth it. It generates an Add method, a property that returns an Item (named after the child class) and a Count property. Then it imports that text file into your project. Already have a class with that name? You’ll probably get an error and the error handling is pretty weak right now.

0:53 Create FindBy There are a couple of things that I find myself typing over and over. One of those is a FindBy property in the parent class. This utility sets it up for you. It prompts you for a space delimited list of colon delimited strings (got that?). In the video, I want a property that returns a CEmployee instance given the EmployeeName property. I type EmployeeName:String to create the code. I could also have created a FindBy property for two or more properties. If I had typed EmployeeName:String HireDate:Date, it would have generated a FindBy that looks like this:

And that’s it for class creation. Parent, child, and FindBy in about one minute. The rest of the video is writing a procedure to show that it compiles and works.

The other code that I’m constantly writing but don’t want to is FillFromRange code. If I have a list of employees in a spreadsheet, I want to generate the code that creates all the CEmployee instances and adds them to the parent class. Coming soon I hope. Enjoy and let me know how it goes.

You probably know that you can save an Excel workbook in HTML format. I do it for my golf league and it works fine. It also generates a pig of an HTML file, mainly because Microsoft is trying to have high fidelity (make it look the same in the browser as it does in the desktop).

For my most recent sports-related project, I just didn’t want such a large file. I run an NFL survivor pool. You can read all the rules if you like, but basically each participant selects one NFL team per week. If that team wins, the participant survives. If they lose, they’re done. Whoever is left at the end is the winner. I needed a quick and easy way to update the results on a web page.

I start with this spreadsheet:

Bold teams means a loss. Italicized teams are winners. Unformatted teams means they haven’t played yet (or I haven’t updated yet). The end result is this:

I found a bunch of images of NFL helmets and a free green checkmark image on the interwebs. Now all I have to do is read the data and convert it to HTML. Here’s the code:

Creating text files from scratch can be a pain in the butt, but HTML files are worse. You have to get all those tags right and properly closed. I hate unindented HTML, so there’s more work there too. Generally I try to work from the inside out on HTML files. That way I can a pass a couple of arguments into a function to make the tags and be assured that I don’t miss something. Take the header section for example. First I pass “DK Survivor Pool” and “title” into the Tag function. That function looks like this:

(WordPress doesn’t like HTML so there’s an extra space in the closing tag part.) I get back something that looks like this

Next, I append the link tag manually because it doesn’t really fit into my inside-out dynamic. Finally, I call sHtml = Tag(sHtml, "head", , True) & vbNewLine to wrap what I have in a head tag. I also set the optional Indent argument to true and get this:

The code wraps whatever I send it, in whatever tag I send it, and indents along the way. The other optional argument is for including attributes within the tag. I want my table tag to have border=”1″ and cellpadding=”5″ so I supply those to the function when needed. I use the class attribute a lot to format winners and losers. I created a separate function to add a class attribute so I wouldn’t have to type it in the code.

The last helper function is to create an image tag. My Tag function is good for enclosing something in opening and closing tags. The image tag is self-closing, so it gets its own home. On the spreadsheet, I record the team name so that it matches the image file name. If I type “eagles” for a Philadelphia pick, the MakeImage function returns <img src="eaglesleft.bmp" />

The main code basically loops through all the cells, determines the HTML necessary, and appends it to one long string. That string is then written to a file. Once complete, I manually FTP that file up to my web server. A couple of other notes on the code:

Losing teams are colored red and that participant doesn’t get to select any more. I wanted all the succeeding weeks to be red also. I use the bLoss variable to handle this. When I get to a loss, I set bLoss to True. When an empty cell is detected (no selection yet), I add the “loss” class to the td tag to color it red.

The last part is the location of the file. My dropbox folder is in two different places on two different computers. I’m not sure why this is, but I think it relates to which operating system was installed when I installed Dropbox. Back in the Windows XP days, Dropbox put it in My DocumentsMy Dropbox and in Windows 7, it’s directly under the user folder and they dropped the “My”. At least I think that’s what happened. To differentiate the two, I found a file that I’m absolutely sure is on one computer and absolutely sure isn’t on the other. I use Len(Dir(..)) to test the existence of the file and change the path accordingly. I think we both know this will break some time in the future, but it works for now.

One of the downsides to the inside-out approach for concatenating HTML is code readability. If I’m just building a string one character at a time, it’s pretty easy to follow along. When I use functions to wrap strings in tags, it’s a little harder. You might expect that the html opening tag would be near the top of the procedure, but it’s actually the last tag I add because it’s “outside”.

Finally, if you’re new to creating big strings in VBA, you should note that to embed a double quote into a string, you use two double quotes in succession.

If you want to get data into Excel from a website that requires a login, you may have already been frustrated that web queries don’t work so well. By automating Internet Explorer and the login process, you can get to that data. This example shows how to login, retrieve a table, and paste it into Excel.

I’ll be using a website I created to demonstrate. The username is

and the password is

. Behind the login, there’s a table that I stole from Contextures.com. I steal that table for all my examples, so don’t tell Debra. If you attempt to get the table, the site recognizes that you’re not logged in and sends you to the login page. Only after logging in can you get to the goods.

To automate Internet Explorer, we’ll need to set a reference to Microsoft Internet Controls.

While you’re in the References dialog, set a reference to Microsoft Forms 2.0 Library. This will already be checked if you have a userform in your project. I’ll use it to put some text into the clipboard.

Before I get to the code, I need to do a little homework. I go to the login page using Firefox and choose Page Source form the View menu.

I take note of the names of the two controls on the form, login and password. I’ll need those later. I also note that the input type is submit. Next, I go to the page I ultimately want and look at its source.

I note that the id for the table I want is “sampletable”. If the table you want doesn’t have an id, you need to loop through all of the tables and try to find some unique characteristic of the table to identify it. Here’s an example that loops through all the tables until it finds the 15-Year-Fixed Average Interest Rate table on Yahoo!’s finance page.

I have all the data I need to produce the code

The code first goes directly to the login page. This wouldn’t be necessary if you already had a cookie remembering that you logged in. However, rather than check to see if we get to the right page, I go login every time.

Next we populate the two controls, login and password, and submit it.

This actually takes us right where we want to be. But that isn’t always the case. Sometimes you’ll want to go a different page than the one that the login script redirects to. Next in the code, I navigate to the page I want.

You’ll notice that every time I navigate somewhere (or submit, causing a redirect), I run the same six lines of code. Those six lines, condensed to two with colons, cause the code to wait around until the page is done loading.

Now that the administrative work is done and my page is loaded, I get the table that has the id “sampletable”. I grab the OuterHTML property, surround it with html tags, and stuff it in the clipboard via the DataObject. The html tags trick Excel into thinking the format of the DataObject is html. Duly tricked, Excel will parse the html and try to make sense of it. It should work just as if you had copied from the web page and pasted into Excel. I select cell A1 and Paste Special Unicode Text.

Finally, I quit IE.

I don’t know if this will work on every website, but it’s worked on a few that I’ve tried. I’ve also experienced some problems with it on other people’s machines. We never got to the bottom of it, but we think it’s firewall related. If you have problems, leave a comment.

I just came across an “Invalid Pattern String” error while use the Like keyword to fill a listbox. I thought Like was more robust than that. Here’s what msdn has to say about it.

I changed my code to this:

Instead of loading the matching sheets, I load “Invalid Pattern” when an error is encountered. I hope I don’t get any other errors.

I stumbled on this when I mistyped an open bracket ([) for a filter. It doesn’t like open brackets without its closing cousin. I did note, however, that "" Like "*[*", that is an empty string for the left hand side, does not produce an error, just False.

Spreadsheets have their CHAR() function, and VBA has its Chr() function. Both return the text character for the specified numerical input, 0 to 255. And spreadsheets have their CODE() function, and VBA has its Asc() function. Both of those return the ASCII code for the leading character of a text string. All well-worn stuff.

But what if you want or need to work with Unicode values? All four functions fail you. As an example, assume you want the true prime character (‘, Unicode 2032) in a string. The prime character, technically, is not an italicized apostrophe (‘), a right single curly quote (‘), or an acute accent (‘).

VBA provides the ChrW() function that does that. ChrW() expects a long as input, but also accepts hexadecimal. Unicode is in hex numbering, so there are two choices: Change U2032 to decimal, or tell ChrW() that the input is in Hex. Since HEX2DEC(2032) is 8242, these two are equivalent:

• ChrW(8242)
• ChrW(&H2032)

Both will put ‘ into a string. If ChrW() repeated the same functionality of Chr() below 256, things would be simple. However, the Windows character set deviates from the Unicode character set for ASCII(128) to ASCII(159). In that range, Chr(CharCode) and ChrW(CharCode) produce different results. As WikiPedia says, Windows “coincides with ISO-8859-1 for all codes except the range 128 to 159 (hex 80 to 9F), where the little-used C1 controls are replaced with additional characters.” Not sure what C1 controls (probably a printer), but if we want to get Unicode to the spreadsheet, do we want it to give the functionality of CHAR()/Chr(), or that of ChrW() which is ISO-8859-1 compliant? Or, optionally both. The function CHARW() takes the optional route. If you set Exact_functionality to TRUE, you can put those C1 controls in your spreadsheet. The default is to do otherwise.

One very nice thing is that you can feed Clng() a hex value, and it will do the HEX2DEC conversion for you.

The VBA function AscW() goes the other way, and has the same ISO problems. It will tell you the decimal code of the first character in a Unicode string, with no regard to the Windows character set. We can make another UDF CODEW() that can optionally specify either the decimal or hex value for the first character is returned, and whether or not to be ISO compliant. The default is to return the HEX unicode (as Uxxxx) and not to comply.

The default will return U2032 when the first character is ‘, and 8242 when Unicode_value is set FALSE. For another example, € is CHAR(128), Chr(128), ChrW(8354), CHARW(128), CHARW(“U80″), CHARW(“U20AC”,TRUE) and CHARW(8364,TRUE).

CODEW(“€”) is “U80″, CODEW(“€”,FALSE) is 128, CODEW(“€”,,TRUE) is “U20AC”, and CODEW(“€”,FALSE,TRUE) is 8354.

To see Unicode characters, the cell’s font has to be set to a Unicode font.

…mrt

As part of “class module week”, I need a function that takes a class module name and makes it plural. I’d like to catch more than 95% of standard nouns and throw in a few non-standard ones for good measure. Here’s what I have so far.

Thoughts?

I wrote some code to list out all the conditional formatting rules in a worksheet. It wasn’t as easy as I thought it would be. In Excel 2007, MS introduced some new format conditions like Icon Sets and Databars that complicate things. As a result, I didn’t get too fancy with the output.

And I got output that looks like this

I couldn’t figure an easy way to loop through all the conditional formatting on a sheet. The FormatConditions collection is a property of the Range object not the Worksheet object. So I looped through all the cells with conditional formatting using SpecialCells. Then to remove the duplicates, I put the FormatCondition object into a collection keyed on the AppliesTo address. Collections don’t allow duplicate keys, so I only get one entry for that FormatCondtion that applies to G5:G10. It’s not fool-proof though. I could have two FormatConditions that apply to the same range and only the first would be taken. That was problem #1.

The second problem was that all conditional formatting aren’t FormatConditions. I put a ColorScale on my sheet and it didn’t like

The FormatConditions.Item property doesn’t return a FormatCondition object, but a ColorScale object. Many of the properties are the same between the FormatCondition and ColorScale objects so I changed cf to a Variant.

I only listed the Formula1 and Formula2 properties if cf was a FormatCondition and ignored all the other types of objects cf could be. It would take too long to list out all the parameters for all the object types and I lost interest.

To convert the Type to a string, I used this function

I wish there was a better way to get at enumerations.

Then, of course, I wanted to show an example of the conditional formatting. Well, that wasn’t going to happen. In conclusion, Excel 2007 conditional formatting is great, but trying to recreate the built-in conditional formatting dialog box is the opposite of great.

Here’s a UDF that’s been done about a million times before. So why write my own? Oh, I don’t know. It’s faster to write it than to find one on the internet and modify it I suppose.

Example 1: Create a wiki table.

Example 2: Create an html table

Whoops, looks like I forgot the ampersand in front of my nbsp. Oh well.