From time to time, a “Why I’m Quitting X” type of article pops up where X = social media buttons. Then it gets yakked about on Hacker News, reddit, etc. The discussions usually boil down to two things – do social buttons work, and why should I let Facebook et al. track my users?

Do Social Buttons Work?

They do for me. When I added them, referrals from social media sites went up. When I removed them, referrals went down. When I added them back, the numbers went up again. Other site owners have numbers that indicate the opposite, so the only logical answer is: test them yourself.

N.B.: I removed the buttons temporarily because I wasn’t thrilled with the idea of my visitors being tracked by social media sites. Plus, I had originally used the AddThis buttons, and they were causing page load times to increase – sometimes dramatically. I added social buttons back after creating the non-tracked versions below.

Creating Non-Tracked Share Buttons

Social Share Privacy allows user opt-in, but that is a click-twice solution. Other sites list non-JavaScript share URL’s, which are fine if you always link to the same page on your site. What I wanted, though, was a mixed implementation. I wanted the social buttons on all pages so the site design is consistent. On “share worthy” pages, the social media buttons needed the share URL to match the visitors current page, but on boring pages (about, privacy policy, etc) the default share URL would be to the home page.

Since JavaScript itself wasn’t the problem (the external scripts were), I cobbled together the following:

The HTML

<div class="social_buttons">
  <ul>
    <li>
      <a id="social_link_twitter"
          href="http://twitter.com/intent/tweet?url=http://www.example.com&text=Default%20Text"
          target="_blank">
      <img alt="twitter" src="/images/twitter.png" /></a>
    </li>
    <li>
      <a id="social_link_linkedin"
          href="http://www.linkedin.com/shareArticle?mini=true&url=http%3A%2F%2Fwww.example.com"
          target="_blank">
      <img alt="linkedin" src="/images/linkedin.png" /></a>
    </li>
    <li>
      <a id="social_link_facebook"
          href="http://facebook.com/sharer.php?u=http://www.example.com"
          target="_blank">
      <img alt="facebook" src="/images/facebook.png" /></a>
    </li>
    <li>
      <a id="social_link_gplus"
          href="https://plus.google.com/share?url=http%3A%2F%2Fwww.example.com"
          target="_blank">
      <img alt="google+" src="/images/gplus.png" /></a>
    </li>
    <li>
      <a id="social_link_email"
          href="mailto:?Subject=Default%20Text&Body=http://www.example.com">
      <img alt="email" src="/images/email.png" /></a>
    </li>
  </ul>
</div>

The CSS

.social_buttons {
    position:absolute;
    top:0;
    right:-10px;
    z-index:300;
    width:220px;
}

.social_buttons ul {
    overflow:hidden;
    margin:40px auto 0;
    padding:0 0 5px;
    width:100%;
    text-align:center;
}

.social_buttons ul li {
    display:inline-block;
    margin:5px 3px 3px 4px;
    list-style:none;
    text-align:center;
}

.social_buttons ul li a {
  display:block;
}

.social_buttons ul li img {
    width:30px;
    height:30px;
    margin:0 auto;
}

.social_buttons ul li span {
    display:block;
    margin-top:10px;
}

The Script:

$(document).ready(function() {
  var pageTitle = $(document).attr('title');
  pageTitle = encodeURIComponent(pageTitle);
  var pageUrl = window.location;
  var encodedPageUrl = encodeURIComponent(pageUrl);
  var twitterShare = "http://twitter.com/intent/tweet?url=" + pageUrl + "&text=" + pageTitle;
  var linkedInShare = "http://www.linkedin.com/shareArticle?mini=true&url=" + encodedPageUrl + "&title=" + pageTitle;
  var facebookShare = "http://facebook.com/sharer.php?u=" + pageUrl;
  var googlePlusShare = "https://plus.google.com/share?url=" + encodedPageUrl;
  var emailShare = "mailto:?Subject=" + pageTitle + "&Body=" + pageUrl;
  if($('#social_link_twitter').length > 0) {
    $('#social_link_twitter').attr('href', twitterShare);
  }
  if($('#social_link_linkedin').length > 0) {
    $('#social_link_linkedin').attr('href', linkedInShare);
  }
  if($('#social_link_facebook').length > 0) {
    $('#social_link_facebook').attr('href', facebookShare);
  }
  if($('#social_link_gplus').length > 0) {
    $('#social_link_gplus').attr('href', googlePlusShare);
  }
  if($('#social_link_email').length > 0) {
    $('#social_link_email').attr('href', emailShare);
  }
});

The Implementation

This option is nice because it’s relatively easy to set up on your site, it loads more quickly than external script calls, and it protects your users’ privacy – all while facilitating social sharing. What’s not to love?

The BreezeTree website uses a commercial theme, so I’m not at liberty to provide the social icon images. However, a quick Google search will provide you with plenty of options.

What About Blog Posts?

You might be looking at this post n the blog home page and wondering where the social buttons are. With this blog, I’ve only added them to the post pages. If you are going to add them to your WordPress or other blog, you’ll need to do a lot of digging around your theme and determine where to put the html, css, and js. Since the javascript above requires jQuery and many blog systems such as WordPress run in No Conflict Mode, you will need to replace “$(document).ready” with “jQuery(document).ready”.

We are happy to announce the release of Spreadspeed. Spreadspeed is a set of productivity utilities and auditing tools for Microsoft Excel. Spreadspeed comes in a standard edition as well as an Auditor edition. As the name implies, the Spreadspeed Auditor features advanced spreadsheet auditing tools. The complete product details are available on the Spreadspeed product page, but a quick list of features is as follows:

  • Xplorer – navigate the worksheets, charts, tables, and named ranges in all the open workbooks.
  • Save a workbook, worksheet, or selected range to your favorites collection
  • Save the formula in the current cell to your favorite formulas collection
  • Create table of contents
  • Sort worksheet tabs
  • Add multiple worksheets
  • Remove empty worksheets
  • Quick hide / unhide sheets
  • Save worksheets to separate files
  • Close and re-open last save
  • Save a backup copy of workbook
  • Quick protect
  • Quick unprotect
  • Delete blank rows in worksheet
  • Delete blank rows in selected range
  • Delete blank columns in worksheet
  • Delete blank columns in selected range
  • Autofit rows and columns
  • Reset UsedRange
  • Split single column into multiple columns
  • Split single row into multiple rows
  • Concatenate columns
  • Concatenate rows
  • Transpose in place
  • Swap ranges
  • Select shapes by type
  • Group all shapes
  • Ungroup all shapes
  • Extract text from shapes
  • Advanced trim tool
  • Trim left
  • Trim right
  • Trim left and right
  • Trim left, right, and double spaces
  • Append / prepend text
  • UPPER CASE
  • lower case
  • Sentence case
  • Title Case
  • Clean text
  • Clear empty strings
  • Fill range with random numbers
  • Math operations in place
  • Negate selected values
  • Convert text to numbers
  • Round to 0 decimal places
  • Round to 2 decimal places
  • Fill a range with random dates from a uniform or normal distribution
  • Convert dates stored as text into excel date values
  • Converts all the dates on a worksheet to the user specified date format
  • Extract the subtotals from a range (without the grouping of the built-in excel subtotal tool)
  • Calculate the sum for a range and generates a percentage and (optional) cumulative percentage column
  • Generate the descriptive statistics for a range
  • Remove repeating data in a column or row, leaving only the first item.
  • Fill gaps in a column or row with repeating data
  • Perform randomized sorting on a range
  • Export a user-specified chart to an image file
  • Export a user-specified range to an image file
  • Export a user-specified range of shapes to an image file
  • Save a range to a comma separated (csv) or tab delimited (txt) file
  • Create an HTML unordered list (ul) from a user-specified range
  • Create an HTML ordered list (ol) from a user-specified range
  • Create an HTML table from a user-specified range
  • Reset all cell styles
  • Delete unused custom cell styles
  • Selectively clear formats
  • Clear excess formatting
  • Convert merged cells to center across selection
  • Find inconsistent formulas in columns
  • Find inconsistent formulas in rows
  • Create dropdown validation list
  • Convert formulas to values
  • Delete all comments in workbook
  • Delete all comments in current worksheet
  • Remove user name from comments
  • Hyperlinks navigator
  • Remove hyperlinks in current worksheet
  • Remove hyperlinks in selected range
  • Convert hyperlink() formulas to standard hyperlinks
  • Display and navigate the precedents and dependents in the active sheet
  • Compare worksheets for content, formulas, number / date formatting, and cell styles
  • Style the worksheet cells by content type to create a visual audit
  • Worksheet comparison report
  • Generate master report
  • Formulas report
  • Cell errors report
  • Circular references report
  • External formula references report
  • Comments report
  • Hyperlinks report
  • Named ranges report

Working on my latest Excel add-in, I’ve spent a lot of time on researching bits and pieces of the Excel object model and viewing code samples on sites like MSDN, StackOverflow, et al. I have noticed a handful of mistakes repeated across the web. While not trying to claim a high level of expertise, I do hope the following list of common mistakes is helpful to the next lucky soul who has to program against Excel with C#.

Note: For simplicity, all the code references below assume that XLApp is your reference to the Excel application.

Always Release COM Objects

Each time you reference an Excel or Office object you need to release it when done. This can be an absolute pain, but it is necessary because failure to do so can lead to runtime errors that may not materialize when testing.

using System.Runtime.InteropServices;
if (comObject != null)
{
    Marshal.ReleaseComObject(comObject);
    comObject = null;
}

Obviously writing this over and over again can be tedious, so code snippets are your friend. Personally, I wrap this in a utility method called MRCO, which I insert via a snippet. I will reference MRCO in the code below. If you’re not sure whether the particular object is a COM object, you can test it with  Marshal.IsComObject(object).

The 2-Dot Rule

Another common COM object issue is implicitly creating COM objects by accessing items several levels deep. For example, the following line …

XLApp.Workbooks[1].Sheets[1].get_Range(“A1”, Type.Missing).Value2 = “some text”;

… implicitly creates the following COM objects:

  • Workbooks
  • Workbook
  • Sheets
  • Worksheet
  • Range

The proper way to handle this would be the following code:

Excel.Workbooks books = XLApp.Workbooks;
Excel.Workbook book = books[1];
Excel.Sheets sheets = book.Sheets;
Excel.Worksheet sheet = sheets[1] as Excel.Worksheet;

// Excel.Sheets can include both Worksheet and Chart objects
if (sheet != null)
{
    Excel.Range range = sheet.get_Range(“A1”, Type.Missing);
    range.Value2 = “some text”;
    MRCO(range);
    MRCO(sheet);
}

MRCO(sheets);
MRCO(book);
MRCO(books);

Note: I commonly see the 2 Dot Rule violated in VSTO forum posts. I don’t know enough about VSTO to know whether it internally manages COM object lifetime for the developer.

Do Not Use foreach() over COM Objects

This is an oft-repeated bit of advice that I’ve read in forums, but I can’t find a technical reference on it. I do know from firsthand experience, though, that using foreach over a range caused utter failure. The correct way to iterate over a collection of COM objects is to get the collection count and use a for loop and index.

Unfortunately, the index accessors are not consistent across Excel objects. For example, here is some code to iterate through the Sheets in a Workbook (given that sheets has already been assigned):

for (int i = 1; i <= sheets.Count; i++)
{
    Excel.Worksheet sheet = sheets[i] as Excel.Worksheet;
    if (sheet != null)
    {
        // do something
    }
}

In the code above the sheets object has a standard [] index accessor. But be aware that some items (like the Shapes class sample below) have Item() or get_Item() accessors.

Excel.Shapes shapes = sheet.Shapes;
for (int i = 1; i <= shapes.Count; i++)
{
    Excel.Shape shape = shapes.Item(i);
    // do something
    MRCO(shape);
}
MRCO(shapes);

 

Adapt to 1-Based Indexes

As the code above shows, most object collections in Excel are 1 based instead of 0. For most programmers that alone can throw you off, but in my own experience the subtle bug to watch out for would be accidentally using something like i < shapes.Count instead of i <= shapes.Count

Test if Range is Single Cell

There are several range functions that can cause problems if you attempt to perform them on a single cell range, including get_Value() and some of the border properties of Range.Interior. Before accessing these, check whether the range is a single cell:

if (range.Count == 1)
  string value = range.Value2 != null ? range.Value2.ToString() : "";
else
  object[,] values = (object[,])range.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);

 

Speeding Up Large Updates

When doing a significant amount of changes to a worksheet, changing a few Excel application settings can speed up operations significantly.

public void UpdatingExampleMethod()
{
    try
    {
        XLApp.ScreenUpdating = false;
        XLApp.Calculation = Excel.XlCalculation.xlCalculationManual;
        XLApp.EnableEvents = false;
        XLApp.DisplayStatusBar = false;
        XLApp.PrintCommunication = false;    // Excel 2010+ only

        // perform actions on worksheet
    }
    catch (Exception ex)
    {
        // handle exception
    }
    finally
    {
        XLApp.ScreenUpdating = true;
        XLApp.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
        XLApp.EnableEvents = true;
        XLApp.DisplayStatusBar = true;
        XLApp.PrintCommunication = true;    // Excel 2010+ only
    }
}

 

Using Range.Value, Range.Value2, and Range.Text

Range.Value is essentially deprecated and should be replaced by Range.Value2 in most cases. Range.Value converts the underlying numeric value into a date representation, whereas Value2 does not, making Value2 faster. Range.Text returns the string as it is displayed in the cell. If you know that a range will only contain numbers or text, you can iterate over it grabbing the values as follows:

if (range.Value2 is String)
  // get string value
else if (range.Value2 is Double)
  // get double value
else
  // cell.Value2 == null

When changing a large amount of data, common advice is to use the range.get_Value() function to retrieve an object[,] array of the content, operate on the content, and then use set_Value() to update the range. However, I have found there are cases when using Value2 is faster, so if you have performance concerns, it’s always worth testing the both methods.

Update: the code below is utility function that I use frequently when testing whether I can perform certain actions on it:

public static CellType GetCellType(Excel.Range cell)
{
    CellType cellType = CellType.Text;
    if (Convert.ToBoolean(cell.HasFormula))
        cellType = CellType.Formula;
    else if (cell.Value2 == null)
        cellType = CellType.Blank;
    else if (cell.Value is double || cell.value is decimal)
        cellType = CellType.Number;
    else if (cell.Value2 is double)
        cellType = CellType.Date;
    return cellType;
}
// related enum
public enum CellType
{
    Blank,
    Date,
    Formula,
    Number,
    Text
}

Handle Culture Issues

Excel is culture dependent, so when accessing Excel objects, you should be careful to set and restore the CurrentCulture. If you don’t, this can cause an “Old format or invalid type library” error.

using System.Globalization;
using System.Threading;
public void SomeMethod()
{
    CultureInfo baseCulture = Thread.CurrentThread.CurrentCulture;
    Thread.CurrentThread.CurrentCulture = new CultureInfo(XLApp.LanguageSettings.LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI));
    // do stuff
    Thread.CurrentThread.CurrentCulture = baseCulture;
}

Also, you can run into trouble when passing string literals such as with Range.Formula or Range.FormulaR1C1. The best course of action is to call these using reflection, as shown in the code samples in this MSDN article: Using Reflection for COM Interop Calls

Wrap Up

Those are the major things I have seen, but of course, when I look back at my code of today a year from now, I’m sure I’ll spot some more!

BreezeTree sells one Excel add-in and another is targeted for release later this year. Despite that, I don’t know that I would recommend getting into this market. Many developers may point to the dwindling influence of Microsoft in the software world, competing online office suites, and the rise of mobile and tablets. Don’t kid yourself. The Microsoft desktop market is still huge, and Microsoft Office still has hundreds of millions of users.

Since the release of Office 2013 and Office 365, I’ve been asked if FlowBreeze supports Office 365. Talking to customers, many of them are left confused by what difference is between the two. (In short, Office 2013 is the desktop installation, and Office 365 augments your desktop version with cloud based storage and web based editing.) It is clear that Microsoft would like to move to a subscription-based, recurring revenue model, but I’m not convinced the user base will follow. So I’m taking a step back to consider whether there is a viable future in Office add-in development.

COM vs. VBA vs Apps for Office

FlowBreeze is a COM add-in. These are Windows-only and allow you to create add-ins using any programming language that can interface with COM. Most COM add-in developers use Visual Studio (with C#, VB.Net, or C++) and create the UI elements with Winforms or WPF. I’ve also seen some nice add-ins developed in Python and Delphi.

[Microsoft itself doesn’t push pure COM add-in development. Instead, they promote VSTO (Visual Studio Tools for Office), which are also COM based. I won’t cover VSTO because, frankly, I don’t know much about it. It always struck me as a tool set for developing in-house, enterprise-level add-ins for Office.]

By “Windows-only” I mean that COM add-ins will work in the desktop Office environment, but will not be accessible when editing a document with the web interface. If you want to target Office for Mac, you would need to develop in Visual Basic for Applications (VBA). If it weren’t for the Mac aspect, I wouldn’t even bother covering VBA, but since cross-platform support is relevant, I will cover it a bit. VBA was introduced back in 1993, and the language kept sync with old school Visual Basic. After VB6’s release back in 1998, the only notable change to VBA was the Long data types to support 64-bit Excel in VBA7. At the same time, the 64-bit version dropped support for the common controls in MSComCtl and MSComCt2 (which was a step backward for Windows development but those controls weren’t cross platform anyhow). VBA has always been limited, and because the form building tools haven’t been updated in the last 15 years, I don’t consider VBA to be viable for commercial add-in development, especially where a polished UI is desired.

To allow developers to target both the desktop and web-based versions of Office applications, Microsoft introduced Apps for Office, which are written using HTML5, XML, CSS3, and JavaScript. Currently, the API’s are very limited in what they can access and only target Excel 2013 and Outlook 2013. They are so limited that the Apps for Office Store is basically a wasteland. Microsoft has developed a reputation for a yoyo-like introduction of technologies in recent years, pushing them out and then pulling back active development and support. They have not provided a clear roadmap as to what’s in store for the future of Apps for Office development. I believe they are committed to it in the long run, but given the initial offering, it doesn’t look like a top priority for Microsoft.

From Leader to Follower to ?

When Google started dominating search, Microsoft followed suit and threw resources at MSN, then Live, and now Bing. Apple had the iPod, and Microsoft launched the Zune. When Apple started dominating the smart phone market, Microsoft again pursued, bringing up the market rear. After the iPad was released, the mess that is Windows 8 was conceived. And it’s not just that they were followers. Google followed Apple into the smart phone and tablet markets and is now a strong competitor in market share. Microsoft’s effort is lackluster by comparison.

When you think that forcing a tablet interface on desktop users is a good idea, maybe it’s time to step back and look at what your core strengths are. Over the past decade, Microsoft has gone from a leader to a follower, chasing whatever technology other tech giants are succeeding with. For Microsoft, two of their big cash cows are Windows and Office. To milk the Office cow further (try not to form a mental image), they are moving to a subscription service with Office 365, but I think they risk weakening their position on two fronts.

First, they recently announced Office 365 for iOS. The smart thing that they did is to make it available for Office 2013 users only. If that requirement is dropped in the future, they risk commoditizing Windows as just another operating system capable of running their cloud-based Office applications.

Second, they do not provide a consistent and fully-capable add-in development system across all environments. Ultimately, anything that lets users leverage even more functionality out of Office applications helps Microsoft and cements their market position.

Software Ecosystems

Google, Apple, and Microsoft each have ecosystems that developers can leverage to make money. Although Apple has a steady ~10% of the computer market, their core strength is the mobile / tablet space, so most developers target their App Store. Apple gets a 30% cut. Google is tech-driven advertising company with a rising mobile / tablet decision, so developers can leverage their ecosystem by buying ads to sell their software, displaying ads within their software, or by selling their apps through the Google Play Store. Obviously, Google makes money off the ads, and they charge a 30% fee for Google Play transactions. So, in short, both Apple and Google have engineered ecosystems that allow them to get a cut of the revenue.

In an attempt to establish their own app marketplace, Microsoft offers two marketing channels for developers – the Windows Store and the Office Store. Neither has succeeded yet.

Ironically, given Microsoft tenuous relationship with OSS segments of the developer world, Microsoft has one of the most open ecosystems for creating and selling software. Other than having to pay (rent-seeking) Authenticode certificate providers, developers are free to create and sell their Windows desktop software directly to customers without paying a 30% transaction fee. The same can be said for non-“Office App” add-ins.

I realize the ridiculousness of a lone developer trying to offer advice to a behemoth like Microsoft, but (ask my wife) I’ve never been one to back away from a futile argument.

I believe that if Microsoft wants to continue their office suite dominance for another decade or two, they need to provide users with reasons not to switch to Google Docs, Zoho, or one of the Open Office variants. The Office applications – especially Outlook, Excel, and Word – are more than just applications. They are extensible development platforms that ISV’s leverage to provide additional utility for end users. They more utility they offer, the more likely it is that customers are anchored to Office. In order to take advantage of that, Microsoft needs an Office add-in development model that provides a full-featured API. I mean a really full-featured API. The tools need to let you develop add-ins that will work on the desktop, the web, and across platforms. And as long as I’m asking Santa for a pony, I will throw in that they need to provide backward compatibility for Office 2010, which has the lion’s share of the Microsoft Office user base. (Except for the Office 2010 support), they are headed in that direction with Office Apps but are nowhere near there yet.

tl;dr: Add-ins extend the functionality of Office. Added functionality anchors your user base. Therefore, Microsoft needs to strengthen its add-in development tools to enable developers to extend Office functionality. In the meantime, I’m sticking with COM.

 

References

This post was inspired by two recent blog posts that I read:

[1] http://www.add-in-express.com/creating-addins-blog/2013/03/04/office-2013-for-sale-office-app-store-already-dead/

[2] http://blogs.msdn.com/b/officeapps/archive/2013/06/18/roadmap-for-apps-for-office-vsto-and-vba.aspx

Back in February, I watched Rob Walling’s talk from MicroConf 2012. Most of the talks were interesting, but the one by Rob was my personal favorite. It’s a great talk, and I highly recommend watching it. One of the things that Rob touched upon was “Flywheel Marketing”. Like its namesake, the concept is that you put the effort in up front, and like a flywheel, it keeps on going from its own momentum.

I’ve never had great success running AdWords campaigns. The CPC is just too high for terms related to business diagramming. And, like Andy Brice’s recent article touched on, the CPC can be frustratingly high even for phrases that have no competition (a la CDC, perhaps CPC stands for Center for Profit Control). And lets face it, a flowcharting add-in for Excel isn’t the kind of software that TechCrunch is going to cover, or people will buzz about on social media sites. So for FlowBreeze, that leaves article writing as a straightforward marketing channel.

What I Do and Don’t Mean by “Article”

When I refer to article writing, I mean producing quality content that helps people who are looking for information. I do not mean something from a cheap outsourcing site. What may surprise some people, though, is that I do not mean blogging.

Blogging is ephemeral. To give an example, a number of years ago, Joel on Software referenced an en brosse haircut. Having no idea what that was, I Googled it. The number one result was Joel’s own blog post. Out of curiosity, I searched for it again a day later. It had dropped a few spots. A week later, it was still on the first page, but near the bottom. Checking again now, I can’t find Joel’s post in the top 100 for en brosse. Blog posts drop off your front page and and drift deeper into your site’s hierarchy. Unless your blog has a lot of traction and a post gets a lot of links, blog posts don’t seem to have the staying power of other written material. Plus, blogging always struck me as requiring too much effort to develop a repeat audience for garnering links. That’s fine if that’s your goal, but you have to be honest with yourself.

Getting Discouraged, Then Inspired

Word article pageviews
How to Flowchart in Word: 0 to 11k / month

OK, so I’ve talked about article writing, but how much have I actually done myself? Sadly, not as much as I should. I also hadn’t been paying too much attention to the analytics for my existing article pages.

After watching Rob’s video, I did a review of all my written content. I noticed that I netted a nice amount of sales from visitors who had landed on an article I was not expecting conversions from. The unique pageviews chart for the article is above. As you can see, it’s a long term game, and if you’re not targeting a link giving audience, you need to have patience. In fact, you need more patience than I had had.

This website benefited early on from a handful of articles I wrote when the site first launched. In the last part of 2011, I started writing again to spur more traffic and sales. Looking at the numbers for the first six months after publishing the fall 2011 block of articles, it just didn’t look like the magic was going to happen again. The total traffic for all the BreezeTree article pages was going down! I became discouraged and decided to put my efforts elsewhere.

So a year later, when I decided to really delve into my analytics data, I discovered what the problem was. It’s too easy to think that your website exists in a vacuum. The traffic for the fall 2011 articles had risen steadily over the course of the year, while the traffic for some of the older articles had fallen off. Some off the content had gotten stale, but the bigger issue was that there was more high quality content to compete with in my niche. It turns out that flywheel is not a perfect metaphor – there is maintenance.

(BTW, getting aggregate data in Google Analytics is simple, and getting detailed data on a single page isn’t too bad. But when you want detailed, non-aggregate data on a large number of pages, it’s an absolute pain in the ass.)

Motivated by the numbers and inspired by Rob Walling’s video, I decided to step it up and work on some more articles.

A Few Simple Rules

As the amount of content on this blog attests to, I am not an prolific writer. So before writing the next block of articles, I decided to set out some ground rules:

  • Stick to “How To” and “What is … ?” topics – reference pieces that hold up for a length of time.
  • Write on topics related to my product.
  • Write a mix of pieces with high traffic / broad appeal and lower traffic / niche appeal.
  • Allot a block of time everyday for a week to write articles. Repeat when the muse strikes.

Odds are you are not going to find 100 things to write about related to your product, so it’s important to stress quality over quantity. After exhausting the easy ideas, I circled the periphery, via brainstorming or by using Google’s latent semantic indexing search to find related terms (search key phrases with a ~ in front of keywords). I came up with a grand total of (drum roll) 18 article ideas. Coming up with good ideas for articles is hard. Writing them is harder. I allotted 2-3 hours / day for one week and managed to write only 7 in that span.

The last bullet point above may be contrary to other advice that you read about writing frequently. I could have set a goal like that, but I know myself well enough to set the bar pretty low.

7 Articles – 3 Months of Visitor Data

3 Months Later

As you can see from the chart on the right, most of the articles have slowly been gaining traction, and one has shot up above the others. Success, right? Well, not immediately. To date, the articles have lead to no traceable sales conversions (according to Google Analytics page value metric, which has its limitations).

Again, this post is about Flywheel Marketing. The goal of writing articles is to put in a nominal amount of effort that has long term payoff. Three months is an reasonable checkpoint, but I may not know if the time spent writing will pay off for another year. Direct sales conversions are the optimal payoff, but they aren’t the only goal. Another goal is to establish your site as an authoritative reference for a given genre.

I imagine someone with a high traffic site might think the graph to the right is abysmal. But when I spent only two hours writing an article about an obscure topic like Nassi-Shneiderman Diagrams, and it is trending towards an additional 2000 visitors / year to my site, it's a nice motivation towards more article writing.

One More Thing to Consider

If you use Google AdWords or other advertising channels, you can opt to show your ads on content networks. When you build up a body of article pages, you are creating a mini content network of your own. It’s a great testing ground for your ad content. I put inline product banners in all the BreezeTree articles and A/B test headlines, content, styles, and buttons. The product banners account for a decent number of FlowBreeze downloads every month – enough that, just thinking about how much it would cost to get the same results through advertising, makes me want to hide my wallet.

Update

I stumbled across semrush.com, which provides traffic and advertising estimation data for websites. Like most of these tools, I’m sure their traffic estimations are a bit off, but they do provide one interesting metric: SE traffic price. It’s the estimated amount that you would need to pay each month to get the same number of visitors through ads that you do through organic traffic. For breezetree.com, the estimate is $21,764 / month. Of course, you would need to multiply that by the percentage of article readers who click through to your product page, but it’s still a pretty convincing reason to develop content for your site.

With the release of version 3.1, FlowBreeze now includes Value Stream Mapping tools. FlowBreeze has two tools that simplify creating Value Stream Maps – the VSM Template Generator and the VSM Symbols task pane. The VSM tools augment the existing text-to-flowchart capabilities of FlowBreeze, and are an added feature for no extra cost – an extremely good value. Plus, FlowBreeze is a Microsoft Excel add-in, so the resulting diagrams can be viewed by and shared with anyone who uses Excel.

VSM Template Generator

The template generator is a way to kick start diagrams by allowing you to make re-usable Excel template files with the base outline on a value stream map. You can set a standard title bar, and add base elements, including supplier, customer, production control, process blocks, and data timelines. Below is an example of a value stream mapping outline created with the template generator.

basic VSM template

VSM Symbols Task Pane

The task pane, shown below, allows you to add symbols two ways: 1) Selecting the symbol, entering shape-specific data, then clicking Add Shape, or 2) by simply doubling-clicking the symbol. Symbols are added to the worksheet in the currently selected cell.

VSM task pane

Available Value Stream Mapping Symbols

  • Process
  • U-Cell
  • Operator
  • Factory (Customer / Supplier)
  • Production Control
  • Schedule
  • Go See
  • Shipment Arrow
  • Push Arrow
  • Manual Information Arrow
  • Electronic Information
  • FIFO lane
  • Load Leveling
  • Kaizen Burst
  • Production Kanban
  • Batch Production Kanban
  • Withdrawal Kanban
  • Batch Withdrawal Kanban
  • Kanban Post
  • Supermarket
  • Buffer
  • Inventory
  • Signal Kanban
  • Physical Pull
  • Sequenced Pull
  • Pull Arrow 1
  • Pull Arrow 2
  • Pull Arrow 3
  • Pull Arrow 4
  • Truck
  • Forklift
  • Airplane
  • Boat
  • Non Value-Added Time
  • Value Added Time
  • Timeline Total
  • Data Table

For more information, visit the FlowBreeze Value Stream Mapping product page. Or, you can go to the product page and download a free trial today.

During the development of FlowBreeze 3.0, I wasted a lot of time mucking around with different approaches to emulating the RefEdit control from C#. If you’re not familiar with it, the RefEdit control looks something like this on a typical Excel form:

(btw, this is actually the after image. the range address would not be present until after the step in the next image.)

Clicking it, you can then select the range with your mouse, as such:

Looking around the net, I found two viable options that would work in C#. One was Gabhan Berry’s article How to code a .NET RefEdit Control. This is a nicely done control, but unfortunately it had two major drawbacks. First, it relied upon a DLL he had created to make working with Excel easier. Second, it was written from the perspective of someone working with Excel externally, whereas I was building a COM add-in. I try to avoid distributing third party libraries where possible – especially if I have to read through the license terms and make sure that I am allowed to distribute the library with a commercial application. The second issue isn’t a show stopper, but it was going to take a decent amount of code re-writing to get it to work with my application.

The other control I came across was RefEdit Emulation for .NET on Code Project. Like Gabhan’s control, this provides a range selector with a small footprint. The major down side, though, was that it only works for non-modal parent forms. This control may be usable on a modal form with a little more work, but by this time I had spent way more time trying to create a range selector then I had originally planned.

Application.InputBox to the Rescue

The old standby alternative to the RefEdit control has been the Application.InputBox() method. In this case, Application refers to the Excel application reference. Instead of the RefEdit range selector with the small footprint, you get a larger forms that looks like this:

The other drawback to the InputBox is that the parent control doesn’t automatically minimize. If you hide the parent before calling InputBox(), the range selector will not become the topmost form and receive focus. In order to activate the range selector, the user would have to notice the flashing menu item on their task bar. The same problem exists if you try to set the parent form’s window state to minimized.

In order to deal with this, I created a little utility function to resize the parent form to the minimum window size, and then “hide” the parent at the base of the Excel window, looking something like this:

Here is the sample code, including a calling button click event, the utility functions to return either the Excel.Range or the range address, as well as an all-purpose method to release COM objects. Due to the general suckiness of this blog format, you will probably need to copy and paste this into an editor to see the full width of the code.

#region ---- Select range ----------------------------------------

private void btnSelectRange_Click(object sender, EventArgs e)
{
    string prompt = "Select the range";
    string title = "Select Range";
    try
    {
        string address = Utilities.PromptForRangeAddress(this, title, prompt);
        if (!String.IsNullOrEmpty(address))
        {
            txtBaseShapeCell.Text = address;
        }
    }
    catch
    {
        MessageBox.Show("An error occurred when selecting the range.", "Range Error");
    }
}

#endregion

#region ---- static utility methods ----------------------------------------
// Requires:
// using System.Runtime.InteropServices;
// using System.Windows.Forms;

DllImport("user32.dll")]
[return: MarshalAs(UnmanagedType.Bool)]
private static extern bool GetWindowRect(IntPtr hWnd, out RECT lpRect);

[StructLayout(LayoutKind.Sequential)]
private struct RECT
{
    public int Left;
    public int Top;
    public int Right;
    public int Bottom;
}

public static string PromptForRangeAddress(Form form, string title, string prompt)
{
    Size windowSize = form.Size;
    form.Size = SystemInformation.MinimumWindowSize;
    Point location = form.Location;
    SetFormPositionForInputBox(form);

    string rangeAddress = string.Empty;
    Excel.Range range = null;
    try
    {
        range = XL.App.InputBox(prompt, title, Type.Missing, Type.Missing, 
                                Type.Missing, Type.Missing, Type.Missing, 8 ) as Excel.Range;
        if (range != null)
            rangeAddress = range.get_AddressLocal(Office.MsoTriState.msoFalse, 
                                                  Office.MsoTriState.msoFalse, 
                                                  Excel.XlReferenceStyle.xlA1, 
                                                  Office.MsoTriState.msoFalse, 
                                                  Type.Missing);
    }
    catch
    {
        throw new Exception("An error occured when selecting the range.");
    }
    finally
    {
        form.Location = location;
        form.Size = windowSize;
        MRCO(range);
    }
    return rangeAddress;
}

public static Excel.Range PromptForRange(Form form, string title, string prompt)
{
    Size windowSize = form.Size;
    Point location = form.Location;
    form.Size = SystemInformation.MinimumWindowSize;
    SetFormPositionForInputBox(form);

    Excel.Range range = null;
    try
    {
        range = XL.App.InputBox(prompt, title, Type.Missing, Type.Missing, 
                                Type.Missing, Type.Missing, Type.Missing, 8  ) as Excel.Range;
    }
    catch
    {
        throw new Exception("An error occured when selecting the range.");
    }
    finally
    {
        form.Location = location;
        form.Size = windowSize;
    }

    return range;
}

public static void SetFormPositionForInputBox(Form form)
{
    int x = form.Location.X;
    bool isSet = false;
    try
    {
        System.Diagnostics.Process[] processes = System.Diagnostics.Process.GetProcessesByName("Excel");
        if (processes != null && processes.Length > 0 && processes[0] != null)
        {
            RECT rect;
            IntPtr ptrXL = processes[0].MainWindowHandle;
            if (!ptrXL.Equals(IntPtr.Zero) && GetWindowRect(ptrXL, out rect))
            {
                form.Location = new Point(x, rect.Bottom - SystemInformation.MinimumWindowSize.Height);
                isSet = true;
            }
        }
    }
    finally
    {
        if (!isSet)
        {
            form.Location = new Point(x, 0);
        }
    }
}

public static void MRCO(object obj)
{
    if (obj == null) { return; }
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
    }
    catch
    {
        // ignore, cf: http://support.microsoft.com/default.aspx/kb/317109
    }
    finally
    {
        obj = null;
    }
}

#endregion

BreezeTree is happy to announce the release of FlowBreeze 3.0 – flowchart add-in for Microsoft Excel. A full list of the changes is as follows.

New Features

  • Added support for 64-bit versions of Excel.
  • Added Connectors task pane to simplifyy connector additions.
  • Added Shapes task pane to create shapes by clicking or typing.
  • Added Split Connector tool to split a connector arrow between two shapes into two labeled connector nodes.
  • Added import / export for settings backup and standardizing settings between different computers / users.
  • Added symbol list editor to allow user to specify the actively used set of symbols.
  • Simplified layout and filtering of Shape Selection Tool.
  • Added Export to Web Page (HTML) tool.
  • Added bulk shape dimension editing.
  • Added sorting options for shape selection fields.
  • Added special printing option for flowchart images.
  • Added Straighten Connectors tools to fix jagged lines in Excel 2007 / 2010.
  • Added user-defined spacing between shapes.
  • Improved internal margin settings for non-rectangular shapes (i.e.

Template Generator Improvements

  • Added logo insertion.
  • Added preset header and footer layouts.
  • Added preset swim lane titles.
  • Added SIPOC layout.

Flowchart Wizard

  • Added simple text versus advanced parsing syntax autodetection.
  • Simplified the advanced parsing syntax.
  • Improved the connector routing algorithm.
  • Added ability to overwrite existing worksheet.
  • Input range selection.

Prerequisite

  • The Microsoft .NET 2.0 framework is now a prerequisite for running FlowBreeze.

Time for a periodic update to the BreezeTree Excel version survey. The numbers are below and speak for themselves. The survey data was collected over the last six months, and the target audience was business users. Standard disclaimer: This is not a scientific poll – but without Microsoft publishing these numbers, it’s probably as good as any other estimate.

The data is below. The percentages are based on 1587 data points (otherwise known as people).

  • Excel 2010: 35%
  • Excel 2007: 57%
  • Excel 2003: 6%
  • Excel 2002: <1%
  • Excel 2000: <1%

For comparison, here is the data from the 2009 Excel version survey.

  • Excel 2007: 39%
  • Excel 2003: 46%
  • Excel 2002: 11%
  • Excel 2000: 5%

Commentary

The latest survey shows that Excel 2010 adoption has grown rapidly and 2007 usage has continued to grow (it has probably peaked by now). Meanwhile Excel 2003 usage has fallen dramatically, and Excel 2000 and 2002 (aka, XP) are looking rusty around the bits.

Another way of looking at the data:

  • Ribbon users: 92%
  • Toolbar users: 8%

For the past few years I’ve been running surveys to see what version of Microsoft Excel people are running (and by extension, which version of Office). Before getting to the latest data, the standard disclaimer applies – this polling data is not scientific. The sample sizes are decent but the user base is skewed.  The data is collected from FlowBreeze trial users, which tend to be business-oriented users, and of those, only a small percentage are polled. Also, FlowBreeze runs on Excel 2000 through 2007 on Windows only. So Mac users and Office 97 hold-outs are not represented, nor are users of Open Office and online spreadsheets.

So with that out of the way, let’s get to the data. The graph below of the last six months of 2009 shows that Excel 2007 makes up half the user base. The other half are holding out because they hate the ribbon. (Ha ha, just kidding!)

Excel Version Survey - 2nd half 2009
Excel Version Survey - 2nd half 2009

What’s striking about the most recent trend is the drop off of Excel 2000 and 2002 (XP). In the last two years, they fell from a combined 20% of users to less than 5% , and a big portion of that drop off was in the last six months. On the eve of Excel 2007’s release, Martin Green did a survey that showed Excel 2003 with just over 50% of the market, with Excel 2000 and XP making up most of the remainder. The early 2008 data points below show that Excel 2003 held the same market share after Excel 2007’s release, indicating that XL 2007 adopters had leapfrogged versions.

Excel Version Trend 2008-2009
Excel Version Trend 2008-2009

If history is a guide, after Excel 2010 is released a small portion of adopters will be Excel 2000 and XP users upgrading older PC’s, another small portion will be Excel 2007 users who like to stay on the cutting edge, but the majority should be Excel 2003 users leapfrogging versions. I say “should” because it’s hard to make a prediction. On one hand, Excel 2003 is still a great piece of software, it doesn’t look dated, and a lot of people do not like the ribbon. On the other hand, users of older versions will face increasing pressure to be able to read the new file formats (esp. those w/o the Office 2007 compatibility pack). I wouldn’t be surprised if Excel 2010 had a slow adoption rate, but it will be interesting to see.