2013 Highlights: A Toilet Makes The List

Yet another blog post about the end of 2013 and the beginning of 2014 and all the things that have happened and have been accomplished and…blah blah blah.

I know. You’re sick of lists. So am I. So don’t feel compelled to read on. This one is largely for my benefit.

What did I accomplish? Quite a lot, actually, looking back on it. Here are the highlights:

Weight – I lost 20 lbs. I feel pretty good about this. Not hugely spectacularly over the moon, but pretty good. The reason I’m not shouting from the rooftops about losing weight is because this is a net number. I had lost 30 lbs going into the holidays, and then gained back 10 lbs during the sugar-infused holiday season. So the net weight loss is 20 lbs. Still, 20 lbs loss has me a lot healthier then where I started, so I’m happy.

Pants Size – I dropped a pants size. This one I am ecstatic about. I went into this year having gained a pant size. I hadn’t been a 38 waist ever in my life. Ever. And for about a year and half, that’s exactly where I was. My primary resolution for 2013 was to drop that pant size. I’ve done just that. Back down to a 36. Boo’ya.

Running – The opening of 2013 found me about as sedentary as a sea slug. Then, in March, after straining my back while I was putting a sweater away in a closet (oh, I kid you not), I said, “Screw this. Time to get moving.” Which I did. I’m now a runner. Not a fast one, but a consistent one. And when I go out the door for my normal run, it’s a five miler without second thought.

Drinking – I quit. Cause, you know, it was time.

Writing – I’ve put up the first two parts of my novel How It Ends, deciding to serialize it. The parts are here and here, and the reason I’m serializing it is here. The novel itself took my six years to write and edit. That’s primarily due to my own procrastination. And then the decision to self-publish it took another year and a half. I hemmed and hawwed, not sure if I should or should not, wondering if I was passing up a better offer just waiting to be made. It’s a phenomenon I like to call “Self-Publication Constipation.” (I’m thinking about trademarking that.) But I finally did it, with parts three and four coming in the new year, as well as an omnibus edition consolidating all four parts into a single volume.

Home Renovations – Over the holiday break, my father and I installed a new toilet in our downstairs bathroom. Finally, the old one that barely flushed is gone, replaced with a new one that works beautifully.

There are probably a few more, little things here and there, but these are the big ones.

As far as major events, there’s really only one that comes to mind. 2013 saw the passing of my grandmother. The heat of August found me driving down to Long Island with my windows rolled down to compensate for the broken A/C and dusting off a black double-breasted suit I don’t think I’d worn since a wedding ten years earlier. It’s been a few years since I went to a funeral or viewing, and 27 years since it was a member of my family. It was a strange time, full of sadness and family and laughter and wet eyes and food and waiting, endless waiting, along with the pacing of floors.

2014 starts tonight at midnight. I have a whole slew of stuff I’d like to get accomplished, but I’ll keep those to myself. They’re my resolutions, after all, and no one else’s, so I don’t need to bother anyone with them.

Here’s hoping that everything we want to get accomplished this year gets accomplished, and that our families remain healthy and happy.

Happy New Year.


How To Draw A Banner

I was drawing with my kids a few weeks ago and they asked me to draw a banner. No idea why. I can’t remember now what the context was. In any event, after I finished the banner, they proclaimed me the greatest artist in the world. Clearly my kids don’t get out enough. That said, I drew up instructions for them to follow on how to draw a banner.

I present them to you for your enjoyment. Just follow the red lines and soon you’ll have a banner of your very own.


Excel Geeking: A Question For Other Excel Geeks

Okay, this one is a bit out on a limb, since I don’t have a lot of followers, and expect most of them follow me for the writing or running posts, but here goes.

I have a question for my fellow Excel geeks and gurus regarding development and design.

Anybody with thoughts or opinions is welcome to jump in with comments.

And who isn’t thinking about Excel programming going into the weekend, amiright?

Here’s the background:

I have a set of workbooks that are basically set up to “act” as databases. There being a restriction on the use of MS Access, Excel became the database of choice. Let’s not publicly debate whether or not Excel is a database, it’s what I inherited and have to work with. We all know it’s “database”, with quotation marks around it. Let’s move on.

I’ve been trying to get the users out of this Excel “database” by using other, separate workbooks for reporting shells, and using SQL to retrieve the data from the Excel “database”. So far, so good.

I’m at a point now, however, where I may have the option to migrate some of this Excel “database” stuff to a real database, while keeping Excel as a front end and user interface. Beautiful, Totally in my wheelhouse.

Here’s where I’m, not stuck exactly, but rather in a bit of a quandary as to how far to take my new approach.

The Excel UI workbooks are going to require support tables to make them run. Those support tables are going to be housed in a database. Upon opening the UI workbook, the SQL statements will fire off, careening over the network via ADO connections, and sucking the data back down the pipe, only to land in the UI workbooks and make everything sunshine and roses.

Which, based on my current programming, it does.

Except I don’t want to hard code five or ten or fifteen or whatever number of SQL queries it will take to load the support tables.

So I came up with what I felt was a nice, tidy, and portable solution. A SQL table range in a worksheet that will house the SQL statements themselves, and as I iterate through it, the SQL (all SELECT–let’s not make life hard with INSERT, UPDATE, and things of that ilk) will be read from the cells, fed into a custom class I built for managing ADO connections to databases (it’s sweet, I’ll share it sometime), and off to the database the SQL will go.

Fantastic so far. No problems. Everything working like a dream.

Until the thought occurred to me, “You’re still hard coding your SQL, you’re just doing it in a worksheet instead of directly in VBA.”

Well, gosh darn it all, that’s true. The SQL would have to be written into the table range prior to the roll-out of the UI workbook, and if it changes, I’d have to recall the workbook and issue a rev’d version.


And here’s where I need the help. My next step, which is done by the way, was to create another table in the database, one that houses the SQL statements themselves. So now, when the UI workbook launches, it loads all the SQL statements first, THEN fires them off one by one until all the supporting tables have been loaded.

At this point I took a step back and thought, am I nuts? I mean, behind the regular brand of nuts? Am I building this thing up to much for a workbook application? The idea behind it was to load SQL statements that could be centrally managed in the database by an administrator. If something in the SQL changes, then the SQL statement housed in the database can be changed, and when the UI workbook runs, it will pull down the new SQL and run with that, rather than having to go into the UI workbook and change it manually in a worksheet.

So the question I have for my fellow geeks and gurus is: have I gone too far? Is this overkill? Am I SQL-drunk?

Why Star Wars Episode III Is Actually A Good Film


This is not a review. If you want a review of the Star Wars prequel movies, Google it. You’ll get somewhere in the neighborhood of a googol of reviews. (See what I did there?) The new movies have been reviewed, dissected, disemboweled, villified, trumpeted, defended, and defecated on more than just about any movie franchise extension I can remember. And rightly so. The original Star Wars trilogy took about two days in 1977 to completely and totally embed itself like a deer tick in the American (and worldwide, really) pop culture psyche.

Naturally, then, when Lucas announced in the mid 90’s that he was embarking on the holy crusade of geekdom and making three new Star Wars movies, the world lost its collective shit. As a child of the 70’s and 80’s, I can safely say, so did I.

I wanted to like the new Star Wars movies. I desperately wanted to like them. Someone asked me (I can’t remember who now, since it’s been fifteen years) what if the movies weren’t that good. And all I can remember thinking was “What are you, nuts? How could they possibily be bad?” How indeed.

My primary gripe with Lucas is that he forgot his own motto, by which he lived back in the time of the first three movies. Specifically, he stated that a special effect without a story is a pretty boring thing. I wish he’d remembered that, and modified it a little. A special effect with TOO much story is also a boring thing. The first of the new moveis, The Phantom Menace, had all kinds of stuff going on in it. A dispute centering on a trade dispute, which causes the aggressors to invade a largely peaceful planet. The political machinations of a dark shadowy figure pushing the aggressor to act first. The discovery of a messianic child and his extraction from his slave existence. The peaceful planet’s not so peaceful coexistence between two species that, while not at war, are certainly not at peace. A noble knight so blinded by his belief in the potential of the messianic child that he’s willing to defy the orders of his elders and train the boy.

I mean, way too much.

The Phantom Menace, while it made a bundle and a half of money, received a lukewarm response from critics. Viewers and devotees of the franchise flocked and reflocked, and flocked a third time, to see the young Obi-Wan, the younger Anakin, the insanely awesomely designed and underused villain Darth Maul, and to lose themselves in new depths of a galaxy they had not visited in sixteen years.

Guilt as charged.

I actually didn’t despise the first movie the way many fans did. I was four when I first visited the barren wastes of Tattooine. Returning to the desert planet was like a homecoming of sorts for me. Despite the inadvertent cultural insults, the hit and miss speed sequence of the pod race, and lackluster acting and dialogue that can only be summarized as being written with a “tin ear”, I enjoyed the first movie.

The second movie was so-so for me. Lucas continued to push forward, continued to direct, folding odd storylines in on themselves, and introducing even more special effects that were, honestly, boring.

While the third movie seemed to showcase Lucas finding his directorial stride, it was still beset with issues ranging from crappy dialogue to continuity holes so big, they could only be filled by the small cadre of party-line devotees that refuse to see the movies’ flaws for what they are, and endlessly debate the ways the continuity holes were “probably” filled.

And yet…

And yet, as I watched the third movie, I realized that, as we came to a point that every Star Wars geek had been waiting for, the visualization of the betrayal of the Jedi order at the hands of Anakin, as I watched him arrive at the Seperatist stronghold on Mustafar and proceed to, um, despense Sith justice to the Seperatist leaders, I realized the Lucas may be a stroytelling genius. These Seperatists–who had been among the primary antagonists of the first two movies, and the first half of the third, who the audience was meant to root against and whom the Jedi struggled to defeat–these individuals were being slaughtered at the hands of the newly annointed Darth Vader, showing ruthless effeciency. As he cut down the Seperatists, I realized that these villains/victims had been the progeneters of the Rebel Alliance, a group that we will ultimately root FOR in Episodes IV through VI.

It was a kind of stark moment for me, and I sat in the theater wondering how I should feel about that. And as I watched the phenominal final lightsaber duel between Anakin and Obi-Wan, a duel made fantastic by the involvement and advise provided to Lucas by his good friend Steve on how to craft a great action sequence, I realized that the characters I thought I knew had changed, as did my feelings toward them. As this realization came to me, I accepted the fact that Episode III was actually a good film.

Through all of the machinations and somersaults Lucas took in his films to set up a political war, a power grab by an evil villain, and the subsequent betrayal of the Jedi order, Lucas created a helluva story arc. What’s unfortunate is that his execution on the movies was feeble, causing his Machievellian like plotting to be overshadowed by mediocre filmmaking.

Maybe these would have been better as books…

Amazon’s Changing Their Payment Policy For E-Books

For all of you self-publishers out there, if you didn’t already get the email from Amazon, then this can be your officially unofficial heads up about Amazon’s payment policy.

For any Kindle Direct Publishing account you have with Amazon, if that account is set up to receive payment via electronic fund transfer (EFT), then you’ll get the money 60 days after the money is earned. In other words, 60 after you a sale of your book!

Previously, for EFT, there was a $10 US threshold. You had to make $10 in royalties before Amazon paid you. No longer. Now, 60 days later, your royalties will be transferred to your account.

There’s some caveat in there about Brazilian publishers, but I ignored that part. Cause, you know, I’m not Brazilian, nor do I reside in Brazil. (I should note that I’m talking about US dollars here.)

And as far as I can tell, there is no change on when Amazon will cut you a check, if you choose to be paid via check. Last time I looked, that threshold was $100.

This is great news for me! It means that 60 days from now, I’ve got an extra cup of coffee coming!

Excel Geeking: Checking If A Workbook Is Present

I’m like a lot of Excel gurus, I suspect, in that I’ll see something that Excel does and think “I can do that better”. Or, better yet, I’ll see something Excel doesn’t do at all an think, “Oh yeah, I have to build that.” It doesn’t really matter if I need the utility or will ever use it more than once, the idea that it could be done looms out there like a challenge waiting to be met.

(Remind me to tell you sometime about the latest windmill I’m tilting toward, and have been for over a week.)

In the last several years of constructing utilities, I’ve come to rely on a simple little routine to tell me whether I should use the routine. No, it doesn’t do anything magical like evaluate my worksheets and choose the best tool automatically. If it did that, then we’d be verging on the days of Skynet. No this is more about whether the environment is right for a utility.

See, I have some utilities that require a workbook to be present. No workbook, no use for the utility. Worse yet, no workbook, potential error as the utility tries to locate one.

With that in mind, I add this tiny function to any add-ins that could potentially be loaded without a workbook being present. It checks for a workbook and returns a TRUE or FALSE depending on what it finds:

Function bWorkbookPresent() As Boolean
‘ Description:  This function test to see if there is a workbook available.

‘ Author:       Scott Lyerly
‘ Contact:      scott.c.lyerly@gmail.com

‘ Name:             Date:           Init:   Modification:
‘ bWorkbookPresent  16-DEC-2013     SCL     Original development

‘ Arguments:    None

‘ Returns:      Boolean     TRUE=workbook present; FALSE=workbook is absent

    ‘ Variable declaration
    Dim wkb As Workbook
    ‘ We’ll turn off error handling to keep this from throwing an error.
    On Error Resume Next
    ‘ Try to set the workbook variables.
    Set wkb = ActiveWorkbook
    ‘ Check if the variable is Nothing
    If wkb Is Nothing Then
        ‘ If it is, then there was no workbook present to set the variable, meaning
        ‘ there is no workbook present.
        MsgBox “No workbook present.” & _
               vbNewLine & vbNewLine & _
               “Operation cancelled.”, _
               vbOKOnly + vbExclamation, _
               “Workbook Error”
        ‘ Return FALSE for the function
        bWorkbookPresent = False
        ‘ Otherwise, there was a workbook present, so we’re good.
        ‘ Return TRUE for the function.
        bWorkbookPresent = True
    End If
    ‘ Make sure to turn error handling back on.
    On Error GoTo 0
End Function

Implementing this is really easy, just a single line of code:

If Not bWorkbookPresent Then Exit Sub

That’s it. Feel free to reuse it. Enjoy.