Version 4.10
Entered the 2024 NFL Schedule.
Version 4.09
Added the plantextsports website as an alternate place
to download the scores from.
Version 4.08
CBS Sports changed their website format again.
Clicking the DOWNLOAD BUTTON didn't work, since CBS is the default
site to download the scores from. (Fixed… I think.)
In an effort to clean up the source code and (hopefully) speed up
the program, earlier this spring I removed a call to a subroutine
that I thought was not needed… I thought it was redundant. However,
it appears otherwise. It seems the logic that determines when a
player is still alive is now working correctly. When I put that
subroutine call back in, it works again.
Version 4.07a
Whoops. A change I made no longer made it
possible for the user to enter their picks in any order. With my
inadvertent change, the order had to agree with the order as I
listed them in the NFL Schedule tab. If not, it didn't recognize the
team. Fixed.
Version 4.07
Entered the 2023 NFL Schedule.
Cleaned up some of the source code.
Updated the Cross Reference worksheet tab. The program
will no longer produce the chart each time the user accesses the
tab, if it doesn't need updating.
Version 4.04
CBS Sports changed their website format from last
year! This is the default website that the program gets the final
scores from. Their internal HTML source code is completely different
than what it used to be. (This has already happened a few times.
Look below on some of the previous Version history for proof.) When
downloading the scores from that website, the code was looking for
certain words (final-score) to correctly parse the final score. With
the internal HTML code now different, it's not finding those words
and thus not able to figure out or retrieve the scores. This is
fixed... I think. Note that there are other websites to download the
score from, and other methods (mentioned in the instructions above)
to let the program know which team won, so it can grade the players
accordingly.
Also, I added yet another optional website to download the scores
from... ESPN... and removed one of the sites that wasn't working
properly.
Finally, at times the program though the user was running a
Confidence Pool... when they were not. Now, a Confidence Pool must
be specified with a 1, 2, or a 3 in the Confidence Points option, in
the User-Options worksheet tab.
Added WSH as a valid entry for Washington, in the Player_Picks
tab. (That's actually the three-letter abbreviation ESPN uses.)
Version 4.03
Entered the 2022 NFL Schedule.
Version 4.02
The Entrants worksheet tab now displays
the winning and losing colors for each winning and losing selection.
Version 4.01
Whoops. We have an extra week this year (2021).
And I did remember to add an extra column for this week in the
Running Totals worksheet tab. However, I forgot to adjust
the totals in this tab by one column. The three totals on the
far right are all off by one column. Fixed.
Version 4.00
Modified the program to accommodate the NFL's new
17 week format.
Entered the 2021 NFL Schedule.
Version 3.85
If the user wished to manually set the winner,
via the TOGGLE WINNER button, and wanted to set the result of the
game as a TIE, the program required the user to first enter a T in
the cell below and then click that toggle button. Well, duh...
that's pretty silly and certainly not intuitive. Why not just set up
the TOGGLE WINNER button so that a TIE is the third toggle option?
Done.
Added another optional website to download the live scores from in
case the CBS website is down.
Under certain conditions, if Price Is Right Scoring is selected, all
of the players weren't being sorted correctly. Yes, the proper
winner was still highlighted, but the order of the players listed
wasn't as desired. Fixed… I think. (Thanks Paul.)
Unless the players in the Player_Picks tab were initially
listed in alphabetical order, the Entrants tab did not list
their Confidence Points correctly, for Confidence Point pools.
Fixed. (Thanks Rusty.)
Added the CHECK VERSION button in this Help & Revisions
worksheet tab, so the user can easily
check to see if they are using the very latest version of the
workbook.
Version 3.70
Ah, another glitch. When clicking the DOWNLOAD SCORES
button, it would not have found Washington's score from the CBS.com
sports website this weekend. Reason: It's because of their silly
mascot name change from Redskins to Football Team.
Fixed. (I hope.)
It appears the nfl.com website changed their website code again! I
don't think it would have found scores from this website at all.
Thus, I'm now removing the nfl website as an option. And yet I want
at least two options to download scores from... in case one of them
is down or not available. So let's also retrieve scores from the
pro-football reference website as well as the pro football database
website.
Clarified what the Entrants worksheet tab is for, with a more
descriptive message at the top of that tab.
Clarified the error message displayed when a user attempts to import
picks from their selected folder, and the name of the worksheet tab
in the pick sheet form doesn't match the name of the tab the program
is looking for, as specified in the User_Options tab.
Added a option to change the name of the tab that you'd like to
import in the Player_Picks worksheet tab, for convenience, so
the user doesn't have to keep going back to the User_Options
tab. (Great idea. Thanks Scott.)
Moved the feature that allows for the user to change the name of a
team's city or mascot if required, from the NFL_Schedule tab
to the User_Options tab.
Minor formatting fixes throughout.
Version 3.3
Whoops. When using the Import Picks
feature and my new, updated 2020 pick sheet forms, the import
process didn't recognize Washington's new mascot name... which is
"Football Team" in the pick sheet forms. (I forget to change this in
the program.)
I rewrote this code to allow the user to set each teams
mascot name. (It's in the hidden rows at the end of the
NFL_Schedule worksheet tab.) This way when Washington changes
their mascot name again, which they will do and maybe later this
season, I don't have to rewrite the code again.
Version 3.2
Entered the 2020 NFL Schedule.
Modified the code to accommodate the Raiders new name. (They are now
the Las Vegas Raiders instead of the Oakland Raiders.)
Changed the default font from Arial to Calibri throughout the
workbook.
Added the Entrants worksheet tab, at the request of a few
users.
Minor formatting fixes to the Player_Picks worksheet tab and
the NFL_Schedule worksheet tab.
Added the RELOAD button… since sometimes Excel seems to get hung up
and the dropdown Week # box doesn't work.
Recoded the import picks logic. (I added all possible choices to a
table, which is a lot cleaner than the big IF statement I had.)
If an NFL games ends in a tie, whether it is via the spread or the
actual score, Confidence Pool users now have the option to score
that game equal to 1/2 of the Confidence Points awarded for that
team. (Earlier versions simply gave all players a zero score.)
Version 3.18
When importing player picks via the new Import
Picks button, the program was not first "trimming" the text
found in each team selection box. (Which is very to do with the VB
TRIM function.) This meant that a box might appear to
be empty (since spaces are "invisible") but really would
contain characters... since "spaces" are considered characters!
Thus, since the program considered any character in that box a selection for that
team, (it doesn't have to be an X) it thought this team was selected
too, even if the other team
was clearly marked with an X! Fixed.
Version 3.17
I added the option for the user to import their
own pick sheet form, if desired.
Some pick sheet forms will undoubtedly be in a slightly different
format than my example form. The columns that contain the team names
may be different, the row containing the player nickname may be
different, the columns in which the user selects the team may be
different, etc. The user can now, in the User_Options
worksheet tab, tell the program where all of the necessary data in
the pick sheet form is located.
The default options for my form are:
Pick Sheet Form Visiting Team Name Column: D
Pick Sheet Form Visiting Team Selection Column: B
Pick Sheet Form Home Team Name Column: H
Pick Sheet Form Home Team Selection Column: J
Pick Sheet Form First Team Row: 7
Pick Sheet Form Selection/Team Offset: 1
Pick Sheet Form Total Points Cell: F40
Pick Sheet Form Player Nickname Cell: F42
Pick Sheet Form Week Number Cell: F2
Pick Sheet Form Tab Name: Weekly Pick Sheet Form
(Note that my form contains games in every second row. If
your form does not, your offset will be 0, not 1.)
Also, and much more importantly, I think the program
was importing the teams that were not picked, rather than the teams that
were picked. Ouch. No idea how I missed this.
Version 3.16
Bug found. (Thanks Mark.) When importing the
player picks with this new feature, the calculator was confusing the
two New York teams and the two Los Angeles teams. Fixed.
Version 3.15
Added the code to determine if a player had PAID
or not PAID when importing picks from the pick sheet form. (Program
simply looks at Cell K41 in the file.) If that cell is not empty
(which usually indicates not paid), the program will color that
player's name with the user-specified color in the User_Options
worksheet tab.
Version 3.14
Added the code to import player picks, from the
Player_Picks worksheet tab.
Version 3.13
Entered the 2019 NFL Schedule.
I removed the Weekly Pick Sheet Form worksheet tab from the
workbook. I've been thinking about removing it for awhile now. The
calculator doesn't require it. I think it's best to make that a
standalone workbook.
If a even-numbered player chose the 49ers as a pick, and if they
entered that pick as "49" (which is a very short form of the mascot
nickname name), the program, seeing that this entry was numeric,
assumed that this was a Confidence Point from the player above!
Ouch. Fixed.
I created a custom TOGGLE SORT button and a CLEAR ALL DATA button on
the Running_Totals worksheet tab, and a custom CLEAR ALL
SCORES button on the NFL_Schedule tab, replacing the Excel
Form Control buttons.
Version 3.11
Are you kidding me? First, several
weeks ago the NFL changes their website format, so that the code I
wrote to download the scores from their website no longer worked.
I was fine with that for now... since the user had the choice to
download the scores from the CBS or the NFL website. So
they can just choose CBS in the User_Options screen and everything
was fine. But now, going into Week 9, that doesn't appear to
work either. As I look at their internal HTML code, CBS also
changed a bunch of things around, so sure enough, my code no longer
worked.
So this is fixed... for now.
Version 3.10
Whoops. The cells in the
NFL_Schedule worksheet tab for the Optional Pointspread were not
all formatted to display one decimal, as they should have been. (For
example, if a user entered a pointspread of -7.5 ,the cell was
displaying 8 and that was not ideal.) Fixed.
Version 3.09
Entered the 2018 NFL Schedule.
Version 3.08
When downloading scores during the playoffs from the
NFL website, the program wasn't finding the scores of the Rams game.
(The NFL website abbreviates the Rams LA, while CBS and this program
uses LAR as the abbreviation.) Fixed.
Version 3.07
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
out.)
Version 3.06
Another formatting glitch fixed.
At times, a player was NOT grayed
out, although they should have been grayed 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 grayed 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.)
Version 3.05
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.
Version 3.04
At times, a player will be "grayed 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.
Version 3.03
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.
Version 3.02
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.)
Version 3.01
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
loaded.
Obviously, It was not my intent to "determine winners" immediately
after the player picks had been loaded.
Version 3.0
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
Sheet Form.)
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
be LV.)
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
tab.
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
hasn't crashed.
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
tab.
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
with byes.
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.
Version 2.32
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.
Version 2.31
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.
Version 2.30
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
Rams.
Replaced Jac with Jax in the code, where necessary. (The NFL
now seems to be using JAX as the three-letter abbreviation for
Jacksonville.)
Added the Weekly Pick Sheet Form to this workbook.
Version 2.29
The changes made to 2.28 caused a bug that could appear under
certain conditions, when loading games. Fixed.
Version 2.28
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.
Version 2.27
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.
Version 2.26
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
instead.
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
worksheet tab.
Version 2.25
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
bug.
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
are saved.
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.
Version 2.24
Entered the 2015 NFL Schedule.
Version 2.23
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
useless fluff.)
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.
Fixed.
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
lowercase. Fixed.
Dozens of minor cosmetic changes made to the program, too numerous
to mention.
Version 2.19
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.)
Version 2.18
The logic
to determine who was still alive got screwed up. Fixed.
Version 2.17
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
Giants, etc.
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.)
Version 2.16
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!)
Version 2.15
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
worksheet tab.
Sorting the list of players automatically, by the number of wins, is
now a user option.
Version 2.14
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
Player_Picks tab.)
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
games.
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
http://www.repole.com/sun4cast/stats/nfl2014lines.csv.
Allowed the user to enter the picks in any format (city name, mascot
name, etc.) they wish. The program will make the necessary
corrections.
Many other updates too numerous to
mention.
Version 2.13
Entered the entire 2014 schedule.
Version 2.12
Added a few more checks to convert invalid team names
entered by the user into the proper two or three-letter
abbreviation.
Version 2.11
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.)
Version 2.10
Corrected another typo found in the 2013
NFL Schedule.
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
checksum.)
Version 2.09
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
made.
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
Help tab.
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.
Version 2.08
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.)
Version 2.07
I didn't fully fix the previous bug. (And I'm not
convinced it's fully fixed now. More testing is called for.)
Version 2.06
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.
Version 2.05
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
undesirably.
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.)
Version 1.04b
Corrected a typo in the NFL schedule.
Version 1.04a
Updated with the entire 2013
NFL Schedule.
Version 1.03
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.
Version 1.02
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
remote.
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.
Version 1.01
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.)
Version 1.0
First release to the general public.
Version .9b
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.
Version .9a
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.
Version .8a
Replaced the old NFL graphic with the graphic currently used by the
NFL.
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
most tabs.
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
accessed.
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
deleted.
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
through AK.
Color coded the buttons to match the home/visitor team, when
clicked.
Coded the programming logic to retrieve past scores, for
prior weeks.
Added
the logic to download the score of the games from the NFL website.
