SheetChange event crashes using Range.set_value (C#)

[Steve Kirk (Insomniac Games)]

When I get a SheetChange event that contains a cell using my custom function
I cannot modify the value of any cells in the target range.
range is the target range passed into the sheet change event
gid_col is the column I wish to validate


for(int a = 1; a <= range.Areas.Count; a++)
{
Excel.Range area = range.Areas[a];
for(int r = 0; r < area.Rows.Count; r++)
{
//Excel.Range cell = (Excel.Range)area.Cells[r,1];
int row = area.Row + r;
if((row_changes[row].Equals(3))
&&(area.Column <= gid_col)
&&(area.Column+area.Columns.Count>gid_col))
{
Excel.Range cell =
(Excel.Range)area.Cells[r+1,gid_col+1-area.Column];
String f = (String)cell.Formula;
try {
cell.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, "wtf"); }
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Sheet change
error\n" + ex.ToString(),"Localization Addin");
}
}
}
area = null;
}


the code works fine if no cell in the target range uses my custom function,
but blows up with this error otherwise...

System.Runtime.InteropServices.COMException(0x800A03EC): Exception from
HRESULT 0x800A03EC.
at System.RuntimeTYpe.ForwardCallToInvokeMember(String memberName, ...)
at Microsoft.Office.Interop.Excel.Range.set_Value(Object
RangeValueDataType, Object)


also if I try to change other cell properties, like Font.Italic it has no
effect if my custom function is used by any cell in the target

This is the code for my custom function
using System;
using System.Windows;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace LocalizationFunctions
{
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

/// <summary>
/// Summary description for Class1.
/// </summary>
//[GuidAttribute("EE98CECC-2DEF-4d0b-B707-1985711163F5"),
ProgId("LocalizationFunctions.Functions")]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public Functions()
{
//
// TODO: Add constructor logic here
//
}
public String TAG(Excel.Range range)
{
String result = "";
try
{
for(int a = 1; a <= range.Areas.Count; a++)
{
Excel.Range area = range.Areas[a];
for(int r = 1; r <= area.Rows.Count; r++)
{
for(int c = 1; c <= area.Columns.Count; c++)
{
Excel.Range _cell = (Excel.Range)area.Cells[r,c];

if(_cell.Value2 != null)
{
String val = _cell.Value2.ToString();
if(val.Length>0)
{
if(result.Length>0)
{
result += "_";
}
result += val;
}
}
//_cell = null;
}
}
//area = null;
}
result = result.Replace(' ','_');
result = result.Replace('\n','_');
result = result.Replace('\t','_');
//result = (String)range.Value2;
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Bugger2!\n"
+ex.ToString(),"Localization Function");

result = "ERROR!";
}

return result;
}

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
Registry.ClassesRoot.CreateSubKey("CLSID\\{" +
type.GUID.ToString().ToUpper() + "}\\Programmable");
RegistryKey key = Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + type.GUID.ToString().ToUpper() +
"}\\InprocServer32");
key.SetValue("",
System.Environment.GetFolderPath(Environment.SpecialFolder.System)
+@"\mscoree.dll");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type),false);
}

private static string GetSubKeyName(Type type)
{
string s = @"CLSID\{" + type.GUID.ToString().ToUpper() +
@"}\Programmable";
return s;
}

}
}

Any ideas?

 

[Peter Huang [MSFT]]

Hi,

Currently I am researching the issue and we will reply here with more
information as soon as possible.
If you have any more concerns on it, please feel free to post here.

Thanks for your understanding!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi

Based on my research, there seems to be infinite recursion - if in the
change event you modify the sheet, which fires another change event, and so
on and so forth.
So we need to prevent reentrancy in your event handler with a bool flag.
bool bChanged = false;
void exApp_SheetChange(object Sh,
Microsoft.Office.Interop.Excel.Range Target)
{
if( bChanged )
return;
try
{
bChanged = true;
Target.Value2 = "Test2";

Target.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, "Test");
Target.Font.Italic = true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
bChanged = false;
}
}

You may have a try.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steve Kirk (Insomniac Games)]

No luck with that
I already had

m_excel_app.EnableEvents=false;
m_excel_app.ScreenUpdating = false;
....
m_excel_app.EnableEvents=true;
m_excel_app.ScreenUpdating = true;

in my SheetChange function, but I tried your suggestion anyway. It didn't
have any effect.

 

[Steve Kirk (Insomniac Games)]

Just tried a few tests and it appears that if I don't use the range passed
into my custom function everything is fine.
So it can be reduced to this test case if that helps...

public String TAG(Excel.Range range)
{
String result = "";
try
{
result = "banana apple";
//comment the following line out to make it work
result += range.Value2.ToString();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Bugger2!\n"
+ex.ToString(),"Localization Function");

result = "ERROR!";
}
return result;
}

 

[Peter Huang [MSFT]]

Hi

Do you mean call the TAG function in the SheetChange event?
Based on my test, I can not reproduce the problem.
Here is my test code.
public String TAG(Excel.Range range)
{
String result = "";
try
{
result = "banana apple";
//comment the following line out to make it work
result += range.Value2.ToString();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show("Bugger2!\n"
+ ex.ToString(), "Localization Function");

result = "ERROR!";
}
return result;
}

bool bChanged = false;
void exApp_SheetChange(object Sh,
Microsoft.Office.Interop.Excel.Range Target)
{
if( bChanged )
return;
try
{
bChanged = true;
TAG(Target);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
finally
{
bChanged = false;
}
}

You may have a try.
If you still have any concern, can you build a simple reproduce sample and
send the whole solution to me via removing "online" from my email address.
So that I can reproduce the problem at my side.
Thanks for your efforts.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steve Kirk (Insomniac Games)]

No TAG is a custom function being used in one of the cells, if I change the
custom function (which must be getting called automatically to evaluate the
cell) as I described I can make the problem appear or disappear.

The code for the custom function was in the first email, along with the
problematic part of the SheetChange callback function.
I'll try and cut down my app to something that I can send you.

 

[Peter Huang [MSFT]]

Hi Gavin,

I have replied to you in the Email.
It is strange that I can not reproduce the problem with your code.

Thanks!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi Gavin,

I can reproduce the problem with your code and now I am trying to contact
the related supporting team to do further troubleshooting.
Also I want to check if you are still monitor the thread.
If yes, please simply drop a note.

Thanks!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi Gavin,

Currently I am contacting the related supported team.
And I will reply to you ASAP!

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

 

[Peter Huang [MSFT]]

Hi Gavin,

Sorry for delay respond.
So far we are still troubleshooting the issue.
We will get back here and update you ASAP.


Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Huang [MSFT]]

Hi Gavin,

I have replied to you via Email, please have a check.
If you still have any concern, please post here.

Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[vini dimple]

Hi

i have a serious problem while i was working in Excel.I want to fetch
columns from an excel worksheet and i need to compare it with an sql
querry fields,so i tried to open an excle worksheet first..


Unfortunately my code was throwing error like


CLSID\{00020819-0000-0000-C000-000000000046} IS NOT VALID OR NOT
REGISTERED.
But the same exe is working fine in other Pc's except mine.Then i
overcome the error with the code


Dim oldCI As System.Globalization.CultureInfo =
System.Threading.Thread.CurrentThread.CurrentCultu re
System.Threading.Thread.CurrentThread.CurrentCultu re = New
System.Globalization.CultureInfo("en-US")


after that excel sheet was opening but i cant read the rows or columns
in it
error throws like "Old format or Invalid Type Library" in the
particular statement wherever i used worksheet.methodname


Here i put the entire code


please help me


Imports System.Windows.Forms
Imports Microsoft.Office.Core
Imports Excel
Public Class Form1
Inherits System.Windows.Forms.Form
Public xlApp As New Excel.Application


Public Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click


Try


Dim strFileName As String
Dim intRows, intCols As Integer
Dim r, c As Integer
Dim Strquerry As String
Dim strpos As Integer
Dim strposc As String
Dim StrMid As String
'Create a dialog box to find the excel file


Dim dlg As New OpenFileDialog
dlg.Filter = "Excel Files (*.xls)|*.xls"
dlg.FilterIndex = 0
dlg.ShowDialog()
strFileName = dlg.FileName


Dim oldCI As System.Globalization.CultureInfo =
System.Threading.Thread.CurrentThread.CurrentCultu re
System.Threading.Thread.CurrentThread.CurrentCultu re = New
System.Globalization.CultureInfo("en-US")


Dim xlWB As Excel.Workbook
xlWB = xlApp.Workbooks.Open(strFileName)
TextBox1.Text = strFileName
xlWB.Application.Visible = True


System.Threading.Thread.CurrentThread.CurrentCultu re = oldCI


Dim xls As Excel.Sheets
Dim xlsheet As New Excel.Worksheet


xls = xlWB.Worksheets


xlsheet = CType(xls.Item(1), Excel.Worksheet)
xlsheet.activate()


intRows = xlsheet.UsedRange.Rows.Count
If intRows <> 0 Then
intCols = xlsheet.UsedRange.Columns.Count
If intCols <> 0 Then


' Scroll through all the rows and columns retrieving values.
For r = 1 To intRows
For c = 1 To intCols
Strquerry = TextBox2.Text
strpos = InStr(1, Strquerry, "(")
strposc = InStr(strpos, Strquerry, ",")
StrMid = Mid(Strquerry, strpos, CType(xlsheet.Cells(r, c),
Excel.Range).Text)
StrMid = Mid(Strquerry, strposc, CType(xlsheet.Cells(r, c),
Excel.Range).Text)
MsgBox(StrMid)
Next
Next


xlWB.Close(False)
xlApp.Quit()
xls = Nothing
xlWB = Nothing
xlApp = Nothing
End If


End If
Catch ex As Exception
MsgBox(ex.Message)
End Try


End Sub


End Class


thanks in advance
Dimple




*** Sent via Developersdex http://www.developersdex.com ***