Daily Dose of Excel

Weekends are kind of slow around here, so let's try this: A post about nothing.

This is where you, a reader, post. Ask questions. Suggest ideas for posts. Tell us how you use Excel. Tell us how you first met Excel. Anything goes as long as it's possibly marginally related somehow to Excel, people who have heard of Excel, or people who have spent time in a cell.

You can even ask questions like, "Hey Dick. how's that eye surgery working out?"

In the Handicap UDF, there's a call to another UDF: GetStartingHC. Every golfer has a handicap to start and it's stored in TblPlayers. For most golfers, it's a carry over from last season. For new members, it can be their USGA handicap. When a golfer doesn't have three scores, the starting handicap is used as one of the scores.

Nothing too fancy, just using the Find method to get to the proper record quickly. This recordset is definitely a candidate for moving global so it doesn't need to be created and destroyed every time the function is called. I so skillfully created a constant to refer to the field in the recordset, but I leave 0.8 and36 as literals. Shameful. If this program will ever be useful outside of my league, those are going to be user settings. I need to make them user settings right now instead of being so lazy.

It's time to write the first formula for my golf league program. I started with a function to compute the handicap because it was at the bottom of my sketch. I knew that this program was going to be primarily UDF's that filled worksheets. Because of that, I need to be aware of speed throughout the development. I start by establishing an ADO connection to my database and keeping that connection live as long as the workbook is open. This is the contents of my MGlobals module:

This sub is called from the Workbook_Open event to get the connection opening overhead out of the way before the first function is even called.

The logic of the handicap formula is this: Every player has a starting handicap and it's stored in TblPlayers. The handicap is the average of a player's most recent three scores times 80%. If there aren't three scores, the starting handicap is used as one of the scores. A player's handicap, then, may be the average of his first round and his starting handicap, thus only two "rounds" would be used. The theory behind these rules is that your handicap will reflect how you're playing now, rather than how you've played over a whole season. Here's the function:

I needed to have the optional week argument because I need to compute two different handicaps. The handicap through last week is the handicap you use for this weeks match play. The handicap through this week determines which golfer you play next week (The low handicap golfer from one two-man team plays the low handicap golfer from another).

Each time this function is called I'm creating a new recordset. So much for those speed considerations. I think what I should do is bring in the recordset to a global variable without any where clauses, then iterate through the records picking out what I need. I'm guessing that the additional overhead of looping through the records will be more than offset by savings from not creating a recordset. Time for a little testing.

By the time I decided to move back to Excel, I had already created an Access form that worked quite nicely. I wasn't really in the mood to recreate the form in Excel, but I knew I had these as bookends:

Input - physical scorecards inputed via a userform.
Output - HTML pages showing results

I had thought through much of the UI and data validation of the userform when I made it in Access. With that justification in hand, I moved on to sketching out the output. It looked like this:

That would be the main results page showing the Teams and how they scored for the week and where they were in the standings. Each of the bracketed numbers is a hyperlink.

[1]Originally, this would link to the team's score by week. Later I abandoned that and now I don't know what to link to, if anything.

[2]

This just shows how they did against their particular opponent that week. It's kind of redundant with [3] so I may just abandon this too.

[3]

Clicking on YTDScore would show all the weeks for that team which would add up to the total and for no particular reason, how their opponent did each week.

[4]

The main page, I like. The handicap page, I like. The other pages, not so much. Basically, I want to go from summary to detail in a drill down fashion, but I seem to be repeating myself. I think I just need to do it as sketched and it will be better than it looks on paper.

After all this, I decided I need to also add a scorecard page which would just be a representation of their hole-by-hole score. This would be accompanied by an image of the actual scorecard. This is an attempt to give the user as much information as they need to recompute their score.

Now that I somewhat have a plan, I'm ready to do some coding. I have a summary page I like and a detail page I like. Always start with the detail page and by the time you get to the summary page, you'll find all the work is done. I'll start with a handicap function, which I'll describe next time. I was quite surprised how easy it was to convert from Access to Excel.

As I mentioned in Everything Ain't a Nail, I'm putting together a little program to keep track of my golf league. It's more difficult than I thought it would be, particularly the algorithms to compute the scores. As long as I'm spending so much energy on this, I might as well document my steps. I set up this category to do just that.

The first thing you need when building an app is a catchy name. Tenth Hole is the name of the league and in a flash of creative genius, I came up with Tenth Hole Tracker. A google search on that phrase turns up no hits, so I'm solid in the trademark department.

Here's how I set up the tables

When I originally set up TblPlayers, Player was a String and was the key field. Why would I need an autonumber when I could use the player's name? People won't be changing their names, will they? In retrospect, that was a pretty stupid decision. First, of course people change their names. Like when women get married. Second, substitute golfers don't write their first and last name on the score card. They just write their first name and they may write "sub" to let me know they're not a regular. Now that I know a certain last name, I want to change it but it would be a major doing.

Also, a mistake could be made when entering the golfers' names. Maybe I don't notice the mistake for a few weeks, but want to change it when I do. But I have all these incorrect keys in my transactions table. What a pain.

I added the PlayerName field and changed Player to an autonumber field. It wouldn't let me do it because I have data in the table already. So I had to copy the structure, make the change, then do an append query. A little renaming later, and I was in business. I was in the business of finding and fixing all the errors this changed caused. Relationships screwed up. SQL statements screwed up. VBA screwed up. I vow, from this moment forward, to ever use fake, contrived, meaningless, autonumber-esque key fields.

Other notes on the setup:

TblPlayers
I went with three email fields rather than a separate one-to-many table. This is a hard limit that could be unlimited if I had done the separate table. Sometimes I think it would be better if everything was unlimited, but I don't know if it's worth it.

The Sub field is a Boolean that's true if the golfer isn't a regular member of a team. I could get the same information by joining this table with TblTeams, which I probably should have done. I may delete that field.

TblCourses
The Holen fields hold the handicaps for those holes. That number is necessary to calculate the points. I added those fields when I started working on the points algorithm. Poor planning, I guess.

TblWeeks
Everything is based off a week number. I wanted to correspond the week number with a date, although I haven't had cause to use it yet. Sometimes I underplan; sometimes I overplan.

TblScores
I started with a structure that had a Hole field and a Score field. I changed it to Hole1, Hole2, etc. In our league, we play nine holes. Never more, never less. I may regret that decision when I want to scale this to an 18 hole league, though.

By the way, I decided to keep the data in Access and build the UI in Excel. Next I'll start showing the functions I'm writing to get the information I need. As always, comments welcome.