Continuing to find that the "formatting" bug I fixed
in version 3.06 and version 3.04 is still not yet fixed. (A
player is still mathematically alive, but the program greys them
Another formatting glitch fixed.
At times, a player was NOT greyed
out, although they should have been greyed out. For example, after
all of the game results were in, the player in second place, who had
the same number of wins as the first player, but lost out on the
tiebreaker, clearly did not win the pool. The program "knew" they
didn't win... it correctly listed the winning player at the top...
but that second player in line was not greyed out, when that was my
intention to do so. Fixed. (I think. This formatting bug was shown
to me by three users, and all three seem to be happy with my fix.)
There appears to be "spaces" in the cells
that should be empty in the NFL_Schedule tab. For example, in
Week 5, in the first empty "box" after the Minnesota / Chicago game,
there are "spaces" in that cell (AE15), instead of what should be
"nothing" in the cell. Because of this, the program was
crashing when downloading the scores from the nfl.com website.
I fixed the NFL_Schedule tab so that all of those "bye week"
cells now DO have nothing in them (meaning, they are now truly
empty, without a blank space in them), and I also fixed the code to
not crash under those circumstances.
At times, a player will be "greyed out" and
deemed no longer mathematically able to win the pool, when there are
still games remaining, when they could still TIE with another
player. (Once all the game scores are known, the program then
figures out and properly identifies the winning player.) Fixed.
Thanks to Jose D.J. for finding this bug.
Ah yes, another bug found. (Thanks Shane.)
With pools that use Confidence Points, the program was reporting a
warning message informing you it didn't recognize some of the teams
that were being loaded. The warning message can be ignored… but it
shouldn't have been displayed in the first place. (The program
thought your Confidence Points were teams, and it didn't
recognize them!) Fixed.
I forgot to add the Bucs - Dolphins game that had been moved from
Week 1 to Week 11 in the NFL_Schedule tab. (I did remove it
from Week 1… how could I not add it to Week 11 at the same time?)
My example formula that added each player's Confidence Points in
column T in the Player_Picks tab had at some point been
replaced with the actual value. Now, a formula is no longer needed.
The program will now enter a formula for you, without the need for
the user to do it.
Finally, I think there was a bug or two when the user clicked the
Recalculate button. (The NFL Office Pool graphic under the RESET
button is a Recalculate button.) Fixed.
The NFL officially postponed the Tampa Bay
Buccaneers - Miami Dolphins game in Week 1 until Week 11. I
updated the NFL_Schedule worksheet tab and the Weekly Pick
Sheet Form worksheet tab to reflect this change. (Users can, of
course, make these two changes themselves.)
If your pool uses the NFL pointspread, your
spread is entered (as you should know) in the appropriately marked
row in the NFL_Schedule worksheet tab. After doing so, after
everyone's picks are loaded from the Main worksheet tab, the
program was immediately attempting to determine the winner of the
pool (using your pointspread, of course) before any games had
been played! To clarify, the program looked at the spread
and added it to the "non existent" scores... it assumed the games
had been played... despite the fact that the user had not yet
toggled any winners nor had he/she downloaded any scores.
Now, you can remedy this yourself, without downloading this version,
simply by clicking the RESET button immediately after the picks are
Obviously, It was not my intent to "determine winners" immediately
after the player picks had been loaded.
Removed all of the Active-X buttons and replaced them with a
customized graphic. (I got tired of people writing to me, telling me
their Active-X Buttons had suddenly become "messed up." It was always a graphics
card conflict / issue and NOT a bug in my program.)
Recoded the program to allow for four additional weeks, to
accommodate the playoffs. (The Running Totals worksheet tab
was updated, as was the NFL_Schedule tab, and the Weekly Pick
Replaced all references of San Diego Chargers to Los Angeles
Chargers. And now with two teams in Los Angeles, LAR is now the
abbreviation I'm using for the Los Angeles Rams (and not just LA), and LAC is
the abbreviation I'm using for the Los Angeles Chargers.
The program should handle the upcoming Raiders move to Las Vegas
with no additional changes. (The two-letter city abbreviation used
by the NFL will most likely
Entered the 2017 NFL Schedule.
Removed a few of the speed bottlenecks, so the program runs a bit
faster. In fact, some of the calculations and screen writes
are ten times faster than before! Yay!
My macro in the hidden "data" worksheet, which downloads the NFL
schedule for me automatically, now also updates the NFL_Schedule
Despite the speed improvements, very large pools (100 or more
players) still take several seconds to load, as the programming
logic goes through the sub routines to to determine the winners, to
highlight the necessary cells, to determine who is still alive and
who has been mathematically eliminated, etc. Thus, I thought it best
to add an optional "progress bar" in the Status Bar area (bottom of
the screen), so the user, if using a slow computer, knows that
"something is happening" - that the program is still working and
Allowed the user to set the number of maximum players themselves.
(It was initially hard coded to 200 and then later hard coded to
500.) In theory, with a smaller number, the program should run a bit
faster. If you only have a dozen or two members in your pool, just
set the max number of players to 30 or so.
Removed the code that generated the Checksum figure. (This was an
old feature I needed a long time ago.)
Added the Testimonials worksheet tab. (Feel free to hide it
so it is out of the way or delete that tab entirely.)
Added an option for the user to download the game scores from either
the NFL website or the CBS sports website.
Added a CLEAR DATA button in the Running_Totals worksheet
Cleaned up the weekly pick sheet forms, so the formula that is used
to list each team doesn't display that funny "0" during the weeks
In all prior versions, if the user toggled the winners
themselves (without downloading the scores), these winning teams as
set by the user were not being saved if the user clicked the SAVE
SCORES & PLAYER RESULTS button. Now, the program will save that winning
team. (It assigns a score to that game of "1" to "0.")
Added a check to see if there were any duplicate names
loaded, and if so, to warn the player with a warning message.
If one or more teams, as entered by the user, aren't
recognized by the program, the user is now alerted with a message.
Dozens of minor cosmetic changes were made to the program, too numerous to mention.
Whoops. Eventually there probably would
have been a problem in the Cross_Reference tab. (The St Louis Rams
were still referenced in the code, instead of the Los Angeles Rams.)
Allowed the user to change the size of the font for the player names
in the top horizontal row of the Cross_Reference tab. (The program
was forcing this to be 10-point.) The font size is now the same size
as the font size of cell B2 of the Cross_Reference worksheet tab.
Shoot. Users who use the pointspread are
experiencing a glitch. When the player picks are loaded, the program
is attempting to add their spread to the game score… which at this
point is nothing and is displayed as a dash/hyphen. You can't add a
numeric field (the spread) to an alphanumeric field (a dash/hyphen).
The result is an error. (Type mismatch.) Fixed.
Changed the buttons in the
Running_Totals worksheet tab from an Active-X button to a
Form Control button.
Under rare conditions, players who were still mathematically alive
were being displayed as if they were not. Fixed.
Entered the 2016 NFL schedule.
Replaced all references to St. Louis Rams with Los Angeles
Replaced Jac with Jax in the code, where necessary. (The NFL
now seems to be using JAX as the three-letter abbreviation for
Added the Weekly Pick Sheet Form to this workbook.
The changes made to 2.28 caused a bug that could appear under
certain conditions, when loading games. Fixed.
The program was not stripping leading or trailing spaces from the
player's Monday Night point total prediction. There shouldn't be
any trailing or leading spaces, of course… it should just be a
number, and the program IS computing the difference correctly,
from the actual Monday Night total. Still, if a prediction is
entered with lots of such spaces, the number isn't centered in the
cell and it looks funny. Fixed.
Apparently the player name and player picks can contain ascii
character 160, in addition to leading or trailing spaces. (Via picks
sent to the host via a cell phone??) I was only checking for and
removing the spaces, not this weird "invisible" ascii
character. And, if ascii character 160 was present, the program
didn't recognize the team name. Fixed.
Increased the limit to the number of pool players form 200 to 500.
Fixed a glitch. Under certain circumstances, the program wasn't
properly displaying who was still alive and who was eliminated,
despite this option being set to "Yes" in the User_Options tab.
(It just required adding a dash ("-") in the NFL_Schedule tab for
the score, for all unplayed games.)
If a user entered a Monday Night score prediction as a non-integer,
the program was rounding it up or down accordingly, to the nearest
integer. This meant a total score prediction of 55.1, for example,
would get rounded down to 55. If another player entered 55 as
their prediction, the program considered these two players to
have same Monday Night tie-breaker score, and graded
accordingly. This isn't the case, of course. A prediction of 55 is
closer to a final score of 47, for example, than 55.1 is to 47.
Although I see NO advantage in guessing scores as non-integers, if a player wishes guess this way,
the program should grade accordingly.
Are you kidding me? Updates somewhere created a bug, with
bye weeks, when no bug existed before. The program expected another
game was still to be played, and didn't calculate the final winner.
This is fixed... I think.
The Player Nickname sorting link, the
Total Wins sorting link, and the Win % sorting link, all located in
the Running Totals worksheet tab, didn't seem to be working for
everyone. I removed the links and added a toggle sort button
Corrected a mistake in the Schedule, in the NFL_Schedule tab.
(Washington hosts Philly in Week 4, not Tampa Bay.)
Added a message in the Running_Totals tab, to let the user know this
tab is populated by hitting the SAVE SCORES button on the Main
The Player Nickname link, in the Running
Totals worksheet tab, that sorts the list alphabetically, wasn't
working. (Seriously?) Fixed.
Tiny glitch with the colors of the toggle buttons not working,
involving St. Louis. Fixed.
Although no one has reported any bug when the Price Is Right
Scoring option is set to Yes, I think I fixed a potential
Pools with 100+ players run very slowly, after the player returns to
the Main worksheet tab after visiting the User Options tab, even if
no options were changed. Now, if no options were changed there
should no be no delay.
If the user modifies the Monday Night Total score in cell S10
(because their pool uses a different number. For example, just the
score of the winning team), the program will now SAVE the score the
user enters in cell S10, in the Running Totals tab, when the scores
If the user did enter their own Monday night point total in Cell
S10, the highlight colors depicting which players were still alive
and which players were dead was not quite correct. Fixed.
Entered the 2015 NFL Schedule.
I eliminated one of the worksheet tabs.
I combined the NFL_Schedule tab and the Saved_Results tab into one
tab. (They basically contained the same data anyway.) I also
redesigned that tab, to be more consistent with nfl.com's website,
in regards to the way the final scores are displayed.
When the scores are saved in the NFL_Schedule tab, I'm setting the
font to bold for the winning team and the winning score. (Again, consistent
with the nfl.com's website, and what you often see on television.)
When a team didn't score any points in the game, the Saved_Results
tab (now the NFL_Schedule tab) was displaying their score with a
dash, instead of a zero. Fixed.
I removed the toggle button that changes all winning teams to the
Home Team or to the Visiting Team. (That button was nothing but
When a prior week was loaded that contained a tie game, the toggle
button didn't display the word TIE. Fixed.
Rewrote the code that shades the cells of all non winners, after all
games have been completed.
If the program was opened and the Display NFL Scores checkbox was
not checked, and then immediately checked, the program crashed.
User Request. Added a new user option to allow the same player to
submit more than one set of picks and yet combine all of these
results into the same player in the Running Totals worksheet tab.
The player's picks should be appended at the end with parenthesis.
Example: Edward (2), Edward (3), etc.
Renamed the SAVE DESIGNATED WINNERS button to SAVE SCORES & RESULTS.
Changed the size of all four buttons (and the checkbox) so they are
shorter (but wider) to allow for more data vertically.
For Week 1 of 2014 (for example), the program downloads the scores
from www.nfl.com/scores/2014/REG1. Rather than hard-code the year
(2014) into the program, it's now a variable that the user can
change themselves each year, in the User_Options tab. Now the only
other thing that needs to be changed each year is the NFL schedule, and
the program will work each year, with no other modifications.
Added an option for the user to set their preferred row height for
the Main tab and the Running_Totals tab.
Added the user option to auto scroll to the top rows of most
worksheet tabs. (All but the Player_Picks tab.)
Added a hyperlink to the name of this program at the top of this
page, to the calculator's website.
When saving the final results to the Running_Totals tab, the total
wins for the player who won is now set in a bold font.
Bug found: The program didn't recognize "Buccaneers" or "Chiefs" as
valid mascot names. Fixed.
Removed the instructions on how to get rid of the "Data Connections
Are Disabled" message, since I think I've solved that for good.
If some of the teams in the NFL_Schedule tab were listed in
uppercase, they didn't match the player's selections if entered in
Dozens of minor cosmetic changes made to the program, too numerous
Replaced the TOGGLE SORT button in the
new Running_Totals worksheet tab with Header Links instead. Single
clicking the Header Link will sort that column!
"SL" is now a valid entry for the Cardinials, "NOS" is now valid for
the Saints, "NEP" is now valid for the Patriots and "sd" and "Sd"
are now valid for the Chargers. (Thanks Joe.)
If an office pool's rules didn't require the user to select all of
the games, the games and buttons weren't always loading correctly.
Fixed. (Thanks Tony.)
to determine who was still alive got screwed up. Fixed.
The program now recognizes even more
teams abbreviations, as well as a few common misspellings. For
example, it knows that "Cheifs" = Kansas City Chiefs, "Id" =
Indianapolis Colts, "Boys" = Dallas Cowboys, "Gaints" = New York
The program now can handle player picks that are entered in
different columns than one another! To clarify, when entering picks in the
Player_Picks tab, the user doesn't have to keep the same teams
together in each column! All that matters is that each player
submit a home or a visiting team for each game. The user can
enter them in or out of order if he/she wishes. The program
will sort everything accordingly! (However, for clarity, the
user should enter each game in the same column.)
I improved the screen display for office pools that don't use a
total score tie-breaker for the Monday Night game.
I removed the requirement for the user to designate if their pool
uses Confidence Point scoring or not. The program can again
determine this on its own.
I added the Running_Totals worksheet tab, and all the logic behind
it, at the request of several users. To populate it, you click
the Save Designated Winners button.
The program now downloads past weeks scores, if requested, from the
nfl.com website, rather than the repole.com website I was using.
(Reason: The repole.com website is down and it doesn't appear it
will be up again any time soon. So let's retrieve past scores from
another website, and I might as well use nfl.com.)
After the final game had been played, the program didn't highlight
the player who won the pool. Instead, the program was only
highlighting the top x number of players who all were tied at the
top with the same number of wins. It didn't go one step
further and determine the overall winner, by taking the tie-breaker
score into effect. It does now.
I removed one of the buttons. (Slightly less clutter on the screen
this way.) The SET all VISITING TEAMS as WINNERS button and the SET
all HOME TEAMS as WINNERS buttons have been combined. This button is
now a toggle button.
Added a Status Bar message after most actions.
If the user entered their own scores in the Saved_Results tab (which
a user wanted to do, because they were no scores available yet to
download from the repole.com website, it turns out the user had
to enter them in the same order as in the Player_Picks tab!
Darn! Not ideal. If they entered them in the same order
of the NFL_Schedule tab, the program didn't work. This is
fixed. The user can now enter the winners and final scores in the
Saved_Results tab in any order. (But the user should still let the
program enter these scores, if possible.)
Gosh darn it. I found two mistakes
in the NFL schedule. (Not my fault, other than I didn't make
time to double-check it. The source I got the schedule
from was wrong!)
The calculator will now, optionally,
take into account the NFL pointspread when determining the winning
team. (User request - their pool uses the pointspread.) The user
must enter the pointspread themselves each week, in the NFL_Schedule
Sorting the list of players automatically, by the number of wins, is
now a user option.
If the user entered a player's name in
the Player_Picks worksheet tab, but hadn't yet filled in that
player's picks (because they didn't have them yet, but were
expecting them) the program
crashed. Fixed. (Thanks Keith, for letting me know about this.)
If you're using Confidence Points, the program will now check to
make sure that everyone's combined Confidence Points equal the same
total. If not, the user will be alerted with an error message.
I cleaned up and changed the format of the Main tab! I think it
looks a lot better! As you can see, the user buttons are now at the
top of the screen, rather than at the side. Also, each player's Monday Night tie-breaker score is now displayed,
rather than keeping it hidden. Also, the all-important difference between the
player's Monday Night prediction and the actual total is displayed.
I spent more time testing the program when Confidence Points are
used, and I may have fixed a few potential bugs (all of them just
cosmetic in nature).
When the picks are retrieved via the Main Page, the Print Area is
now optionally set, and is based upon the number of players who are
playing that week, to make the page more attractive when printing.
The very first time the workbook is opened (and not saved) a brief
message appears informing the user that picks must be entered in the
Player_Picks worksheet tab. (This is the #1 question that I get
e-mailed about. Despite all of my instructions to the contrary, new
users begin entering on the Main worksheet tab instead of on the
Fixed a small bug that would have occurred if the user clicked the
Download NFL Winners button, while it was halftime during one of the
If a user entered a player's picks that contained one or more
leading or trailing spaces, the toggle buttons didn't recognize this
pick (with the "invisible" space at the beginning or end) and wouldn't work
correctly. This is now fixed.
Added the User_Options worksheet tab, and all the logic behind it.
Increased the size of some of the variables that would have resulted
in errors with pools of more than 100 players.
The program now retrieves past game scores from
Allowed the user to enter the picks in any format (city name, mascot
name, etc.) they wish. The program will make the necessary
Many other updates too numerous to
Entered the entire 2014 schedule.
Added a few more checks to convert invalid team names
entered by the user into the proper two or three-letter
Another minor bug fix, related to the
2.06 bug fix. (When the spreadsheet was saved, closed, and then
re-loaded, the week wasn't always being updated correctly.)
Corrected another typo found in the 2013
Added a secret checksum routine, so that when a co-worker and I each
enter everyone's picks each week, we can instantly see if our
entries match, indicating we each made no mistakes, by now comparing
the checksum. If the checksums don't match, we know at least one of
us has made a mistake. (After loading the games, ATL-C displays the
Now that I've learned a little bit more
about Excel and VBA, I've cleaned up the code a bit. For example,
the user can now rename the six worksheet tabs, if desired,
and the program won't crash. (The names of the tabs were hard-coded
prior to this.) A few other internal enhancements / changes were
Found a typo (bug) when retrieving the data from prior weeks. The
saved scores weren't always being pulled in correctly. Fixed.
If your office pool uses Confidence Points, the program began
calculating which players are still alive when there were 11 games
(or less) remaining. However, with slower computers, 11 games is
still too many games... the program takes too long. (Excel is
slow......) This default is now 8 games and this number can now be
set by the user. (You do this above, by modifying the last two
characters in the DETERMINE WHO IS STILL ALIVE paragraph, on the
The program now automatically switches to full view mode upon
startup. (Hit ESC to get out of this mode.)
Very minor bugs found, when I spent some time and attempted
to make the program crash. Fixed. For this reason alone users should
download and use this version.
Last year our office pool didn't use the Monday Night Game
as a tie-breaker. We're doing so this year and I can already see a
few potential problems. For one, the program was sorting based upon
this tie-breaker... before the Monday night game has been played!
It's sorting on a 0-0 score! This is confusing and makes no
sense. Fixed. Now if your office pool is
using the Monday Night game as a tie-breaker, and if
this game has a score available, the program will then sort based
upon player score, tie-breaker, name. Otherwise it just sorts
based upon player score and then name.
Added this Version History section to
the program. (It currently just existed on the website.)
I didn't fully fix the previous bug. (And I'm not
convinced it's fully fixed now. More testing is called for.)
If you saved the worksheet, and then re-opened it, the
program didn't know how many players there were, and didn't update
the scores correctly.
Modified the program to now allow the user to enter
Confidence Points. (Good idea, Scott. Thanks.)
If the user selected a week that had no picks yet, when then
selecting another valid week, the font and formatting changed
Removed two buttons and three checkboxes. (The Main worksheet
tab was becoming too cluttered!)
Reformatted the Player_Picks worksheet tab a little bit.
Cleaned up some of the worksheet tabs. (Hid columns, removed
Excel headings, etc.)
Changed the initial week from 1 to 17. (Forcing the user to
change it back to 1 to pull in the data.)
Corrected a typo in the NFL schedule.
Updated with the entire 2013 schedule.
Whoo hoo! I just found out my calculator is being used
by someone else!
And they told me they had a problem, initially. They entered
the teams in uppercase, something my program didn't expect.
(Despite the fact that's exactly what my help file said to do.
Whoops.) Although they figured it out on their own, I
needed to fix this.
With Version 1.03, the user can now enter each player's picks in
uppercase or lowercase. The program will convert everything to the
proper case that is required when it comes time to load them from
the Main tab.
Modified the program to accept a TIE game as a possible
final result. (This past weekend, the Ram/49er game ended in a tie.)
The program was already capable of correctly determined your office
pool winner if a tie occurred. The user would simply not enter a
winner for that game - they would leave that toggle button alone, at
its initial "neutral" state.
The problem with that, however, was the program logic considered
that game was still had yet to be played... and thus the logic to
determine which players were still alive and which players were
mathematically eliminated wasn't giving correct results.
I'm a perfectionist... this wasn't acceptable to me, even though the
chances of another tie happening in the next several years is
This is now fixed.
To set a game as a tie, simply enter a "t" in the cell above the
toggle button (row 2) and then click on the toggle button.
With this version, the programming logic first looks to see if a "t"
is entered in that cell. If so, the "t" that was entered will be
erased and the program will mark that game as final, with a tied
result. Also, if you choose to set the toggle button results
automatically, by downloading the NFL results, the words TIE will
now appear in the toggle box, for all games that end in a tie.
Fixed a slight display glitch involving the Winner Toggle
Buttons. (At times, "n/a" was being displayed, and the button
wouldn't toggle, when it should have been able to.)
First release to the general public.
Added code to help check to make sure the names of the teams are
being input properly, in the Player_Picks worksheet tab.
Fixed a bug - the small "H" wasn't being displayed (to indicate
Halftime) above the toggle buttons, when downloading the scores
during halftime of a game.
Fixed a bug in the Cross_Reference chart tab, when displaying the
help message, which would have occurred during a week when no one
had a one-game difference with anyone else.
Added the Cross_Reference tab.
Rewrote portions of the code, now that I'm becoming more familiar
with the syntax of Visual Basic.
Allowed the user to enter the weekly picks in ANY column order...
meaning, the games no longer have to sync up with the games as they
are entered on the NFL_Schedule tab.
Replaced the old NFL graphic with the graphic currently used by the
Added the code to save winners to a new tab, so the scores wouldn't
have to be downloaded again, each time that week was viewed.
Added the logic to accept and handle the total score in the Monday
night game, as a tie-breaker.
Turned off the display of headings and gridlines on
Rewrote the Help tab.
Repositioned most of the buttons.
Repositioned the title, week number, and number of weekly players in
the otherwise unused section of the upper-right corner.
Reduced some of the screen flicker when a few of the buttons were
Changed the week selector into a data validation drop-down box.
Cleaned up the rows - when a week is accessed that had fewer entries
than the previous week that was just viewed, empty rows are now
Changed the spreadsheet format - instead of 17 different tabs, there
are now just TWO tabs... one to hold all of the player picks and the
Main page which retrieves them.
Changed the buttons from Excel "Toggle"
Buttons to Excel "Command" Buttons.
Eliminated the need for the data in the hidden cells, in columns W
Color coded the buttons to match the home/visitor team, when
Coded the programming logic to retrieve past scores, for
the logic to download the score of the games from the NFL website.