i have followed the following same approach as u suggested to create
automation add-in.
http://blogs.msdn.com/eric_carter/ar...01/273127.aspx
..
and created following code

using System;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using System.Text;
using Microsoft.Office.Interop.Excel;

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Diagnostics;


namespace lnzv2
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class wsf
{
Excel.DocEvents_ChangeEventHandler EventDel_CellsChange;

Excel.Application xlApp;
Excel.Workbook xlBook;
Excel.Worksheet xlSheet1, xlSheet2, xlSheet3;

public wsf()
{

myInitialization();

}

public void myInitialization()
{
xlApp = new Excel.Application();
xlBook = xlApp.Workbooks.Add(Missing.Value);
xlBook.Windows.get_Item(1).Caption = "XL Event Test";
xlSheet1 = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
xlSheet2 = (Excel.Worksheet)xlBook.Worksheets.get_Item(2);
xlSheet3 = (Excel.Worksheet)xlBook.Worksheets.get_Item(3);
xlSheet2.Activate();
EventDel_CellsChange = new Excel.DocEvents_ChangeEventHandler(
CellsChange);
xlSheet1.Change += EventDel_CellsChange; // ........question1

}

private void CellsChange(Excel.Range Target )
{
Target.Value2=10;;


}


public int NumberOfCells(object Range)
{



int [,] TheValues = new int[1, 1];
TheValues[0, 0] = 1;
TheValues[0, 1] = 2;
TheValues[1, 0] = 3;
TheValues[1, 1] = 4;


Excel.Range r = Range as Excel.Range;

r.set_Value(Missing.Value,TheValues);//......question2


return r.Cells.Count;
}
}
}


Given question1 I am unable to invoke the event.So please give me an
idea
how the event can be used and correct me if i am wrong.
In question2 I am unable to get the value in the cells of the given
range
passes in the function and it gives an error in the cell where i have
written
the UDF =NumberOfCells(b2:c3).

Please give a helping hand.
thanks.