[Excel][C#][Com Server][Bug] Read property on "Insert function" cl

[Niels Van Vliet]

First I am going to explain how to reproduce a bug in Excel, then I am going
to give the source code of the function, and last I am going to provide other
notes.

Note that the function is very simple, it simply reads the property Bold of
a cell of a range.

I. DESCRIPTION OF THE BUG

Normaly if the formula of cell Sheet1!A1 is modified, is should NOT modify
the FORMULA of the cell Sheet2!A2. This statement is not true due to the bug.

Here is how to reproduce the bug:

1) Write the code:
- Create an Excel add-in,
- Add the class MyClass, which contains a Com server, with the function
MyFunction.
- set the varialble MyClass.application when the Excel add-in starts.
2) open excel:
- Sheet 2: Write in sheet2!A1 the text "Foo".
- Sheet 2: Select the cell sheet2!A2.
- Sheet 1: edit the formula of sheet1!A1:
- write in sheet1!a1 "=MyFunction(", then USE THE ASSISTANT to enter
the function arguments. What I call the assistant is the Insert function
dialogue box that let the user enter the argument. It pops up when the user
clicks on the icon "fx" on the left of the edit bar.
- for the only argument of MyFunction, select the cell sheet2!A1, on
sheet 2.
- click "OK" in the assistant. This will produce the bug:
- sheet 2: the FORMULA of the cell sheet2!A2 has also been modified !!!
It contains the same formla that the one edited in sheet1 A1.

Note that if the assistant (the "fx" icon) is NOT used, it does not produce
the bug (i.e. the formula fo sheet2!A2 is not modified by the edition of the
sheet1!A1).

This has been tested on 3 PCs (configuration explained later).

II THE CLASS MYFUNCTION:

public class MyFunction
{
// This is set by the add in class.
public Microsoft.Office.Interop.Excel.Application application;

public string MyFunction(Microsoft.Office.Interop.Excel.Range MyRange)
{
try{
// Removing these 4 lines does not change the problem:
if (application.Ready == false)
return "Not ready";
else
Application.Volatile(Missing.Value);
Microsoft.Office.Interop.Excel.Range r = MyRange[1, 1] as
Microsoft.Office.Interop.Excel.Range;
if (r == null)
return "Range null";
bool? b = r.Font.Bold as bool?;
if (b == null)
return "null null";
return b ==true ? "Bold" : "NOT Bold";
}
catch (Exception e) { return "Error: " + e.Message;}
}


[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type t){
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\\{" +
t.GUID.ToString().ToUpper() +
"}\\Programmable");}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type t){
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey("CLSID\\{" +
t.GUID.ToString().ToUpper() +
"}\\Programmable");}
}

III. SOME ADDITIONAL COMMENTS:
Note also that:
- Using reflection does not change the problem. By reflection, I mean using
o.GetType().InvokeMember(....).
- Using application.Ready does not change the problem.
- if I do not read the r.Font.Bold, the bug does not appear (thus
r.Font.Bold is the problem).
- if I read only the Font, the bug does not appear (thus r.Font is NOT the
problem).
- Cindy Meister[MVP] told me that Bold is an Long property not a bool
property, but
"r.Font.Bold is boolean" is true, (and "is Int32" or "is long" is false).
- the function works well outside the assistant.
I use:
- I use Excel 2002 (10.6809.6804) SP3.
- Visual Studio 2005 (patched for office add ins)
- C#,
- oxppia, the version of the assembly Microsoft.Office.Interop.Excel.dll is
10:0:4504:0
- This problem has been reproduced on 3 other computers.

Also, note that the result of the function is stupid ("null null" or "Not
ready"...), of course this is NOT the problem. The problem is the BUG of
excel, which writes the edited formula in two differents cell.

I googled, but could not find a work arround.

Thanks,
(email: niels AT nvv DOT name).

 

[Niels Van Vliet]

First, I posted this bug in a wrong forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=775591&SiteID=1

I got the following answer from Andrew Whitechapel:
"We've investigated this a little, and it does look like there might be a
bug around the Font.Bold property. The Excel team will investigate this
further. Thank you for identifying this issue."

Niels VAN VLIET
(email: niels AT nvv DOT name).

 

[XL-Dennis]

Hi Niels,

Thanks for coming back and let us know that is not a 'feature' rather a bug.