Happy Halloween

Happy Halloween everybody! 

Now go door to door begging for candy. 


Excel Geeking: When List Object Columns Attack, or Why Won’t This Calculated Column In My List Object Table Calculate Anymore

I know the title of this post is too long. But it captures the essence of what I’m trying to figure out.

I’m sure this has happened to someone else, and maybe even someone else who has figured out how to correct it. If you have, let me know, leave a comment below, send a smoke signal, anything to help me close the loop on this thing.

Here’s the problem: in a list object, or table as they’re often called, on occasion a field that is normally calculated stops doing just that. More clinically, the formula replication functionality in Excel coughs twice, falls down in the dusty road of the information highway, and gives up the ghost. Not on all tables, mind you. Not even on all columns in the same table. In specific columns only.

I’m on the hunt to figure out why.

The primary problem in searching for an answer is that you can’t quite find what you’re looking for through Google. Or maybe its just me, and that I could very easily suck and searching the interwebs. When I try searching for words such as “list object calculated column not working”, or “replicate formula no longer working”, I always find myself awash in a sea of posts about how to change the setting in Options for replicating formulas. That’s not what I need. I also find a lot of posts on how to turn Calculation back on in a workbook. Not what I need. I know these two things already. I need something else.

I checked to make sure that the cell isn’t formatted for Text. If you’ve ever formatted a column as Text and tried to change the formatting back to Number or General, you know that Excel can occasionally develop amnesia about how formulas work in those cells. Once you turn a cell to Text, changing it back is a pain in the butt given that Excel likes to keep treating formulas as if they’re Text. No, that’s not the issue I have here.

I tried adjusting the Options settings. I turned the Replicate Formulas option off, then back on. I had a hope that this would essentially pop the clutch on these calculated columns. No dice. I tried doing it through the code. Also no dice.

I could have spent all day rotting this thing out, but guess is that is never actually find it. It was simpler to build a new table with the same columns and move my data into it. Which is what I did.

Still, I figure there has to be a means of resetting a calculated column. Anyone? Anyone? Bueller? Bueller?

Excel Geeking: Extracting Comments Using VBA

A couple of months ago a colleague moved into a new position at work and inherited a big ugly spreadsheet. Happens to the best of us, amiright? The biggest challenge with this spreadsheet is that the previous owner, who has since retired (and good for him!) didn’t update his status notes and comments in a cell or a column. He kept them all in in-cell comments.

In. Comments.

Needless to say, the spreadsheet my colleague inherited had a couple of billion comments, some of which were absolutely enormous. She wanted to know if there was a quick way to extract all of the text out of the comments, and put them in the first available cell to the right. And if the old comments could be deleted as part of the extraction, that would be a bonus.

To which I said, of course that can be done!

And so, I wrote this little routine as a way of pulling the text from comments, placing it in the first available cell to the right, and blowing away the old comments if desired. Enjoy!

Sub ExtractComments()
' Description:  This iterates through all of the used cells in a worksheet
'               and where comments exist, extracts them and places them
'               in the next free cell to the right in the row where the
'               comment exists.
' Author:       Scott Lyerly
' Contact:      scott.c.lyerly@gmail.com
' Notes:        None.
' Arguments:    None
' Returns:      None
' Change Log:
' Name:                 Date:           Init:   Modification:
' ExtractComments V1    10-APR-2015     SCL     Original development
On Error GoTo ErrorHandler

' Variable declarations.
Dim sh          As Worksheet
Dim rng         As Range
Dim rCell       As Range
Dim rComments   As Range
Dim lAns        As Long
Dim lCalc       As Long

' Set some references to the active sheet and the sheet's used range.
Set sh = ActiveSheet
Set rng = sh.UsedRange

' Check to see if there are any comments in the sheet.
Set rComments = rng.SpecialCells(xlCellTypeComments)
If rComments Is Nothing Then GoTo Exit_Clean

' Ask user if they want to delete the comments as they are extracted.
lAns = MsgBox("Do you want to delete the comments as they are extracted?", vbYesNo + vbQuestion)

' Speed up the processing time.
With Application
lCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' Iterate through the used range and where there are comments,
' extract their text and add it to the row.
For Each rCell In rng
If Not Intersect(rCell, rComments) Is Nothing Then
sh.Cells(rCell.Row, sh.Range("XFD" & rCell.Row).End(xlToLeft).Column + 1).Value = rCell.Comment.Text
' If the user selected YES above, delete the comments.
If lAns = vbYes Then rCell.Comment.Delete
End If
Next rCell


' Restore the environment.
With Application
.Calculation = lCalc
.ScreenUpdating = True
End With

' Delete objects to memory.
Set sh = Nothing
Set rng = Nothing
Set rComments = Nothing

Exit Sub

MsgBox Err.Number & ": " & Err.Description
Resume Exit_Clean

End Sub

Book Review: “The Girl On The Train” by Paula Hawkins

girl on trainThe last of the books I snarfed down at the end of this summer was “The Girl On The Train” by Paula Hawkins. This was the “it” book of the summer, the one everyone was talking about and that everyone couldn’t put down. It was supposed to be the page-turner with the twists you couldn’t see coming. And so I too engulfed this one to see what everyone was talking about.

Truthfully, if I had to sum the book up in a word, it would be “meh”.

It’s hard to give too much of a plot summary without giving too much away, and while I, unlike the rest of the world, did not think the book was all that and a bag of chips, I still don’t wish to spoil it for those you enjoy these things more than I do. Or as my wife would describe it, “everyone else but you.”

Fair enough.

The essence of the plot, then, is that a woman named Rachel, down on her luck and sipping gin and tonics from a can while taking the train back and forth to London, fantasizes about what the people in the houses behind the tracks do, the kind of lives they lead. Her own life has fallen apart and she takes a kind of solace in the lives these fantasy people lead in her head. She has even given them names, since their real names are unknown to her.

Then, one day, she sees something as she’s staring through the train window, something that turns her fantasy on its head. At that moment, her life changes from fantasies about these people to an all out obsession about what she saw. An obsession so deep, she risks her life and livelihood, and perhaps darkest of all, her integrity to get close enough to discover the truth.

It sounds like an enthralling premise, doesn’t it? Critics have been using the term Hitchcockian to describe it. It does have a certain “Rear Window” quality to it, that strange voyeuristic quality of Jeff Jefferies looking out his back window and seeing something he thinks is a murder. But that’s were the comparison ends. It starts with a voyeur and turns into a study of a life in freefall. In that regard, the book actually became hard to read. As the main character made bad, then worse, then catastrophic decisions, I wondered how much longer I could read until the unraveling of her life became too unpalatable. It never quite got there, but it came really darn close.

The plot relies largely on what the New York Times referred to as “unreliable narration”, meaning you can’t trust what the narrator is telling you. Except that you can. The narration flickers between three narrators, all of the women, all of whom are involved in the plot. Each one has their quirks and problems, each has moments of fooling themselves, but that’s really all they’re fooling. Unreliable narration only goes so far, and when at least one of the characters is a blackout drunk, you can readily expect that their memory will be a bit, shall we say, fuzzy.

Another of the devices used to confuse the reader is a jumbling of the timing of the scenes. The cutting is designed to bring the reader back and forth and possibly add some confusion, but a careful reading will show that each chapter is timestamped. You get a certain anticipatory feeling as you near the time when you know that the precipitating event happened, and that you’ll finally get to see it, feeling like maybe you’ll be surprised by what’s around the corner. But you aren’t.

Or at least I wasn’t.

Also, the book was written in the first person present tense. Don’t get me started.

The climax doesn’t twist nearly as much as everyone had been claiming it did. I found myself unsurprised at the ending, indeed, hoping for something different. I was disappointed when I was right. Of the four books I’ve reviewed in the last few days, having gulped them all down like a thirsty man in a desert, I’d have to say that “The Girl On The Train” was my least favorite.

Sorry everybody.

Queue up the backlash. 

Book Review: “Robert B. Parker’s Wonderland” by Ace Atkins

wonderlandI needed something to cleanse the mental pallet after Lou Berney’s “The Long and Faraway Gone.” It was such an intense read, the way Berney knifes the reader in nearly every single scene, that I needed to visit some familiar friends. Queue up Spenser.

Since Robert Parker’s sudden death in 2010, Ace Atkins has been carrying on the Spenser tradition with the blessing of the estate. Hand picked to keep Spenser going, Atkins has written four Spenser books thus far, and will likely keep going for the foreseeable future. The first one, called “Lullaby”, I reviewed here. It wasn’t a bad book, so I thought I’d give “Wonderland” a try.

Doesn’t hurt that I got it on remainder.

This time, the client is Henry Cimoli, the owner of the Boston gym where Spenser and Hawk used to (and still do) box. He’s being muscled out of the condo he owns so that a Las Vegas casino developer can move in and build. The novel explorers Henry more than I remember Parker ever doing, and Atkins mostly nails him. There is of course the usual cavalcade of characters that Atkins likes to bring to the stage, and to a certain extent, this feels like Atkins is trying to prove to the Parker faithful that he knows the Spenser work so well. Despite the fact that the book is chock full of characters Spenser has had contact with in the past, the characterizations largely work to the novels advantage.

There is one exception. The relatively new player in the on-going adventures of Spenser is one Zebulon Sixkill, the American Indian that Spenser has taken under his wing to train as a private investigator. This relationship feels forced, as if Atkins picked up a leftover character from the Spenser dinner table and decided to do something with him. In the final Spenser book by Parker, “Sixkill“, we’re introduced to “Z”, as Zebulon has come to be called, but there is nothing to indicate that he’ll be a continued or recurring character. I’m not sure if this is Atkins taking liberties, or whether any of Parker’s notes indicate that Z will continue, but in this case, it feels forced.

As a crime novel, it’s a good one. The plot is more complex than the ones Parker used to set up, at least in the later Spenser novels. Spenser’s sarcasm is present, though more muted than it was in “Lullaby”, which isn’t a bad thing. “Lullaby” felt a little over the top with the hard-boiled quips. Spenser is still sarcastic in “Wonderland”, but he never ventures down the path of snarky. The prose is not as lean as Parker’s was, but then again, few hard-boiled novels written these days are as lean as Parker wrote. Parker, self-admittedly, loved dialogue because it “chew[ed] up a lot of pages.” One could look at this as being lazy, but I find that Parker’s style was such that the plot, characters, and danger were all conveyed convincingly through dialogue, and sometimes dialogue alone. A great example of how this works is through the Jesse Stone novels (which I highly, highly recommend).

After the last page is done, Atkins still isn’t Parker. They’re tough shoes to fill. But Atkins does a nice job with most of the characters and the location of Boston itself. Spenser will continue to live on, and in Atkins hands, that’s not a bad thing.

Movie Review: “The Martian”

the-martian-posterRarely do I make a point of going to see a movie on opening night. There are few that I want to plan my schedule around. But after reading Andy Weir’s relentlessly readable debut “The Martian“, and after seeing the commercials and trailers for the movie version for the last few months, I knew I’d have to crave a slot in my calendar to fit it in.

And I’m so glad I did. Holy cow, what a movie.

For the uninitiated, the premise of the book and movie is that, in a near future when mankind has made successful exploratory missions to Mars, one mission goes wrong. A powerful storm descends upon the mission and they are forced to abort the remainder of the mission. But in the effort to get to the evacuation ship, one crew member, Mark Watney, is struck by debris from a collapsing communication array and lost to a gust of Mars-strength wind. With his bio-monitor returning nothing, the remaining crew members blast off from the red planet, leaving the body of their fallen comrade behind.

Except that Mark’s not quite dead yet. And having been presumed dead and left behind, he’s now stranded on a planet that does not support human life. This then is the crux of the story: how to survive on a uninhabitable planet for the three years it would take for NASA to mount a rescue mission? That is, presuming he can figure out how to contact them in the first place.

The book was one hot read that I opened on a Friday over Christmas and didn’t put down until I finished it that Sunday. Given that I essentially unhinged my literary jaw and swallowed the book like a python, I couldn’t wait to see the movie.

Is it a fully faithful adaption of the book? Of course not. What movie is? The book is one life threatening sequence of events after another. As Scott Kelly once said, “Space is hard.” In the interest of time, there was simply no way to put every single struggle that Mark Watney, the titular Martian, has to overcome. The movie would be four plus hours. As it stands, the movie clocks in and two hours and twenty-one minutes, not a short stretch by any means.

The key for director Ridley Scott, then, is to focus on those things that are the most dramatic. And there are several. Anyone who has read the book will tell you that the challenges range from the of farming potatoes in Martian soil to deliberating starting fires inside the habitat (or “HAB”), from rovers with short battery life to the lack of water, from the lack of food to the unbelievable dust storms–the list goes on and on. These, and a few others, are more than enough to fill one film.

Scott does a remarkable job with the material, making sure we are in close with Matt Damon’s Mark Watney so that we can feel Watney’s plight, and then offering huge sweeping visuals to remind us of just how alone Watney is. He keeps the pressure on, allowing victories over overwhelming odds before throwing another problem at Watney. In some ways, the approach reminded me of how James Cameron treats his characters, throwing as much as he can at them to see what they’re capable of. The narrative toggles back and forth between Watney’s attempt to survive in the red wasteland and the key players at NASA who are trying to figure out how to get him home, struggling with their own boundaries, knowing that they must push past them if they are to save Watney.

Matt Damon is the perfect choice for Watney, conveying as he does so well the likability of the character, letting us feel his fear when things go bad, feel his excitement when he solves another life-threatening problem, and feel his tense sense of near-relief when he’s on the verge of being rescued. He keeps his spirits up by focusing on each task at hand and by making occasional light of the situation. There’s a gentle sarcasm to the character that never veers into snarky or nasty, but is always just on the precipice of defeated, without falling into that crater. Damon has always done a marvelous job of allowing the audience into his characters, eliciting empathy from the viewers, whether he’s trapped on Mars, hunted by the CIA, or being rescued by Tom Hanks.

The other standout for me was Jeff Daniels as NASA Director Teddy Sanders. If you boil the NASA Director’s job down to brass tacks, Teddy Sanders is a paper-pusher, the highest paid account in the building, with whom the buck stops. Each decision regarding the rescue mission goes through him and the weight of each decision is evident on his face. He keeps up the steely-eyed resolve even as some of decisions are not the popular ones. But someone has to be in charge, and Sanders is that man.

There are many great performances, including Chiwetel Ejiofor, Donald Glover, Jessica Chastain, and Benedict Wong. Even actors who are given very little to do, such as Kristen Wiig and Sean Bean, stand out, making the most of the material at hand. (There is a “Lord of the Rings” joke in there that had me on the floor.)

You could boil this movie down to the simple catch-phrase of “Apollo 13 on Mars”, but that does it a disservice. Is it as good as “Apollo 13“, to which it will likely be compared? Probably not. “Apollo 13” is one of the finest films of the thirty years. But Ridley Scott and crew know how to deliver tense, riveting entertainment, and I was on the edge of my seat even though I knew how the film would end. It’s helluva good film and one of the best you’ll see all year.

Book Review: “The Long and Faraway Gone” by Lou Berney

Right after reading “Third Rail”, I jumped into Lou Berney’s gut-wrenching “The Long and Faraway Gone“. And when I say guy-wrenching, I mean knife plunging into the gut, then barbs opening at the tip, and then twisting all around until your digestive tract is the groundest of ground meat.

long and faraway goneOstensibly a mystery, the book opens with a movie theater robbery prologue that leaves your legs wobbling before moving forward with the story proper. Picking up a couple of decades after the opener, we follow one of two main characters, Wyatt, as he comes back to the city of the prologue and face to face with doubts and demons that filled him before he left, being the only survivor of a brutal slaying.

At the same time, the book follows the story of Julianna as she wrestles with the disappearance of her big sister Genevieve, who vanished around roughly the same time period as the movie theater robbery. She’s been trying to find out what happened to her sister. It’s become an obsession for her, costing her money, and even potentially her job.

The book is, essentially, two separate stories that barely overlap and where both could just as easily be a book unto their own. Why Berney felt compelled to collect two crime stories, each with its own arc, into a single book is unclear. It gives the book volume, and allows the narrative point of view to flicker back and forth without staying with one character the entire time. Perhaps it’s out of a desire to avoid point-of-view reader fatigue that the individual arcs are combined.

Does it work? Sure, well enough for the stories. The tension Berney builds is at times intolerable and you beg for the release of a scene change. He invests the reader in the obsessions of these two primary characters so that you root for them to find the answers they’re looking for. Each chapter examines the characters, both as they are now, and how the events of their earlier lives have made them into who they are. But each examination draws on the sorrow and madness of the pivotal moment of their youth, and with each examination, Berney gives the reader another rabbit punch to the kidneys.

As Wyatt and Julianna crisscross the city trying to dig just a little deeper into their wounds, there grows in the reader the hope that perhaps they will cross paths. But if you’re expecting them to find each other, join forces, and help resolve each other’s quest for answers, you’ll be left wanting. Wyatt and Julianna intersect briefly, and then part, each continuing to pursue their own obsession.
Somehow, this theme of partial satisfaction was the major theme that emerged for me while reading. As I neared the end, I realized that neat answers will not be handed out like pretty little presents left under the tree. This feeling of partial satisfaction is overwhelmingly what the reader feels by the end. Wyatt and Julianna both find the answers to the questions they have been asking, but the answers provide no solace, no real closure. Maybe they have been asking the wrong questions. Or maybe life is messy and unsatisfying and sometimes the answers you get don’t get you anything you need or hope for.

Berney offers the reader only a slight small satisfaction that the protagonists never get. It’s contained in the final two chapters, which feel more like a concession to the journey the reader has taken more than a requirement of the story. By the end of the book, I was exhausted and when I closed the last chapter, I felt like I had read a powerful and moving statement on the human condition. And I was happy to leave it in the past and not look at it again.