Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 28. November 2010 16:22

Sometimes, you need to know when your user decided to move to another Worksheet in Excel. Fortunately, Excel exposes some events for this. At the workbook level, Workbook.SheetActivate and Workbook.SheetDeactivate are fired when the user activates or deactivates a sheet in the Workbook, and at the application level, Application.WorkbookActivate and Application.WorkbookDeactivate are triggered when the user changes Workbooks.

This looks all nice and simple, except that there is a small catch, which caused me a bit of grief on my current project. I naively thought that when a user activated a new Workbook, it would fire WorkbookActivate, and SheetActivate. Wrong – when you activate a new Workbook, only WorkbookActivate is triggered.

The following VSTO code illustrates the point: Excel traps when a new Workbook is added, and begins tracking the Sheet activation/deactivation for that new Workbook.

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
   var excel = this.Application;
   WorkbookAdded(excel.ActiveWorkbook);
   ((Excel.AppEvents_Event)this.Application).NewWorkbook += WorkbookAdded;
   excel.WorkbookOpen += WorkbookAdded;
   excel.WorkbookActivate += WorkbookActivated;
   excel.WorkbookDeactivate += WorkbookDeactivated;
}

private void WorkbookAdded(Excel.Workbook workbook)
{
   workbook.SheetActivate += SheetActivated;
   workbook.SheetDeactivate += SheetDeactivated;
}

private void WorkbookActivated(Excel.Workbook workbook)
{
   MessageBox.Show("Workbook activated.");
}

private void WorkbookDeactivated(Excel.Workbook workbook)
{
   MessageBox.Show("Workbook deactivated.");
}

private void SheetActivated(object sheet)
{
   MessageBox.Show("Sheet activated.");
}

private void SheetDeactivated(object sheet)
{
   MessageBox.Show("Sheet deactivated.");
}

If you run this code, you will note that when you change sheets within a Workbook, the Message Box “Sheet deactivated” pops up, followed by “Sheet activated”. However, if you add multiple workbooks, and start changing workbooks, only “Workbook activated” / “Workbook deactivated” shows up.

The morale of the story is that if you are interested in tracking when a user changed the active worksheet across workbooks, you can’t simply rely on SheetActivated: you will need to look out for Workbook level events, and when these occur, figure out through the Workbook active worksheet which sheet has been activated or deactivated.

I think the reason this caught me off-guard is that I had this Worksheet-centric mental image of Excel: when I am changing workbooks, my goal is to select a Worksheet in that Workbook, the Workbook is simply a means to an end – and I expected the events to reflect that. However, if you consider the Workbook as its own isolated entity, it makes sense: when I leave a Workbook, it simply becomes invisible, but otherwise nothing changed: the Worksheet that is active remains active, and will still be active when I come back later.

The other interesting pitfall is that when you start Excel, there is a Workbook active – but because it is created before you can begin trapping events, you have to register it manually if you want to track its behavior as well.

by Mathias 26. April 2010 08:32

Today we will write code that reads the contents (values & formulas) of the two selected worksheets, generates a list of their differences, and feeds it to the view that displays the comparison. The code isn’t particularly complicated, and uses mostly ideas which have been seen in the previous posts, so rather than break this into multiple small posts, I chose to bite the bullet and get done with a large chunk, all at once. I hope you survive it and bear with me – promise, we are almost there!

You can find the complete list of episodes, and a link to the source code of theExcel VSTO add-in tutorial here.

Extracting the comparison from the comparison ViewModel

In the previous installment, I created a stub to provide a “canned” list of differences to the ComparisonViewModel, so that we would have something to display. It’s time to replace that stub, and generate a real comparison between 2 worksheets. We could add the required code inside the existing classes; however, we will likely have a good amount of logic, so to avoid clutter, and to keep our design tight, we will extract that responsibility into its own class, the WorksheetsComparer.

First, let’s create a stub for the class, in the Comparison folder; its key method will be FindDifferences, and we will temporarily move the “fake” comparison that was in the ComparisonViewModel into that method:

public class WorksheetsComparer
{
   public static List<Difference> FindDifferences(Excel.Worksheet firstSheet, Excel.Worksheet secondSheet)
   {
      // Temporary code
      var difference1 = new Difference() { Row = 3, Column = 3 };
      var difference2 = new Difference() { Row = 3, Column = 5 };
      var difference3 = new Difference() { Row = 5, Column = 8 };
      
      var differences = new List<Difference>();
      differences.Add(difference1);
      differences.Add(difference2);
      differences.Add(difference3);

      return differences;
   }
}

Now we need to hook it up to the add-in. We will add a button to the AnakinView, which, when clicked, will call WorksheetsComparer.FindDifferences, and pass the result to the ComparisonViewModel. Let’s first plug a button in the AnakinView, and bind it to a Command on the ViewModel, GenerateComparison:

<StackPanel Margin="5">
  <Comparison:ComparisonView x:Name="ComparisonView" />
  <Button Command="{Binding GenerateComparison}" 
          Content="Compare" Width="75" Height="25" Margin="5"/>

Following the same approach as in the previous post, we implement the command in the View Model (only added code is displayed):

public class AnakinViewModel
{
   private ICommand generateComparison;

   public ICommand GenerateComparison
   {
      get
      {
         if (this.generateComparison == null)
         {
            this.generateComparison = new RelayCommand(GenerateComparisonExecute);
         }
         return this.generateComparison;
      }
   }

   private void GenerateComparisonExecute(object target)
   {
      var differences = WorksheetsComparer.FindDifferences(null, null);
      this.comparisonViewModel.SetDifferences(differences);
   }
}

More...

by Mathias 27. March 2010 11:18

Previous episodes

  1. Getting Started
  2. Using the Custom Task Pane
  3. Using the Ribbon
  4. Adding a WPF control
  5. Displaying open workbooks & worksheets in a TreeView
  6. Using Excel events to update the TreeView

Today is the day, we will finally close “chapter one” of these series, with some minor improvements of the tree view display of open workbooks and worksheets. The final result of our work looks like this, with a TreeView displaying all open workbooks and worksheets, refreshing its contents (quasi) automatically, and with some home-made icons just for kicks.

FullTreeView

Rather than a systematic walk-through, I will just explain the changes I implemented to the code base, which I have now posted on a dedicated Wiki page.

Download Anakin project code

More...

by Mathias 17. March 2010 10:10

Previous episodes

  1. Getting Started
  2. Using the Custom Task Pane
  3. Using the Ribbon
  4. Adding a WPF control
  5. Displaying open workbooks & worksheets in a TreeView

It’s time to wrap-up the first part of this tutorial, and hook up our tree view to Excel events, to update the contents of the tree when the user changes what is open.

We need to capture the following: we need to update the TreeView when the user

  • Opens, creates or closes a workbook
  • Adds or deletes a worksheet

Added Worksheets and Workbooks

Let’s start with adding a worksheet to a workbook. Excel exposes that event, through the Workbook.NewSheet event. We want the WorkbookViewModel to take care of his children, so we modify the constructor the following way:

internal WorkbookViewModel(Excel.Workbook workbook)
{
   this.workbook = workbook;
   workbook.NewSheet += new Excel.WorkbookEvents_NewSheetEventHandler(workbook_NewSheet);
   // no change here, code stays the same
}

void workbook_NewSheet(object newSheet)
{
   var worksheet = newSheet as Excel.Worksheet;
   if (worksheet != null)
   {
      var worksheetViewModel = new WorksheetViewModel(worksheet);
      this.worksheetViewModels.Add(worksheetViewModel);
   }
}

Creating event handlers can be a bit tedious; fortunately, Visual Studio simplifies the task quite a bit. When you type workbook.NewSheet +=, you should see a tooltip appear, which “suggests” an event handler. Type Tab, and Tab again – Visual Studio will create for you the empty event handler, with the right arguments and types, where you can now insert the logic of what should happen when the event is triggered.

More...

by Mathias 11. March 2010 12:15

While working on my VSTO Excel add-in tutorial, I came across the following issue: I need to know whether a worksheet has been deleted. The reason I care is that when it happens, I need to update the display of the worksheets that are currently open, and remove it from there.

I was very surprised to find out that there seems to be no event for this. The Application object, which represents the Excel application, has a WorkbookBeforeClose event; the Workbook object has an event BeforeClose, triggered when the Workbook is being closed. So naturally, I expected to find something equivalent for the Worksheet object, at either the Application, Workbook, Sheets, Worksheets, or Worksheet level – no such luck.

I looked around on the web, and from what I can tell, there is no native event for this, and I came across multiple posts advocating to handle this through Worksheet.Activate and/or Worksheet.Deactivate. I see how this catches the obvious use case, namely, the user selects the sheet and deletes it – which causes the sheet to be activated, and then another worksheet to be activated once the deletion is performed. Unfortunately, this doesn’t catch all the cases: as far as I can tell, it is perfectly possible to delete a worksheet without ever changing which sheet is active. To prove the point, create a workbook, and add the following macro:

Public Sub DeleteSheet3()
    Application.DisplayAlerts = False
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True
End Sub

More...

Comments

Comment RSS