Range.Value2 in C# (Excel 2007, VSTO 2008)?

[Fabz]

After doing intense research on the net, I finally found out that I cannot
simply use VSTO 2008 ThisAddIn to create UDFs (for Excel 2007), at least not
without writing any further VBA code to call the UDF defined. I thought this
was possible, but read that actually to write UDFs without VBA I either have
to use COM AddIns or Automation AddIns. I'd be happy to be proved I'm wrong.

So I wrote an Automation AddIn for Excel 2007 (using C#). It again took me a
long time to understand that unlike with VB.NET in C# I simply cannot use
myRange.Value but must use myRange.Value2 instead. Fact is, myRange.Value
simply doesn't exist as a public accessible field in
Microsoft.Office.Interop.Excel. (Of course all the code examples on the net
fail to mention this fact.) How do I then access and modify the value of a
cell?

The following code example works fine:

public String Foo(object Range)
{
Type type = Range.GetType();
return type.ToString();
}

However, the next example does not work:

public String Foo2(object Range)
{
Excel.Range rng = (Excel.Range) Range;
Object obj = (Object) rng.Value2; // (Object)rng.Cells[1,1].Value2 does
not work either
Type type = obj.GetType();
return type.ToString();
}

The result in Excel is #VALUE! independent of what type the method parameter
actually is. I have no clue whatsoever why I cannot access rng.Value2. Can
anyone explain? Thanks.

Fabz

 

[Fabz]

Alright. Finally, I have found out this is probably a Microsoft bug. As soon
as I access myRange.Value2, a COMException is thrown (which I only saw when
debugging):
"Old format or invalid type library" error

See this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;320369

And for a bit more background:
http://blogs.msdn.com/vsto/archive/...rror-when-automating-excel-christin-boyd.aspx

As it seems, the bug has NOT been resolved in Visual Studio 2008.

Fabz

 

[Fabz]

Finally, this solved my problem:
http://www.made4dotnet.com/Default.aspx?tabid=141&aid=15

It's ugly, okay, but blame it on MS.

Fabz

Alright. Finally, I have found out this is probably a Microsoft bug. As
soon as I access myRange.Value2, a COMException is thrown (which I only
saw when debugging):
"Old format or invalid type library" error

See this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;320369

And for a bit more background:
http://blogs.msdn.com/vsto/archive/...rror-when-automating-excel-christin-boyd.aspx

As it seems, the bug has NOT been resolved in Visual Studio 2008.

Fabz

After doing intense research on the net, I finally found out that I
cannot simply use VSTO 2008 ThisAddIn to create UDFs (for Excel 2007), at
least not without writing any further VBA code to call the UDF defined. I
thought this was possible, but read that actually to write UDFs without
VBA I either have to use COM AddIns or Automation AddIns. I'd be happy to
be proved I'm wrong.

So I wrote an Automation AddIn for Excel 2007 (using C#). It again took
me a long time to understand that unlike with VB.NET in C# I simply
cannot use myRange.Value but must use myRange.Value2 instead. Fact is,
myRange.Value simply doesn't exist as a public accessible field in
Microsoft.Office.Interop.Excel. (Of course all the code examples on the
net fail to mention this fact.) How do I then access and modify the value
of a cell?

The following code example works fine:

public String Foo(object Range)
{
Type type = Range.GetType();
return type.ToString();
}

However, the next example does not work:

public String Foo2(object Range)
{
Excel.Range rng = (Excel.Range) Range;
Object obj = (Object) rng.Value2; // (Object)rng.Cells[1,1].Value2
does not work either
Type type = obj.GetType();
return type.ToString();
}

The result in Excel is #VALUE! independent of what type the method
parameter actually is. I have no clue whatsoever why I cannot access
rng.Value2. Can anyone explain? Thanks.

Fabz