If anyone is interested in joining a face-to-face Excel Developers Group in the Massapequa, NY area (give or take 25 miles), please leave a reply or contact me at mdagosta AT statesidesoftware.com.



SQL to escape all SQL wildcard characters in a string:

DECLARE @DirtyString nvarchar(1000) = ‘How\now % [brown_cow]’;
DECLARE @CleanString nvarchar(1000);

SET @CleanString = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@DirtyString, ‘\’, ‘\\’, ‘%’, ‘\%’), ‘_’, ‘\_’), ‘[‘, ‘\[‘), ‘]’, ‘\]’);

PRINT ‘@CleanString = ‘ + @CleanString;

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.


You can only use the Redim Preserve statement to resize the last dimension of a multidimensional array, but there are many times you’ll need to resize the first dimension. The following function uses built-in Excel worksheet function “Transpose” to work around this limitation:

Public Sub ArrayResize1stDim(ArrayToResize As Variant, NewDim As Long)
'Allow the 1st dimension of the passed array to be resized.
' ArrayToResize = The array to be resized (must be passed ByRef)
' NewDim = The total number of elements desired in the
' 1st dimension of the resized array.
Dim lngOrigCols As Long

lngOrigCols = UBound(ArrayToResize, 2)

ArrayToResize = Application.Transpose(ArrayToResize)
ReDim Preserve ArrayToResize(1 To lngOrigCols, 1 To NewDim)
ArrayToResize = Application.Transpose(ArrayToResize)

End Sub

(Note: Due to the usage of Application.Transpose this function will only work with Excel VBA)

Sometimes you just need to quickly identify cells in column B that don’t match the values in the corresponding rows in column A. Sure, you can define Conditional Formatting rules to identify these rows, but for a quick & dirty fix, try using one of the often overlooked “Go To… Special” functions.

Assume that you’re comparing Column A, Rows 1 through 50 to Column B, Rows 1 through 50.

* Select both columns of data A1:B50 (i.e. every cell in both columns that contain names to be compared).

* Click F5 to bring up the “Go To” dialog.

* Click the “Special…” button.

* Select “Row differences” option, then click OK.

* This will “select” every cell in column 2 that is different from the same row’s value in column 1.

* While the cells are still selected, just select a “Fill Color” from the ribbon and every mismatched cell in column 2 will be highlighted with that fill color.


* While the cells are still selected, press CTRL-C to copy them, then right-click on Cell C1 and select “Paste Special…” from the pop-up menu. This will bring up the “Paste Special” dialog. Click the “Skip Blanks” check box, then click OK. Column C will be populated with only the cells in column B that were mismatched.

Don’t know if this will ever be needed again, but it was useful to me once, so I pass it along…

The following code loops through every file in a specified folder and prints every PDF that it finds:

'Use of FileSystemObject requires a reference to "Microsoft Scripting Runtime"
Dim objFSO As Scripting.FileSystemObject
Dim objFld As Scripting.Folder
Dim objFil As Scripting.file

'The ADOBE_PATH constant assumes a Win7 PC; your actual location may be different
Const ADOBE_PATH As String = "C:\Program Files (x86)\Adobe\Reader 9.0\Reader\AcroRd32.exe"
Const FILE_PATH As String = "C:\MyInvoiceFolder\"
Const FILE_EXT As String = "PDF"

Set objFSO = New Scripting.FileSystemObject
Set objFld = objFSO.GetFolder(FILE_PATH)

'Loop through each file in C:MyInvoiceFolder
For Each objFil In objFld.Files

If UCase$(Right$(objFil.Name, 3)) = FILE_EXT Then  'Only process PDFs

Application.StatusBar = "Printing Invoice file: " & objFil.Name

Shell """" & ADOBE_PATH & """/a /p /h """ & objFil.Path & """"

End If

Next objFil

'Clean up

Set objFil = Nothing
Set objFld = Nothing
Set objFSO = Nothing

String manipulation and parsing tasks are just as common in Excel/VBA development as they are in apps developed in any other programming language. C, C# and C++ have powerful built-in string classes that greatly reduce the amount of code that must be written to accomplish these tasks. While VBA string manipulation code will always be more “wordy” than it’s C++ counterparts, there’s no reason that it has to be any less capable or powerful.

The task at hand was to write a utility that would parse lines in a CSV file. However, commas that appeared inside quotation marks should be ignored. For example, the string A,B,C,”D,E”,F,”G,H,I”,J,K, should be split into 8 elements:

Many beginner to intermediate VBA programmers may not be aware that Microsoft provides a powerful regex component that can be integrated into any Excel/VBA project. As soon as I understood the dimensions of the task, I immediately thought of Microsoft’s RegEx component. I’m sure there are many ways of doing this, but I think the RegEx object provides the most efficient method. Let’s get right to it.

Using RegEx in Excel/VBA

1. Start a new Excel workbook, open the VBA editor, and add a reference to “Microsoft VBScript Regular Expressions 5.5” (the current version as of this writing).

2. Add an ActiveX command button to the worksheet, name it “btnShowForm”, and change the Caption to “Show Regex Form”.

3. Back in the VBA editor, insert a new UserForm into the project, name it “frmParse”, and change the Caption to “Parse Special”.

4. Add the following controls to the new UserForm: one Label named “lblInput” (Caption = “String to Parse”), one Textbox named “txtInput”, one Listbox named “lstOutput” and two Command Buttons. The first command button should be named “btnParse” (Caption = “Parse”) and the second button should be named “btnExit” (Caption = “Exit”).

Your UserForm should look something like this:

New UserForm

New UserForm

5. Next, add the following code to the btnExit_Click event:


6. Next, add the following code to the btnParse_Click event (don’t worry, we’ll create the clsRegEx class module next):

Dim arrData() As String
Dim intWord As Integer
Dim objRegex As clsRegex

Set objRegex = New clsRegex
arrData = Split(objRegex.RegexParse(txtInput.Text), ",")
For intWord = 0 To UBound(arrData)
lstOutput.AddItem Replace(arrData(intWord), "?", ",")
Next intWord

Set objRegex = Nothing

7. Next, add a new Class module to the project, name it “clsRegex” and add the following function to the new class:

Public Function RegexParse(ByVal ToParse As String) As String
'Use regular expressions to find and replace quoted text in the CSV input.
'NOTE: Must add a reference to "Microsoft VBScript Regular Expressions 5.5".
Dim strPat As String, strReturn As String
Dim objRegExp As VBScript_RegExp_55.RegExp
Dim objMatch As VBScript_RegExp_55.Match

On Error GoTo RPError

strReturn = ToParse

If InStr(ToParse, Chr$(34)) > 0 Then
Set objRegExp = New VBScript_RegExp_55.RegExp
With objRegExp
.IgnoreCase = True
.Global = True 'Identify ALL instances that match regex pattern, not just first one.

        'NOTE: "[^"]+" regex code means "Match a double quote, match one or more chars that
        ' are not double quotes, then match a second double quote".
strPat = Chr$(34) & "[^" & Chr$(34) & "]+" & Chr$(34)
.Pattern = strPat
End With

For Each objMatch In objRegExp.Execute(strReturn)
strReturn = Replace(strReturn, objMatch.Value, _
Replace(Replace(objMatch.Value, Chr$(34), vbNullString), ",", "?"))
Next objMatch
End If

RegexParse = strReturn
Set objMatch = Nothing
Set objRegExp = Nothing
Exit Function

MsgBox "Error: " & Err.Number & vbCrLf & vbCrLf & "Description: " & _
Err.Description, vbCritical, "Regex Parsing Error"
strReturn = ToParse 'Set the return value back to the original string
Resume RPResume

End Function

8. Finally, go back to the worksheet and add the following code to the cmdShowForm_Click event:

frmParse.Show vbModal
Unload frmParse

That’s it for the coding. Now save and open your new workbook. Click the “Show Form” button on the worksheet. After the UserForm appears, enter the following in the input text box:


Click the “Parse” button and you should see the Listbox fill with eight rows. The string in the text box should be broken out exactly as required.

RegEx form with Listbox filled

RegEx form with Listbox filled

Notice the way the “RegExp” and “Match” classes of the VBScript_RegExp_55 module are used to accomplish the task. What actually happens is that the RegExp module is used to identify the pattern where a comma appears within quotation marks. It then changes those quotes to question marks, so the calling routine (cmdParse_Click) can use the Split function to push the CSV line into an array correctly. The btnParse code then switches the question marks back to commas before outputting the data to the Listbox.

So, don’t forget the “RegEx” the next time you’re faced with a pattern-matching problem.