Just some semi-elegant stuff I've conjured up recently to get column names in Excel and do some other functional things.  I wrote this code in a true TDD fashion.  It still, for some reason, seems odd to me to write tests in VB, since it was always a language that encouraged dirtiness.  But alas, this is fairly solid stuff.

Imports NUnit.Framework

<TestFixture()> _
Public Class HelperTests
    <Test()> <Category("Presenter Helper Tests")> _
    Public Sub TestHelperGetExcelColumnLetterSingleLetter()
        Dim theLetter As String = Helper.GetExcelColumnLetter(3)
        Assert.AreEqual(theLetter, "C")
    End Sub

    <Test()> <Category("Presenter Helper Tests")> _
    Public Sub TestHelperGetExcelColumnLetterDoubleLetter()
        Dim theLetter As String = Helper.GetExcelColumnLetter(29)
        Assert.AreEqual(theLetter, "AC")
    End Sub

    <Test()> <Category("Presenter Helper Tests")> _
    Public Sub TestHelperGetExcelColumnDoubleLetterThirdAlphabet()
        Dim theLetter As String = Helper.GetExcelColumnLetter(56)
        Assert.AreEqual(theLetter, "BD")
    End Sub

    <Test()> <Category("Presenter Helper Tests")> _
   Public Sub TestHelperGetExcelColumnTripleLetter()
        Dim theLetter As String = Helper.GetExcelColumnLetter(2356)
        Assert.AreEqual(theLetter, "CLP")
    End Sub

    <Test()> <Category("Presenter Helper Tests")> _
    Public Sub TestHelperGetExcelColumnTripleLetterSomething()
        Dim theLetter As String = Helper.GetExcelColumnLetter(14999)
        Assert.AreEqual(theLetter, "VDW")
    End Sub

    <Test()> <Category("Presenter Helper Tests")> _
        Public Sub TestHelperGetExcelColumnTripleLetterTheEndOfZzz()
        Dim theLetter As String = Helper.GetExcelColumnLetter(18278)
        Assert.AreEqual(theLetter, "ZZZ")
    End Sub

    <Test()> <Category("Presenter Helper Tests")> _
    Public Sub TestHelperGetExcelColumnF1C1()
        Dim theColumnF1C1 As String = Helper.GetExcelColumnF1C1(3, 6)
        Assert.AreEqual("C:F", theColumnF1C1)
    End Sub

End Class

I churned out each of these tests, the first few I did at one time, counting out where the column "letters" would be derived at various column numbers.  It almost got confusing a few times.  All tests of course went red and I began coding the innards of the method.

What I came up with, after a few gotchas here and there is this.

Public Class Helper
    ''' <summary>
    ''' This method gets the appropriate "A" thru "ZZZ" column header for Excel based
    ''' on a passed in long data type.
    ''' </summary>
    ''' <param name="colNum">Pass in a long type to derive the appropriate column header letter.</param>
    ''' <returns>A string of the appropriate "A" thru "ZZZ" header letter.</returns>
    Public Shared Function GetExcelColumnLetter(ByVal colNum As Long) As String
        Do
            GetExcelColumnLetter = Chr(65 + (colNum - 1) Mod 26) & GetExcelColumnLetter
            colNum = (colNum - 1) \ 26
        Loop While colNum > 0
    End Function

    ''' <summary>
    ''' This method returns the ("A:ZZZ") part of the Range("A:ZZZ") column interface.
    ''' </summary>
    ''' <param name="firstColumn">Enter the first column to start the range with.</param>
    ''' <param name="secondColumn">Enter the second column to end the range with.</param>
    ''' <returns>A string formatted as A:ZZZ is returned from this method.</returns>
    Public Shared Function GetExcelColumnF1C1(ByVal firstColumn As String, ByVal secondColumn As String) As String
        Return GetExcelColumnLetter(firstColumn) + ":" + GetExcelColumnLetter(secondColumn)
    End Function
End Class

If you work with Excel frequently you know all about F1C1, which I decided to stick on the end of the GetExcelColumnF1C1.  Not sure if it really makes sense, but since so much of Excel is kind of just mess, I figured it might conjure the right thought in a coder's mind.

The mumbo jumbo of use case, requirements gathering, and all that are a necessary evil of software design.  We developers cannot unfortunately just haphazardly build our UIs and other such things.  It is necessary that in some way the user interface is dictated by the users.

In the past, especially with waterfall, this has been a massive failure point for companies having software built.  The UI not reflecting actual use or features of what was actually needed (vs. what is asked for, because we all know that what is asked for usually isn't really what someone wants).  To get to what someone actually wants and needs takes multiple sessions of sitting and going through the UI.  This of course entails all the underpinning architecture and functionality too.

The crux of all this is it eats up valuable developer time, which is why the solution in the past was always to put people in between the developers and the users.  However, that just means there is another communication failure point between the user and the developers.  Major problem...

Enter, User Stories.

User stories in the agile, sometimes eXtreme Agile sense, provide an alternative way to get specifications and requirements without the customer shooting themselves in the foot.  It has been documented so many times the customer either goes beyond or doesn't provide even close to what they should in the requirements and causes development to slow to a minimum speed.  Sometimes development functionally stops while developers "thrash" and struggle to find day to day tasks.  This effectively kills a project.  I myself have seen entire projects, with burn rates in excess of a quarter million dollars (yeah, that's $250,000) a month effectively not achieve a single tangible task because of faulty and ill planned requirements.

User stories are simple, so simple that they almost cause a customer to make the application request what it should be, simple!  A simple application is by far the most useful application for a user.  The second things get complicated it is usually because of ill planned requirements that state too much or too little in the way of what is actually needed.

A user story goes something like this, "A user can create a document to provide a customer bill."  The story doesn't tell a developer what type of document it needs to be.  The story doesn't tell a developer that it needs put in a particular place or put into a bit stream or anything in an implementation sense.  The story simply states what the user needs to do to perform a business need.

The user story is something that should be able to be placed on a brochure or pamphlet as a selling point of the application.  If it is something you wouldn't put in a document like that, it is highly likely that you've either given too much or too little information in the user story.

Some specifics about user stories, and identifying a good story versus a bad or unusable story is that they must be;  independent, negotiable, valuable to the customer, small, and very important to the developer, it must be testable.

I run into a little problem with project managers, scrum leads, or whoever taking these and estimating schedule times based off of these things, but some very well respected agile proponents suggest that a user story must also be estimatable (which is not a word really, so maybe it is just a joke).  I assume that they intend to schedule and estimate a project plan of some sort with user stories.  This can be done, but a team should not be dictated a strict schedule based entirely off of user stories.  In a very Six Sigma, Lean, Agile way estimates at this level are still NOT measurable with a high level of accuracy, there is still a double digit percentile of risk involved that must be mitigated if planning is done at this level.

But I digress, back to the user stories...

Beyond the Written User Story

Beyond the basic user story there are other elements of what has to be done.  The most important part, more so than even writing down the user stories, is to communicate the user story.

During the iteration the team must get these stories and organize them in a way to have current work, backlog, and done.  Each of these unto itself is worth an article each, but I'll continue on with user stories.  Keep in mind though that each of these points is very specific in the use of user stories.

The Gurus

For more information check out InfoQ for some decent articles.  For the master of user stories check out Mike Cohn over at Mountain Goat Software.  Mike has an article titled "Advantages of User Stories for Requirements" which is a must read!

So get your user stories together, or fail. 

Some weeks just suck.  From the first minutes ticking away on a Monday one can see these types of weeks coming.  When just absurd off the wall stuff starts to break.  Next the complex things break that have easy solutions, just that no one has ever seen the breakages to know the solutions.

My last week was like that.  Having just returned from the Oregon Brewer's Festival I sure hope that this week is an improvement in smooth flow.  I have big plans for this week.  First off there are major functional pieces I need to get done ASAP.  I'm on a good course, even after losing 3 plus days last week to interruptions (build issues, breakages, code problems, visual studio problems - you name it, it happened).  On Thursday I have a brown bag on the PIAB, which at this point I'm not prepared to the level I'd like.  I still need a few more demo examples and such.  Then on Friday I want to fully begin implementation of the PIAB into our architecture.  Overall, a rather big week of things to do.  In addition I'm going to be packing and preparing to move from one end of town to the other.

...I tell ya, never nothing to do.

Someone was saying something about not seeing IT jobs in the help wanted section of the newspaper.  In response someone wrote, and I quote,

"Employers don't really expect IT people to be looking at dead tree medium for jobs."

Seriously, if you're in IT and you're digging through the newspaper, you're doing the industry and especially yourself a disservice.

I had to post this, as I love the "dead tree medium" reference.

This is why one writes unit tests and refactors.  Re: line # 13:47

[13:39] Jabley: I reverted the last change I made and it is still broke. Grrr!

[13:45] Adron: ok

[13:45] Jabley: I am just trying to figure out how the f*** to salvage this.

[13:45] Adron: kewl.

[13:45] Adron: not sure what kind of advice for that situation.

[13:45] Jabley: I desperately need to refactor my code

[13:45] Adron: I just punish myself and write it over if I fail on such a grand scale...

[13:45] Jabley: There isn't any

[13:45] Adron: and refactor a ton...

[13:45] Adron: prevents the situation from happening.

[13:45] Jabley: Thats what I am considering

[13:45] Adron: ...at least mostly prevents it.

[13:46] Jabley: I know what I did wrong but I can't figure out how to make it write in the current app without a full rewrite. That full rewrite would be a ton better because I can refactor the code, which it needs, and start it correctly with UTs

[13:46] Jabley: write = right

[13:47] Jabley: Jeez I want to kick myself in the balls right now

[13:52] Adron: ouch.

So live and learn, WRITE UNIT TESTS AND REFACTOR, the alternatives are painful. 

Sometimes I want to use the enterprise library, but just not all 200+ MB of the thing.  So I go about piecing together the pieces I want, by project, into the main solution.  This can be troublesome as all the pieces don't just drop in simply.

First Bump in the Road :: GlobalAssemblyInfo.cs

The first problem I've run into this time around was the GlobalAssemblyInfo.cs file wasn't available.  After fiddling around a minute I realized this needed to be added to the solution at a global level.  The way to do this is to right click and add existing file to the actual Solution.  Unfortunately I had statically added a copy of this file to my projects I was adding and simply re-added those projects with the appropriate file mapping again.  Note to self, "If adding individual blocks from the enterprise library make sure to add the GlobalAssemblyInfo.cs file first and then the individual blocks".

Second Bump ::  The "Common" Project

The common project is used in pretty much every single project in the enterprise library solution.  For some reason when I added all my projects, the common reference went away in numerous projects.  So this was a case of just adding the reference back to several of the projects.  Maybe I shouldn't add them this way, but regardless, it gets the job done.

So far I'm now up and running, but just wanted to present a searchable note about these bumps I've hit.  Once these things are resolved the library blocks are flawless.

Yesterday was rough going, mainly just because there was so many topics to cover.  Today I hope to nail a ton more functional code segments, requirements, and get some unit tests in my work.

Yesterday

• Finished clarification of the task list and working to find the priorities for this iteration.
• Met to clarify design goals and functional requirements for new features.

Today

• Core focus will be on functional requirements and getting the respective unit tests, preferably via TDD for the interface based controller and presenter code.  I'll be happy with test after for the UI elements since they're all COM Interop.  i.e.,  today is code day.

Tomorrow

• Continue focus on functional specifications and respective tests.

Road Blocks & Risks

• Continued - Time constraints have all but eliminated efforts to research or implement a proof around the PIAB, limiting our exception handling, logging, and other cross cutting concerns we need to have in place.
• Continued - The IoC and DI architecture points have not been clarified.  The more UI work we do without these pieces in place the more tightly coupled, untested, and unworkable refactor efforts will be in the future.
• Continued - Significant risks include; lack of significant code coverage from unit tests, unclear way for testing the upper layers of Excel/UI functionality.

So yeah, if you don't know him, or haven't worked with him, or haven't tasted any of his fine beverages you should make a point to meet this guy.  If you can...

I'm fortunate I got to work with Mr. Thatcher as he is an A+ Developer (That's an A+ without a bell curve either, if that was the case he'd have like a 180% and I don't know what that letter would be - maybe AAA+ or something)

Anyway, he's gettin famous so read up about the fella.

We scrummed, but then kind of fell of into planning today.  Thus no real serious throughput was achieved.  We did rectify many questions and clarify what was meant for many of the specifications for the application.  It was a very productive day, just not in code productivity.

Yesterday

1. Finished clarification of the task list and working to find the priorities for this iteration.
2. Met to clarify design goals and functional requirements for new features.

Today

1. Finish clarification of the iteration priorities.  Break items down to more specific functional goals.
2. Met with others on other functional topics to provide consult on my functional task topics.
3. Started some code clean up, minor change requests, and another refactor effort against some of the existing UI Views.

Tomorrow

1. Begin research of the PIAB (Policy Injection Application Block).
2. Figure out where and how our IoC (Inversion of Control) and DI (Dependency Inversion) architecture points will meet.
3. Implement the beginnings of a vertical integration with these concepts.

Road Blocks & Risks

1. Time constraints have all but eliminated efforts to research or implement a proof around the PIAB, limiting our exception handling, logging, and other cross cutting concerns we need to have in place.
2. The IoC and DI architecture points have not been clarified.  The more UI work we do without these pieces in place the more tightly coupled, untested, and unworkable refactor efforts will be in the future.
3. Significant risks include; lack of significant code coverage from unit tests, unclear way for testing the upper layers of Excel/UI functionality.

I'm not sure if this would be interesting or not to others, but first and foremost this blog is about me maintaining a history of what I do on the projects I work, what I'm accomplishing, and the technologies I'm working with.  With that said this is entry Part #1 of numerous entries I am going to attempt to start writing based on daily efforts and the stand ups that I attend.

Yesterday

1. Finished view find functionality.  This included the UI, presenter, and test data for the functionality.
2. Finished the add multiple single items, add single items multiple times, and their associated UI and presenter code.

Today

1. Begin refactor of yesterdays code, clarification and refactor of previously written code.
2. Start another set of unit and integration tests for the next phase of functionality.  Make sure I have coverage, as far as feasible, for the existing functionality.
3. Meet and discuss functionality for near future revisions, additions, and changes.

Tomorrow

1. Begin research of the PIAB (Policy Injection Application Block).
2. Figure out where and how our IoC (Inversion of Control) and DI (Dependency Inversion) architecture points will meet.
3. Implement the beginnings of a vertical integration with these concepts.

Road Blocks & Risks

1. No significant road blocks at this time.
2. Significant risks include; lack of significant code coverage from unit tests, unclear way for testing the upper layers of Excel/UI functionality.