Daily Dose of Excel

Lunch Pairings Classes
Populating Lunch Pairings

My method for creating proposed lunches is to add three random contacts to make a lunch, then see if that lunch meets the conditions. If it does, I add it to the lunches collection class. If not, I try another combination. The portion of the FillMonth method that does this looks like:

I’ve put all my previously mentioned conditions into one property called IsRepeat.

I loop through all the existing lunches, from both past months and those from the month I’m working on. In the first test, I check to see if the lunch is in the same month as the proposed lunch. Then I check if any of the attendees match via the AttendeeMatch property.

This property is bit weird. There are some unused variables in there, for one. In a later post, I’ll talk about the performance problems I had and how I tried to reduce execution time. The AttendeeList property returns a string of contact names, pipe delimited, and sorted by first names. Now that I look at it, I don’t think those names need to be sorted, but I use that property later where they do, so it’s there.

I’m checking my condition that a contact only have one lunch per month. I passed in a “1″ to lMatchMax so that if there was even one match, the lunch gets booted.

For my next condition, I test to make sure that no two contacts are in a lunch in the last two months. That starts by testing whether the lunch is within the last two months via the IsWithin property. Terrible name, I know.

If it passes this test, I call AttendeeMatch again, but this time with a lMatchMax of 2.

The final condition is that no three contacts are in the same group in the last ten months. It’s the same as the previous condition except that IsWithin gets passed a 10 and AttendeeMatch gets passed a 3.

Finally, I write the lunches by month to a range. Back in the main calling procedure,

The RangeOutput property returns a two-dimensional array

The Facilitator is listed first, then the other two attendees. I also spit out the sorted AttendeeList so I can do a countif and make sure I didn’t screw anything up. And I screwed up plenty.

Next up, I’ll discuss some of the performance problems I had and how I solved them.

Yesterday I set up Contact objects and Lunch objects. Now I want to create new Lunch objects that meet the conditions that I defined. From my main LunchTrios procedure:

Ah, the brevity that class modules provide is a beautiful thing. I need FillMonth to:

1. Get a list of active contacts
2. Create a lunch with three of those contacts
3. See if that lunch meets my conditions
4. Keep the lunch if it passes, try another combination if it doesn’t

My first step is to get a list of active participants. I create a new instance of CContacts and store it in the variable called clsActive. Then I create a Property of the CContacts class to return an instance only containing active contacts. My main list of contacts is sorted by first name. I also have 29 active contacts, so two people every month won’t have a lunch. If I keep my contacts sorted, people like Wyatt James are always going to be left out because of his first name. I will have filled up my lunches by the time I get down to him. I need to test lunches in a somewhat random order so that the two people who get left out aren’t the same every month. To do this, I sort clsActive randomly. But I don’t really like sorting right after I populate a class. I have to loop through the contacts to populate the class, then again to sort it. It just seems wasteful. So I do it all in one fell swoop.

As I added qualified contacts to the collection class, I insert the new contact before a random existing contact. This has the effect of giving me a randomly sorted list of contacts in clsActive.

Now that I have my list of active contacts, I loop through them three times creating a proposed lunch. I check if that lunch meets my conditions with IsRepeat and if so, add it to my Lunches collection class. I have a lot of code in there to skip out of the For loop when it’s found a match because looping takes forever. I also skip out when I have enough lunches for the month.

Next up, I’ll go through the IsRepeat property that checks to see if a lunch meets the conditions.

I have a list of 32 people, 29 of whom are “Active”. Every quarter, I need to generate a lunch schedule with certain conditions. A partial list of people looks like this:

The conditions are:

• Three people to a lunch
• The facilitator is chosen at random
• Each person has one lunch per month at most
• Two person combinations (different third person) can repeat after two months
• Three person combinations can repeat after 10 months

It’s to the benefit of the attendees to not have lunch with the same person too often, so the conditions are set to minimize repeated pairings in certain time frames. If I have lunch with Joe this month, there’s no value in having lunch with him next month, even with a different third person, because not enough will have changed with regard to our lunch topic.

My first step is to identify the nouns in my scenario. The nouns are Contact and Lunch so I create a CContact class and a CLunch class and their respective collection classes CContacts and CLunches.

My main procedure is called LunchTrios and looks like this

The FillClasses procedure reads the contact information from a range and creates a bunch of CContact objects. If wshLunch.Range(“A4″) isn’t empty, that means there are existing lunches and I need to read those in. Existing lunches look like this:

The code to populate existing lunches lives in the CLunches collection class.

As people come and go from the group, sometimes an attendee from a past lunch won’t exist in the contact list anymore. I check to make sure that they exist before I add them to the Attendees property. I also set the Facilitator property to 1 as the facilitator is the first attendee listed. For this code to work, I’m going to need an Attendee property and a Facilitator property in my CLunch class.

I have a CContacts class that holds a bunch of CContact instances. One instance of CContacts is held in a global variable called gclsContacts and that one holds all of the contacts on the wshContacts sheet. Each of my CLunch instances also has a CContacts instance to hold the three attendees. I don’t need a separate Attendees class because it would look just like CContacts. The CContacts class module is a template that defines the characteristics of a Contacts object. The instances that are created from that template are separate animals. They are defined by the same properties, but the values of those properties are different for each instance. For example, every cow has a height and a gender, but not every cow has the same height and gender. Height and gender define a cow (a little simplistically), but each instance of a cow is defined by three things: that it was created from a cow template, its specific height, and its specific gender.

The instance of CContacts that I store in gclsContacts has a Count property just like the instance of CContacts that I store in gclsLunches.Lunch(1).Attendees. But the first instance has a Count value of 32 and the second instance has a Count value of 3.

So far I have gclsContacts that’s holding all my Contact objects and gclsLunches that’s holding all my existing Lunch objects. Each Lunch instance also has up to three Contact objects related to it via its Attendees property. Tomorrow, I’ll loop through the months I want to populate new Lunch objects that meet my conditions. No more farm animal analogies, I promise.

I use the heck out the JoinRange function I wrote a few years back. The vast majority of the time I’m using it for two purposes: creating a table in a Trac wiki page or creating an HTML table. I’ve been typing those same delimiters over and over and it has to stop.

I add an optional sMacro argument as the first argument. I can fill this argument with some predefined terms and it will create the necessary delimiters. And as long as I was in there, I change the range looping to array looping. Here’s what it looks like now.

Oh, I’m going to save so much typing.

Contextures had a nice post last week on converting a PDF to an Excel file using pdftoexcelconverter.net. Talk about a URL that says what it does. Jeff Weir commented that he’s used the OCR capabilities of OneNote to do the same thing. I thought a test was in order.

The original Excel file that was printed to PDF using CutePDF

I followed Debra’s instructions and got this result from pdftoexcelconverter.net

It converted the font to Times New Roman and ignored borders, bold, merged cells, underlines, and italics. For pure data though, a pretty good job.

Next, I dragged-and-dropped the PDF into OneNote and chose the “Print Out” option. I right-clicked on the image and chose “Copy Text…” and pasted in Excel.

Yikes. Font conversion is the least of my worries here. Finally, I dragged-and-dropped the PDF into Google Docs and chose the options to convert to Google Docs format and to OCR it. Google Docs converts PDFs to Documents (not spreadsheets) so I wasn’t very hopeful. I didn’t see a way to convert the Doc to a spreadsheet so I saved it as HTML, then opened in Excel.

The whole table is one cell. My conclusion is that OCRing tables is hard.

I love RSS. If you have a website and don’t have a feed, I don’t follow it. I monitor stuff via Google Reader and it’s a great way to keep up on a lot of sites. So it should only follow that monitoring spreadsheet changes via RSS would be great too. Right? Well, not really, but that never stopped me before.

First some caveats. This is just an experiment and not meant for general use. There may actually be some bugs in it, if you can believe that. If you want to modify the code for your own use, be warned that if you monitor too many cells it might be slow. Or you might get so much information that it’s worthless. OK, now that that’s over.

An RSS feed is an XML file that sits on a web server. This blog has such an XML file. When I post this blog entry, WordPress will update the XML file with an entry for this post. Occasionally Google Reader will check the XML file and see if there’s anything new. If there is, it will display the new stuff for anyone who has subscribed to the feed.

It’s fairly trivial to create an XML file, even from Excel. The potentially difficult part is putting that file on a web server. Oh, except for one little thing. I have a webserver right on my computer and you might too. Mine is called “C:\Users\dick\Dropbox\Public\”. Did I trick you? Dropbox has a Public folder and you can get a “public link” from any file in that folder, including the XML file we’re about to create. What a simple way to publish something to the intertubes. Here’s how you get that public link.

Alright, enough screwing around. Let’s get to the code.

I start with a class module called CChange (and its parent CChanges). CChange has the following read/write properties:

I’ve named a range on the sheet called RSSWatch. When the workbook opens, a CChange object is created for every cell in that range.

These CChange objects are just sitting out there waiting to record any changes. They all have a Modified date of 12:00:00 AM (because I didn’t set anything) and in this state they won’t be written to the XML file. In the ThisWorkbook module, I use the Workbook_SheetChange event to monitor my range for changes. You might notice that I switch pretty liberally between sheet-specific references and general references. For instance, in the above Initialize method, I limit my range to Sheet1. When I went to code the event, I thought that someday I would want this to monitor different ranges on different sheets, so I used a Workbook level event. It’s totally inconsistent, but it will be helpful if you just ignore it.

For every cell that has changed, I find it’s CChange brother and change the NewValue and Modified properties. I haven’t written the XML file yet. I still just have a bunch of CChange objects, except that at least one of them has a Modified property that will make it eligible to be included in the file. Changes get made and recorded and only the last value and time are saved. Then, when the workbook is saved, any eligible CChange objects are written to the file.

And that’s pretty much all there is. Just kidding, there’s lots more. Before we look at more code, let’s take a look at what the file might look like after a few changes.

I’m sure you’re all experts on XML, so I’ll spare you the details, but the general hierarchy goes like this

Now that you know what the file looks like, let’s write one. I called the WriteRSS method from the Workbook_BeforeSave event.

Generally, I’m creating an XML file if it doesn’t exist, reading that file in, appending Items to it for any changes, and writing that file back out. The first thing I do is make sure there’s something to write. In line 10, I call the HasChanges property, which loops through all the CChange instances to see what’s eligible. If nothing has changed, there’s no need to create the XML file.

Line 20: If there isn’t already a file, I need to make one. I check to see if it’s out there.

You’ll need to set a reference to Microsoft XML, v6.0 or similar. CreateFile sets up everything in the file that’s not an Item, like the title, link, description, language, etc. The basics of XML generation are 1) create a new node and 2) append it to its parent node.

Line 40: I read in the existing file or the one I just created. The Load method populates the XMLDOMDocument with all the hierarchies and data. Line 50 and 60 go find specific nodes in the file that I’m interested in. I want the Channel node because I’ll be appending Items to it. I want the lastBuildDate node so I can compare that to CChange Modified properties and only write new changes.

Line 70: The RSS example I got off the web showed dates formatted like Tue, 06 Mar 2012 21:28:01 CST. Neither Excel’s CDate or Datevalue functions can convert that to a date, so I have to strip off the day and the timezone, which I do in a separate function.

Line 80: Ultimately I want to make my lastBuildDate in the XML file equal to the latest date of all the CChange objects. I’m initializing the maximum variable here. If I don’t, every cell’s Modified would be greater than this date.

Line 100: For every CChange, I check to see if it needs to be written to the file.

If it’s been modified since the last time I wrote the file, it gets written this time. I really should also check to see if OldValue is different than NewValue. Right now if you change a cell and then change it back, it will still show up as a change. Sounds like a V2 enhancement.

The rest of the loop creates an Item, appends it to the Channel, and updates the maximum Modified date. The xmlItem property returns an object that can be appended.

Not much to this – make a node and append it. The Description property is a read-only property that makes a nice English sentence describing what happened.

Line 160: I change the LastBuildDate to equal the max, then change that node in the XML file.

Line 180: This code I stole from VB Helper. It adds the line breaks and indentation that, while not necessary, is really helpful when debugging. No, I didn’t write this code perfectly the first time.

Finally I save the modified XML document. Dropbox publishes to the web and Google Reader reads it. Here’s what the last change looks like in the reader.

If I ever look at this again, here’s what I’d do in version 2

• Put all the changes in one Item rather than one per cell
• Check to see if a cell was changed and changed back and exclude it
• Get the timezone from the Windows API rather than hardcoding it in a constant
• Modify to use mulitple ranges on multiple sheets
• Put the code in an add-in and look for workbooks to monitor
• Change the Description to be easier to read

I like this Public Dropbox folder. I’m thinking of using it as a version control system to keep my add-ins up-to-date from multiple computers. It’s a shame that some companies block it. Oh well, your thoughts on the RSS code are welcome.

You can download RSSChanges.zip

Well, I finally done it. I’ve gone “paperless” in the AP department. Those quotes are because I haven’t gone totally paperless, I’m just done filing paid invoices. Instead I’m scanning them. I bought this happy little customer.

And it’s proving to be quite a good scanner. The worst part about scanning paid invoices is renaming the file. There are some sophisticated accounting packages out there than handle this better, but the one that I have access to has it’s problems, not the least of which is that it’s a subscription. The scanner automatically names the file with some string of number representing the date and time. I want the file name to be VendorName_CheckNumber_Date.pdf, but typing that for every scan would be a pain. So I automated it. All scans are manually renamed as CheckNumber.pdf for paper check and eAmount.pdf for electronic transfers. Then I run the below code.

Here are the basics: Get a list of paid invoices from the accounting database. Try to match them up with scanned document. If there’s a match, flag it to be moved. Move and rename all the flagged documents to their proper folder. First, I fill a bunch of CPaid class instances with data from the accounting software. In CPaids:

Nothing too fancy here. Get a recordset, loop through it, fill the class. I go back 90 days, which is overkill, but you never know. That hardcoded 90 should really be an argument. Once the classes are all filled, I loop through the PDF files in the designated folder. For every file I find, I try to match it up.

Whenever I try to convince someone to try using class modules, I can never seem to make a compelling argument. It’s one of things you have to force yourself to try to see if you prefer it. But for me, this is a good example of why I like classes. This loops through all the child classes and returns a match if it finds one. The good part is the ScanFile property. I like to break down my logic into an absurd number of properties. You can look at this property’s name and usage and understand what it does, even if you don’t know what the heck ScanFile is. You don’t really need to know what it’s matching to understand that it is matching something. I could put all the logic for the match right in this property, but what the hell fun would that be. If you are interested in what is being matched, you simply drill down to ScanFile.

The logic to determine if this instance is a check or an electronic payment is really simple. You can see it in the next piece of code. And it might seem stupid to obfuscate that away from this procedure. Everything that’s involved in the IsCheck property could be put on the that one If line. Instead, I have a totally separate property and far more lines that necessary. But there are at least two advantages to doing it the way I did. The first is readability. A well named property like IsCheck tells me everything I need to know about why I have a conditional there in the first place: I do something different for checks. The second reason is maintainability. Right now, anything that starts with an “e” is an electronic payment and doesn’t have a proper check number. If that changes, say because I want to start prefixing them with “ach” instead of “e”, I can modify the code in one place. That should be, and is, the only place in the code where I make that determination so it’s the only place to make the modification.

If I was really a good programmer, I wouldn’t be naming properties names like IsCheck. I would name them IsType(ByVal eType as PaidType) and have a PaidType enum. That way if I want to differentiate between ACH and wire transfers (both currently “e” types) I could do it with less modification. There’s a balance though. If you go down that road too far, you end up writing code that writes code instead of doing actual productive work. Here’s the super-simple IsCheck property whose existence some people might consider superfluous.

At this point, the only thing I need to do is determine where the file goes.

This property builds the path and filename, then starts appending integers to the end if there are any conflicts.

I have more than 26 folders in which to store these scanned files: one for every letter of the alphabet and a few for vendors for whom I’ve determined it would be advantageous to have a separate folder. CleanPayee removes anything from the vendor name that’s not a letter or a number. If there’s a folder that matches that vendor’s cleaned name, it will store it there. If not, it stores it in the folder that matches the first letter of the vendor name.

Lastly, I build the file name from properties of the class. And that’s it. On the fifty or so scanned files that I tested it on, it ran so fast that I assumed it didn’t work until I checked the folders. I really thought I was going to have performance problems with the Name statement for renaming and moving files, but it doesn’t seem to be the case. As Jon Peltier once said, don’t optimize until you know you have a problem and you know what’s causing the problem.

You can download RenameScans.zip

Ken Puls and I were discussing the merits of custom class modules recently. Shortly after that conversation, I started rewriting a small utility app with the intention of using the Rethrow method mention by Stephen Bullen. If I’m such a class module evangelist, why am I not using a custom error object? Good question.

I decided to rewrite the PED Error Handler using a class. Below is the central error handling function with more comments than are necessary, I think.

This isn’t an exact replacement for the one in the book. It only uses the Rethrow method, so it won’t be a good solution if you need to clean up after an error. My goal was not to duplicate it exactly, but rather to kill some time during one of the less relevant MVP Summit sessions. Here are a couple of highlights:

I wrote a write-once property for the Message property. Later, I changed the main function to only write the Message property when a new CError object is created so it’s redundant.

Writing to the log file uses some other custom properties that are basically string builders.

I modified the standard Let Number property to use a default “User Cancel” message.

One of the things I like about using class modules is turning Boolean logic into easy-to-understand English. I could have coded

but I much prefer to see

and to put that Boolean logic in the property

I get the benefit of using and reusing ShouldShowMessage wherever I want and if the logic changes, I change it only in one place. I only use it once and probably won’t use it anywhere else, but beyond that I just like that the intent is embedded in the code so the reader doesn’t have to try to figure it out unless they want to.

And here’s some fake code to see if it works.

Thanks to Bob Phillips for telling me to use an Enum instead of a constant: ErrorType.UserCancel vs. glUSERCANCEL.

You can download ErrorClass.zip

Excel Power Analyst Bootcamp Omaha

Microsoft MVPs Dick Kusleika (Daily Dose of Excel) and Mike Alexander (DataPig) are joining together to bring you our acclaimed Power Analyst Boot Camp!

This two-day boot camp is designed for Excel Power Analysts who are looking to more effectively build and manage better data reporting mechanisms. During this workshop, you’ll be introduced to a wide array of tips and techniques that will muscle up your skills in Data Crunching, Reporting, and Automation.

Register early to get a $150 per seat discount. Only $700 for two days of awesome training.

Also, if you didn’t know, Omaha is my home town. That doesn’t just mean that I’ll be more rested during the training, it also means the class will fill up fast as I pressure my colleagues, friends, and family to attend. Don’t wait to sign up. Register here.

I had no idea this existed.

It’s right there in the help for the Print # Statement, but I’ve never paid attention to it.