Posts Tagged ‘Microsoft Excel’

I have played around with VSTO over the last few years but always found it to be too quirky, awkward and difficult to deploy, and so never considered using it for serious development. The version that shipped with VS 2008, and targeted Office 2007, was as buggy as Office 2007 itself. However, with the release of Visual Studio 2013 Community Edition and Office 2013, I thought it was time to give it another try.

It’s become apparent (to me, anyway) that VBA is finally going the way of the dinosaurs. Microsoft’s decision to not replace the 32-bit ActiveX controls with 64-bit versions, thus making VBA development near impossible in 64-bit Office, was the final warning that we need to “move on”. I don’t think I’m ready to make the transition to Apps for Office. Plugging HTML and Javascript-powered browser apps into my Excel workbooks still sounds a bit too experimental to me. But harnessing the full power of Visual Studio and the .Net libraries for use in my Excel applications appears to be a better bet.

It is difficult to locate any comprehensive VSTO examples on the web, so I decided to create my own in a series of posts that cover everything from the very basics to intermediate usage of code and controls. One of the difficulties I’ve faced in using publicly available samples is the “version-itis” problem. If I’m using an Express Edition instead of an Enterprise edition, or VS2010 instead of VS2012, things just don’t behave the same way (although sometimes, in theory, they should). So I think the most important thing for any “how-to” type of article is to get everyone on the same page, using the same tools. For this test run, I’ll be using:

  • Visual Studio Community 2013 Edition version: 12.0.31101.00
  • .NET version: 4.5.51650
  • Microsoft Excel 2013 (15.0.4693.1000) MSO (15.0.4693.1001) 32-bit (Part of Microsoft Office 365)

The VS 2013 Community Edition is free so there’s no financial impediment to installing the same version I have. For about $10 a month, you can sign up for Office 365 which gains you access to the web versions of Excel, Word, etc., and also allows you to install the desktop edition of Excel 2013. That’s the least expensive way to get on the same page. Note: In future examples, I’ll be using Microsoft SQL Server 2012, the free “Express” edition, but we’ll cross that bridge when we come to it).

Your version of .NET and Office may be slightly different depending on how religious you are about installing updates, but the top-level .NET version should be 4.5 and the Office version must be 2013 (15.xxx). The Visual Studio Community 2013 Edition version should be very close to identical and you must have the following extension installed:

* Microsoft Office Developer Tools for Visual Studio 2013 (November 2014 Update).

I’m currently running on Windows 8.1, but I don’t think that matters at all for purposes of this demonstration, because I’ve run the exact same thing on Windows 7 and Excel 2013 and the results are identical.

I prefer VB and will be using it for this example. If you prefer C#, hopefully you’ll be able to follow along, substituting the C# equivalents as necessary.

Once you’re sure that everything listed above is installed properly, proceed to the following simple example to confirm that everything is working properly.

1. Create new project. Visual Basic… Office/Sharepoint… Office Add-ins… Excel 2013 Add-in. I de-select the option to “Create Directory for solution” (want to keep the folder structure simple and there will be no additional projects added), and name the project “ReadyPrimeTime1”.

Create New VSTO Project

Create New VSTO Project

Your should see a new project containing a code file named “ThisAddIn.vb”. This is the only file we need to be concerned about right now, and we will leave it named as is. Your project and IDE work space should look something like this now:

VSTO Project after initial creation.

VSTO Project after initial creation.

2. Create a new Ribbon item for our add-in. We’re going to have a custom tab appear on the Ribbon and the tab will contain custom ribbon buttons and other controls. So, add a new Ribbon (Visual Designer) item to the project and name it “RibbonManager.vb”.

Add New Ribbon (Visual Designer) item

Add New Ribbon (Visual Designer) item

After the new designer is added, you should see a ribbon template appear, ready for your customizations.

Ribbon designer created and ready for edits

Ribbon designer created and ready for edits.

3. Select the new Tab by clicking where it says “TabAddIns (Built-In)” and let’s change some properties by selecting the control/item and using the Properties window:

  • Change the “(Name)” property to tabMain
  • Change the “Label” property to Our Add-in Tab.

4. Select the new Ribbon Group control by clicking where it says “Group 1”:

  • Change the “(Name)” property to “rgpFirst”.
  • Change the “Label” property to “First Group”.

5. Open the Toolbox and you should see a bunch of controls under the heading of Office Ribbon Controls. Select the ToggleButton and drag it on to “First Group”. Change the following properties:

  • Change the “(Name)” property to “tgbPane1”.
  • Change the “Label” property to “Show Pane #1”.
All Ribbon Properties Set

All Ribbon Properties Set

Save all files in the Project now before proceeding to the next step.

6. Next, we’re going to create a TaskPane that will appear along side the worksheet and will allow the user to interact with the worksheet using the controls on the TaskPane. TaskPanes are created as UserControls in VSTO. Let’s add a new UserControl object to the project and name it “FirstPaneTester.vb”.

Add UserControl for TaskPane

Add UserControl for TaskPane

Change the “Width” property of the new UserControl to “550” and the “Height” to “400”. I’m changing the height here just to make some room to place controls on the UserControl. When the program runs, the Height won’t really matter because we will be “docking” it on the left side, causing the control to fill the height of the screen. (Strangely, many of the properties, like “DockPosition”, are not available in design mode and must be set at run time).

Next, add the following standard Windows Forms controls to the UserControl and set properties:

  • Label – (Name) = “lblTitle”, Autosize = “False”, BackColor = “Black”, ForeColor = “White”, Location = “0, 0”, Width = “550”, Font Size = “12”, TextAlign = “MiddleCenter”, Text = “First Pane Test”
  • Label – (Name) = “lblSelect”, Text = “Select Message:”, Location = “40, 50”
  • ComboBox – (Name) = “cboMessage”, Location = “160, 50”, Width = “375”, Items = (see graphic below for items to manually add)
  • Button – (Name) = “btnSendMessage”, Location = “335, 90”, Width = “200”, Text = “Send Message to Sheet”
Manually entered ComboBox items

Manually entered ComboBox items using the “Items” property

Save everything now. We’ll come back to this UserControl shortly to write some code, but for now let’s return to the main Add-in module.

7. Open the “ThisAddin.vb” code module and add the following module-level variables:

'Create a new instance of our custom task pane.
Private m_tkpFirst As FirstPaneTester
'Create another custom take pane object that will handle events.
Private WithEvents m_tkpFirstValue As Microsoft.Office.Tools.CustomTaskPane

Next, add the code that will run when the Add-in is first started, or attached, to Excel. It should be entered in the ThisAddIn_Startup event block. See the code comments for details of what each line is doing.

Private Sub ThisAddIn_Startup() Handles Me.Startup
'Set the initial task pane variables and settings.

'Create a new instance of our First Task Pane object
m_tkpFirst = New FirstPaneTester()

'Add the new custom task pane instance to the Add-in's CustomTaskPanes collection.
m_tkpFirstValue = Me.CustomTaskPanes.Add(m_tkpFirst, "First Task Pane")

'Set initial visual properties of the new task pane
With m_tkpFirstValue
.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionLeft
.Width = 550
.Visible = False
End With

End Sub

Next, we must write code to handle the “VisibleChanged” event of the custom task pane object. Since we defined this object with “WithEvents”, we can handle any events that it raises. The important one we’re concerned with is the “VisibleChanged” event. This code will demonstrate how the action on the custom task pane can trigger a change on the Ribbon.

Private Sub m_tkpFirstValue_VisibleChanged(sender As Object, e As EventArgs) Handles m_tkpFirstValue.VisibleChanged

'Change the state of the Toggle Button on the ribbon to reflect the current Visibility
'of the custom task pane.
'Note: The reason we defined the m_tkpFirstValue object "WithEvents" is so we could
'capture events like this.
Globals.Ribbons.RibbonManager.tgbPane1.Checked = m_tkpFirstValue.Visible

End Sub

Code to handle raised VisibleChanged event

Code to handle raised VisibleChanged event

The last thing we want to do in the ThisAddIn.vb code module is add a Property so that external routines can create an instance of our custom task pane. It’s a read-only property so we don’t have to worry about creating a module-level instance to track state. And we don’t have to create a corresponding “Set” property.

Public ReadOnly Property FirstTaskPane() As Microsoft.Office.Tools.CustomTaskPane
'Return an instance of the "WithEvents" custom task pane variable to
'external calling routines.
Return m_tkpFirstValue
End Get
End Property

8. Open the “RibbonManager.vb” code module and add the following code:

Private Sub tgbPane1_Click(sender As Object, e As RibbonControlEventArgs) Handles tgbPane1.Click

'Change the Visibility state of the Custom Task Pane when the toggle
'button on the ribbon is clicked.
Globals.ThisAddIn.FirstTaskPane.Visible = TryCast(sender, RibbonToggleButton).Checked

End Sub

9. Finally, what to do when the user clicks the Button on the custom task pane (UserControl)? Let’s write the code to transfer the text in the ComboBox to the active worksheet. Open up the “FirstPaneTester.vb [Design]” window and double-click the “Send Message to Sheet” command button to open the code-behind window. Write the following code in the btnSendMessage_Click code block that is created:

Private Sub btnSendMessage_Click(sender As Object, e As EventArgs) Handles btnSendMessage.Click

'If a valid message has been selected from the ComboBox then update the worksheet.
If cboMessage.SelectedIndex = -1 Then
MessageBox.Show("You must select a message from the ComboBox first!")
Exit Sub
'Create a worksheet object (ws) for the current "Active" worksheet,
' and copy the text from the ComboBox to the B3 cell.
Dim ws As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
ws.Range("B3").Value = cboMessage.Text
End If

End Sub

Troubleshooting Tips:

  1. If you have trouble with the “MessageBox.Show” method, make sure you have a reference to System.Windows.Forms library by placing a “Imports System.Windows.Forms” as the very first line of the FirstPaneTest.vb code module.
  2. If you have trouble with the Excel.Worksheet reference, make sure that your project has a reference to the Excel InterOp assembly. On the main menu, click “Project”… “Add Reference…”. When the references window opens, expand “Assemblies” and click “Extensions”. Scroll down to make sure that Microsoft.Office.Interop.Excel is checked.
  3. If the new custom Task Pane will simply not appear when you click the toggle button, then it’s almost certainly a conflict of some type with another Add-in. Disable all other Add-ins and try it again and it should work. On one of my computers, the “RibbonX Visual Designer” add-in was the culprit. I did some Bing searches on the Microsoft sites and found others saying that Microsoft’s own Analysis Toolpak add-in was causing the conflict. In any case, just disable them all and this add-in should work.

10. Now, save everything and try to Build the project (Build… Build Solution). If you do not receive any errors, go ahead and run the project by clicking the “Start” button on the toolbar or by pressing the “F5” key.

If all goes well, you should see Excel open up and wait for you to select a worksheet. Select the blank (empty) worksheet template.

You should now see the new tab titled “OUR ADD-IN TAB”. Click it and you will see the “First Group” ribbon group containing the “Show Pane #1” toggle button.

The new add-in appears on the Excel ribbon

The new add-in appears on the Excel ribbon.

11. Click the “Show Pane #1” toggle button on the ribbon and you should see our custom task pane appear.

New TaskPane with controls.

New TaskPane with controls.

Select one of the three messages fro the ComboBox, then click the “Send Message to Sheet” button to watch the magic happen! Unfortunately, all that’s going to happen is that our code will write the selected message to cell B3 on the worksheet. I know it seems a lot of work to perform such a menial task, but we’ve now laid the groundwork for expanding and enhancing our application.

If you click the “Show Pane #1” toggle button again, the custom task pane will be hidden. Click it again to show the task pane again.

VB code in task pane writes text out to worksheet cell B3.

VB code in task pane writes text out to worksheet cell B3.

One more important tip: Once you run the program, that new tab will remain present in Excel. Even after you close Visual Studio, the next time you open Excel, the add-in will still be there. In order to “clean up” after running the program you must go to the main Visual Studio menu (with our project still open) and click “Build”… “Clean Solution”. If that clean operation succeeds, the Add-in will be completely detached and removed from Excel.

The syntax for interacting with Excel in older VSTO versions was pretty wordy and complicated, which is what scared me off initially. But now, it appears to be almost as simple as coding in VBA… but now with the full power of Visual Studio behind it.

I think it’s possible that VSTO may indeed be ready for “prime time”, but we’ll have to continue our journey a bit farther before we can make such an assertion. The problem with conflicting add-ins (see “Troubleshooting Tips”, above) can be a real showstopper. You can’t simply tell clients that they need to disable all their add-ins so that yours will work!

I’ll try to put out one new article each month but, in the meantime, please let me know what your own experimenting uncovers.



Read Full Post »