Ask Kathleen

Return Double Values in Excel

Learn how to work around a couple bugs in Excel to return double values; drill down on lists with anonymous types; and learn the difference between Build and Rebuild.

• By Kathleen Dollard
• 07/01/2008

Q I'm creating a library that will return double values to Excel 2003, but I encounter a problem when assigning double types through COM Interop to an Excel cell when the double value is PositiveInfinity, NegativeInfinity, or NaN -- I'm alarmed to see the cell value set to 65535! Think how bad things could get when Infinity is meant, but 65535 is given and then 65535 is used in a formula!

I've reproduced this problem using Excel 2007 under Windows Vista using with this code:

[Guid("F9E15EF1-0F32-4153-
   A04A-597EFD9DB95A")]
[ComVisible(true)]
public interface ITestDouble
{ double GetPosInfinity(); }

[Guid("E34EFC46-33AF-4e95-
   AE4C-BAF83BAA3718")]
[ComVisible(true)]
[ClassInterface( 
   ClassInterfaceType.None)]
[ComDefaultInterface(
   typeof(ITestDouble))]
public class TestDouble : 
   ITestDouble
{ public double GetPosInfinity()
   {
      return Double.PositiveInfinity;
   }
}

A The problem isn't in the COM Interop, which correctly assigns the double value. You can see this if you set a breakpoint in your VBA code and use the Immediate window to check the value (c is an instance of the TestDouble class):

? c.GetPosInfinity
1.#INF 

Depending on how you assign the cell, one of two wrong values are assigned. The default assignment results in 65535, while assigning Val() results in 1 -- both of which are spectacularly wrong and represent a bug in Excel. There are three solutions, all of them terrible if you want IEEE behavior.

You could raise an error when encountering an infinity or NaN value in your .NET library. This might require an extra wrapper so any .NET callers get a correct result, while you return an error to Excel or other Office applications that can't manage the infinity value. The benefit of this approach is that it will work with all spreadsheets using your library.

Another alternative is wrapping the call to your .NET library in VBA code that recognizes the infinity condition and places something more intelligent into the cell (probably an error value). This allows customized behavior, but if the spreadsheet neglects to take special steps, the invalid value is contained in the cell, which makes further calculations incorrect.

The third possibility is to return a string value. This allows you to capture the information for display, but spreadsheet users are quite likely to use simplistic conversion code that places a 1 in the target cell: Val(A1)

These are all terrible solutions, but the first approach best protects your spreadsheets from calculations based on bogus values.

Q I'm trying to create a list of employees and include an "All Employees" item at the top of my list. Here's how I retrieve the employees using LINQ to SQL:

Dim dc As New _
   BizLibrary.AdventureWorks _
   LTDataContext
Dim employees = From c _
   In dc.Employees Distinct _
   Select c.EmployeeID, name = _
   c.LastName & ", " & c.FirstName

Then I create another anonymous type for the "All Employees" item and shove it into a collection so that it's queryable:

Dim allEmpOption = New With _
   {.employeeid = 0, .name = _
   "*All Employees*"}
Dim coll As New Collection
coll.Add(allEmpOption)

Next, I try to Union these two queries:

Dim fullList = employees.Union( _
   from e in coll select e)

But I get this exception when I do this:

Error	3	Option Strict On
disallows implicit conversions
from 'Microsoft.VisualBasic.Collection'
 to System.Collections.Generic.
 IEnumerable(Of <anonymous type>)'

A There are a couple of things going on here. First, the Collection contains an item of type Object, which can't be implicitly cast to the correct type. The Visual Basic Collection class also has performance issues, so avoid using it.

The easiest way to deal with anonymous types is to pass them to a generic function. You can then create a new list or an array of the anonymous type and use it to perform the union:

Private Function AddItem2(Of T)( _
   ByVal list As IEnumerable(Of T), _
   ByVal item As T) _
   As IEnumerable(Of T)
   Dim items = New T() {item}
   Return list.Union(items)
End Function

You can create the array explicitly, but that limits the method to adding only a single item. To simplify the method and add multiple items, use a parameter array. The Union operator combines two IEnumerable(Of T) collections by placing items contained in the parameter at the end of the main list. By switching which list is passed as the parameter, you can control whether new items are included at the beginning or end of the list:

Private Function AddItem(Of T)( _
   ByVal list As IEnumerable(Of T), _
   ByVal atTop As Boolean, _
   ByVal ParamArray items() As T) _
   As IEnumerable(Of T)
   If atTop Then
      Return items.Union(list)
   End If
   Return list.Union(items)
End Function

Calling this function exposes the second problem in your sample code because it exposes a type error:

Dim allCustomers = New With 
   {.CustomerId = 0, _
   .name = "----- All -----"}
AddItem(customers, allCustomers)

This problem occurs because Visual Basic lets you choose the level of immutability of your anonymous type. Unfortunately, the slight variation in how you've created these two anonymous types results in different immutability -- the two anonymous types have the same fields, but differ by immutability. If you create a version of your application that compiles by commenting out the failing code and use Reflector to view the IL, you'll find you have anonymous types VB$AnonymousType_0<T0, T1> and VB$AnonymousType_1<T0, T1>. The IL fragments differ by whether they override Equals, GetHashCode, and IEquatable, as well as whether the CustomerId and name are read only (the immutable version won't have property setters).

Immutability describes whether you can change the values of the anonymous type's objects. This is linked to equality and hash codes because changing the value of hash codes after objects have been placed in a dictionary would wreak havoc. To avoid this, VB supports reference type semantics and also provides the Key keyword to let you specify what values within anonymous types are immutable, and only these values are used in GetHashCode. In most cases, if you define an anonymous type without the Key keyword, the object reference is used as the hash. This is the default behavior of reference types, so Equals, GetHashCode, and IEquatable aren't overridden, and you can change any value in the object. You can choose which to use, but you can't combine them as they're fundamentally different types.

When you create an anonymous type outside a LINQ expression, and define it without the Key keyword, the resulting anonymous type is fully mutable and uses reference type semantics. Apparently for consistency with C#, when you create an anonymous type without the Key keyword within a LINQ expression, it's fully immutable -- as though the Key keyword had been added to every field.

You can't combine objects of anonymous types with different immutability, so you must force the two types to have the same immutability. You can either make the LINQ query produce a mutable anonymous type or force the constructor-based creation of the anonymous type to create an immutable type. You force LINQ to create a mutable type by calling the anonymous type's constructor explicitly:

Dim customers = From c In dc. _
   Customers Distinct Select _
   New With {c.CustomerID, _ 
   .name = c.LastName & ", " & _
   c.FirstName}

You force creation of an immutable anonymous type by including the Key keyword on every field:

Dim allCustomers = New With {Key _
   .CustomerId = 0, Key .name = _
   "----- All -----"}

Make either (but not both) of these changes, and the new method will add the "All Employees" entry to your list, where it will be available to your bound combo box.

Q I'm converting a WinForms application to Windows Presentation Foundation (WPF), but I can't find a replacement for the Timer control. How do I add a timer in WPF?

A The WPF timer was added as the DispatcherTimer class in the System.Windows.Threading namespace, along with the Dispatcher class it works with. One key point about this timer: When you're working with WPF, the handlers fire on the UI thread, allowing you to make changes to UI components. You can change the background color of a button after a delay using code like this:

System.Windows.Threading.DispatcherTimer 
   timer = new 
   System.Windows.Threading.DispatcherTimer();
private void Button_Click(object sender, 
   RoutedEventArgs e)
{
   timer.Interval = new TimeSpan(0, 0, 2);
   timer.Tick += Timer_Tick;
   timer.IsEnabled = true;
}

private void Timer_Tick(object sender, EventArgs e)
{ this.Background = Brushes.Red; }

Q What's the difference between Build and Rebuild All in Visual Basic?

A The Visual Basic background compiler runs on a separate thread and moves all the projects of your solution through a series of states from "No state" to "Compiled." When you ask for a build on a project, the background compiler finishes up the specific projects you've requested and writes the required information to disk, updating the .DLL, and potentially, a .PDB and other files. This leverages the work of the background compiler.

Hitting Rebuild on an assembly resets its state to No State, and by necessity, sets all the dependent projects to No State. This effectively throws out the work of the background compiler. Rebuild also differs because it's dependent on project build order. If the build order is incorrect, the compiler winds up rebuilding the same assembly multiple times, which is inefficient and can cause performance problems.

In general, you can save time by using Build. If you get different results with Rebuild, it's a bug. If you can reproduce these different results, please submit it through Help/Report a Bug. If Rebuild takes a long time, check whether the dependency order of your projects is correct.

Q I can't get the immediate window to return the correct value for Guid variables in Visual Basic. I have a variable named CustomerId, which is a Guid. When I use the immediate window, it always says that it is empty, even though I know from running the code that it's not empty.

? CustomerId
{System.Guid}
    Empty: Nothing

A Immediate has a quirk (bug) where it doesn't return the value for ToString on the Guid instance; rather it returns the name of the class and its sole shared field (Empty). It's not telling you that you're variable value is empty, but that your Guid has a shared field called Empty. This is not helpful and is likely to be fixed in a future version of Visual Studio.

In the meantime, you'll need to call the ToString() method explicitly, so you can see the Guid value:

? CustomerId.ToString()
"62a6185a-7746-4e95-a81f-225f95b75367"