OK—my title is kind of colloquial. But I'm in a hurry, and the title conveys the idea well enough.

Here's the deal: I have a spreadsheet with a bunch of important data. But, some of the most distinctive data are all merged into one cell. To make the data more usable, I needed to parse the data elements in that cell and separate each out into its own column. That way, I can filter and sort for each specific element. VBA made this possible and in short order (see my recent post Why VBA Still Makes Sense).

Here's what the column looked like before:

And, here's what it looks like after:

You can see that the data that used to be in one column and now they are in three columns.

Here's the code I wrote to make it happen:

1 Dim strParty As String

2 Dim strPV As String

3 Dim strDist As String

'Step through rows of data one at a time

7 Dim FinalRow As Integer

8 Dim counter As Integer

9 Dim curCell As Range

11 FinalRow = Range("A2", Range("A2").End(xlDown)).Rows.Count

13 For counter = 1 To FinalRow

14 curCell = Worksheets("Sheet1").Cells(counter, 7)

15 strParty = Mid(curCell.Text, 5, 1)

16 strPV = Mid(curCell.Text, 32, 1)

17 strDist = Mid(curCell.Text, 41, 4)

19 curCell.EntireRow.Cells(1, 8).Value = strParty

20 curCell.EntireRow.Cells(1, 9).Value = strPV

21 curCell.EntireRow.Cells(1, 10).Value = strDist

22 Next counter

16 lines of actual code to loop through thousands of rows in a single moment. It saved me tons of time and it made my data way more usable.

Let's take a quick look at what the code means.

Lines 1-3 are just setting up my placeholders to temporarily contain the values of the three separate data items in the column for a given row.

Lines 7-9 are placeholders for values for the looping routine. Final Row and curCell depend on how many rows are in the spreadsheet and also which row I'm on.

Line 11 establishes how many total rows need to be looped through.

Lines 13-21 is where the work occurs. For each row, the code grabs the cell in the unparsed column of the worksheet. Then, it parses out the first, second, and third values. The code places these values in the variable placeholders. Finally, it takes the values in those variables and puts them each in their own column cell for that same row.

The code then loops through the next row.

Now, I knew how to code this because I've been doing this kind of thing for years. But, for people who are new, just pressing F1 brings up a rich help system for VBA coding. It contains examples, explanations, and other resources to help you get there. Your first VBA code may stretch you, but your skills will grow rapidly.

Rock Thought of the Day: Annie Lennox

I'll never forget watching Annie Lennox sing "Here Comes the Rain Again" during a music awards show back in the 90's. Previously, a lot of so-called "artists" had gone out and lip-sync'd/guide-vocal'd their way through their songs. Then, Annie comes out and shows them how it's really done. She laid down a version of this song that remains fixed in my mind as one of the best performances I've ever seen. My only regret was that I was not there to see it live. Every singer should find videos of her live performances and study them. I wish I could find that performance on YouTube. No luck so far.

Rock On

One of the presentations I delivered at SPC 2009 was the title of this blog post. Don't worry—I'll be posting the video when it becomes available. And, we've got some MSDN content coming out before RTM that will show you the details around programming the Microsoft Office 2010 Backstage view and the Ribbon UI. Consider this blog post the prelude to all that. I'm going to introduce you to the basics of Backstage programming. For a great intro what Backstage is go here.

Update: Mirko Mandic has newly added a post about Office 2010 UI Extensibility--- great companion reading to my post.

Here's the basic flow of building your customization:

1. Add XML markup to define the view and what it contains, then make this XML available to the Office application so it knows to load it
2. Create methods in code to which your controls and other items in Backstage will call back.
3. Add these method calls to your XML markup so that the Office application knows which methods to call when a control event fires

If these three steps for Backstage programming sound just like what you do in Ribbon customizations in Office 2007 then you are on the right track. In this post, I'll walk through the basics of the Step #1.

So, let's look a very simple example and understand the terms.

Looking at the overall Backstage view you'll notice the word Custom highlighted on the left toward the bottom. This is called a Tab and in this case it is a custom Tab. When a user selects a tab, the two columns you see to its left are then shown. You can only work with two columns after a Tab is selected. What you put in there is up to your needs and what the Backstage XML schema will allow. Again, I won't go into all of that detail in this post as this is meant to be a quick/simple introduction.

You'll also notice that just above the custom Tab is a little button with a happy face.

This is called a Fast Command. You can add your own Fast Commands to the Backstage view. Office applications come with some Fast Commands already built in, like Save or Save As. You can hook up your custom Fast Command to some code procedure that runs code meaningful to your users activity.

Looking back again to the Custom Tab, you see that in the first column there is text, "My Category".

This is a Category, and you can have multiple Category items in a given column. Below that Category is a custom Task called "My Task" and it is accompanied by an image--- a happy face. As with other controls in the Backstage view, you can designate a code procedure that will run when the user selects the Task. To the right of the Category is a Group called "My group".

The key difference between a Category and a Group is what they contain and how they can be organized. Once again, we'll wait on our more thorough documentation to help you through all of the details. What is interesting with "My group" is that it contains a different kind of control—in this case what we call a "Hero Button". This Hero Button displays a different image, in this case the FileSave image. As you can expect, this button can be hooked up to a code procedure.

Now, let's look at the basic XML syntax to make all of this happen.

NOTE: You'll notice that the XML begins with a namespace declaration. Keep in mind that this is pre-release, and namespaces or the rest of the schema may change in the lead up to Beta 2 and RTM. This is common as we continue to refine the product in the remaining milestones.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

 <backstage>

  <button id="customButton" label="Button" imageMso="HappyFace"/>

   <tab id="customTab" label="Custom">

    <firstColumn>

     <taskFormGroup id="GroupShare">

      <category id="ButtonCategoryAvailableFileTypes" label="My Category">

       <task id="100001" label="My Task" imageMso="HappyFace">

        <group id="100002" label="My group">

         <primaryItem>

           <menu id="100003" label="Hero" imageMso="FileSave">

            <menuGroup id="100004" itemSize="large">

              <button id="100005" label="Button" description="Some Description" imageMso="HappyFace"/>

            </menuGroup>

         </menu>

       </primaryItem>

     </group>

   </task>

  </category>

 </taskFormGroup>

 </firstColumn>

 </tab>

</backstage>

</customUI>

The outermost tag for Backstage view and Ribbon is <customUI></customUI>. All things fall within there. If you had a Ribbon customization, it would also appear here as a sibling with the Backstage view code. The next tag of relevance is what contains the Backstage view markup: <backstage></backstage>. From there, you can decipher fairly easily how the markup relates to the items I have described already. You see the Fast Command appears as <button/>. You then see the first column and its contents. It contains the <category></category> with its children <task></task> and <group></group> that contains a sub menu item with the Hero Button <menu id="100003" label="Hero" imageMso="FileSave"></menu >.

Don't fret that there is a little more to the syntax such as the <primaryItem></primaryItem> and so forth. We'll continue to explore the possibilities of the Backstage view by showing what the rest of the schema allows. For now, this should get you up-and-running with a basic Backstage.

The last thing you need to do is place it in proper location of the file hierarchy in the Office XML file format. The XML for Ribbon and Backstage view go in a directory called "CustomUI" as you see below.

Next Post: Hooking up the controls and buttons to code procedures

Big thanks to Trang Luu, one of our loyal testers, for helping me with this post. There are so many people like Trang who quietly and magnificently make the product great.

Rock Thought of the Day: U2 In Las Vegas

Alright- I need to talk about U2 in Las Vegas. I was able to go to the show (Thanks to Gray Knowlton), and it was end-to-end a high-quality, moving, entertaining show. The opening act was the Black Eyed Peas. I was really unsure how they would go over with the crowd. I have only heard one BEP song, and I'm more of a hard-core rock-n-roller. But, BEP brought it to the crowd. I was in a huge mass of people jumping up and down. BOOM BOOM POW! Then, U2 comes out starting with Larry's drum solo. They break into "Breathe". The show is dazzling, and it is easy to see how the learnings from the PopMart tour along with the Elevation and Vertigo tours have added up to this rather refined rock show. Hearing "Sunday Bloody Sunday" re-directed to the struggle in Tehran was pretty special. And, the show finished with my favorite song from the new record, "Moment of Surrender". During the entire show, the crowd was singing along word-for-word, song-for-song. I have never experienced it so fully in previous shows.

One minor critique: While I'm pretty supportive of Bono's efforts to relieve 3^rd World debts and to bring more assistance to places where infants are dying at alarming rates, the multi-praise sessions for President Clinton mingled with other politics was a little more prominent and longer than I prefer. But, Bono's heart is in the right place, and it wasn't overbearing. It was almost just right. I have to say, I really dig Desmond Tutu. I'd love to know if he can play the blues. He's clearly got soul.

The only unpleasant part was finding a taxi after the show. We ended up walking 3-4 miles from the stadium and going into a bidding war to secure a cab. It was still worth it though.

Here are three shots from the show: Bono, Bono On Bridge, Larry

Rock On

Not infrequently I am asked, “So, should I use VBA? Is it going to be around in Office 2010? Is it supported? Should I migrate away from VBA now? Can I count on this technology?” (Here I go with a response!)

These are fair questions, because customers need to know that the software systems they employ are ones they can count on. There’s no question that the IT landscape in terms of teams, tools, software, networks, and so forth have changed dramatically since 1993, when VBA, or Visual Basic for Applications, made its way into Excel. But, VBA still has a place in this world. It still makes sense, and I’ll explain why.

First, here are some answers: 1) VBA is included in Office 2010 much as it was in Office 2007. 2) It is indeed supported 3) You should continue to use VBA where it fits the needs of your business and migrate only if the need arises.

Let me elaborate on #3 a little more, because it is the locus of most questions and issues.

In contrast to 1993, business productivity solution developers on the Microsoft Office platform have more options in terms of what they use to create, build, deploy, and maintain solutions. For example, many developers now target the .NET Framework, and they are accustomed to using VB.NET, C#, or another .NET-compatible language. Many of these developers use Visual Studio .NET, and they are building a wide variety of solutions that integrate Web, databases, middleware, and client applications. More importantly, the user experience is quite different from 1993. Now, users are working in a highly collaborative, real-time sharing, online/offline, mobile, global world. They are populating documents with data from a wide variety of sources, and they are re-purposing the documents and data in very creative ways.

In 1993, users worked mostly in a monovalent way. Work was done in an exclusive application that didn’t have the broad reach into databases and Web sites like today. Importing CSV files was the primary way of ‘reaching’ into other data. Sharing consisted of saving a spreadsheet up to a file share. Customizing the application was primarily the task of users. Most IT departments had other things on their mind than tinkering with Office customizations. VBA gained huge popularity because of its ability to allow a non-greenscreen programmer to customize her or his application experience. For example, a user could write a VBA ‘script’ or macro that would automate repetitive tasks and save a ton of time. Over the years, many hundreds of millions of documents have been imbued with this kind of code and saved users untold numbers of hours.

The good news: Even though the user context has changed a lot since 1993, 1997 and beyond, using VBA can still help users save time and effort by automating tasks and customizing their Microsoft Office experience. And, as the application features have evolved to adapt to new user needs has also evolved and grown. Gladly, VBA has remained in step with these evolutionary changes. For example: a great little routine I wrote a long time ago is some VBA that I hooked up to a custom button in Outlook. It allows me, with the click of a button, to save off the attachments for any number of selected emails. The code loops through all selected emails, saves the attachments to a central location I have designated with some logic about how they are stored there so I can sort and find them easily. It then optionally deletes the selected emails. It’s a great little routine that saves me lots of time.

So, what’s the VBA authoring experience like? What are the advantages?

1. You can record macros in some of the applications. So, if there is a task you do over and over, just start the macro recorder and the Office application will communicate with the VBA environment for you and write the code for you. You can then re-run this macro any time to run the steps automatically.

2. OK—recording macros is great, but it’s not always enough. And, there are some applications that, while not endowed with full macro-recording capability, still allow you to write the code yourself. VBA really sets itself apart by having such a rich set of built-in tools. First, you have a rich Visual Basic Editor (VBE).

This is the environment for authoring VBA code. The VBE has Intellisense which makes it very easy to call into the application and pass the right information.

You also have integrated Help. If you are unsure what to type, what a specific method does, etc., just press F1, and the help system will give you the information, and, in many many cases, a sample of what to type.

VBE also has debugging tools. While writing your code or even just looking at a recorded macro, you might want to run the code but stop at a certain location and see what the code is doing.

There are lots of other debugging tools that let you alter code on the fly and gain greater insight into what the code is doing.

Additionally, VBE lets you easily reference applications and code that is not in your immediate project.

Has a co-worker created a special collection of code routines that everyone typically needs or uses when writing VBA solutions? No problem, just reference this external code, and it’s now available in your VBA solution. Above all, the VBA syntax is pretty easy to learn. It’s based on BASIC (there’s an intriguing history tracing through QBasic, Alan Cooper, and so forth that I won’t go into here), so it’s pretty easy to get into and start using.

For example, here’s some syntax that checks, when my document is closed, to see if I left comments in there (fortunately, in Office 2007 we made it easier to scub docs of this kind of thing without needing a custom macro, but you get the idea):

Private Sub Document_Close()
    If Me.Comments.Count > 0 Then
        MsgBox "Hey-- you forgot to remove comments", vbInformation, "Comment Removal"
    End If
End Sub

With very little effort, I could write code that would loop through the entire collection of comments in the document and save them all to a database. If I get stuck, there is a ton of additional help, samples, tutorials, and so forth on the MSDN Office Developer Center. There is also a very active community (see the Community section of the MSDN site) with Newsgroups, MVPs, and more to help me make progress.

Finally, the language of VBA is pretty easy to learn, and it has a fair amount of power. There is good control of flow language (If…Then, Select Case) and so much more.

3. VBA is a great all-in-one kit for customizing your Office experience

All of the things I have shared up to this point are included with Office out-of-the-box. You can start developing a custom little solution today with what is included in the software on your desktop. Also, you can extend the reach of your little solution by connecting to external code libraries. We also have design the Office applications themselves to be Web-aware. For example, you can connect to Web sites in Excel and import their data directly into a workbook. Using VBA, you can write code to alter which site the user connects to. You can add little buttons to the users Ribbon so they can click one button and import from one site, click another button and import from another site. The possibilities are endless and are bound only by your imagination. In short, the out-of-the-box Microsoft Office experience is great. People love what it can do. But, there will always be a need to customize the applications to fit more specific needs, automate tasks, and reduce busy work. VBA has been and continues to be a great way to meet those needs.

4. But, what about .NET? Aren’t there great development tools in Visual Studio .NET for creating Office-based solutions?

Yes! Visual Studio .NET has an excellent set of tools that allow you to create solutions targeting the Office applications like Excel, Word, PowerPoint, and Outlook. Solutions built using Visual Studio and using .NET code to customize the Office experience are created in a very different way, with different tools, and with different requirements. Visual Studio .NET is a set of professional developer tools. There is more to learning Visual Studio’s tools, IDE and so forth than you need to know to use VBA. Also, because Visual Studio targets .NET code, there are different security, deployment, and maintenance requirements than for VBA. In short, VBA is probably an easier way to begin customizing the Office experience for many users, especially those with less technical expertise. This does not mean that Visual Studio development is particularly difficult. It only means that it requires sacrificing a little more lunch time to learn what’s involved. VBA’s simplicity makes it a great candidate for a lot of ad hoc automation tasks.

Visual Studio and .NET do provide some appealing aspects to your solution. For example, .NET provides a layer of strong security to your solution that VBA does not provide. But, overall, in my view, the main reason for creating solutions in Visual Studio .NET is to take advantage of the professional development environment, IDE, security, and so forth to create multi-tiered, industrial-strength solutions. Also, it is really, really hard to not be completely won over by how Visual Studio .NET’s Office tools create strongly typed objects out of items you add to the document surface. For example, if I add a content control to a Word document, Visual Studio automatically declares a .NET object representing this content control. Additionally, it is very easy to create task pane solutions in Office using Visual Studio .NET. With three lines of code, the task pane is all wired up.

Visual Studio also has really slick tools for creating customizations in the Office Fluent UI.

If you are just beginning, VBA is a great place to start. You can also use VBA for complex solutions. All the same, if you are willing to deepen your technical skill (always a good career move) then exploring Visual Studio is a great idea, and you will find that most of what you learned in VBA code is applicable while coding Office solutions in Visual Studio. For example, the API calls for Office in .NET are roughly synonymous with those you made in VBA. Also, you can call VBA routines from your .NET code in Visual Studio, so investments in VBA are not lost as you migrate a solution to Visual Studio.

In the final analysis, it’s about choice and flexibility. It is not an ‘either/or’ dilemma. Use VBA for ad hoc task automation, and it has a pretty low entry bar in terms of knowledge and skill. Use Visual Studio if you are or aspire to be a professional .NET developer and all that this implies.

Some things to help you get going:

• The Microsoft Office Developer Roadmap
• Check out this book chapter excerpt: VBA Primer
• The Office Developer Center

Rock Thought of the Day:

I’ve been reading “U2 by U2”, a great book that gives insight into every song, album, and tour. Read it, and you come away with clues as to how the band really ‘ticks, what has kept them together for so many years, and what the costs of being rock-and-roll stars can be. Most important, their humanity, genuine love of music, and their desire to always get things right comes ringing through--- even when they mess things up or fail to get things right.

Rock On

You might be tempted to think that, given that I’m so-called “insider” I’d know all about everything in Office 2010. But, the fact is that there is so much to the products in Office that few people can know it all and know it early.

So, this brings me to when I was recently demo’ing Office 2010 and someone asked me to show off new Excel features. (BTW: there is an Excellent series of posts on the Excel Team blog about Sparklines, how to use and customize them. Read it and the posts that follow it there).

Well, I had seen a few presentations on Sparklines months ago, but the sheer velocity and volume of my workload prevented me from doing much experimentation. So, there I stood with a perfectly delightful customer asking me to demo anything new in Excel, and, while I could speak to the feature and knew the general API for it, my hands-on experience was….lacking. Fortunately, I learned in a matter of moments and was able to demo the feature. The customer was delighted. Whew!

I then showed off Sparklines and Slicers (more on in next post) to every person who came to our kiosk to see Office 2010 unveiled. Each one, without exception, was dazzled by the features and could immediately see how they would save users time.

Excel developers are known for doing some of the most amazing things, and the Excel OM is very rich—including the newly added OM members for Sparklines.

Sparklines are organized into SparklineGroups, and each SparklineGroup contains a variable number of Sparkline items. In the following image you can see my rows and columns of data with the Sparkline items in the last column:

Now, how does what is shown here map to the three objects I just mentioned? In this case, there is one SparklineGroup representing all the financial data, and there are four Sparkline items:

You can easily write some VBA to get access to these various object instances and work with their properties and methods. At the simplest, the following code shows you the basics:

Private Sub EnumSparklines()
    Dim slgs As SparklineGroups
    Dim slg As SparklineGroup
    Dim sl As Sparkline
    Dim r As Range
    Dim slCount As Integer
    Dim i As Integer
   'Using a named range to quickly find the Groups
    Set r = ThisWorkbook.Names("spark1").RefersToRange
    Set slgs = r.SparklineGroups
    Set slg = slgs.Item(1)
    slCount = slg.Count
    For i = 1 To slCount
        Set sl = slg.Item(i)
        MsgBox sl.SourceData
    Next

End Sub

Obviously, there is a lot more to the OM here than what I am showing. For example, the Sparkline object has a ModifyLocation method that lets you quickly modify the Range to which the Sparkline instance refers.

Rock Thought of the Day:

I can understand why some people do not like Country music. How much can crooners sing about tractors, anyway? Look, I love a John Deere or McCormick tractor like any other corn-fed boy from the mid-west or the south, but there is a limit to the number of meaningful odes one can write in their honor.

But, what I have found is that most people really like genuine Country music when they hear it. One of the best producers of the best Nashville can offer is Brad Paisley. His new album, American Saturday Night is instantly appealing. And, it proves once again that he is simply one of the best guitar players the music industry (of any genre) has ever seen. If you want smart music that wears its roots on its sleeve—check this record out. I know Johnny Cash, Hank Williams, Buck Owens, and Merle Haggard are proud.

Rock On

So, I just bought 5 copies of Office 2007 for a friend of mine in the good land of Minnesota. He’s upgrading all of his home computers and those for his kids scattered about the country. One of the first things he’ll notice is the new Office Fluent UI, and he’ll likely need some help finding his way around the new way we display the commands. No more “File | New” etc. in some of the applications. Sure, Office 2007 has been out for a while now, but there are still many thousands of people moving to the new version as the weeks pass. And, it’s good to learn one’s way around the apps even when the old menu structure has been mostly unchanged.

So, I am pushing the reference guides and how to customize the UI to the top of everyone’s inbox:

Access -- http://www.microsoft.com/downloads/details.aspx?FamilyID=b9574c72-657f-438c-9de9-f8f70dd2d40d&DisplayLang=en

Excel -- http://www.microsoft.com/downloads/details.aspx?familyid=89718ABD-2758-47B3-9F90-93788112B985&displaylang=en

Outlook -- http://www.microsoft.com/downloads/details.aspx?familyid=CC37CC1E-028D-4D30-9093-96CC6513ECA1&displaylang=en

PowerPoint -- http://www.microsoft.com/downloads/details.aspx?familyid=BEF41DC3-8E28-4282-82D4-CEC2F416CD40&displaylang=en

Word -- http://www.microsoft.com/downloads/details.aspx?FamilyID=9044790b-4e24-4277-b714-66d7b18d0aa1&DisplayLang=en

Here’s the location on the MSDN Office Developer Center where you can find developer info about how to customize the UI:

http://msdn.microsoft.com/en-us/office/aa905530.aspx

One of the things that is encouraging is how many non-Microsoft applications are revisiting their UI layout and adopting a similar approach to what we introduced in Office 2007. Over the coming years you’ll see more applications (from us and others) going this direction mainly because how people work in the applications is so very different from what it was in the 90’s. I also fee persuaded that we have barely scratched the “Surface” (pun intended) in exploring new ways to make it easier for people to interact with the systems upon which they depend.

Rock Thought of the Day:

What if a hugely famous band decided to let the fans be part of the process of making new music? How would that work? What are the logistics? How would they react? How would the band adjust to the reaction? What would (ugh) critics think? Would it matter? Well, only a few bands in history have really taken up such a project in earnest, and one is, of course, The Smashing Pumpkins back in 2007. And, they packaged up the journey in a compelling documentary called “If All Goes Wrong”. It’s honest, revealing, and irresistibly insightful. I own my own, but I’ve been buying copies for friends. What is best is the focus on the music. It’s all about snatching songs out of the universe, playing them, recording them, and seeing what happens. Get a copy.

Rock On

TechEd 2009 is a done deal. I wanted to toss out a few observations from the event. Here’s what really stood out:

1) The overall mood was pleasantly up-beat and the attendance was solid.

The economic times have hit every budget everywhere, but the tempo was not sagging at TechEd. I heard a number of people say that they were lucky that their organization had bugeted for the event as early as last summer. The people there were really excited about technologies across the spectrum. No single thing stood out to me anecdotally more than another.

2) The interest in Office 2010 was extremely high

We announced a technical preview available to attendees at the show if they signed up while at TechEd. This was the #1 question at the booth: “How can I sign up for the Technical Preview?!”

I was really pleased to see so many people interested in the next version of our Office program. As I fielded many developer questions at the booth I was regretting that it is not yet time to discuss the next version and what’s in store for the developer crowd. Oh well… that time will come!

3) Many people really “get” our Microsoft value proposition

In some circles, it’s “chic” to bash on Microsoft. I understand all that for whatever reason. All the same, it’s really great when a customer walks up and just gushes with excitement about the products he or she is deploying and the solutions that are being built on top. It really stokes my engines when I hear a customer sharing how he built a whole system in Word that radically simplifies how they write contracts in their organization. Another customer talked about how they use PowePoint for all of their training and repair manuals for complex equipment. They use code to generate the PowerPoint decks from other data sources.

4) We’ve got a lot of work to do to help SharePoint developers get up to speed on what Office client application programmability can do for them

As I chatted with SharePoint developers who passed by, they were eager to let me show them what Office programmability means and what it can do. They, for the most part, had no idea that Office could be programmed so easily and that it had so much hidden power. This tells me that they probably came to SharePoint development from the Web dev space. This is a strong trend, and we are going to be doing a lot to help them get into the Office side of things as it will strengthen their solutions considerably.

On the lighter side—I add these observations:

1) The food was not as plentiful. Yeah—I know it’s wrong to complain about food when we have it so abundantly compared to many in the world. But, the spoiled, selfish part of me wanted to see the coolers full of Dove and Hagen Dasz bars return! I loved eating the desserts and so on that crowded the halls outside of the session rooms.

2) The TV in my hotel room was an old-school 20 inch non-flatscreen thing sitting on a stand 15 feet from my bed. Watching NatGeo was pretty lame on that set.

3) My rental car was a Nissan 350Z (free upgrade, baby!). It was really, really fun to drive. Would have preferred a Corvette ZR1 however :)

4) Having dinner with my colleagues, Charles Maxson and Michael Kiselman were highlights of the week for me.

5) Traffic in LA is worse than the hideous reputation that precedes it.

6) Seeing my old contacts Ty Anderson, Robert Bogue, Matt Nunn, Rob Tiffany, Mary Chipman, Brian Randell, and dozens and dozens of others is always satisfying. I was also really pleased to see many customers that I have met in previous years and know that they remember me. People are cool!

7) Missing my wife and sons is not so great.

8) Walking around the streets of LA in the late evening by myself listening to Smashing Pumpkins on my Zune while singing out loud was refreshing and peaceful.

9) The weather in LA is just hard to beat. I love heat.

10) Visiting one of the temples for my church in Redlands was also a highlight. It’s a beautiful and peaceful place.

 Rock Thought of the Day:

I’ve often blogged about The Smashing Pumpkins among the many bands I have discussed in my Rock Thought. One of the more intriguing ideas to recently appear in the music industry comes from Billy Corgan. He has proposed a way for his legions of devoted fans to gain unprecedented access to how he conceives, writes, and records his songs. It’s a novel concept and I predict that other artists will explore similar terrain as they try to connect with their audiences in new and creative ways. As Billy noted in one of his posts, it’s not really about trying to find a new product that can be peddled to fans. Everyone who has worked in the music industry, which I have, knows that touring is THE money printer for music artists. The primary way to earn loads of money is by touring. It’s hard work, lonely, and grueling, but it is usually very lucrative. But, I think that artists are exploring new ways of connecting with fans for different reasons at least initially. Check out what Billy has proposed and sign up for the newsletter so you can get involved!

Rock On

Follow me: http://www.twitter.com/johnrdurant

I recent wrote (“Twitter from Excel 2007”) about how Chandoo (Plus JP) caught my attention with their quirky take on updating Twitter from Excel. In there, I said I would lay down an expanded, more useful version. In so doing, I believe I have also provided a raison d’être for the thing in the first place!

Here’s the shakedown (keeping in mind that These postings are provided "AS IS" with no warranties, and confer no rights.”:

I wrote the VS 2008 Add-in for Excel 2007 with a smart tag. The smart tag recognizes phrases or keywords that you have typed into Excel and then, with one click, let’s you post the cell (containing the keyword[s]) or List to Twitter.

You can change what/how the smart tag recognizes (for example using regular expressions), and I have personally written much on this topic already on MSDN (see list of resources at the end of this post). You can also change how the smart tag action handler works of which I have also written a fair amount (see resources again).

Gilding the lily even further, when I post a dialog for your Twitter credentials I encrypt the username and password and store the hashes statefully so that the next time you do the one-click Twitter post you don’t need to re-enter your credentials. I just decrypt them and use the stored ones. This is not mega-industrial strength security practice, but it sure beats putting passwords in the code or storing them as plain-text. At the same, it also provides a superior user experience, because the user does not have to repeatedly provide credentials which would be, at the very least, annoying.

Making this work inside of Word is a snap—very, very trivial. Again, look at the documentation on creating smart tags, and you’ll see how easy that is.

In the near future, I am going to figure out how to do this for Facebook.

Obviously, you’ll need a Twitter account to test this. So, sit back with your bag of M&M’s and strap in for the technical detail!

1) For the password encryption/decryption I used the Microsoft Patterns & Practices Application Blocks 4.1. I strongly recommend working through one of the samples before attempting to go it alone. I’ve done a lot with these blocks over the years, and they are never as ‘plug-and-go’ as one hopes. Anyway, in those blocks is a Security.Cryptographer building block. I use it, and I show you everything I do in this post.

2) Create a VS 2008 Excel 2007 Add-in using VB.NET

3) In the new project, add a form called “TwitterLogin”. Configure it to look like this:

4) Your control names should be the following:

5) Add references so that they look like this:

6) Add a new class called HashHelper.

7) Add a new app.config file

8) Your overall project contents should look like this (you see a key file in here also, and you’ll be adding it in another step or two):

9) Configure the app.config file to use a new symmetric provider for the encryption/description by right-clicking on app.config in VS. Open the file in the configuration editor by selecting Edit Enterprise Library Configuration on the context menu.

10) In the editor, right click on the Application (the path with your app.config file name) and select New | Cryptography Application Block.

11) Select Symmetric Providers in the new block just added and choose New. Then, select Symmetric Algorithm Provider.

12) In the resulting dialog, select RijndaelManaged as the type, and press the Generate button to automatically generate a key, click OK. You’ll want the name/path of the key to be in the directory of your application (could be anywhere but it’s easier to manager this way) as shown in the previous solution explorer image.

13) Name your new provider “symmProvider”. Verify that it look like this one:

14) Close the app.config file and save your changes.

15) Return to your TwitterLogin form and double-click the button to bring up the cmdLogin_click event handler. Add this to that handler:

       Dim UserNameEncrypt As String
        Dim PasswordEncrypt As String

        UserNameEncrypt = HashHelper.EncryptText(txtUsername.Text)
        PasswordEncrypt = HashHelper.EncryptText(txtPassword.Text)

        My.Settings.Username = UserNameEncrypt
        My.Settings.Password = PasswordEncrypt

16) Make sure you have included the following Imports statements at the top of the class for that form:

Imports System.Security.Cryptography
Imports Microsoft.Practices.EnterpriseLibrary.Security.Cryptography

17) Close and save the form.

18) Open the HashHelper.vb file

19) Add the following definition code to that file. Your login form and add-in code will be calling into this code to encrypt and decrypt the password as well as store their hashes.

Imports Microsoft.Practices.EnterpriseLibrary.Security.Cryptography
Imports System.Text

Friend Class HashHelper

    Private Sub New()
    End Sub

    ' Hash provider name must match app.config
    Private Const Provider As String = "symmProvider"

    Public Shared Function EncryptText(ByVal plainText As String) As String
        Dim returnText As String
        returnText = Cryptographer.EncryptSymmetric(Provider, plainText)
        Return returnText
    End Function

    Public Shared Function DecryptText(ByVal HashString As String) As String
        Dim DecryptedString As String
        DecryptedString = Cryptographer.DecryptSymmetric(Provider, HashString)
        Return DecryptedString
    End Function

End Class

20) Open your ThisAddIn.vb code file and add these Imports statements:

Imports Microsoft.Office.Tools.Excel
Imports System.Windows.Forms
Imports Microsoft.Office.Interop.SmartTag
Imports System.Net
Imports System.IO
Imports Microsoft.Practices.EnterpriseLibrary.Security.Cryptography

21) Add the following global variable to your ThisAddIn Class:

    Private TwitterTag As TwitterSmartTag

22) Add this to your ThisAddIn_Startup method:

        TwitterTag = New TwitterSmartTag()
        Me.VstoSmartTags.Add(TwitterTag)
   

23) Add a new class called TwitterSmartTag and add code so it looks like this:

Public Class TwitterSmartTag
    Inherits SmartTag

    ' Declare Actions for this SmartTag
    WithEvents Action1 As New Action("")
    WithEvents Action2 As New Action("whatever")

    Public Sub New()
        MyBase.New("http://painjunkie.spaces.live.com/jrd#TwitterTag", _
            "Excel-to-Twitter Smart Tag")
        Me.Terms.AddRange(New String() {"Soulfly", "Motorhead", "Judas Priest", "Tool", "Pantera"})
        Actions = New Action() {Action1}
    End Sub

    Protected Overrides Sub Recognize(ByVal text As String, _
        ByVal site As ISmartTagRecognizerSite, _
        ByVal tokenList As ISmartTagTokenList)

        ' Determine whether each smart tag term exists in 
        ' the document text.
        Dim Term As String
        For Each Term In Me.Terms

            ' Search the cell text for the first instance of 
            ' the current smart tag term.
            Dim index As Integer = Me.CellText.IndexOf(Term, 0)

            If (index >= 0) Then

                ' Create a smart tag token and a property bag for the 
                ' recognized term.
                Dim propertyBag As ISmartTagProperties = _
                    site.GetNewPropertyBag()

                ' Write a new property value.
                Dim key As String = "Key1"
                propertyBag.Write(key, DateTime.Now)

                ' Attach the smart tag to the term in the document
                Me.PersistTag(propertyBag)

                ' This implementation only finds the first instance
                ' of a smart tag term in the cell. 
                Exit For
            End If
        Next
    End Sub

    Private Sub Action1_BeforeCaptionShow(ByVal sender As Object, ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) Handles Action1.BeforeCaptionShow
        Dim ClickedAction As Action = sender
        If e.Range.ListObject Is Nothing Then
            sender.Caption = "Tweet this cell"
        Else
            sender.Caption = "Tweet this list"
        End If
    End Sub

    ' This action displays the property value for the term.
    Private Sub Action1_Click(ByVal sender As Object, _
        ByVal e As ActionEventArgs) Handles Action1.Click

        Dim propertyBag As ISmartTagProperties = e.Properties
        Dim key As String = "Key1"
        Dim Tweeter As New TweetThis()
        Try
            If My.Settings.Username.Length = 0 Or My.Settings.Password.Length = 0 Then
                Dim LoginForm As New TwitterLogin()
                LoginForm.ShowDialog()
            End If
            If Tweeter.TweetIt(e.Range.Text).Length > 0 Then
            Else
                MessageBox.Show("Twitter successfully updated!", "Twitter Status", MessageBoxButtons.OK)
                Exit Try
            End If
            Throw New Exception
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Twitter Update Error", MessageBoxButtons.OK)
        End Try

    End Sub
End Class

24) Lastly, add this class, TweetThis, which actually does the work of posting to your Twitter account:

Public Class TweetThis
    Public Function TweetIt(ByVal msg As String) As String
        Dim username, password As String
        Try

            username = HashHelper.DecryptText(My.Settings.Username)
            password = HashHelper.DecryptText(My.Settings.Password)

            System.Net.ServicePointManager.Expect100Continue = False

            Dim bytes() As Byte = System.Text.Encoding.ASCII.GetBytes("status=" & msg)

            Dim request As HttpWebRequest = CType(WebRequest.Create("http://twitter.com/statuses/update.xml"), HttpWebRequest)
            request.Credentials = New System.Net.NetworkCredential(username, password)
            request.Method = "POST"
            request.ContentType = "application/x-www-form-urlencoded"
            request.ContentLength = bytes.Length

            Dim reqStream As Stream = request.GetRequestStream()
            reqStream.Write(bytes, 0, bytes.Length)
            reqStream.Close()

            Dim response As HttpWebResponse = request.GetResponse
            Dim reader As New System.IO.StreamReader(response.GetResponseStream)

            Dim retValue As String = reader.ReadToEnd()
            reader.Close()

            Return ""
        Catch ex As Exception
            Return "error"
        End Try
    End Function
End Class

25) That about does it. Build the solution and make sure things look good there. Then, run it and see what happens.

BTW: Follow me:

http://painjunkie.spaces.live.com/

http://twitter.com/johnrdurant

Smart Tags Development Resources:

Content I have written

Office Developer Center (just search for ‘Smart Tags’ there and you’ll see pretty good links)