A review of Add-in Express

A while ago I started transitioning from VBA to VB.NET again, attempting to build a tool to manipulate the new Power Pivot components in Excel 2013. While I was able to get part way to a working solution using Visual Studio 2012 and VSTO, I ran into two key issues:

1. VSTO seems to be “bit” specific, meaning that I would need to keep one version of the solution for 32 bit versions of Office, and another for 64 bit versions, and

2. While I could run my code, the Power Pivot engine would crash on me unless I opened Power Pivot BEFORE I ran any of my code.

Both of these issues were rather severe to me, as I didn’t want to maintain multiple versions of the same code, nor could I release something and expect users to open Power Pivot before running my project. A friend of mine suggested I try Add-in Express to deal with these issues.

In the spirit of full disclosure, I contacted them and asked if they had a trial version. They don’t, but offered to let me trial it if I’d blog on my experiences. I agreed to do that, and what follows is my honest observations of the software.

I do want to preface that, like my friend Rob Collie, I am not a “Read the manual” kind of guy. Ironic, since I write a lot of material, but I take the approach of diving in, and hitting Bing for a quick pointer when I get stuck. It usually causes me a lot of pain, but I tend to learn better that way.

So, here’s how it all came together for me…

When you install Add-in Express, you get a new set of templates. I started my project by creating a new ADX COM Add-in targeting the .NET Framework 2.0. (For those not in the know, in order to target Excel 2010 with VSTO, you need to use .NET 4.0 and Excel 2013 is .NET 4.5. So .NET 2.0 is way too early a framework to have ever even heard of Power Pivot!)

adx

From there you pretty much follow the prompts through the setup. I called it “Connect”, I set the minimum supported version to Office 2010 (since PowerPivot didn’t exist in 2007), I chose a Visual Basic Project, I selected Excel, and I left the rest of the settings at their defaults. Pretty easy, that part.

Once I had a project to work with, I created a new VB Module, just like normal, and built the code I would need to refresh my PowerPivot model and all the PivotTables. (Be aware that, for simplicity of the post, this is 2013 specific code, and will not work with Excel 2010.)

The key piece in this is making sure the xlApp declaration is correct, as you need to refer to the AddinModule portion in order to bind it to Add-in Express’s handlers, instead of just binding to the Interop.Excel objects. That change is what makes Add-in Express work:

[vb]Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

Public xlApp = Connect.AddinModule.CurrentInstance.ExcelApp

Public Sub RefreshPowerPivotTables()

Dim xlWorkbook As Excel.Workbook = xlApp.ActiveWorkbook

Dim ws As Excel.Worksheet

Dim pvt As Excel.PivotTable

'Attempt to connect

Try

xlWorkbook.Connections("ThisWorkbookDataModel").Refresh()

Catch ex As Exception

'Data connection could not be refreshed.

MsgBox("Sorry, but I could not refresh the model! Are you sure this workbook has one?")

Exit Sub

End Try

'Refresh all PivotTables

For Each ws In xlWorkbook.Worksheets

For Each pvt In ws.PivotTables

With pvt

.PivotCache.Refresh()

.RefreshTable()

End With

Next

Next

End Sub

End Module[/vb]

So far so good. Now I needed a user interface.

To be fair, it took me a bit to figure this one out. Once I finally realized that you need to open the “AddinModule.vb” portion in the solution explorer, then add a Ribbon tab to the canvas, then things got easier.

Despite reading very little documentation, with a little help from the blogs and articles on the Add-in Express site when I did get stuck, I was very quickly able to build a simple UI. There’s a good article on doing this here, which I wish I’d read earlier in the process.

As I say, it’s a really simple UI: a tab called “Model”, a single group, and a button with an image on it.

One criticism I do have is that it would be nice to be able to link the buttons and other controls to their callbacks inside the visual designer. As is, it’s a bit clunky, as you have to select the designer, then choose the other controls in the properties window. It’s not totally intuitive, but once you know where to look (read the article linked to above), it is workable.

My callback code for my button (which I didn’t bother renaming) is as follows:

[vb]Private Sub AdxRibbonButton1_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles AdxRibbonButton1.OnClick

Call RefreshPowerPivotTables()

End Sub[/vb]

And with that done, it came time to debug. Again, fairly straight forward:

  • Build -> Build the project
  • Build -> Register ADX project
  • Make sure Excel is closed
  • Start the debugging engine

My “Model” tab showed up, with the command I’d built, as shown below:

onLoad

And with a single click (and a bit of a wait since Power Pivot is so slow), my Power Pivot data was refreshed, and the PivotTable updated to reflect the changes I made in my database. Notice the new customer and the new sales transactions for 8/1/2013:

onRefresh

But the best part is this:

  • The solution is deployable to both 32 bit and 64 bit Office platforms, and
  • I can open my Power Pivot project even if I run my code first, and it doesn’t crash.

I can honestly say that I fought that Power Pivot crash issue for about 2 months with VSTO, and I was really worried that it was going to kill my project completely. No amount of searching would turn up a fix, and other help calls didn’t yield any gold either; where they were answered, it was with a “don’t know” answer. Add-in Express has actually made this goal achievable.

I’ll also tell you that, while refreshing Power Pivot isn’t the focus on my full project, I have been able to use Add-in Express to successfully target and manipulate Power Pivot in both 32 and 64 bit versions of both Excel 2010 and 2013. I.e. multi version deployment with one code base. Pretty damn awesome.

I should also mention that their support has been phenomenal as well. Not only have they answered my emails, but I even ended up on a call with one of their lead people to examine why I didn’t seem able to use the debugging tools at first (a blog post for another day). 30 minutes, problem solved, and I’m good to go. Again, pretty damn awesome.

Readers of this blog will know that I don’t endorse very many products at all. Sure, I use Google Adwords and stuff, but I don’t write too many blog posts talking about how awesome a product is. Here’s my word on Add-in Express:

I’m sold. This product has been a life saver, and I won’t develop using VSTO.

Conditional Formatting Formula Inspector

David Hagar made a comment in my last blog post that I should add a userform to the code that I built there.  Since I had to use it on 40 workbooks, and figured that it could come in useful again, I did exactly that.  If you’d like to try it, you can download a copy.

It needs to unzipped and installed as an add-in, at which point you’ll end up with a CF Inspector button on the Formulas tab:

image

That takes you into the userform where you can type your text and click Find.  It will search all conditional formatting formulas in the workbook for your text, and return the list.

image

Once you select and item, it’s Applies To address and the formula will show in the boxes below so you can see them all.

It’s pretty simple, but could be useful since this kind of facility doesn’t exist in Excel by default.  Yes, it would be nice if it would do the replace as well, but given the stability issues I mentioned in the last post, I wasn’t going to try it on my workbook at this point.  Maybe in future if I added a backup facility first.

Let me know what you think!

Find and Replace In Conditional Formats

Yesterday I made a comment on twitter that I was trying to figure out if it would be faster to check the conditional formatting formulas in 40 workbooks manually to ensure they were correct, vs writing a tool to do it.  My buddy Jon Peltier tweeted back saying “Don't know about faster, but it's got to be more fun to code it.”  I thought about it, but then when I found that one workbook returned a count of 250 rules… the answer was to code it.

Well, yes, Jon was right… if your definition of fun is akin to performing home dentistry for your root canal.

The code I used to modify my conditional formats has been incredibly unreliable, and is returning different effects depending on the users’ machine that it is run on, the version of Excel, and maybe a few other things.  (It did different things after I had lunch too!)

I finally gave up on re-writing the conditional formatting rules, and ended up just running a macro to tell me which formulas I needed to edit:

[vb]Sub FindCFIssues()

    Dim ws As Worksheet
    Dim ftc As FormatCondition
    Dim sFormula As String
    Dim sFind As String
    Dim sReplace As String
    Dim lCount As Long

    On Error Resume Next
    sFind = "ops-Internal"
    sReplace = "ops_Internal"

    For Each ws In ActiveWorkbook.Worksheets
        For Each ftc In ws.UsedRange.FormatConditions
            With ftc
                sFormula = Replace(.Formula1, sFind, sReplace, 1, compare:=vbTextCompare)
                If .Formula1 <> sFormula Then
                    Debug.Print ws.Name & ": " & .AppliesTo.Address
                    lCount = lCount + 1
                End If
            End With
        Next ftc
    Next ws

    If lCount > 0 Then
        MsgBox "All done, a few issues were found", vbCritical
    Else
        MsgBox "No issues!", vbInformation
    End If
End Sub

[/vb]

Ultimately, this code searches for “ops-internal”, and flags my attention, because it should be “ops_Internal”.

Some weirdness here too… the “Next ftc” portion of the code runs until it’s gone through all the rules, then errors.  I had to add the On Error Resume Next above in order to let it continue, as it choked with just “Next” inside the other loop.

So at least I’ve got something now that quickly tells me if I have an issue, and prints out the offending rules in the VBA Immediate window.  I’d rather have the replacement done, but really don’t have the time to work through all the idiosyncrasies… err.. I mean fun… that will be involved in getting it right.