Enjoying Accomplishment

I’ve mentioned a few times the online spreadsheet I created to keep me on track with my annual Bible reading (finishing the whole Bible in about 11 months). Recently, besides my own personal reading schedule, I wanted to include my reading in preparation for the mid-week meeting. The assigned congregation’s annual Bible reading is usually on a different schedule than my own. For example, I am currently in Judges while the congregation is in Jeremiah. Since I am already reading the congregation’s assignment, when my personal schedule comes up for the same passages (sometime around July), I didn’t want to repeat them but plan on skipping ahead somewhere in the Greek scriptures.

However, my current system doesn’t allow for making notes for particular chapters. It also doesn’t have the versatility to know when to switch between the 1984 and 2013 recorded audio versions. Due to those limitations and others, I decided to recreate the system from scratch. It took me four days of intense and focused effort. I had to create VBA (Visual Basic for Applications) code in Excel in order to generate the data for what otherwise would have been a very tedious manual keying task, listing every chapter of every Bible book, (1,189 in case you’re curious). Then I had to assign a “day of year” for each chapter. (Some, like Psalm 119, are so long that it alone is assigned a single day, whereas other chapters in Psalms are short enough to read multiple chapters per day. I try to keep each day under 20 minutes.) Next I needed to, using formulas, create hyperlink references to either the new or old readings depending on availability of the new ones. (See all the books marked with the audio icon.) Next was another tedious task of taking the duration of each sound track and extracting it so I could use it in my new file to determine total listening time for each day. (For this I used PowerShell ISE.) Next up was the formulas needed to read the beginning and ending chapters and corresponding notations. (There were other interim/supporting calculations that needed to be made, but I hit on the major ones.) Finally, the interface was designed.

(The above image is from a screenshot taken 3/10/2017)

I mention the above mostly as explanation as to why I’ve been absent from blogging. Not just the four days of actual work, but the preparation and planning took time as well. I also mention it because it has been a long time since I’ve done anything as intensive as this in Excel. I love it because it is like solving a puzzle – both VBA and formula writing, when my efforts work the way I want, it is very satisfying. I actually had a heartwarming sense of accomplishment.

I was going to post a more detailed explanation and screenshots about the development and data, but I decided not to. In that I have never received any inquiries about the system, I won’t post a link to the new one. It appears I am the only one that has been using it. Again, I just wanted to share what’s kept me busy lately.

About