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.

My brilliant piece of altruistic code posted in Get Outlooks Currently Selected Time isn’t all that it’s cracked up to be. Had it been for anyone except a blind guy, it would have been just a poor hack job. But for him, it’s practically unusable. You would think the visually impaired would have no need for code such as Application.ScreenUpdating = False. You would think screen flicker would be the least of their concerns. However, the screen reading programs tries to read the screen when it’s updated, so it’s a big mess.

I haven’t been able to find a way, in Outlook, to stop the screen from updating. If you know of a way, please drop me a line.

John asks how one macro can be used to stop another macro. One way to do it is with a variable that both procedures can see. In this implementation, I have two buttons from the Forms toolbar that start and stop a counter in A1.

The code is all in one standard module and looks like this:

StartIt runs until the variable is made true by StopIt. The DoEvents in the loop releases control of the computer to any other processes in the queue. In this case, running StopIt will queue it up but won’t run unless you let it. It’s kind of like letting people in to your lane – DoEvents is that jerk in front of you that’s letting everyone in.

I set the Locked property of the Start button, but I’m not sure if I know what I’m doing. Without those lines pressing Start repeatedly crashed Excel without fail. With those lines, no problems.

The key is to make your variable available to both the start and stop procedures. Since both procedures in this example are in a standard module, my variable can be private to that module. It’s the smallest scope that both procedures can see. If my procedures were in a userform, a variable that was private to the userform’s class module would do the trick. If the procedures are in different modules, you’ll need to declare the variable as Public in a standard module.

Jed asks:

I’ve got a spreadsheet that is essentially a list of items in priority order. I’d like to be able to add buttons to each row that will enable me to move that row up or down. Is this possible?

Here’s the way I would do it: I would put a SpinButton control on the worksheet and the user would use that to move cells. Assume you have a list in A1:A5 and you want to allow the user to order the list however they want. First, put a SpinButton control (from the Control Toolbox) on the sheet.

Then put this code in the sheet’s module:

The SelectionChange event hides or shows (and positions) the SpinButton when a non-empty cell in Column A is selected. This way, you only need one control on the worksheet. The SpinUp and SpinDown events trade values with the cell above or below, respectively.

There are a couple of error checks in the SpinUp and SpinDown events. Look at the SpinUp event, for example. If A1 is the ActiveCell, then you can’t spin up, so the code only runs if the row is two or greater. Next, it doesn’t allow a trade with an empty cell. It assumes that empty cells are the borders of the list.

Okay, now the funky part. Select cell A1. Click the Up button of the SpinButton. With the arrow keys, not the mouse, select A2. Did the values from A1 and A2 swap? They did for me. When I put break points in the code, everything works fine.

Mike asks

…But when you try to do Selection.PasteSpecial Paste:=xlColumnWidths and try to run the macro, Excel say xlColumnWidths is undefined. Strange, since this is what the macro recorder gave me. …

I can honestly say that I’ve never pasted special column widths before. In fact, I didn’t even know it was an option. But Mike did indeed discover a bug that MS describes in KB231090. When I recorded a macro, I got

But it looks like the Excel guys forgot to create the constant xlColumnWidths. Use it’s intended literal value, 8, instead, e.g.

The license plate holder on the back of the Maxima is getting pretty faded. It’s a Colorado Avalanche license plate holder, and because I’m so embittered about the strike, I won’t be replacing it with a similar one. (They forced me to watch baseball this Spring. I don’t know if I can forgive them for that.)

The only obvious choice for a replacement is a custom Daily Dose of Excel license plate holder. I went over to cafepress.com to see what they had, and it looks pretty good. Now I just need some art to upload and I’ll be set. That’s where you come in. You see, my skills with graphic design and the computer programs associated with it have been compared to a monkey having “relations” to a football. You want proof? Here’s what 36 straight hours of graphic design gets me:

The interlaced D was a pixel-by-pixel affair that I hope I never have to repeat.

If you are so inclined, you can create a logo for me. I generally like the concept of the above logo, but I’m not married to it. Feel free to use parts of it, or not. If you email a new logo to me by June 24th and I use it on this site, you get a prize. You will receive, shipped free to your North American address, the first season of Carnivale on DVD and your choice of DDE logo wear from cafepress. If you’re not in North America, you can still win, but I reserve the right not to pay the shipping. I doubt, however, that the DVDs will work for you anyway.

If you send me the second best logo, you still win a prize. You get your choice of DDE logo wear. Of course, the logo will not be the one you designed and will be a constant reminder of your failure.

If you’re moderately good at this kind of thing, and you like the HBO show Carnivale, then this is the contest for you.

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.

Chris directed me to an interesting thread at Mr. Excel regarding inpsecting the properties of a cell. There’s two parts to this thread: Viewing the properties and looping through the properties. I’ve tried to create a program that loops through the properties by reading the library. I always start at Chip’s site and his TypeLib Utilities download. I pour through the code getting ever closer to understanding it. Then my head explodes and I give up. I’m not saying it’s possible even if I did understand it. Just that it seems like a good place to start, and that’s as far as I get.

You could easily put all the properties of a cell into a message box, but you’d have to hard code all the properties, which brings us back to looping through the properties. What would be nice, I think, is if the Properties dialog (Control Toolbox > Properties) had a Selection object choice in the dropdown. Then, the properties of whatever you had selected would show in that box, be it a range or a shape or whatever. Alas, it doesn’t.

The only other convenient place to find the properties, that I couldn’t think of, is the Locals Window. To that end, I wrote this:

It may be possible to 1) make “cell” global so that you don’t have to Stop the macro and 2) use API’s to show the Locals Windows without showing the VBE as if it was a child window of the main Excel application. I leave that to you.