Nate Oliver's blog

Reversing Excel Cell Contents

Nate Oliver — Wed, 09 Sep 2009 22:43:00 GMT

This is a bit of a tricky subject, in that it depends what you mean by reversing. This particular entry will explore reversing blocks of a String, versus an exact reverse of everything.

So, here's an example, how do I get from this:

4189 > 3743 > 2040 > 949 > 195 > 5

To this:

5 > 195 > 949 > 2040 > 3743 > 4189

There's no way to do this natively, in Excel, that I can think of. At least, not without really working your tail off. So, we'll, naturally, roll our own.

We'll make this a two-step process, a UDF to accept the data, and a Sub Routine to sort it. That would appear as the following:

Public Function stringReverse(ByRef strIn As String) As String
Dim tmpArr() As String
tmpArr = Split(strIn)
Call Reverse(tmpArr)
stringReverse = Join(tmpArr, " ")
End Function

Public Sub Reverse(ByRef lParams() As String)
Dim i As Long, tmp As String
For i = 0 To UBound(lParams) / 2 - 1
    tmp = lParams(i)
    lParams(i) = lParams(UBound(lParams) - i)
    lParams(UBound(lParams) - i) = tmp
Next i
End Sub

A couple of things to note.

1) This requires a VBA environment greater than Excel 97, as it uses String Functions that were introduced in VB6.
2) This is a fairly efficient process, you might note that the processing Sub Routine reduces the iteration against the array by 2.

There's a few different ways to implement this. The first is with VBA, e.g.,

Sub foo()
MsgBox stringReverse("4189 > 3743 > 2040 > 949 > 195 > 5")
End Sub

And you might be wondering at this point, why not use VBA's native strReverse() function? E.g.,

Sub bar()
MsgBox strReverse("4189 > 3743 > 2040 > 949 > 195 > 5")
End Sub

Well, strReverse() reverses everything a little too well. Notice what happens with 4189, for example - it becomes 9184 - not quite what we're after.

The other manner in which to use this, which is why we created a UDF as the initial call, is as a Worksheet User Defined Function call, e..g,

=stringreverse(A1)

In addition to the text examples in this blog post, I'm attaching a working example. It includes VBA and is attached with no assurances or warranties - open it at your own risk. Otherwise, have a nice evening.

Monthly and Annual IRR Values

Nate Oliver — Mon, 07 Sep 2009 23:41:00 GMT

This entry is based on a thread I responded to, here:

http://www.mrexcel.com/forum/showthread.php?t=76494

Occasionally the question comes up as to how to derive Monthly Internal Rate of Return (IRR) values. The answer is, well, that would depend what format your data is in, in terms of timing.

If your Spreadsheet houses monthly data, IRR() will naturally do this. XIRR() will not, it wil annualize your data.

However, see the attached, it shows examples of flipping back and forth (with both), considering different data constructs. There's no VBA, this is a pure-play exercise in Worksheet Functions.

The 2nd example (Worksheet) is required to be array-entered as Transpose() won't fire, effectively, otherwise.

Also note that multiplying by 12 might serve as a decent gut-check, but won't be accurate, as you're looking at compounding growth, by month - a different mathematical paradigm, it's not linear growth.

Lastly, there's another important concept baked into the first Worksheet - tie your numbers out via different means, until you're really comfortable with various Excel functions and visualizing them.

Hope you had a good, long weekend (for those that had one)!

Back Solve Cash Flows to a Target IRR

Nate Oliver — Sun, 06 Sep 2009 17:54:00 GMT

Today's Blog entry comes from a real-World experience I once had, while purchasing a large asset on a deferred payment schedule. The asset's price, like many large assets, wasn't fixed, but negotiable. So, prior to heading into negotiations, our team wanted to understand what various cash flows would look like if we altered the payment schedule to match a targeted Internal Rate of Return (IRR).

In order to expedite this process, I used an Excel-native utility known as Goal Seek. In fact, in order to further expedite the process, I used Goal Seek in a VBA procedure. To find Goal Seek, pends on your Excel version. Pre Excel 2007, it can be found in the 'Tools' area. Post Excel 2007, you'll want to click into the 'Data' tab, and explore the 'What If Analysis' button.

Goal Seek, Solver's younger brother, is pretty slick. It allows you to change a cell's value based on a calculated value, elsewhere. There is a trick to implementing Goal Seek when you're dealing with small numbers, like percentages. You want to change your calculation options to enable iteration, as desribed here:

Office Online Link

Maximize the number of iterations, to 32767, as mentioned, here:

Office Online Link #2

And crank your max change way down, to a very small number. As the first link notes, this will slow Goal Seek down a little bit, but your results will be much better. I.e., there's not much point in getting the wrong result, faster.

I'm attaching a simplified example of my construct. It contains a simple VBA procedure, and it is provided with no assurances or warranties - if you chose to open it, you do so at your own risk. That VBA procedure is as follows:

Sub BackSolve()
Range("B6").GoalSeek _
Goal:=Range("B8").Value, ChangingCell:=Range("G2")
End Sub

This procedure allows us to bypass the Goal Seek UI dialog, and simply process our request. What does it do? It modifies the year 5 cash payment (outflow), in Range G2, by changing to our targeted IRR in Range B8, which is an input cell (yellow). You'll note as you run this that your calculated IRR, in Range B6, will update to match our request, due to our cash outflows adjusting. This is another good trick to modeling - tieing out your results.

While the example is a simplified one, it can be implemented in larger, more sophisticated models. And with that, Happy Labor Day Weekend!

Fantasy Football Draft Board Tracking

Nate Oliver — Mon, 31 Aug 2009 18:41:00 GMT

People use Excel for a lot of different things, accounting, engineering, statistical work, etc... Yesterday, a friend asked for help with formatting projected player stats to manage her fantasy draft. I'm attaching the file, there is no VBA.

This file isn't extremely refined, as I only spent a few minutes on it. But what it allows you to do is scratch players off the main board, and then quickly scan who remains, by position. And of course you can add Worksheets to compile your draft, add comments, etc.. The projections aren't mine, they come from:

http://www.fftoolbox.com/index.cfm

So, obviously feel free to disagree with them. Go into the Overall Worksheet, change the Drafted Column (I), there's a drop-down. Notice the formatting flow to other Worksheets.

You might notice my SumProduct() function appears to be working harder than it should. When I received the data, the trailing spaces in the players names on the main board did not always match the individual Worksheets, and it was easier for me to approach that problem as such versus cleansing the data.

So, perhaps this will help you manage your FF draft. And as huge football fan, enjoy the season! Oh, and go Vikings!!

Interesting question - How to learn Excel?

Nate Oliver — Sat, 18 Jul 2009 19:27:00 GMT

I received an email from a nice fellow, from a very large Minneapolis-based organization, yesterday, asking how a coworker could learn to use Excel.

This is a pretty good question, for mature individuals in the workforce, or potential hobbyists who might think they'll have a need for the software, at some point.

Here's my advice, based on my experience.

I would not start on your own - the realm of possibilities are too vast and while it's easy enough to say "read the manual", you won't know what you're looking for.

A primer is in order. I would scour local Community Colleges for primer courses, e.g., if you were in the Twin Cities, Normandale Community College offers a beginner Excel course, on the weekends, for $99:

http://normandale.augusoft.net/index.cfm?method=ClassInfo.ClassInformation&int_class_id=6235&int_category_id=5&int_sub_category_id=26&int_catalog_id=0#

The point in doing this is two-fold:

Friendly price-point
Establish a base understanding of Excel with hands-on assistance

I don't necessarily recommend that particular course or institution, as I haven't attended either, but it's an example of the concept. And they offer more advanced courses, too.

Once you have a good working base knowledge of Excel, and remember, we're learning to crawl before we walk, and walk before run, then I recommend to self-study.

Microsoft Excel MVP, Tom Urtis, has a write-up on this very subject, layering on additional Excel insight, in the following link:

http://www.atlaspm.com/faqxllearnmore.html#104

And I agree with this, as this is how I approach learning how to use Excel.

A few additional comments. You're not Neo, and this isn't 'The Matrix', you can't download an understanding of Excel directly into your brain. If you want to learn how to effectively use Excel, you've got to want it, and study it quite a bit, to the point where you feel your working knowledge is adequate.

I really believe that self-study and experimentation on Spreadsheets, that are mission-critical to you, are imperative for learning how to use Excel, versus contrived academic work. Have you ever been in a car that you're driving to a location you've never been, and you can recall how to get there, next time? But, while being a passenger, none of the voyage is retained?

In my experience, Excel is very much like this. It's easier to learn and retain if you're piloting, and what you're practicing is important to you.

With that, good hunting!

Excel on a Mac - My Experience

Nate Oliver — Sun, 05 Jul 2009 17:43:00 GMT

Okay, let's take Excel 2008 off the table, it ships VBA-less; this is a deal-breaker for me. Note, this is simply my thoughts based on my brief experience, i.e., if you enjoy using Excel on your Mac, more power to you. And this is my take on an out-of-the-box interface, itself, not attempting to set up Jet Connections, etc...

I conducted a poll at MrExcel.com, to see if people with PC vs. Mac Excel experience preferred one to the other. The turn-out was underwhelming, but the majority does appear to favor the PC, and can be found, here:

http://www.mrexcel.com/forum/showthread.php?t=353557

I had never worked with Excel on a Mac, until recently. I do pro bono consulting for the Women's Professional Billiards Association (WPBA), and my contact, on their Board of Directors, uses a Mac. Right, so I was off to North Carolina to provide training and some non-trivial project-work, on a Mac.

Nice enough looking machine; however, there were a couple of things that were driving me batty while trying to simply work with Excel on a Mac.

No right-click. I knew before I even jumped on this thing that it's not available, but that didn't stop me, it's engrained in my brain. Perhaps I didn't realize just how much I right-click? Quick sort? Quickly set some Column Widths? Paste Special? Forget about it, you're going to the menu. I can't recall why I was attempting to right-click so much, but I must have done it 10 times in an hour. This being the working definition of insanity - attempting the same thing over and over again while expecting different results.

There's probably a simple fix for this, a key combination perhaps, but we didn't know it: the F-keys don't work? I like my F-keys too, apparently. I know I tried to use F2 and F9 while auditing some Worksheet Functions, and nothing. The one F-key combination that did work was Alt-F11, more on this in a moment.

I found myself missing normal keyboard keys. There is no Delete key, there is, but it functions like a Backspace. The Mac's keyboard is missing other keys, too, like End? Someone help me out here (I only did this for 2 hours). It's missing a key that I use a lot for scrolling, must be End...

And for my favorite drain-bamager... Like I said, Alt-F11 works, nice. So I get back into the Project, add a Module, I am ready to rip. When I write a lot of code that interacts with complex Worksheet data I tend to jump back and forth between the front and back end of Excel quite a bit; here's where the fun really begins.

So I flip back to the Worksheet, look my data, flip back to the Project. The Project's still open, but the Module's gone. Where did that thing go? At this point, I feel like I'm watching Eddie Murphy's 'Delirious', where Eddie's old man calls the family dog, Cocoa, and that dog is outta there. Where the [bleep] is that dog going?! "The dog is stupid, Eddie!"

I'm messing around in the Project Explorer, I can't get this thing to maximize? Fine, I kill the VBE, and step into the procedure from the front-end. Again, more working definition of insanity, I repeat said view flip; it's gone again. My contact says, "I bet I know where it is." She cursors over to the left side of the screen, in the VBE, and this magical, vertical task bar appears from out of nowhere. If you cursor down to the very lower-left of the screen, at this point, there's your Module. I would have never found that thing!

Performance was fine, other than an issue with Variant Arrays, but the interface almost drove me insane - or, maybe it did. Not quite like a fish being out of water, but it was just different enough where every few minutes or so I found myself wondering "Wait a second, here. What?"

Granted, I'm very new to that interface, so I was probably guilty of some Mac-Newb gaffers.

Some Pretty Bizarre Excel Behavior (Mac-based)

Nate Oliver — Sun, 05 Jul 2009 00:32:00 GMT

Macs are the rage, right? Stable, intuitive, splashy looking? Unless you're me - I don't use them - perhaps a subject for another day. But, I do have friends who do, and while I give them an ear-load for doing so, so be it.

I recently received a fairly strange email from a friend of mine, using Excel, in an Office 2004 environment:

"I have an excel file open with multiple tabs, but all of the tab titles are blank with the exception of the one on the screen. It happened after I copied one of them, and now all excel files I open with multiple tabs have the same issue."

Ce qui? Having never seen anything quite like this, I figured, either someone had a long night out on the town, or something freakish happened. My advice? Reboot your machine and try that again.

No good - to my surprise, not only did the issue persist, I actually got a screen-shot of it, here:

I've blurred out the cells' text to protect the innocent, but I didn't play games with Worksheets 2-4 tab names. Notice how they have no names, what-so-ever? Another issue with this Workbook is that the cell-formatting isn't correct in the Worksheets, either.

This is strange stuff. I've heard the song about having dessert on a horse with no name, but snacking while staring at a Worksheet with no name? This is new to me and, apparently, for real.

Not having a Mac close-by, where I could even attempt to replicate this strange phenomenon, I decided to scour the WWW. Oddly enough, while limited, I did get some pings, e.g.,

http://forum.soft32.com/mac/thread-sheet-tabs-ftopict82279.html

"I now noticed that if the file is double clicked to start Excel the blank sheet tabs (not really blank, just white text) will appear as with all subsequent files opened."

And the big one:

http://forum.soft32.com/mac/Text-disappears-worksheet-tabs-ftopict82593.html

"I discovered that it was due to a conflich with Acrobat's PDF Maker plug-in which was in the Excel startup folder. Once I removed it, the problem disappeared."

Spelling and terminology aside, it turns out that the Acrobat Distiller Add-in was the issue, in this case. Once she unloaded that, everything works as expected. Two thoughts:

This only appears to be a problem with specific Excel files, although it can daisy-chain to other files in the same Excel instance
This isn't to say all Add-ins are bad news, but some might cause problems

Part of me wonders how this Add-in managed to create this scenario? It's not normal, to say the least - is it a neat trick? I've never seen this on a PC, so I assume most readers will never run into this, and your chances of seeing this are probably slim on a Mac, as well.

This is on my top-5 of weird, unexpected, things seen in Excel, before.

Happy 4th of July!

XL 2007 Pivot Table Book Giveaway (on Facebook)

Nate Oliver — Sat, 27 Jun 2009 21:01:00 GMT

If you're a member of Facebook (who isn't? - don't answer that), there's a free draw for a book on Excel 2007 Pivot Tables.

And here's where it gets good: it's not written by just anyone, but Debra Dalgleish, undisputed heavyweight champion of Pivot Tables.

Here's the offering:

http://www.facebook.com/home.php?ref=home#/event.php?eid=109974158504

As I mentioned in my free, online Excel resources blog entry:

http://msmvps.com/blogs/nateoliver/archive/2009/05/29/online-excel-resources.aspx

One can learn a lot about Pivot Tables by reading Debra's insights, e.g.,

http://contextures.com/xlfaqPivot.html

While I haven't had the chance to read this offering, yet, myself, I can only expect it to be much more comprehensive than her [great] website.

So, if you're a Facebook member, and are feeling lucky, sign up. This appears to end tomorrow, so don't hesitate.

Incidentally, Debra also does a great Jon Peltier impression.

Introduction to Excel - Top-5 Functions

Nate Oliver — Sat, 27 Jun 2009 00:03:00 GMT

Top-5 Primer Worksheet Functions

Excel’s a robust and dynamic piece of software offering a variety of capabilities. Today we will discuss the most common use of Excel, an analytical tool with given data sets and 5 must-know, general usage Worksheet Functions to begin to work with Excel. These 5 Worksheet Functions include Sum(), Count(), SumIf(), CountIf() and VLookup().

Sum()

Sum() allows one to add values in a variety of manners. The most common use would be to sum a given, contiguous range, e.g., =Sum(A1:D10), the result being every cell in A1 through D10 being added together.

Sum() also allows for arguments via the use of a comma, which allows for multiple, non-contiguous ranges to be added. Two differing examples would include both single-cell, non-contiguous ranges, e.g., =Sum(A1,D1,F1) and multi-cell, non-contiguous ranges, e.g., =Sum(A1:A5,C1:C5,E1:E5).

One thing to note with Sum() is that it does not ignore error values in your data, e.g., if you have 1,000 valid cells of data and a single cell in the calculation range contains an error (e.g., DIV/0 – division by 0), Sum() will not return the sum of the range, but the error, DIV/0. This behavior is by design, to alert you that there is a potential issue with your data. However, later we will explore a simple work-around (see SumIf()).

Count()

Count() allows one to gather a count numeric data in their spreadsheet. As with the previously discussed Sum() function, Count() can work with single ranges, non-contiguous ranges and multi-cell ranges - the latter with comma-separated arguments.

SumIf()

Sum() works great if you want to add everything in sight, but what if we want to add values based on a specific criteria?

Enter SumIf(), which will sum a given range of values, if a given criteria is met. In pseudo code, SumIf() reads as =SumIf(Range of Criteria Values, Your Criteria, Range to be Summed). The last argument, separated by commas, is optional – if omitted, the Range of Criteria Values is summed.

Note that SumIf() supports wildcard characters and mathematical operators, as mentioned here:

http://office.microsoft.com/en-us/excel/HP052092921033.aspx?pid=CH062528291033

As promised, a quick and risk-free work-around to summing ranges that house error values within its cells. It’s not recommended that Excel users allow error values to flow through their spreadsheets; however, it is imaginable that an individual has a pressing engagement, say a tee-time, and needs to provide a colleague a sum of data that houses errors, much sooner than later.

SumIf() allows one to do this by invoking a simple mathematical operator, which forces SumIf() to ignore all non-numeric data, of any sort, including error values. So, the following transaction is risk-free: =SUMIF(A1:A10,"<=9.99999999999999E+307")

The reason that this transaction is risk-free is that 9.99999999999999E+307 is the largest number allowable in Excel (see the help file on “Limitations”), so all functional numbers in Excel must either equal or be less than this number, and only numeric cell entries will be considered at this point.

Be careful with 9.99999999999999E+307, there’s a reason I’m burying it in quotes, Excel can do strange things with numbers, due to its Data Types – a subject for another day.

CountIf()

Like SumIf(), CountIf() provides us with a manner of conditional counting. The slight variance is that it takes two arguments, the range you want to evaluate and the criteria to evaluate that range by, e.g., =CountIf(Range you want to count, Criteria to be met).

CountIf() also supports wildcard characters and mathematical operators, as should be noted, here:

http://office.microsoft.com/en-us/excel/HP052090291033.aspx?pid=CH062528311033

VLookup()

VLookup() is an extremely useful worksheet function, in Excel, and chances are if you’re interviewing for a job that requires somewhat advanced Excel experience, you will be asked about it. While we won’t cover the several underlying algorithms, let’s have a look at really understanding the implementation.

Most of Excel’s worksheet functions are designed with names that are memorable, in an English-sense, and VLookup() is no exception, in layman’s terms, it is short for “Vertical Lookup”. This function performs a search on a column of values, a column being vertical, and if said value is found, offsets a number of specified columns, and returns a corresponding value.

Before using, I recommend reviewing the help file for this function, as well:

http://office.microsoft.com/en-us/excel/HP052093351033.aspx?pid=CH062528281033

So what does this mean? First of all, pretend you don’t know the last argument, for now, let’s stick with False. Here in pseudo code is what this means:

=VLOOKUP(Value to Find, Range of Values – Including the Offset Range, Number of Columns to Offset from first Column, False - for now)

The left-most column of the 2^nd argument refers to the column you’re actually searching, and the right-most isn’t what this is limited to, but is generally recommended to be - the farthest column you want to offset to, to return your value. So, if you want to return a value from column B, that is dependant on a match from column A, the example could look something like this:

=VLOOKUP("August",A1:B12,2,FALSE)

Where 2 indicates to return a value in the 2^nd column of the specified Range of values.

Returning to the 4^th argument of VLookup() and the True argument. This can be more efficient due to the manner in which the underlying algorithm processes. But, as noted in the help file, your first column has to be sorted in ascending order for it to be trustworthy, due to a difference in the way it searches.

Conclusion

There are quite a few of differing, very useful worksheet functions in Excel, and most of them are well documented. This is a quick-hit list of some that might prove to be more frequently used and useful for general purposes.

Off-topic: CCleaner

Nate Oliver — Tue, 16 Jun 2009 15:11:00 GMT

Chances are pretty good that if you're reading this blog, you use your computer quite a bit. Today I wanted to mention a nice freeware utility to help you manage your resources, CCleaner:

http://www.ccleaner.com

What is CCleaner? In their words:

"CCleaner is a freeware system optimization, privacy and cleaning tool. It removes unused files from your system - allowing Windows to run faster and freeing up valuable hard disk space. It also cleans traces of your online activities such as your Internet history. Additionally it contains a fully featured registry cleaner. But the best part is that it's fast (normally taking less than a second to run) and contains NO Spyware or Adware! :)"

That's a mouthful! Basically, it's free software that removes junk that accumulates in your file space and registry. If you run CCleaner, you might be surprised how much of aforementioned junk accumulates. I ran CCleaner last Sunday and it removed 700 MBs of temporary junk.

The registry cleaner is great, too. It's not recommended that you play games with your registry without a utility like this, unless you have a very good handle on what you're doing - a false step in the registry could mean an OS reinstall. And some of the junk that accumulates in your registry is non-trivial. E.g., while upgrading Ad-Aware, the upgrade process left an old Ad=Aware registry entry in place, not only being unnecessary, but actually causing Ad-Aware to simply not work. Clean up the registry, and you're in like Flynn.

There's two other nice utilities, as well, an Application uninstaller, and a memory manager which allows you to control what on your system is loaded at start up. The latter's nice, e.g., I want Windows Live on my PC, as an example, but I don't want it to launch on every start up. A lot of programs you want and install have a nasty habbit of doing this for you - problem solved.

The GUI is intuitive and the program itself is small and has a light footprint in memory. Others seem to agree that this is nice software:

http://www.ccleaner.com/reviews

So, if interested, you can download, here:

http://www.ccleaner.com/download

Personally, I can't recall life before using CCleaner.

The CAGR - Installment 2

Nate Oliver — Tue, 09 Jun 2009 00:24:00 GMT

Now, it's go-time. We had some fun considering the differences between various growth models, we know how to handle the compounding one, and why it's of interest, i.e.,

http://msmvps.com/blogs/nateoliver/archive/2009/06/06/the-cagr-installment-1.aspx

So, let's handle it!

I wish I was smart enough to have conceptualized this, but it comes from a question, from the University of Florida - I'm a Big 10 guy. And you can tell I'm not enthused, at first:

http://www.mrexcel.com/forum/showpost.php?p=1583022

"You're talking compounding growth from 1985-1990? And you want to do that scenario for each state for every possible combination of years?

That would require a ton of iterations on each state. It's doable, and my CAGR example is how you do it, but I didn't set it up to easily do that. You'd need a pretty big table for each state (unless you did this with VBA) to accomplish this. I suppose I could do a UDF..."

She wants every single possibility where the CAGR, in any given year, exceeds her threshold. She's not looking at preferred dividends, she's looking at real, real estate price indexes. Real as in not nominal - inflation adjusted.

It's a cool problem, from an analytical standpoint - it won't brew your morning coffee, but it will flag periods that might be worth reviewing and attempting to figure out why they grew at such strong rates.

In order to solve this problem I took a night off. Sounds odd, right? I often don't write my best code while looking at my computer. Here's what I came up with:

Public Function CAGRThresh( _
    ByRef rngYears As Range, _
    ByRef rngPrincipal As Range, _
    ByVal curThresh As Currency) As String
Dim varYears() As Variant, varPrincipal() As Variant
Dim strRet() As String
Dim i As Long, j As Long, lngCount As Long
Dim lngUpper As Long
Dim curCAGR As Currency
Let varYears = rngYears.Value
Let varPrincipal = rngPrincipal.Value
Let lngUpper = UBound(varPrincipal, 2)
ReDim strRet(1 To (lngUpper ^ 2 * 0.5 + lngUpper * -0.5))
For i = LBound(varPrincipal, 2) To lngUpper - 1
    For j = i + 1 To lngUpper
        Let curCAGR = ((varPrincipal(1, j) / _
            varPrincipal(1, i)) ^ (1 / (j - i))) - 1
        If curCAGR >= curThresh Then
            Let strRet(lngCount + 1) = varYears(1, i) & _
                "-" & varYears(1, j) & ": " & _
                Format$(curCAGR, "0.00%")
            Let lngCount = lngCount + 1
        End If
    Next j
Next i
If lngCount > 0 Then
    ReDim Preserve strRet(1 To lngCount)
    Let CAGRThresh = Join$(strRet, ", ")
    Else: Let CAGRThresh = "N/A"
End If
End Function

There's a few things to note in all of this. As we've been discussing, this is a buffered Array:

ReDim strRet(1 To (lngUpper ^ 2 * 0.5 + lngUpper * -0.5))

How did I come to this conclusion? I used multiple-regression - we'll talk about this, soon.

Your data needs to be cross-tabbed, or pivoted, whichever terminology you prefer. Your data can be anything given a compounding growth model, as long as you have items to the left and time periods across the top.

I'll upload an example, but note, it has two VBA UDFs, two Subroutines and an ActiveX control embedded in it. If you or your organization aren't comfortable with this, I advise you to ignore said attachment - I provide it with no assurances, of any sort.

The first Worksheet in question speaks to the CAGR example. The second speaks to arithmetic growth, and why it doesn't work.

The third worksheet speaks to why I picked such a wild return format, a String? You can quickly parse all of this with Text-to-Columns, given a careful construct.

And, of course, that approach is limited in context, too. E.g., you could also stack an Array or disconnected Recordset.

The CAGR - Installment 1

Nate Oliver — Sat, 06 Jun 2009 19:19:00 GMT

I love saying "the CAGR!" It reminds me of the movie 'Highlander', and the Kurgan. There can be only one! I use a full-on Scottish accent when I say it, too.

So, what is the CAGR? It's a financial acronym, which is Compounding Annual Growth Rate. And it's widely used, as the Motley Fool mentions here:

http://www.fool.com/workshop/2000/workshop000302.htm

The term's a little misleading, as it implies the periods have to be annual, and they don't. The CAGR is to Financial Analysts what would be otherwise known as Geometric or Exponential Growth:

http://en.wikipedia.org/wiki/Exponential_growth

This phenomenon occurs in many different walks of life, investment values hopefully grow at an exponential rate (the S&P 500 tends to), biological cell growth could show this, rabbit population tends to follow this pattern, etc... The other day, while discussing String Concatenation and performance degradation, we were looking at geometric decay.

Why is the CAGR important? It shows trends. So, for an example, assuming resources are scarce, and you as a business planner have various choices as to where to invest, how do you choose? The goal is to maximize, and your best bet could be the highest area of compounding growth - noting history is not necessarily an indicator of the future.

Right, so how do we calculate the CAGR in Excel? There is no native way in Excel, that I know of, to calculate a CAGR in general, but a little arithmetic gets us there. E.g.,

=((B9/B2)^(1/7))-1

Or, in pseudo-speak:

=((Last Value/First Value)^(1/Number of periods less 1))-1

Make sure your time-periods are equally spaced with this logic.

I'm also uploading an example Excel File (w/ no VBA) - a fairly contrived one. With that, my next entry will explore a more extreme CAGR implementation. If you're an analyst, looking at compounding growth patterns, you might enjoy this.

Cropping an .MP3 file - with Excel

Nate Oliver — Thu, 04 Jun 2009 02:21:00 GMT

This isn't perfect, and it isn't high-end A/V - but it's a potential approach to snagging a segment of a song. Perhaps a party trick, literally.

So, in the following we use Binary File Access and try to figure out where we are in terms of Bytes and Seconds within an existing song file and create a new file with the cropped song of our specifications:

Sub CropMp3s()

Dim b() As Byte, i As Long
Dim FileNum As Long
Dim lngFileLength As Long

'File length variables
Dim lngFileSeconds As Long
Dim lngStart As Long
Dim lngFinish As Long
Dim lngTimeDiff As Long

'\\\\File Input\Output
Const strFile As String = "c:\SongIn.mp3"
Const strOutput As String = "c:\SongIn_cropped.mp3"
'\\\\\

'\\\\Song Constants
Const lngStartSeconds As Long = 20 'Starting seconds into song
Const lngStartMinutes As Long = 4 'Starting minutes into song

Const lngEndSeconds As Long = 21 'Ending seconds into song
Const lngEndMinutes As Long = 5 'Ending minutes into song

Const lngSongSeconds As Long = 9 'Song's entire ending ending length in seconds
Const lngSongMinutes As Long = 7 'Song's entire ending ending length in minutes
'\\\\

Let lngFileLength = FileLen(strFile)
Let lngFileSeconds = (TimeSerial(0, lngSongMinutes, lngSongSeconds) * 86400)
Let lngStart = (TimeSerial(0, lngStartMinutes, lngStartSeconds) * 86400) * _
lngFileLength / lngFileSeconds
Let lngFinish = (TimeSerial(0, lngEndMinutes, lngEndSeconds) * 86400) * _
lngFileLength / lngFileSeconds
Let lngTimeDiff = lngFinish - lngStart

ReDim b(1 To lngTimeDiff)

Let FileNum = FreeFile
Open strFile For Binary As #FileNum
For i = LBound(b) To UBound(b)
Get FileNum, i + lngStart, b(i)
Next
Close FileNum

Let FileNum = FreeFile
Open strOutput For Binary As #FileNum
Put FileNum, , UBound(b)
Put FileNum, , b
Close FileNum

End Sub

I've set up a few constants that need to be changed, first the file names. Then, the starting point you want, separately for both seconds and minutes, the ending point you want, again, separately for both seconds and minutes, and the the total length of the song, in seconds and minutes. I did not include hours into this, figuring that might be extreme...

A few questions you might have. One, why would anyone do this?

For the sake of doing it
While there's plenty of Freeware .exe's out there to do this, I'm not crazy about downloading random .exe files - hard to say what's in them

Two, why doesn't it work consistently?

I haven't accounted for a few potential Bytes on the front or tail-end of the file structure, which appear in the form of ID Tags, I'm not intimate with the .MP3 file structure. Otherwise, the ratios should work, in terms of kbps, and consequent file-size. It gets you pretty close, it's a matter of tinkering...

As I said, not really close to perfect, but a potential approach/start. If you're interesting in Binary File Access, or File I/O in general, see the following:

http://msdn.microsoft.com/en-us/library/aa716281.aspx

Sequential and Random File Access are worth reading up on, too.

Be Mindful of your Data Types - and TypeName()

Nate Oliver — Tue, 02 Jun 2009 21:37:00 GMT

As I was writing today's blog entry, I realized I needed to bake in a little arithmetic. I could have done this in the front-end of Excel, but this is a VBA sample, and we're in RAD-mode. So, I, without much thought, tried the following:

Sub foo1()
Debug.Print (24 * 3600)
End Sub

Whoops - overflow error. Serves me right, eh? I should be doing this sort of thing in my head.

VBA is treating these numbers as Integer Data Types and the result, which wants to also be an Integer, is out of the allowable range of this Data Type (in VBA/VB6). The range of numbers being documented in the Help File.

How do we deal with this? There are two workarounds, both of which have come up recently. The first is to use a Type-Declaration Character as explained, here, to make sure we're working with Long Values:

http://msdn.microsoft.com/en-us/library/s9cz43ek.aspx

And this works well, e.g.,

Sub foo2()
Debug.Print (24& * 3600&)
End Sub

Actually, you can make sure the first value in question, only, is a Long, and you're fine from there, e.g.,

Sub foo3()
Debug.Print (24& * 3600)
End Sub

The second approach is to use VBA's explicit Type Conversion functions, that are mentioned at the end of the following article:

http://msdn.microsoft.com/en-us/library/aa716185.aspx

And an example would be as such:

Sub foo4()
Debug.Print (CLng(24) * 3600)
Debug.Print TypeName((CLng(24) * 3600))
End Sub

As you can see, I've introduced something different here - a call to TypeName(). TypeName(), is described here:

http://msdn.microsoft.com/en-us/library/aa263394.aspx

TypeName() can be a very useful function when in doubt as to what Data Type, and limitations, you're working with. You can end up with a lot of funky Types in VBA, whether it's due to you performing implicit Type conversion or under-the-hood workings that you didn't intend - something to keep in mind. I know I've made some surprising calls to TypeName(), in the past...

And, as a last example, we can see that the Type-Declaration character does indeed change our perspective on the matters at hand:

Sub foo5()
Debug.Print TypeName(24)
Debug.Print TypeName(24&)
End Sub

This behavior's a little quirky, seeing as it's my understanding that 32-bit VBA wants a Long-integer vs. an Integer-integer, either way. But it's what we have, so we'll work with it. And, today's entry will be deferred until tomorrow - until then.

Excel 2007 VBA - Watch your order of operations

Nate Oliver — Mon, 01 Jun 2009 00:39:00 GMT

This seems like pretty straight-forward advice, right? E.g., make sure the light is green before you enter the intersection. Generally, follow the standard arithmetic order of operations, unless you're using Excel, which has its own order of operations (documented in the help file). Structure your words in a manner that follow typical English rules, should you be speaking English, etc...

Unless you're Yoda, somehow he can violate all of this, and it seems to work... But, the force is really strong in that one...

Back to topic, there can be issues with Excel 2007 VBA, if you violate its expected order of operations, in terms of unexpected behavior.

Take the following example, which I pretty much recorded, in Excel 2002 (XP). The goal is to create a red, transparent (i.e., the cells contents are showing) oval around D2:F2:

Sub foo()
Dim shp As Shape
Application.ScreenUpdating = False
With Worksheets(1).Range("D2")
    Set shp = .Parent.Shapes.AddShape(msoShapeOval, _
        .Left, .Top - .RowHeight / 4, _
        .Width * 3, .RowHeight * 1.5)
End With
With shp
    With .Fill
        .Visible = msoFalse
        .Transparency = 0#
    End With
    With .Line
        .Weight = 1.25
        .ForeColor.SchemeColor = 10
        .BackColor.RGB = RGB(255, 255, 255)
    End With
End With
Application.ScreenUpdating = True
Set shp = Nothing
End Sub

This works as expected in Excel 2002, it's basically the syntax the Excel 2002 Macro recorder gives you, in that order. I admit, I tweaked the recorded Macro a little, but the order in which the Property toggles occur in is essentially what the recorder gives you, or me...

Try that in Excel 2007. You find yourself with a red-outlined oval in the expected range, except, it's not transparent; it's filled and you can't see the underlying cell contents - which was a significant part of the point, in this case.

After staring at this thing for a while and not quite following the problem (to put it mildly), I asked for an assist. I got a good one, J-Walk followed up and mentioned that my Transparency Property toggle wasn't necessary and turned out to be the problem. He's right, the following works fine in Excel 2007:

Sub foo()
Dim shp As Shape
Application.ScreenUpdating = False
With Worksheets(1).Range("D2")
    Set shp = .Parent.Shapes.AddShape(msoShapeOval, _
        .Left, .Top - .RowHeight / 4, _
        .Width * 3, .RowHeight * 1.5)
End With
With shp
    With .Fill
        .Visible = msoFalse
        '.Transparency = 0#
    End With
    With .Line
        .Weight = 1.25
        .ForeColor.SchemeColor = 10
        .BackColor.RGB = RGB(255, 255, 255)
    End With
End With
Application.ScreenUpdating = True
Set shp = Nothing
End Sub

Note the commented-out line of code - the Transparency Property. However, this is kind of strange, no? That should be a non-op. After playing with it, it appears to be an order of operations issue.

Why wouldn't Fill be able to keep in mind that it has several Properties, like Transparency and Visible, and remember them, irrespective of which order they were set to? The answer to that question? It should - but it doesn't. While the Transparency Property isn't really necessary, here, it can have the non-effect we want it to, if we change the order of operations, i.e., change:

With .Fill
.Visible = msoFalse
.Transparency = 0#
End With

To:

With .Fill
.Transparency = 0#
.Visible = msoFalse
End With

So even though toggling the Transparency Property might not make a lot of sense, here, it turns out to only be an issue if used later vs. sooner - it appears to cancel out the the Visible Property when toggled after. With that in mind, the following works as expected in Excel 2007:

Sub foo()
Dim shp As Shape
Application.ScreenUpdating = False
With Worksheets(1).Range("D2")
    Set shp = .Parent.Shapes.AddShape(msoShapeOval, _
        .Left, .Top - .RowHeight / 4, _
        .Width * 3, .RowHeight * 1.5)
End With
With shp
    With .Fill
        .Transparency = 0#
        .Visible = msoFalse
    End With
    With .Line
        .Weight = 1.25
        .ForeColor.SchemeColor = 10
        .BackColor.RGB = RGB(255, 255, 255)
    End With
End With
Application.ScreenUpdating = True
Set shp = Nothing
End Sub

Which does include the Transparency Property toggle. In my opinion, this is a bug, the order of these particular operations probably shouldn't matter, never have, but they do.

Either way, that's not really the point of the examples. The point is when you review code that doesn't work as expected between one version of Excel to the next, there's two things to consider:

1) Test, test, test
2) Consider the order of operations you're proceeding with

Well, there's quite a few more things to consider than this, but these are two, amongst many.

Excel Q&A and my Inbox

Nate Oliver — Sat, 30 May 2009 18:25:00 GMT

This might surprise some, but to be honest, I really don't mind people contacting me via email, via this blog. The volume of email I'm likely to receive from this blog pales in comparison to the outrageous amount of inbox activity that Facebook generates... And if you want to say hello, well, I like friendly people.

However, it's not recommended that you send me random Excel questions in an offline format such as this for a few reasons:

1) That's not really the intent of this blog, nor my existence.
2) I'm not always very good about responding to these, for a variety of reasons, e.g., I sleep, sometimes. Maybe I don't know the answer? Well, that's not likely. ;)

What is recommended is that you find an Excel Q&A forum, preferably, for your sake, a free one, and post your question there. I personally recommend the following Excel Q&A forum:

http://www.mrexcel.com/forum/index.php

This particular Q&A forum is free and anonymous, should you choose a random handle. It is specifically designed to serve the very purpose of why you would be contacting me, in this case. It is the point of its existence.

There's a few benefits to approaching your quandary in this manner. These forums function 24/7, there are always experts online and you're likely to get a much, much (with emphasis on much) faster response than hitting my inbox. Who knows what I'm preoccupied with, going Byte Array to Byte Array?

This isn't to say there aren't other high-quality, free Excel Q&A forums, there are. This is just a recommendation.

However, do feel free to say hello, if so inclined.

Online Excel Resources

Nate Oliver — Fri, 29 May 2009 20:26:00 GMT

There was a recent blog entry that listed the top-10 online Excel resources:

http://www.odesk.com/blog/2009/05/excel/

I don't necessarily agree with, nor endorse, this list or the order, etc... But I would like to take a moment to speak to several items that are on the list, and some that aren't on the list.

When you make a list like this, given the vast amount of high-quality Excel resources on the Internet, you're going to get it a little right, and probably a little wrong, as you can see by this thread:

http://www.mrexcel.com/forum/showthread.php?t=388373

Such a list doesn't go without controversy. In this particular blog entry, I'll attempt to avoid dogging anything on said list, and look to augment its contents - it's Friday - I'm in a good mood.

Let's start with MrExcel.com, which is owned and maintained by fellow Microsoft Excel MVP, Bill Jelen. I need to disclaim this a little bit, I've been active there since 2000ish, and am an Administrator for their forums - I'm what sports fans would call a "homer". However, there are some interesting aspects to this site that I would like to highlight.

The first are the 500+, free, Excel articles:

http://www.mrexcel.com/articles.shtml

If you're into podcasts, Bill creates these, too:

http://www.mrexcel.com/podcast.shtml

The third are the web-based forums:

http://www.mrexcel.com/forum/index.php

These are great forums, some of the best Excel-minds on Earth stop by to take part in Q&A, for free. One particular sub-forum, within the forums, I'd like to highlight is the "Hall of Fame" forum:

http://www.mrexcel.com/forum/forumdisplay.php?f=17

As you can see, it's fairly lean: There's only 12 subjects taken from ~2,000,000 forum posts. We take this area of the forums fairly seriously, only really dynamite posts need apply for this sub-forum.

Experts-Exchange is a great site, I participate there from time-to-time, and there are a lot experts there, as implied. This is a little misleading, however:

"Suck it up and register for the site, answer a few questions for people that are more of a novice than you, and save yourself the monthly fee."

It's not just a matter of providing the answer, the Original Poster of said question needs to consider your answer the best, and there could be many. And, of course, that's out of your hands.

Office Online and MSDN can be great resources - if you want to learn how to use Excel from a novice to expert level, these are sites you should be reading. I find the following quote to be interesting:

"Yes, that’s right…Microsoft actually provides some things for free."

Microsoft provides a ton of content, for free, and they actually continually look to improve both their content and delivery mechanisms - it's a serious operation. How do I know this? I volunteered to assist with the Excel Content Partnership Council, which is, for the most part (myself not included), an internal group at Microsoft that's committed to driving great Excel content.

As a Microsoft [Excel] MVP, I have a non-disclosure agreement with Microsoft and can't disclose the details of my involvement - in a nutshell, they put me under flood-lights and beat me up on their content. Just kidding, but Microsoft really does care about their content and is committed to helping their customers with their products. I was told my involvement was helpful, so if you notice their content improving with time - you're welcome. ;)

Jon Peltier's site is mind-boggling excellent when it comes to Excel Charts. I recall spending a few hours creating a fairly complex chart, and when done, I thought "I bet Jon Peltier would be impressed". Then I went to his site and noted he had already fully documented what I had done. I had bet incorrectly - whoops.

Also, the following quote is technically incorrect:

"Currently, there are only 90 people ever granted the title of Excel MVP by Microsoft, so you can see how important of a designation it is."

Currently there are just over 100 Microsoft Excel MVPs, spread throughout the world. The word 'ever' isn't quite right, either, some of us are newer, and there have been several retired Excel MVPs. Still, a small number - I'm currently the only Microsoft Excel MVP residing in the state of Minnesota.

The Excel Product Team blog is a great resource, and something to keep your eye on if you're really interested in Excel. For a fairly obvious reason, you're reading content directly from the people who make Excel, well, Excel.

Good. Now let's talk about some resources that aren't included on the aforementioned list, and some examples of why I like them.

Chip Pearson's site:

http://www.cpearson.com/Excel/MainPage.aspx

Chip, a fellow Excel MVP, really knows his stuff, and his content is outstanding. E.g., if someone asks me about running a scheduled/timed procedure, I send them directly to Chip's site (as a start), to review the OnTime Method:

http://www.cpearson.com/excel/OnTime.aspx

It's such a well written article on the subject, it doesn't make sense for me to reinvent that particular wheel.

Mr. Spreadsheet, John Walkenbach's (J-Walk), site:

http://spreadsheetpage.com/

John's a fellow Excel MVP, and again, the content is outstanding and the variety ranges, quite a bit.

I find Debra Dalgleish's (Excel MVP) site to be excellent as well:

http://contextures.com/index.html

Especially when it comes to Pivot Tables:

http://contextures.com/xlfaqPivot.html

The last resource I will speak to are the Newsgroups, via Google. Some of the content that is available there, and highly searchable, is outstanding. E.g., take Laurent Longre's post on the Evaluate Method:

http://groups.google.com/group/microsoft.public.excel.programming/msg/a6b26610d3b8abc3

Now, at this point in life, for me, this is old-hat. But when I first read that, I thought "whoa". We'll be revisiting the Evaluate Method, by the way. In any event, there's a ton of great content in the Newsgroups.

I've certainly left off a number of other great resources, e.g., UtterAccess.com, XtremeVBTalk.com, Daily Dose of Excel, etc... It's a little difficult to speak to every single great, free resource out there. But, there are great Excel resources out there on the WWW, so if you're interested in learning about Excel, get out there and explore!

Excel Expertise - You be the Judge

Nate Oliver — Thu, 28 May 2009 23:40:00 GMT

Today we'll take a different direction: How well does the person you're talking to actually know Excel?

To be honest, I've never put together my own scale - I simply talk to people and come up with some sort of subjective opinion. But others have, including Aaron Blood, as noted here:

http://www.xl-logic.com/modules.php?name=Content&pa=showpage&pid=1

I really like his list. Should you be in HR and interviewing, or otherwise, the following is absolutely true:

"I do find it somewhat amusing that most intermediate level users are VERY over-confident in their Excel ability. My experience has been that if I ask an intermediate user to rank their Excel skill level on a scale of 1 to 10 the response is typically between 8 and 10 and they often list themselves as an Excel Expert on their resumes."

The reason I know this is true? Allow me to make an example of myself: I actually would have listed myself as an expert in 1998 - and, today, I know about 1/0 what I knew about Excel back then. I hate to explain my own jokes, but that's infinity. Rise over run sort of thing...

As always, I don't fully agree with everything that's being presented to us, e.g.,

"Expert Functions:
A true expert knows the strengths & weaknesses of every single one and has probably had to build some new ones that were not available."

I might be taking this slightly out of context, in terms of specialized functions... But...

That's not exactly true, nor necessary, in my opinion, to be an expert. Excel houses a lot of native functions - it's really mature software. To know every single one of them, inside and out, would actually be kind of insane. E.g., if you don't have a degree in Finance, and aren't working on a Finance-type projects, but still want to work with Excel at a sophisticated level, there really might not be a need for you understand IRR(), or XIRR(), or MIRR().

And I guarantee, as a Finance Major, studying this stuff by hand, you don't want to rebuild IRR(); an iterative, hit-or-miss algorithm.

But, overall the user-scale is pretty good, perhaps really good. In fact, if you are in HR (or serving in that capacity), you're reading this, and you're posting for job applicants with Excel experience; please, do not say anything along the lines of the following:

"Excel Expert: Must be an expert with VLOOKUPS".

That's embarrassing for everyone involved. If I were taking part in said interview, I'd enter it embarrassed. And then it might be your turn.

Would you like said applicant to provide the explanation of the possibilities regarding the sequential or binary search implications associated with the algorithm? If you want an explanation to the column-offset... that's fair enough. But an important note: it's not VLOOKUPS.

In any event, perhaps that helps show where the bar potentially lies.

Text to Columns doesn't suck

Nate Oliver — Wed, 27 May 2009 21:57:00 GMT

Picking right up where I left off yesterday, let's talk about some native functionality in Excel, an algorithm, that doesn't suck when it comes to String parsing - Text to Columns. If you recall from yesterday:

http://msmvps.com/blogs/nateoliver/archive/2009/05/26/recommended-free-reading-bruce-mckinney.aspx

One of Bruce McKinney's issues with VB's Split() function is that it can't handle multiple, consecutive delimiters. Text to Columns can, Bruce would be pleased.

It's a little unfair to compare Text to Columns to Split(), as Text to Columns works on Range Objects in a column, while Split() works on single Strings. However, it is my Blog and I'll take such artistic licenses from time-to-time.

Text to Columns is one of the most underrated, overlooked native Excel functionalities, in my opinion. Even just the other day I was involved in a thread where I recommended looking at it, and it's almost as if my post didn't exist?

http://www.mrexcel.com/forum/showthread.php?t=389241

Side-bar rant: This is why points-based forums don't always work. The OP, awarding the points/correct answer, is not 100% guaranteed to choose what is probably the best approach. I don't mean to pick on anyone, here, it's just a recent example of me noting Text to Columns is a good approach to the issue at hand.

So, here's a working example. Take the following:

Foo, Bar

We have two delimiters, a comma and a space. Copy and paste that in A1 through A100000 (or less if you're using Excel pre-2007). Now select Column A, in Excel 2007 select the Data Tab, or earlier, Data menu, click into Text to Columns.

You now have a dialog, let's go with Delimited -> Check Space and Comma as your Delimiters and check 'Treat consecutive delimiters as one'. Now click Finish. Wow, that was fast.

It's also pretty easy to record a Macro to replicate this - should you want automation, e.g.,

Sub foo()
Range("A:A").TextToColumns Destination:=Range("A1"), _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, Comma:=True, Space:=True, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1))
End Sub

Man, that's fast, let's revisit that. 100,000 parsed Strings, eh? How fast is that? Let's time it, we'll call foo() with the following timer-procedure:

Public Declare Function QueryPerformanceFrequency _
Lib "kernel32.dll" ( _
lpFrequency As Currency) As Long

Public Declare Function QueryPerformanceCounter _
Lib "kernel32.dll" ( _
lpPerformanceCount As Currency) As Long

Sub bar()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
Call foo
QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

It runs in roughly 1 second (on my machine) to parse 100,000 Strings (in Range Objects), with multiple, consecutive delimiters. I find that to be really impressive. I'm not sure who wrote this particular algorithm, but dollar earned that day.

There's a brief introduction to Text to Columns, but if you explore the dialog/GUI, you can see there's more options available to us than what I've spoken to, e.g., Fixed-length parsing, the ability to skip parsed columns of return values, marshaling your data to different locations, various formatting options, etc...

Text to Columns won't solve all of life's problems, but if you want to parse a column of data, it certainly is a good place to start.

Recommended [Free] Reading - Bruce McKinney

Nate Oliver — Tue, 26 May 2009 17:24:00 GMT

First off, who is Bruce McKinney? In his own words:

http://brucem.mystarband.net/author.htm

"Bruce McKinney was a factory assembly worker, a logger, a newspaper reporter, a PR flunkie, and an unpublished fiction writer before he discovered computers and lost all interest in the real world. During his years at Microsoft, he wrote programming manuals and online help for assembler, Basic, and C. Bruce also served for a time as czar of sample programs, writing or revising many of the samples known to longtime Microsoft language customers. He was a software developer on Microsoft C 7 and FORTRAN Power Station 1. He left Microsoft in 1997 and is now trying to decide what to do when he grows up."

So, we have a very insightful power user, from Microsoft itself. The main body of work in question is 'Hardcore Visual Basic', which is available online, for free, here:

http://vb.mvps.org/hardcore/

And Bruce provides some updates to this work, here:

http://brucem.mystarband.net/mckinney1.htm

While it's true that VB6 and the language as we know it, on that platform, is at the end of its life-cycle, it still lives on in a certain sense, in VBA. So, if you're interested in VBA outside of Office-specific Object Models, this body of work is still relevant and insightful.

And, even better, it makes for good reading. IMO, Bruce has a great sense of humor and can string together fairly clever rants and strongly worded opinions in addition to technical insight. This isn't to say I agree with everything Bruce has to say, e.g., Bruce's take on the Let Statement:

http://vb.mvps.org/hardcore/html/let.htm

"If you think about it, Let a = 1 is technically accurate. It also lets compilers optimize more efficiently. In other words, this pedantic nonsense makes perfect sense. Fortunately, Microsoft didn’t buy this argument, and neither should you."

He's correct, Let is optional. However, I lke this pedantic nonsense, and tend to use it where applicable in my VBA. It doesn't cost me many keystrokes and it flags intrinsic assignment. So, we agree to disagree.

Bruce also provides us with one of my favorite all-time rants: "Split Sucks":

http://brucem.mystarband.net/mckinney2b.htm#18

"Well, of course you can't do 1 because (unfortunately) users can turn off error trapping. And 2 just doesn't fully express your contempt for those who disregard your instructions. So 3 is the correct answer because it's so deliciously unpredictable.

Enough sarcasm. It's hard to fully express my contempt for the VBScript developer who wrote this crap or for the VB designers who accepted it into their library without checking to see that it meets minimum standards of competence."

I'm not sure if I should find that to be as funny as I do, but I do. Laughing matters aside, Bruce does have a good point. Split() is useful, I use it all the time, but as he's noting, it could, and perhaps should, be more useful and more robust along the lines of consecutive delimeters.

Which leads me directly into my next topic, on String parsing, natively, in Excel... Until then, thanks Bruce!