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…
- 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’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.
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.
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.
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).
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
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.
Manipulating object requires events to trigger the action. Let’s look at a few events that will prove useful in your Excel add-ins.
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.
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.
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.
This sample Excel add-in was developed using Add-in Express for Office and .net:
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