via Automate Excel

Debra at Contextures has added an RSS feed to her wonderful site. It’s on my blogroll and you should consider putting it on yours too.

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.

Bill asks what the best way to get data from a userform is: class module? global variables? I’m not a fan of global variables because it’s not scalable. That is, if I add a new control, I have to add a new variable. That’s too much maintenance.

So it’s class module, then? Well, yes and no. I do use a class module, but I don’t create a new one with a bunch of Property Get and Let statements. Remember that the userform is itself a class module. All you need to do is keep the variable that refers to your userform in scope and you can refer to the controls all you want. You get a bunch of built-in Gets and Lets. Here’s an example

Behind the userform’s Go button, I have

Hiding the form causes UseFormData to continue execution right after the Show line. I can use my ufForm1 variable to access the controls on the form. ufForm1 can also be passed to called procedures, so you don’t need to keep everything in one sub.

You could use Unload Me behind the Go button, but I tend to use Hide. Since the variable ufForm1 still refers to the class, you can actually unload it. It has the same effect, Hide just seems clearer.

If you need your values to persist longer than the current sub, you can change the scope of ufForm1 to module level or global.

I received this file and I can’t figure out a formula. When I retype a similar formula, the capitalization of OR turns into oR. Here’s what’s happening as far as I can tell: The construct ISBLANK(invalidname) does not produce an error, but produces FALSE. The invalid name produces a #NAME? error when used by itself, but that #NAME? error doesn’t propagate beyond ISBLANK. That, in itself, seems like a bug to me. But it still doesn’t explain why the capitalization of OR changes when used in this manner. There are no defined names that I can see. Anyone?

By the way, the proper syntax for the formula is =IF(OR(ISBLANK(D9),ISBLANK(E9)),"",D9+E9)

Update: Spellchecking only works when you use it. Thanks for catch, Double D.

You can use negative numbers in the ROUND worksheet function to make numbers less precise, i.e.

returns 57,000

I just learned that the Round method in VBA doesn’t have that feature. “Invalid procedure call or argument” it tells me. I could use the WorksheetFunction object and the Round method that comes with it, but instead I used integer division:

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.

Here’s my solution for the encryption grid problem.

A8 has the formula: =IF(AND(ISNA(MATCH(CHAR(ROW()+57),$A$1:$E$1,FALSE)), ISNA(MATCH(CHAR(ROW()+57),$A$2:$E$2,FALSE)), ISNA(MATCH(CHAR(ROW()+57),$A$3:$E$3,FALSE)), ISNA(MATCH(CHAR(ROW()+57),$A$4:$E$4,FALSE)), ISNA(MATCH(CHAR(ROW()+57),$A$5:$E$5,FALSE))),ROW()+57,1000)

You have to adjust the number to “+58? when you get to J in order to skip it. The G8 helper range is just numbers typed in 25 to 1. The named range ‘abc’ starts in A8 and ‘fillrng’ starts in A1.