Excel add-in development in Visual Studio: Application and base objects

Today we start a new series to explain the basics (and more) of Excel add-in development. This is going to be serious fun because Excel rocks. Next to Outlook it might be the most popular target of Office add-ins.

If Outlook is for the productivity-obsessed…

And Word is for the word nerds…

Then Excel is for the number crunchers…

• Accountants,
• Financial analysts,
• Budget makers, and…
• elementary school math students

Excel is for lovers… of numbers. Let’s look at the base objects that are the primary building blocks of any Excel add-in.

• Excel base objects
• Application object
• Workbook object
• Worksheet object
• Range object
• Useful events

Excel base objects

Excel’s object model is similar to the other document-based applications:

• Application: The Excel application itself. This object sits at the top of the heap and provides access to all other objects in the Excel object model.
• Workbook: This is an Excel file. Workbooks contain many other objects… chief among them is the worksheet.
• Worksheet: This is a single spreadsheet (or tab) within a workbook. It contains rows, columns, and cells. These row, columns, and cells work together as range object.
• Range: A range can be a single cell or multiple cells. The key is the range contains a contiguous group of cells.

These are the main characters in this drama. Let’s cover a few basics for accessing them and making them do something.

Accessing Excel base objects with code

This is what we do. We are developers and we access base objects with code. Does it matter what the application is? No, not really. Just point me to the application and I’ll access its base objects… with code.

Let’s do this.

Application object

This sample shows how to access the Excel application object and change a few settings:

Private Sub SetExcelOptions()
    ExcelApp.DisplayStatusBar = False
    ExcelApp.DisplayFullScreen = True
    ExcelApp.DisplayAlerts = False
    ExcelApp.EnableAnimations = False
    ExcelApp.DefaultFilePath = "C:\Users\Tyrant\Desktop\Spreadsheets\"
End Sub

The ExcelApp object is the Microsoft Excel application. By calling it, we have easy access to Excel’s settings and can change them to meet our needs.

Workbook object

I explained earlier that workbook is an Excel file. It is the container for all the file contents. Given its stature, you will want to master a few key strategies.

Enumerate workbooks

First up is the strategy for enumerating all open Excel workbooks. You achieve this by access the Workbooks collection residing under the ExcelApp object.

Private Sub EnumerateWorkbooks()
    Dim wbs As Excel.Workbooks = ExcelApp.Workbooks
 
    For i As Integer = wbs.Count To 1 Step -1
        Dim wb As Excel.Workbook = wbs(i)
        wb.Close(Excel.XlSaveAction.xlSaveChanges)
        Marshal.ReleaseComObject(wb)
    Next
 
    Marshal.ReleaseComObject(wbs)
End Sub

After we grab a reference to the Workbooks collection, enumerating is easy.

Create a new Excel workbook

You can create a new workbook in a few different ways. The code bellows creates a new workbook by adding new one to the Workbooks collection.

Private Sub CreateNewWorkbook(fileName As String)
    Dim newWb As Excel.Workbook = Nothing
    Dim wbs As Excel.Workbooks = ExcelApp.Workbooks
    newWb = wbs.Add()
    newWb.SaveAs(ExcelApp.DefaultFilePath & fileName)
 
    Marshal.ReleaseComObject(wbs)
    Marshal.ReleaseComObject(newWb)
End Sub

To beef-up the example, the code automatically saves the new file using the filename passed as a parameter.

Create a new workbook from a template

Another method for creating a workbook is to base the new file on a template.

Private Sub NewWorkBookFromTemplate(templatePath As String)
    Dim newWb As Excel.Workbook = Nothing
    Dim wbs As Excel.Workbooks = ExcelApp.Workbooks
    newWb = wbs.Add(templatePath)
 
    Marshal.ReleaseComObject(wbs)
    Marshal.ReleaseComObject(newWb)
End Sub

It works the same as the previous example… EXCEPT… you pass the path to the template file to the Add method.

Open an existing workbook

You can open an existing Excel file by calling the Workbooks collection’s Open method. Just call it and pass the file path to it.

Private Sub OpenExistingWorkbook(filePath As String)
    Dim wb As Excel.Workbook = Nothing
    Dim wbs As Excel.Workbooks = ExcelApp.Workbooks
 
    wb = wbs.Open(filePath)
    '---OR----
    'wb = wbs.Add(filePath)
 
    Marshal.ReleaseComObject(wbs)
    Marshal.ReleaseComObject(wb)
End Sub

You can also achieve the same result by calling the Add method and passing the file path (not the commented code).

Worksheet object

Enumerate sheets

The main of object of a Workbook is a Worksheet. Naturally, you will need to process all worksheets in a single operation… from time-to-time. Here is how you do it.

Private Sub EnumerateSheets(wb As Excel.Workbook)
    Dim i As Integer
    Dim sheets As Excel.Sheets = wb.Worksheets
 
    For i = 1 To sheets.Count
        Dim sheet As Excel.Worksheet = TryCast(sheets(i), Excel.Worksheet)
        If sheet IsNot Nothing Then
            sheet.Tab.Color = 255 - (i * 7)
            Marshal.ReleaseComObject(sheet)
        End If
    Next
 
    Marshal.ReleaseComObject(sheets)
End Sub

The Worksheets collection resides under a Workbook object. This procedure receives a workbook and the proceeds to enumerate all worksheets and change their tab color to red.

Add a new sheet

Adding a new sheet requires only that you call the Worksheets collection’s Add method.

Private Sub AddNewSheet(sheetName As String)
    Dim sheet As Excel.Worksheet = ExcelApp.ActiveWorkbook.Worksheets.Add()
    sheet.Name = sheetName
 
    Marshal.ReleaseComObject(sheet)
End Sub

This sample also changes the name of the worksheet to the string value passed to the procedure.

Delete a sheet

To delete an Excel sheet, you can access it and call its Delete method. This sample enumerates all sheets until it finds worksheet that matches the passed string. If found, it is deleted.

Private Sub DeleteSheet(sheetName As String)
    ExcelApp.DisplayAlerts = False
 
    Dim i As Integer
    Dim sheets As Excel.Sheets = ExcelApp.ActiveWorkbook.Worksheets
 
    For i = 1 To sheets.Count
        Dim sheet As Excel.Worksheet = TryCast(sheets(i), Excel.Worksheet)
        If sheet.Name = sheetName Then
            sheet.Delete()
            Exit For
        End If
        Marshal.ReleaseComObject(sheet)
    Next
 
    ExcelApp.DisplayAlerts = True
    Marshal.ReleaseComObject(sheets)
End Sub

Range object

The Range object contains cells… either a group or a single cell. This object is what you use when inserting data.

Access a cell and set a cell value: version 1

I like to include a header in all my worksheets. It helps me track what the worksheet contains. In this sample, we take the passed Worksheet and insert three rows.

Private Sub AddCompanyHeader(sheet As Excel.Worksheet)
 
    If Len(sheet.Range("A1").Text) Or Len(sheet.Range("A2").Text) Or Len(sheet.Range("A3").Text) Then
        'Insert 3 new rows
        sheet.Range("A1").EntireRow.Insert(Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)
        sheet.Range("A1").EntireRow.Insert(Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)
        sheet.Range("A1").EntireRow.Insert(Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove)
    End If
 
    sheet.Range("A1").Value = "ADD-IN EXPRESS"
    sheet.Range("A2").Value = "[INSERT WORKSHEET TITLE HERE]"
    sheet.Range("A3").Value = DateTime.Now()
End Sub

If the any of the first three rows contain data in the first column, we insert three rows. If not, we use the existing rows. Either way, we then insert the company name, a title placeholder, and the current date.

Access a cell and set a cell value: version 2

There is more than one way to skin a cat. The version above rummages around the worksheet structure. This method calls specific cells by name (the names must already exist) and inserts data.

Private Sub AddCompanyHeaderUsingNamedRanges(sheet As Excel.Worksheet)
    Try
        sheet.Range("CompanyName").Value = "ADD-IN EXPRESS"
        sheet.Range("SheetTitle").Value = "[INSERT WORKSHEET TITLE HERE]"
        sheet.Range("SheetDate").Value = DateTime.Now()
    Catch ex As Exception
    End Try
End Sub

I like this method better but it does require a bit more planning.

Useful events

Manipulating object requires events to trigger the action. Let’s look at a few events that will prove useful in your Excel add-ins.

WorkbookOpen event

The WorkbookOpen event occurs when Excel opens a workbook. It is ideal for taking a quick look at the file and doing something with it.

Private Sub adxExcelEvents_WorkbookOpen(sender As Object, hostObj As Object) _
    Handles adxExcelEvents.WorkbookOpen
 
    Dim wb As Excel.Workbook = TryCast(hostObj, Excel.Workbook)
    Dim sheet As Excel.Worksheet = wb.Sheets(1)
 
    AddCompanyHeader(sheet)
 
    Marshal.ReleaseComObject(sheet)
    wb.Save()
End Sub

Something like adding a company header to it and saving it.

Workbook Activate event

This event triggers every time the Excel window containing the workbook receives the focus.

Private Sub adxExcelEvents_WorkbookActivate(sender As Object, hostObj As Object) _
    Handles adxExcelEvents.WorkbookActivate
 
    Dim sheet As Excel.Worksheet = ExcelApp.ActiveSheet
 
    Try
        'This will fail when creating a new Workbook
        sheet.Range("A1").Select()
    Catch ex As Exception
    End Try
 
    Marshal.ReleaseComObject(sheet)
End Sub

In this sample, I use the event to select cell A1. I bet you can think of a better example.

WorkbookBeforeSave event

The WorkbookBeforeSave event executes before the Excel saves the workbook. It’s useful for taking some last minute action before writing the file to disk.

Private Sub adxExcelEvents_WorkbookBeforeSave(sender As Object, e As ADXHostBeforeSaveEventArgs) _
    Handles adxExcelEvents.WorkbookBeforeSave
 
    Dim wb As Excel.Workbook = TryCast(e.HostObject, Excel.Workbook)
    Dim ws As Excel.Worksheets = TryCast(wb.Worksheets, Excel.Worksheets)
    Try
        Dim hasComments As Boolean = False
 
        For i = 1 To ws.Count
            Dim sheet As Excel.Worksheet = ws.Item(i)
            If sheet.Comments.Count > 1 Then
                hasComments = True
            End If
            Marshal.ReleaseComObject(sheet)
        Next
        If hasComments Then
            MsgBox("This document has comments")
            e.Cancel = True
        End If
    Catch ex As Exception
    End Try
 
    Marshal.ReleaseComObject(ws)
    Marshal.ReleaseComObject(wb)
End Sub

This sample loops through the worksheets collection. If it finds comments, it notifies the user and cancels the save action. I don’t know that this is a valid reason to cancel the save action but I have seen stranger business rules than this one.

SheetSelectionChange event

I’ll end with a sample that I think will actually be appreciated by users. I like to use comments in my Excel files. I have never liked how inconspicuous comments are in Excel. People that receive my files don’t always realize it contains comments.

So, I tend to force my comments to be visible. Making them visible is sometimes annoying. This sample solves the issue by keeping all comments hidden until the user selects a range containing a comment.

Private Sub adxExcelEvents_SheetSelectionChange(sender As Object, _
    sheet As Object, range As Object) Handles adxExcelEvents.SheetSelectionChange
 
    Dim ws As Excel.Worksheet = TryCast(sheet, Excel.Worksheet)
    Dim rng As Excel.Range = TryCast(range, Excel.Range)
 
    'First hide all comments
    For iComments = 1 To ws.Comments.Count
        ws.Comments(iComments).Visible = False
    Next
 
    'Now - Show all comments withing the newly selected range.
    For i = 1 To rng.Cells.Count
        Dim cell As Excel.Range = rng.Cells(i)
        If Len(cell.NoteText) Then
            cell.Comment.Visible = True
        End If
        Marshal.ReleaseComObject(cell)
    Next
 
    Marshal.ReleaseComObject(rng)
    Marshal.ReleaseComObject(ws)
End Sub

If the selected range contains comments, they instantly display. When the user moves to a different range that does not include the comments, they code hides the comments once again.

*****

Excel is a big topic and there is certainly more “101” topics to cover. We’ll do exactly that in this Beginning Excel Development series.

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

VB.NET Excel Base Objects add-in

Excel add-in development in Visual Studio for beginners

• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 8: Working with Excel charts
• Part 9: Working with Excel tables and ranges
• Part 10: Importing data from SQL databases and other sources to Excel
• Part 11: Working with Excel pivot tables: VB.NET code examples