The Maffetone Method – 5 Months In

Okay, I realize the month’s not over yet, as it is still the last day of February. But I can give you my solemn word that I will not be going for a run between now and midnight tonight, so I think the data is safe to review.

So how did February go? I guess if sum it up by calling it a mixed bag.

Let’s start with the graphics:

running month 5


By the list of stats, you can see that I dropped another thirteen seconds off my pace time. Which is great. That’s the whole idea. Keep running slow and eventually the pace comes down to meet your heart rate without your heart rate having to rise to the occasion. Except my heart rate did rise a little bit, on average. More on that in a minute.

The runs themselves were really good through the first half of the month. I ran my first long run in a while, getting out for a good 8 miles on a Sunday morning. I realize 8 miles is not a long run to a lot of “serious” runners, but for me, it was. I hadn’t run a good long distance run since late last summer, and even when I was getting out more regularly, my longest run ever was 12 miles. Since my goal is to run a half marathon in June, I got start cranking up the mileage to make sure I’ll cross the finish line, rather than be carried over it.

So through the first two weeks of Feb, I was feeling good, running 5 to 6 miles without a care in the world. And then it snowed. And not just snowed, because that’s already happened plenty this winter. No, I mean Snowed with a capital S. In the third week in February we saw four snowstorms over an eight day period. I mean, honest to god, really? That threw a huge monkey in the wrench in terms of aerobic base training. One of Maffetone’s primary recommendations is to train slowly all winter long so that you can race in the spring and summer. Do your aerobic training in winter so your spring/summer racing can serve as your anaerobic training. ‘Cept how do you train during the winter when winter won’t let you?

This is where the treadmill at the local gym should have come in. Shoulda woulda coulda. I should have altered my morning routine to go run on the treadmill to keep the training going. But I hate treadmills. They bore me to tears. Guess I wasn’t all that committed after all.

On top of the Snow with a capital S, it’s been cold. Damn cold. Like, Cormac McCarthy’s The Road kinda cold.

So the last two weeks of February saw just two runs for a total of 8 miles. Pretty anemic. But winter was only half the problem I ran into. (Get it? “Ran” into? Never mind…)

My heart rate monitor has been acting fairly wonky lately. I work in IT and I can tell you that “wonky” is a very technical term. Basically, it would give me strange readings as I ran, usually only for a moment, and return to normal. For example, I looked down at it as I leveled out on one particular street during a morning run. I watched it go from 137 to 213 then back down to 137. Whiskey tango foxtrot. And the monitor doesn’t know enough to think “hmm, that’s strange, maybe that’s a blip we should throw out.” When the run is over and I check the average heart rate and max rate, sure enough, the max reads 213. That does me no good. I have to spend the rest of the run closely monitoring the readings and estimating from what I saw what I think the max heart rate was. God only knows what this is doing to the average. The primary concern I have with this is, how do I know any of the readings have ever been right? I’ve replaced the batteries, but it still happens here and there. I really want to get a new monitor, but the
Magellan Echo is still a little out of my price range.

Also on the subject of heart rate, I feel like I’ve lost my feel for pace. The last three runs I’ve run, my average heart rate has been 144. I can usually keep it to 141, 142. My pace for the first two of these runs saw my pace lower that the monthly average by 15 seconds. I thought maybe I was losing conditioning. The last of these three runs I watched my heart rate climb up into that 144 arena. “Here we go again,” I thought. So I (stupidly) cute my run short out of frustration. Three miles and done. I looked at the pace for that run and found I had run it 10 seconds under the average for February. I could have, and would have, sworn I was running a pace like the first two. So I feel I’ve lost my feeling for pace, which is a little bothersome. And to bring in my other issue, how much of this due to a strangely functioning heart rate monitor versus simply losing my feeling for pace. No earthly idea.

So that’s how month five went. I figure winter has to clear out of here at some point. There’s a 10k north of me that happens at the end of April that thinking about. It would be a nice way to get into a racing frame of mind for the June half marathon. But first, I should probably get through March.


Excel Geeking: My Picks For Getting Started With Programming Excel

I’ve fielded this question a couple of times, so I thought I’d give to formal list here. These are the books I’ve used (and continue to) to get started with VBA/Excel programming (among other things). I also list the sites I visit regularly when I feel like expanding my Excel/VBA knowledge.


power programming

Excel 2010 Power Programming with VBA, by John Walkenbach – After I finished poking around Google trying to find the answers to my questions, I decided it was time to get serious and get some structured learning. This was the first book I bought, and remains one of the two I refer to the most.


Professional Excel Development, by Bullen, Bovey, and Green – This is the other book I hit on a regular basis. I never fail to learn something when I go to it (usually something I’ve learned before and had simply forgotten). But take note: this puppy is NOT for beginners. It’s barely for the intermediate programmers. There is a gold mine of information in there, but you really need to know what you’re doing in VBA to digest some of the content. But if you’re ready for it, you can’t do better.

excel prog ref

Excel 2002 VBA Programmers Reference, by Bovey and Rosenberg – This one’s an old one, and has probably been updated since I bought it. But this is the one I’ve got. It has some nice stuff on connecting Excel to Access using both ADO and DAO, including how to connect to Excel as a data source with ADO, which I’ve referenced quite a bit.

prog excel

Programming Excel with VBA and .NET, by Webb and Saunders – I like this one because it’s essentially a paper version of the Excel object model, but instead of being just a straight list of objects, properties, and methods, it gives some textual description of said objects, properties, and methods, and usually gives some nice short code examples of how to use them.

vba dev hnbk

VBA Developer’s Handbook, 2nd Edition, by Getz and Gilbert – This one goes DEEP. Mike is Product Manager with Microsoft’s Developer Tools division, specializing in VBA. He knows this stuff inside out and upside down. The result is some incredibly deep knowledge that can be very useful, but can also go over your head. But it contains things you won’t find in a lot of places. You want to program media files? This is the book. Harness the information about your monitor? It’s in there. Build custom data structures? Yup. It’s all in there. But this this level of programming is pretty complicated, so tread carefully.

pvt tbl

Pivot Table Data Crunching, by Jelen and Alexander – I love this one. It’s a compact book for a computer book (only 288 pages), but it gives so much great info on pivot tables. And not just pivot tables from a user interface perspective, but also includes how to program them with VBA.


Sams Teach Yourself SQL in 10 Minutes, by Ben Forta – This one isn’t a VBA book (as you can clearly see by the name). This one is a great, slim little gem that gives you the basics of how to program SQL. Syntax is everything. And since there will come a point where you have to hook an Excel sheet up to and Access database (don’t shake your head at me, I’m telling you, it WILL happen), this gives you the fundamentals you need to keep from screwing up the syntax.


Really there are only three sites I hit up on a regular basis.

Daily Dose Of Excel – Dick has been blogging about Excel for at least ten years. He’s got an easy conversational style that’s fun to read and easy to digest. I landed on this site a number of times while searching for VBA solutions I couldn’t figure out on my own. There’s one thing about Dick’s site that makes it unique: it’s the site at which everybody gathers to talk about Excel and VBA. Daily Dose of Excel is not always updated in a timely manner, with sometimes a month between entries (Dick, like most of the rest of us, has a day job), and it’s not like the content is organized into neat categories with each topic covered and cataloged. You can navigate the categories dropdown list in the blog’s sidebar, but don’t expect a page listing all of the topics in an index-like fashion. And yet, every Big Name Excel Personality flocks to it. I don’t know why, but there it is. Daily Dose Of Excel is the Cheers of Excel blogs, and Dick is Sam “Mayday” Malone.

Contextures – This is Deb Dalgleish’s site. It is loaded with content, updated nearly every day. Deb also includes a weekly round up of other Excel related items she’s found on other sites throughout the week. Her content is categorized, with each category giving you examples and tips and tricks and sometimes even quirks to watch out for. To give you an example, I had a column in a sheet two days ago with broken data validation. You could type any old thing in the cells when you should have been limited to a specific list. I figured there had to be a way to fix it. I remembered Deb had a ton of content on data validation on her site, so I went digging. Sure enough, I found what I was looking for and fixed it. But if that’s not enough to convince you, let me add that Deb might have the largest collection of multimedia, primarily videos demonstrating how to implement some of the content she covers, of any Excel site I’ve ever seen.

Pearson Software Consulting – Chip Pearson’s site is a fantastic collection of explanations on all kinds on Excel and VBA topics. But what really makes the content pop is that Chip usually adds custom routines the make your programming easier. For example, you might need to do some work with Names in Excel. Chip walks through the aspects of to code with Names, and then puts the things he’s just discussed together into prepackaged functions that you can call to make your programming life easier. I’ve used his site as a reference point for years.

So that’s it. Those are my recommendations. Have fun.

Pictures? We Don’t Need No Stinkin Pictures!

I’ve seen a couple of articles recently about the next generation’a digital presence. Some are best practices, some are just downright alarmist. Take this article from Slate, which feels like it’s verging on paranoid. “Facial recognition”, “corporate data mining”, these things are described in an almost Orwellian manner.

Maybe I’m being naive about Facebook and how the pictures I upload will be used in the future. Perhaps the Peoples Republic of Facebook is only a few years away. But I doubt it. The fact is, I like sharing pictures of my kids on Facebook so my friends can see them. Posting pictures to Facebook has become the digital replacement for the proud father pulling out his wallet to show you his little girl in her ballet costume. I’ve been loading pictures onto Facebook for years. Yes, my girls will probably moan and groan when they are old enough that I can share photos with their boyfriends. Again we see the digital replacement for going through old photo albums.

However, I’m not so naive to think posting pictures here there and everywhere is a good or safe thing. Because it is not. I share my pictures on Facebook where my friends can see them, and on Instagram, because my account is private and you need my permission to see my content. Where do I not share my pictures? Twitter, Tumblr, and this blog.

I can’t control who views this blog. I could technically make my user account on Twitter private, but that kind of negates the fun of Twitter. But this blog is open for anyone to read. And, like Twitter, that’s the fun of blogging. Creating an online space where you can record your thoughts, feelings, activities, likes and dislikes, all free and open for the entire online world to view.

With my content available to all the world, why would I want to post pictures of my kids?

Cause let’s face it: there are whackos out there. The odds of one lone whacko stumbling across pictures of my kids and seeking them out are probably the equivalent of being struck by lightning while being eaten by a shark all the while holding the winning Powerball ticket. And this scenario (the whacko one, not the lightning/shark/Powerball one) is definitely worst case scenario kind of stuff. But it still gives me a measure of comfort knowing I’m keeping people I don’t know from viewing my kids. I have friends who post pictures of their kids on Twitter and I cringe every time. But that’s my gut reaction. Yours might be different.

Nobody really knows what’s going to happen when the current youth generation comes of age in a socially media driven world. Facebook is only ten years old. What happens when my kids hit the age of consent for social media sites and sign up and see they’ve already been all over the site for ten or fifteen years? Nobody really knows. In the interim, I’ll keep sharing pictures with friends and keeping them away from strangers. My hope is that this will both keep them safe while I share their fun moments, and keep me from being a digitally helicoptering parent.

Excel Geeking: Finding Out If A Range Intersects With PivotCache Source Date

As I was developing a utility to help clean up text for a co-worker, I ran into a error I have been since unable to replicate. Basically I could not get the native Find/Replace function in Excel to actually find and replace items if the range being modified was part of the source data for a pivot table.

So I embarked on a quest to determine if a selected range intersects with a pivot table’s data source, which is otherwise know as a pivot cache.

This should have been a lot simpler than it was. The primary issue is that, while you can read the SourceData property for a pivot cache into a variable, the property returns a string. Which means parsing the string, breaking it into its two parts (the worksheet name and the range address), converting the range address from R1C1 to A1 (since the SourceData property spits out as a R1C1 format), then setting the range variables and seeing if they intersect. Feels like a lot of work just to see if two ranges interset, but there it is. If there’s a better way to do it, I’m all ears.

The final routine is below. It’s set up as a sub, but with a couple of easy modifications it could (and probably should) be converted to a function returning a boolean. But I’ll leave that for you to do. Why should I have all the fun?

Here it is:

Private Sub DoesPivotCacheIntersect()
‘ Description:  This iterates through all the pivot caches in a workbook
‘               and determines whether the selected range intersects with them.

‘ Variable declarations.
Dim wb As Workbook
Dim sh As Worksheet
Dim pvtCache As PivotCache
Dim rSelection As Range
Dim rPivotData As Range
Dim lCnt As Long
Dim sPvtCache As String
Dim sShName As String
Dim sRngName As String

‘ Start by setting one range variable to our selection.
Set rSelection = Selection
‘ Also set the wb to the active workbook on which we’re using the utility.
Set wb = ActiveWorkbook

‘ Iterate through all the pivot caches in the workbook.
For Each pvtCache In wb.PivotCaches

‘ Set the string variable to the SourceData property value.
sPvtCache = pvtCache.SourceData

‘ We need to parse the source data to separate the sheet name from
‘ the range name. We’ll parse backwards, since a sheet name can contain
‘ and exclamation point, and an R1C1 range string cannot.
For lCnt = Len(sPvtCache) To 1 Step -1
If Mid(sPvtCache, lCnt, 1) = “!” Then
sShName = Left(sPvtCache, lCnt – 1)
sRngName = Right(sPvtCache, Len(sPvtCache) – lCnt)
Exit For
End If
Next lCnt

‘ If the sheet name is bracketed by apostrophes,
‘ then we need to trim them or else we’ll get an error
‘ we we try to set the range variable.
If Left(sShName, 1) = “‘” And Right(sShName, 1) = “‘” Then
sShName = Left(sShName, Len(sShName) – 1)
sShName = Right(sShName, Len(sShName) – 1)
End If

‘ We need to convert the R1C1 range that we get from the SourceData property.
‘ To do this, we’ll use ConvertFormula.
‘ We need to add an equal sign to make ConvertFormula think we are dealing
‘ with an actual formula.
sRngName = Application.ConvertFormula(“=” & sRngName, xlR1C1, xlA1)
‘ Once we’ve converted the range string to A1, we remove the equal sign.
sRngName = Replace(sRngName, “=”, “”)

‘ Finally, after much ado, set the range variable
Set rPivotData = wb.Worksheets(sShName).Range(sRngName)

‘ Now let’s see if we have an intersection.
If Not Intersect(rSelection, rPivotData) Is Nothing Then
MsgBox “Your selection intersects with a pivot cache in the active workbook.”, vbOKOnly
End If

Next pvtCache

End Sub

Editing My Fiction (or, What I Should Be Doing Instead Of Watching TV)

I don’t like editing. Like, really don’t like it.


Editing to me has always be one of those things I put off and put off and put off (and put off) until I finally run out of excuses. Then I scrounge around under the couch, certain there must be some old, partially eaten, mold-covered excuses I can use until I can get to the store and buy some more.

I’m a world class procrastinator, and when it comes to things I don’t like, I never fail to put off for decades what I can do today. But that just doesn’t fly when it comes to publishing.

I’ve written three books. One I’ve edited to the point where I’m now publishing it on Amazon. The other two are in draft mode. The picture you see at the top of this post is the first two pages of the second book I wrote (and finished–given the number of abandoned writing projects on my computer, it feels important to add that the the first drafts were completed). It is still in first draft status and has been for about seven months or so. I’ve red-penned the first twenty-six pages out of two hundred plus pages. I’ve got a ways to go.

I started editing this second book, “Dirt”, about four weeks ago. It was immediately after I finished the first draft of my third book. I was on a roll and didn’t want the mojo to vanish, didn’t want to slow the momentum [Scott looks around his desk for another tired metaphor and, failing to find one, moves on]. I wanted to continue on and prove to myself that I could conquer the Editing Beast.



The Editing Beast was definitely not a windmill. It broke my lance, killed my horse, and ripped through my armor to tear out my still-beating determination with its fearsome jaws. This sumbitch tore me apart.

I have done just about everything to avoid editing. There has been food, and running, and reading. There has been TV. Justified is back on (woohoo!) as is The Walking Dead (meh). I have discovered Longmire, the whole first season of which I took in like a python unhinging its jaw to swallow a goat. In short, I’ve done everything I can to avoid “Dirt”. And here’s the secret as to why [leans in to whisper]: I don’t like “Dirt”.

It’s true. I shake my head in disbelief at my own feelings about my own work, but it’s true. I don’t like it. “Dirt” is the most Stephen King-like thing I’ve ever written, long or short. It just had that sort of vibe to it. And I dig Stephen King. One hundred years from now, when people ask which author is the most remembered author of the twentith century, it will be Stephen King, just like it is Charles Dicken for the nineteenth century. He’s not writing high fiction, some of his stuff is (by his own admission) a “clunker”. But he tells a helluva story. When I was commuting 120 miles roundtrip every day for work (barefoot, through the snow, without a coat) I would listen to King on audiobook. Since his one goal for so long was to scare the crap out of you, he was great to take on long car rides. Guaranteed to keep you awake. King was one of the first adult authors I remember reading as I grew older.

So it naturally follows that a novel I’ve written with a King-esque feel to it would completely jazz me, right? Wrong. “Dirt” started off as a short story that couldn’t find an ending. The cast of characters grew and grew. Then the primary antagonist introduced himself and I stepped back and asked the book “Really? Is this REALLY where we’re going?” The book nodded emphatically and then dug its heels in as I tried to drag it acros the finish line like a dog going to the vet. It was a rock fight to get this thing done.

Now I’m staring down the editing barrel and asking myself how much do I really want to tackle it. There is so much work to do. Like, a dump truck’s worth of manure to shovel through to find a lost engagement ring. And I’m asking myself, how badly do I want to work on it. Especially since I have another book, my third, that I really did enjoy writing and that I’m looking forward to editing. I think I started doing some editing on “Dirt” because I knew I’d have to abandon it in March when I get to work on scrubbing the hard-boiled detective book. So if I didn’t get around to doing anything on “Dirt”, well does anybody really care?

Unfortunately, yes, I do care. I’m not happy with it, but do care quite a bit. I want to see it finished, I want to see it to completion. I want to see it improved and published and enjoyed. So it might be time to stop scouring the house for excuses, putting it off, and get down to some serious work. Which I’ll do.

I hearby make a solemn vow to stop procastinating over editing “Dirt” and to begin the serious work on getting it into publishable shape.

Right after I edit the other book.

Review: “The Cold Dish” by Craig Johnson, and A&E’s “Longmire”

Today I’m feelin’ generous, so it’s a two-fer. Two reviews in one post. Lucky you! I recently read Craig Johnson’s debut mystery novel “The Cold Dish“, which introduced his character Walter Longmire to readers everywhere. After finishing the book, I powered through the first season of A&E’s original series “Longmire“, which is based on the characters in the Johnson novels.

cold dish

Let’s start with the book. “The Cold Dish” introduces us to Sheriff Walt Longmire, Sheriff of Absaroka County in Wymoing. He’s an aging sheriff, looking forward to a retirement that doesn’t appear too far off. He’s a widower, having lost his wife to cancer about four years back, and has, in many ways, lost his zest for life. Longmire is not the first hero of hard-boiled mysteries to be introduced to the reading public as a cynical, drinking, down-on-life-and-maybe-himself character. Nor will he, doubtlessly, be the last. But he has a voice all his own, part of which comes from the proximity to the sweeping landscapes of the American West. Longmire is a man who knows himself, knows his limits, and is cautious about pushing them.

But push them he does when the situation calls for it. And in “Cold Dish” the situation calls for it quickly. Walt is called to the scene of a crime where the body of a young man is found, shot through the back. The complication here that Walt must deal with is the fact that this deceased young man is one of four that were acquitted of raping a Native American girl with fetal alcohol syndrome a couple years back. It’s a perfect, though perhaps cliched set-up for Walt Longmire to show us what he’s got. Joining him on this venture is a cast of characters that are fairly well-drawn, even if one or two of them ar characitures. There is the female deputy Victoria “Vic” Moretti, a tough-as-nails, unwilling transplant cop from Philly; there is Henry Standing Bear, Walt’s long-time friend and connection to the Native American community on and off the reservation; there is Ruby, the dispatcher and Sheriff’s Office manager who keeps the office moving along while giving Walt stern matronly glares whenever appropriate.

I found “The Cold Dish” a fun read that kept me reading, which is saying a lot these days. At approximately 400 pages, it’s too long, and suffers from a certain laziness in the editing process. I’m used to the sharp crisp sentences of Robert B Parker, and if you’re looking for those here, you’ll be disappointed. There is also a section that starts off as spiritual and quickly borders on supernatural as Walt fights his way up a mountain through a blizzard. It reminded me of the Halloween episode of [insert cop show name here] where the good guys are plagues at every turn by events that seem other-worldly to them, but turn out to be completely plausiable.

But not to leave you thinking I didn’t like the book, because I did. The scenery is well-described so you get a strong sense of the Wyoming countryside. In addition, Johnson, a resident of Ucross, Wyoming, (pop. 25)  gives us insight into the relations between Native Americans (who are called and call themselves “Indians”) and the white population living outside of the “Rez”. These are insights that, coming from a different writer, would feel forced, but never once do they feel so with Johnson at the wheel.

I’m definitely looking forward to the next in the Walt Longmire series. So much so that I decided I had to check out the TV series “Longmire”.

And how is “Longmire”? So glad you asked.


As a cop show, it’s average. The plots are standard murder plots, with the usual number of twists and somersaults built in in order to keep you guessing until the end. One of these days, somebody is going to come up with a cop show where you know whodunit, and you (and the detectives) spend the rest of the episode trying to get enough evidence to convict. That’s not this show. There is also, not unlike a lot of shows these days, and over-arching plot that slowly threads its way through each episode and ties itself together in the season finale. The season ends on a sort-of cliffhanger. There’s clearly a lot more story to develop with the characters, but you’re not left wondering who shot JR.

But this is no different than any other cop show these days, which leaves the question, why watch it? The answer: Robert Taylor. Taylor is an Aussie actor who American audiences will only recognize as Agent Jones from “The Matrix”.


This role couldn’t be farther from that, and Taylor makes the character of Walt his own. His widowership is only a year (whereas in teh books it’s four years) which makes the pain much closer. He’s aided by Katee Sackhoff in the role of Vic, and she too makes the role her own. Described in the books as a handsome not pretty, square-jawed woman, Sackhoff’s features seem cut to order for the role. Add to that her ability to play hard-nosed, characters (see “Battlestar Galactica” for reference) and she is perfect in the role. Rounding out the cast is Lou Diamond Phillips in the role of Henry Standing Bear. Phillips is not the first person who comes to mind when you think of a bar-owning native American friend of Walt, especially since they are approximately the same age. The first person I thought of for the role was Wes Studi, an excellent Native American actor (see “Last of the Mohicans” for some amazing work). But Phillips, like Taylor and Sackhoff inhabits the role and you never once question whether he belongs there.

I really enjoyed the first season of “Longmire”, for as run-of-the-mill as the murder plots are, the characters are fun to watch and the scenery, well, it can’t be beat.

So there you have it. “The Cold Dish” by Craig Johnson, and “Longmire” the show inspired by Johnson’s characters. I’d recommend both.

Excel Geeking: Counting Instances Of Find/Replace With VBA

A coworker gets a workbook full of data that he needs for generating metrics every week. The problem is that this workbook is loaded with textual errors. There are leading and trailing spaces all over the place, there are misspellings of all sorts, there are multiple entries that all mean the same thing. He might see one record for “Business Analyst”, and another for “BA”. He might also see an entry for “Busniess Analyst”. The data in this workbook is clearly manually entered, and because my coworker doesn’t own it, he can’t control the cleanup of the data. He has to cleanse it every week in order to extract the metrics he needs.

This week he finally had had enough and asked my help developing a couple of routines to clean up this spreadsheet. The first was to trim all the extra spaces from the data. Easy-peasy. The next was part was to develop a method to find/replace a number of entries, which meant setting up a sort of batch-like utility where he could load up the things he wanted fixed and run them with the touch of a button. This was pretty straightforward, except I, of course, couldn’t stop at just a quick little utility. No. I had to build my own thing that could be used by anyone and had all the normal functionality in the native Find/Replace, except that it would run in a batch-like mode.

I’ll post more about this utility in another few posts, but today’s post was emulating the messaging that Find/Replace gives you when you use the native function. Specifically, I wanted to give the user a total count of the changes made after running the “batch”.

Here’s where I had to put on the ol’ thinking cap. When you use the Find/Replace function in Excel, you get a response that looks something like this:

find replace count

Okay, great. Exactly what I want to present. However, when you use the Range.Replace method in VBA, you don’t get a count of items replaced as the return for the method. The Range.Replace method returns a boolean. How frickin annoying is that?

So I started thinking what was the best way to capture the total number of items changed? The first one that springs to mind is the good old For/Next loop. Set a range variable, loop through all the cells in that range, check ’em out to see if their contents match your “find” string (which you have doubtlessly captured as part of an input box or something similar), and if the contents match, change them with the “replace” string and increment a counter variable.

Sure. That would work. But I don’t like to iterate through ranges if I don’t have to. Doing so in VBA tends to take a lot of processing time, especially the bigger your range. I really wanted to use the Range.Replace method if I could because it was designed to take on large chunks of ranges in a single statement.

So, how to do it?

I decided on another function that’s designed to take on large ranges in a single statement: COUNTIF. Using Application.WorksheetFunctions.CountIf, I was able to use the range I’m acting upon as the first parameter, and the “find” string as the second. Bracket the “find” string with a couple wildcard “*”s and pass the COUNTIF result to a long variable, and suddenly I had captured the number of changes I’d be making and actually making the changes, all in two lines of code.

Now, the code looks something like this:

Const sWILDCARD As String = "*"

Dim sFind As String
Dim rReplace As Range
Dim lCount As Long

sFind = Application.InputBox("Enter the string to be found:")

Set rReplace = Selection

lCount = Application.WorksheetFunction.CountIf(Rng, sWILDCARD & sFind & sWILDCARD)

'...more code here as needed

Two lines of code. It’s a beautiful thing.

More to come on this one as it continues development…

How To Spend A Snow Day

We’ve got another one on the way. Another winter storm that they’re saying could be a “major” storm. Fun fun. What will we do if we have another day where schools, businesses, towns, governments, and Dunkin Donuts are all closed? (Okay, let’s be honest for a moment: I live in New England. Dunkin Donuts is NEVER closed.)

In all likelihood we’ll do what we did last Wednesday. Last Wednesday we ended up with (you guessed it) another snow day, stuck inside a small house with each of every one of us eventually going a little bit stir-crazy. (I don’t mean to sound bitter about all the snow, I did voluntarily move north fifteen years ago after all. But c’mon, enough’s enough.)

So what did we do to pass the time last Wedneaday?

We cleaned.

(Doesn’t everyone do that on a snow day?)

We’re still digging out from under all of the Christmas and birthday boxes and paper and crap that’s exploded all over the place. It felt good to really dig into some dark corners, tear it all apart, and put it back together again in a neat and orderly fashion. Not to mention the throwing out of a ton of shit that we just don’t need or use. I think we ended up with five big black trash bags worth of stuff we junked.

But the pièce de résistance belongs to my wife, who took our arts & crafts closet for the kids from this:


To this:


It’s a thing of beauty.

PS – I could hear my wife even as I wrote this, in my head, going “My god, don’t show them what it looked like!” Yeah, it was a mess. But if you’ve been in my house, you’ve already seen it. The fact is, this is life, life with two kids in a small house, and you know what? It’s messy. That’s something I try to make myself at peace with every day. It’s not easy, and I fail at the “make peace” part about 50% of the time. But that’s life. It’s not all sitcom. And the fact is, getting this beast cleaned up was a HUGE victory in the house, a victory belonging to my wife. So, I post it.

Excel Geeking: How I (Now) Name Range Variables

This one is gonna ramble a bit since there is (in my head) a lot of exposition to get through. It will probably also be a relatively useless one to Excel programmers, since everybody has their own preferences for variable naming conventions. But it’s my blog so I get to write about what I want to write about. So there.

Plus, I’ve been programming Excel for about seven years and this one just occurred to me within the last month. I never claimed to be quick.

When naming variables, I tend to follow what you might call “Hungarian Notation Lite”. You can read all about Hungarian Notation at the all-important-and-doubtlessly-true-because-it’s-on-the-internet cache of information known as Wikipedia. If you have read the most excellent book by Bullen, Bovey, and Green called Professional Excel Development, you’ll find their examples all use Hungarian Notation. They even have a section in one of the early chapters dedicated to proper naming conventions for all aspects of your Excel programming.

The type of Hungarian Notation that they use is what the Wikipedia article refers to as “system” notation, where the prefix of the variable name is represents the physical data type of the variable. What does that mean? It means that if you have a string variable called Message, then the actual variable name is prefaced with nomenclature that represents the fact that Message is a string variable. In this case, Message becomes strMessage, with the “str” representing the fact that Message is a string variable. Why use this kind of naming convention? Because when you’re reading through code you can quickly determine the Message is a string variable without having to stop and go back to the declarations to make sure you’re remembering it properly.

I use this method, but I use what I consider to be a “lite” version of it. If I have a string variable, I don’t type “str” in front of the variable, I just type “s”. In my world, strMessage becomes sMessage. Why do I do this? Part of it is laziness I suppose, I find it a pain to type three letters when I find one will do. Another reason is that I find it more readable. For some reason my eyes can take in an “s” and translate that to “string” better than “str”. My eyes stumble over “str” (or “lng” or “bool” or “rng”, etc.) and slows up the rest of my reading of the code. Cause, you know, I’ve curled up with a good multi-page printout of VBA code before and read it to relax. (Haven’t you?)

Okay, so to get down to the purpose of why I started this long-winded blog post, it’s about the range variables. Which I start with “r” instead of “rng”. (Get over it.)

I, like a ton of other Excel programmers, iterate through ranges in my code on a fairly regular basis. If you do anything that’s table-driven, odds are you’re using a For/Next loop to cycle through the ranges and find the values you’re looking for. To do this, I use the Range collection. How that works is that you set a specific range variable to something, then use another range variable to cycle through the first one. It looks something like this:

Dim rng As Range
Dim rCell As Range

Set rng = Worksheet("Some Worksheet").Range("Some Range")

For Each rCell in rng
Do something here...
Next rCell

Essentially what I’ve done is set up a whole range of cells to be the first variable. This creates all these cells as a collection housed within that variable. Then I can use the second variable to loop through the whole collection and evaluate each member of that collection. I look at the first cell, see if it meets whatever criteria I have to take further action, then move on to the next cell using the Next rCell line.

Here’s where I’ve been slow.

I’ve always used the following to name my variables:

Dim rng As Range
Dim rCell As Range

For Each rCell in rng
Do something here...
Next rCell

Where it gets muddy is when you have to iterate through multiple ranges in nested For/Next loops. It ends up looking something like this:

Dim rng1 As Range
Dim rCell1 As Range
Dim rng2 As Range
Dim rCell2 As Range

For Each rCell1 in rng1
For Each rCell2 in rng2
Do something here...
Next rCell2
Next rCell1

Yeah. No. That’s flippin ugly.

Yet this is how I did this until about a month ago when it dawned on me: why not name my range variables in the same way other collection are named? For example, to iterate through all the worksheets in a workbook you would use something that looks like:

For Each Worksheet in Workbook.Worksheets.

Brilliant Holmes! Now, if I’m looping through a range, my variables will look something like this:

Dim rSettings As Range
Dim rSetting As Range

For Each rSetting in rSettings

Much cleaner, much easier to read, and you can quickly determine which set of ranges belong to each other.

Only took me seven years to figure it out. Like I said, I’m kinda slow sometimes.

Blog at

Up ↑