Excel: AddIn, fill active sheet cell ?

[Frank M. Walter]

Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem System.
Nachinstallieren???
namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel. Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}
return s;
}
}

[Nicholas Paldino [.NET/C# MVP]]

Frank,

Is there any more information to the exception? On top of that, are you
sure that region is populated, and not null?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Frank M. Walter" <f.******@hotmail.com> wrote in message
news:44***********************@news.freenet.de...

Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem System.
Nachinstallieren???
namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel. Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}
return s;
}
}

[Frank M. Walter]

Hello Nicholas,
the function is being called.
There is no more information about exception.

Frank

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> schrieb
im Newsbeitrag news:%2****************@TK2MSFTNGP05.phx.gbl...

Frank,

Is there any more information to the exception? On top of that, are
you sure that region is populated, and not null?
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Frank M. Walter" <f.******@hotmail.com> wrote in message
news:44***********************@news.freenet.de...

Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System. Nachinstallieren???
namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel. Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}
return s;
}
}

[Alan]

applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10", Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

"Frank M. Walter" wrote:

Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem System.
Nachinstallieren???
namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel. Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}
return s;
}
}

[Frank M. Walter]

Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...

applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

"Frank M. Walter" wrote:

Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System.
Nachinstallieren???
namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel. Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}
return s;
}
}

[Alan]

Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio 2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class library
and shared add-in projects...

"Frank M. Walter" wrote:

Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...

applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

"Frank M. Walter" wrote:

Hello,
I have made an small AddIn with udf for excel 2003.
I use vs2003.
The point of view is the function __T()
I call it in excel sheet writing =__T() [enter]

I am not able to set a value to a given cell.
region.Value2="qwe"; //bumm!
A exception will be trown. On all PCs with excel.
HRESULT 0x800A03EC

Is it com-registration problem?

But, it is possible to read the name of worksheet.
s=ws.Name.ToString(); //OK!

Who knows, what is the problem?
Thanks

microsoft.public.dotnet.languages.csharp

using System;

using System.Runtime.InteropServices;

using Microsoft.Win32;

using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
System.
Nachinstallieren???
namespace ExcelAddInFunc
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class fncAdd
{

public fncAdd()
{
}

public string __T()
{
string s="Test";
Excel.Application m_objExcel =
(Excel.Application)Marshal.GetActiveObject("Excel. Application");
Excel.Worksheet ws =
(Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
Excel.Range region=(Excel.Range)ws.Cells[10,10];

s=ws.Name.ToString(); //OK!

try
{
region.Value2="qwe"; //bumm!
}
catch(Exception e)
{
s=e.ToString();

}
return s;
}
}

[Frank M. Walter]

Hi Allan,

I can not manage it....
would you like to be so kind to send me your sample project ?
As a zip-file ?
Frank

Please take this address
ivobraun@ gmx.net

THANKS!!!
"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:D5**********************************@microsof t.com...

Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio
2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class
library
and shared add-in projects...

"Frank M. Walter" wrote:

Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...

> applicationObject is from OnConnection() or shared add-in
>
> // Get the active worksheet.
> object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
> BindingFlags.GetProperty, null, m_applicationObject, null );
>
> // Get cell J10
> object range = sheet.GetType().InvokeMember( "Range",
> BindingFlags.GetProperty, null, sheet, new object[] { "J10",
> Missing.Value }
> );
>
> // Set the value of cell J10
> range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
> range, new object[] { "Test" } );
>
> "Frank M. Walter" wrote:
>
>>
>> Hello,
>> I have made an small AddIn with udf for excel 2003.
>> I use vs2003.
>> The point of view is the function __T()
>> I call it in excel sheet writing =__T() [enter]
>>
>> I am not able to set a value to a given cell.
>> region.Value2="qwe"; //bumm!
>> A exception will be trown. On all PCs with excel.
>> HRESULT 0x800A03EC
>>
>> Is it com-registration problem?
>>
>> But, it is possible to read the name of worksheet.
>> s=ws.Name.ToString(); //OK!
>>
>> Who knows, what is the problem?
>> Thanks
>>
>> microsoft.public.dotnet.languages.csharp
>>
>> using System;
>>
>> using System.Runtime.InteropServices;
>>
>> using Microsoft.Win32;
>>
>> using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
>> System.
>> Nachinstallieren???
>>
>>
>> namespace ExcelAddInFunc
>> {
>> [ClassInterface(ClassInterfaceType.AutoDual)]
>> public class fncAdd
>> {
>>
>> public fncAdd()
>> {
>> }
>>
>> public string __T()
>> {
>> string s="Test";
>> Excel.Application m_objExcel =
>> (Excel.Application)Marshal.GetActiveObject("Excel. Application");
>> Excel.Worksheet ws =
>> (Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
>> Excel.Range region=(Excel.Range)ws.Cells[10,10];
>>
>> s=ws.Name.ToString(); //OK!
>>
>> try
>> {
>> region.Value2="qwe"; //bumm!
>> }
>> catch(Exception e)
>> {
>> s=e.ToString();
>>
>> }
>>
>>
>> return s;
>> }
>> }
>>
>>
>>

[Alan]

Try this code...
namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish
to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
ExcelPlayAreaSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute( "01926917-EBC7-4279-A4A1-BFC25871F0CE" ), ProgId(
"ExcelPlayArea.Connect" )]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class fncAdd : Object, Extensibility.IDTExtensibility2
{

/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}

/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection( object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array
custom )
{
applicationObject = application;
addInInstance = addInInst;

}

/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection( Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom )
{
}

/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate( ref System.Array custom )
{
}

/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete( ref System.Array custom )
{
}

/// <summary>
/// Implements the OnBeginShutdown method of the
IDTExtensibility2 interface.
/// Receives notification that the host application is being
unloaded.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnBeginShutdown( ref System.Array custom )
{
}

private object applicationObject;
private object addInInstance;

public string __T()
{
string s = "Test";
// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember(
"Range",BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value });

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty,
null, range, new object[] { s } );
return s;
}


#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

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

"Alan" wrote:

Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio 2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class library
and shared add-in projects...

"Frank M. Walter" wrote:

Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...

applicationObject is from OnConnection() or shared add-in

// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value }
);

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
range, new object[] { "Test" } );

"Frank M. Walter" wrote:

>
> Hello,
> I have made an small AddIn with udf for excel 2003.
> I use vs2003.
> The point of view is the function __T()
> I call it in excel sheet writing =__T() [enter]
>
> I am not able to set a value to a given cell.
> region.Value2="qwe"; //bumm!
> A exception will be trown. On all PCs with excel.
> HRESULT 0x800A03EC
>
> Is it com-registration problem?
>
> But, it is possible to read the name of worksheet.
> s=ws.Name.ToString(); //OK!
>
> Who knows, what is the problem?
> Thanks
>
> microsoft.public.dotnet.languages.csharp
>
> using System;
>
> using System.Runtime.InteropServices;
>
> using Microsoft.Win32;
>
> using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
> System.
> Nachinstallieren???
>
>
> namespace ExcelAddInFunc
> {
> [ClassInterface(ClassInterfaceType.AutoDual)]
> public class fncAdd
> {
>
> public fncAdd()
> {
> }
>
> public string __T()
> {
> string s="Test";
> Excel.Application m_objExcel =
> (Excel.Application)Marshal.GetActiveObject("Excel. Application");
> Excel.Worksheet ws =
> (Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
> Excel.Range region=(Excel.Range)ws.Cells[10,10];
>
> s=ws.Name.ToString(); //OK!
>
> try
> {
> region.Value2="qwe"; //bumm!
> }
> catch(Exception e)
> {
> s=e.ToString();
>
> }
>
>
> return s;
> }
> }
>
>
>

[Alan]

Try this code...

namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish
to remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
ExcelPlayAreaSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute( "01926917-EBC7-4279-A4A1-BFC25871F0CE" ), ProgId(
"ExcelPlayArea.Connect" )]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class fncAdd : Object, Extensibility.IDTExtensibility2
{

/// <summary>
/// Implements the constructor for the Add-in object.
/// Place your initialization code within this method.
/// </summary>
public Connect()
{
}

/// <summary>
/// Implements the OnConnection method of the IDTExtensibility2
interface.
/// Receives notification that the Add-in is being loaded.
/// </summary>
/// <param term='application'>
/// Root object of the host application.
/// </param>
/// <param term='connectMode'>
/// Describes how the Add-in is being loaded.
/// </param>
/// <param term='addInInst'>
/// Object representing this Add-in.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnConnection( object application,
Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array
custom )
{
applicationObject = application;
addInInstance = addInInst;

}

/// <summary>
/// Implements the OnDisconnection method of the
IDTExtensibility2 interface.
/// Receives notification that the Add-in is being unloaded.
/// </summary>
/// <param term='disconnectMode'>
/// Describes how the Add-in is being unloaded.
/// </param>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnDisconnection( Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom )
{
}

/// <summary>
/// Implements the OnAddInsUpdate method of the
IDTExtensibility2 interface.
/// Receives notification that the collection of Add-ins has
changed.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnAddInsUpdate( ref System.Array custom )
{
}

/// <summary>
/// Implements the OnStartupComplete method of the
IDTExtensibility2 interface.
/// Receives notification that the host application has
completed loading.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnStartupComplete( ref System.Array custom )
{
}

/// <summary>
/// Implements the OnBeginShutdown method of the
IDTExtensibility2 interface.
/// Receives notification that the host application is being
unloaded.
/// </summary>
/// <param term='custom'>
/// Array of parameters that are host application specific.
/// </param>
/// <seealso class='IDTExtensibility2' />
public void OnBeginShutdown( ref System.Array custom )
{
}

private object applicationObject;
private object addInInstance;

public string __T()
{
string s = "Test";
// Get the active worksheet.
object sheet = applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember(
"Range",BindingFlags.GetProperty, null, sheet, new object[] { "J10",
Missing.Value });

// Set the value of cell J10
range.GetType().InvokeMember( "Value", BindingFlags.SetProperty,
null, range, new object[] { s } );
return s;
}


#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

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

"Frank M. Walter" wrote:

Hi Allan,

I can not manage it....
would you like to be so kind to send me your sample project ?
As a zip-file ?
Frank

Please take this address
ivobraun@ gmx.net

THANKS!!!
"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:D5**********************************@microsof t.com...

Is your project a Class Library ?

You need to inherit from : Object, Extensibility.IDTExtensibility2

The way I did it was to create a shared add-in project is visual studio
2005
and then merge the class library code with the shared add-in project ...

So just to clarify ... i think you need a combination of both class
library
and shared add-in projects...

"Frank M. Walter" wrote:

Does not work.
The same exception.

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:1C**********************************@microsof t.com...
> applicationObject is from OnConnection() or shared add-in
>
> // Get the active worksheet.
> object sheet = applicationObject.GetType().InvokeMember( "ActiveSheet",
> BindingFlags.GetProperty, null, m_applicationObject, null );
>
> // Get cell J10
> object range = sheet.GetType().InvokeMember( "Range",
> BindingFlags.GetProperty, null, sheet, new object[] { "J10",
> Missing.Value }
> );
>
> // Set the value of cell J10
> range.GetType().InvokeMember( "Value", BindingFlags.SetProperty, null,
> range, new object[] { "Test" } );
>
> "Frank M. Walter" wrote:
>
>>
>> Hello,
>> I have made an small AddIn with udf for excel 2003.
>> I use vs2003.
>> The point of view is the function __T()
>> I call it in excel sheet writing =__T() [enter]
>>
>> I am not able to set a value to a given cell.
>> region.Value2="qwe"; //bumm!
>> A exception will be trown. On all PCs with excel.
>> HRESULT 0x800A03EC
>>
>> Is it com-registration problem?
>>
>> But, it is possible to read the name of worksheet.
>> s=ws.Name.ToString(); //OK!
>>
>> Who knows, what is the problem?
>> Thanks
>>
>> microsoft.public.dotnet.languages.csharp
>>
>> using System;
>>
>> using System.Runtime.InteropServices;
>>
>> using Microsoft.Win32;
>>
>> using Excel=Microsoft.Office.Interop.Excel; // ist nicht auf jedem
>> System.
>> Nachinstallieren???
>>
>>
>> namespace ExcelAddInFunc
>> {
>> [ClassInterface(ClassInterfaceType.AutoDual)]
>> public class fncAdd
>> {
>>
>> public fncAdd()
>> {
>> }
>>
>> public string __T()
>> {
>> string s="Test";
>> Excel.Application m_objExcel =
>> (Excel.Application)Marshal.GetActiveObject("Excel. Application");
>> Excel.Worksheet ws =
>> (Excel.Worksheet)m_objExcel.ActiveWorkbook.ActiveS heet;
>> Excel.Range region=(Excel.Range)ws.Cells[10,10];
>>
>> s=ws.Name.ToString(); //OK!
>>
>> try
>> {
>> region.Value2="qwe"; //bumm!
>> }
>> catch(Exception e)
>> {
>> s=e.ToString();
>>
>> }
>>
>>
>> return s;
>> }
>> }
>>
>>
>>

[Frank M. Walter]

Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...

[Alan]

Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...
namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish to
remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
ExcelAddInFuncSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}
public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing, omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject, null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] { "J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion
}
}
"Frank M. Walter" wrote:

Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...

[Frank M. Walter]

Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...

Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...
namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish
to
remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
ExcelAddInFuncSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}
public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion
}
}
"Frank M. Walter" wrote:

Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...

[Galcho[MCSD.NET]]

you should install it with MS Office - check detailed options for each
application and select ".NET Programatically support"

VSTO installs primary interops too

I hope this helps
Galin Iliev[MCSD.NET]
www.galcho.com

[Frank M. Walter]

It is installed with original office 2003 CD
Also .NET p.support.

The interops can be see in GAC.

They can not be put as reference to vs2005 project. Why?

Frank
"Galcho[MCSD.NET]" <ga****@gmail.com> schrieb im Newsbeitrag
news:11**********************@i39g2000cwa.googlegr oups.com...

you should install it with MS Office - check detailed options for each
application and select ".NET Programatically support"

VSTO installs primary interops too

I hope this helps
Galin Iliev[MCSD.NET]
www.galcho.com

[Frank M. Walter]

The only way to put a reference is editing of vcproj

<ItemGroup>
<COMReference Include="Excel">
<Guid>{00020813-0000-0000-C000-000000000046}</Guid>
<VersionMajor>1</VersionMajor>
<VersionMinor>5</VersionMinor>
<Lcid>0</Lcid>
<WrapperTool>primary</WrapperTool>
<Isolated>False</Isolated>
</COMReference>
<COMReference Include="Microsoft.Office.Core">
<Guid>{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}</Guid>
<VersionMajor>2</VersionMajor>
<VersionMinor>3</VersionMinor>
<Lcid>0</Lcid>
<WrapperTool>primary</WrapperTool>
<Isolated>False</Isolated>
</COMReference>
<COMReference Include="VBIDE">
<Guid>{0002E157-0000-0000-C000-000000000046}</Guid>
<VersionMajor>5</VersionMajor>
<VersionMinor>3</VersionMinor>
<Lcid>0</Lcid>
<WrapperTool>primary</WrapperTool>
<Isolated>False</Isolated>
</COMReference>
</ItemGroup>

[Alan]

To reference of Microsoft.Office.Interop.Excel

right click on references --> add reference --> COM Tab --> choose Microsoft
Excel 11.0 Object Library --> OK

"Frank M. Walter" wrote:

Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...

Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...
namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish
to
remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
ExcelAddInFuncSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}
public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion
}
}
"Frank M. Walter" wrote:

Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...

[Alan]

I dont think it's a bug... It's probably a security issues, do you really
want a UDF to modify another cell automatically ? it could overwrite data.

So I dont think your going to be able to modify a cell other than the one
calling the UDF... The way I got around this was to create a tool bar
button...
You could add an

"Frank M. Walter" wrote:

Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...

Ok, The problem appears to be caused by setting a cell other than the
calling cell ...

If you create a command bar button you can write to any cell that buttons
click event ...
namespace ExcelAddInFunc
{
using System;
using System.Collections.Specialized;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text;
using Extensibility;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

#region Read me for Add-in installation and setup information.
// When run, the Add-in wizard prepared the registry for the Add-in.
// At a later time, if the Add-in becomes unavailable for reasons such as:
// 1) You moved this project to a computer other than which is was
originally created on.
// 2) You chose 'Yes' when presented with a message asking if you wish
to
remove the Add-in.
// 3) Registry corruption.
// you will need to re-register the Add-in by building the
ExcelAddInFuncSetup project,
// right click the project in the Solution Explorer, then choose install.
#endregion

/// <summary>
/// The object for implementing an Add-in.
/// </summary>
/// <seealso class='IDTExtensibility2' />
[GuidAttribute("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
ProgId("ExcelAddInFunc.Connect")]
[ClassInterface( ClassInterfaceType.AutoDual )]
[ComVisible( true )]
public class Connect : Object, Extensibility.IDTExtensibility2
{

private CommandBar m_CommandBar;
private CommandBarButton m_Button;
private object m_applicationObject;
private object addInInstance;

public Connect()
{
}

public void OnConnection(object application, Extensibility.ext_ConnectMode
connectMode, object addInInst, ref System.Array custom)
{
m_applicationObject = application;
addInInstance = addInInst;
if( connectMode != Extensibility.ext_ConnectMode.ext_cm_Startup
&& connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
{
OnStartupComplete( ref custom );
}
}

public void OnDisconnection(Extensibility.ext_DisconnectMode
disconnectMode, ref System.Array custom)
{
m_applicationObject = null;

}

public void OnAddInsUpdate(ref System.Array custom)
{
}

public void OnStartupComplete( ref System.Array custom )
{
CommandBars oCommandBars = null;
CommandBar oStandardBar = null;

try
{

oCommandBars = ( CommandBars
)m_applicationObject.GetType().InvokeMember(
"CommandBars",
BindingFlags.GetProperty,
null,
m_applicationObject,
null );
}
catch { return; }

// Set up a custom button on the "Standard" commandbar.
try
{
oStandardBar = oCommandBars[ "Standard" ];
}
catch { return; }

try
{
m_CommandBar = oCommandBars[ "AIR" ];
}
catch
{
m_CommandBar = oCommandBars.Add( "AIR", 1,
System.Reflection.Missing.Value, false );
}

m_CommandBar.Visible = true;

CreateButton();

oStandardBar = null;
oCommandBars = null;
}
public void OnBeginShutdown(ref System.Array custom)
{
}

private void CreateButton()
{
int nNumberOfControls = this.m_CommandBar.Controls.Count;

for( int i = 1; i <= nNumberOfControls; i++ )
{
if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
{
m_Button = ( CommandBarButton
)this.m_CommandBar.Controls[ i ];
break;
}
}

if( m_Button == null )
{
object omissing = System.Reflection.Missing.Value;

m_Button = ( CommandBarButton
)this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
omissing );

m_Button.Enabled = true;

m_Button.Caption = "Set Cell J10";

m_Button.Style = MsoButtonStyle.msoButtonCaption;

m_Button.Tag = "MyButton";

m_Button.Visible = true;

m_Button.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler( this.T );
}
return;
}
private void T( CommandBarButton cmdBarbutton, ref bool cancel )
{
string s = "Test";
// Get the active worksheet.
object sheet = m_applicationObject.GetType().InvokeMember(
"ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
null );

// Get cell J10
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, new object[] {
"J10",Missing.Value } );

try
{
// Set the value of cell J10
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, new object[] { s } );
}
catch( Exception ex )
{
MessageBox.Show( ex.ToString() );
}

}

#region COM Registration
[ComRegisterFunctionAttribute]
public static void RegisterFunctionAIRASF2( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction( System.Type t )
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
( "CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable" );
}
#endregion
}
}
"Frank M. Walter" wrote:

Sorry...
Exception has been thrown by the target of invocation.

In debug-mode
range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
range, new object[] { s });

It was this function...

[Frank M. Walter]

YES !!!

I was looking in .NET

THANKS!

Frank
"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:2A**********************************@microsof t.com...

To reference of Microsoft.Office.Interop.Excel

right click on references --> add reference --> COM Tab --> choose
Microsoft
Excel 11.0 Object Library --> OK

"Frank M. Walter" wrote:

Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...

> Ok, The problem appears to be caused by setting a cell other than the
> calling cell ...
>
> If you create a command bar button you can write to any cell that
> buttons
> click event ...
>
>
> namespace ExcelAddInFunc
> {
> using System;
> using System.Collections.Specialized;
> using System.Drawing;
> using System.Runtime.InteropServices;
> using System.Reflection;
> using System.Text;
> using Extensibility;
> using Microsoft.Office.Core;
> using Excel = Microsoft.Office.Interop.Excel;
> using System.Windows.Forms;
>
> #region Read me for Add-in installation and setup information.
> // When run, the Add-in wizard prepared the registry for the Add-in.
> // At a later time, if the Add-in becomes unavailable for reasons such
> as:
> // 1) You moved this project to a computer other than which is was
> originally created on.
> // 2) You chose 'Yes' when presented with a message asking if you
> wish
> to
> remove the Add-in.
> // 3) Registry corruption.
> // you will need to re-register the Add-in by building the
> ExcelAddInFuncSetup project,
> // right click the project in the Solution Explorer, then choose
> install.
> #endregion
>
> /// <summary>
> /// The object for implementing an Add-in.
> /// </summary>
> /// <seealso class='IDTExtensibility2' />
> [GuidAttribute("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
> ProgId("ExcelAddInFunc.Connect")]
> [ClassInterface( ClassInterfaceType.AutoDual )]
> [ComVisible( true )]
> public class Connect : Object, Extensibility.IDTExtensibility2
> {
>
> private CommandBar m_CommandBar;
> private CommandBarButton m_Button;
> private object m_applicationObject;
> private object addInInstance;
>
> public Connect()
> {
> }
>
> public void OnConnection(object application,
> Extensibility.ext_ConnectMode
> connectMode, object addInInst, ref System.Array custom)
> {
> m_applicationObject = application;
> addInInstance = addInInst;
> if( connectMode !=
> Extensibility.ext_ConnectMode.ext_cm_Startup
> && connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
> {
> OnStartupComplete( ref custom );
> }
> }
>
> public void OnDisconnection(Extensibility.ext_DisconnectMode
> disconnectMode, ref System.Array custom)
> {
> m_applicationObject = null;
>
> }
>
> public void OnAddInsUpdate(ref System.Array custom)
> {
> }
>
> public void OnStartupComplete( ref System.Array custom )
> {
> CommandBars oCommandBars = null;
> CommandBar oStandardBar = null;
>
> try
> {
>
> oCommandBars = ( CommandBars
> )m_applicationObject.GetType().InvokeMember(
> "CommandBars",
> BindingFlags.GetProperty,
> null,
> m_applicationObject,
> null );
> }
> catch { return; }
>
> // Set up a custom button on the "Standard" commandbar.
> try
> {
> oStandardBar = oCommandBars[ "Standard" ];
> }
> catch { return; }
>
> try
> {
> m_CommandBar = oCommandBars[ "AIR" ];
> }
> catch
> {
> m_CommandBar = oCommandBars.Add( "AIR", 1,
> System.Reflection.Missing.Value, false );
> }
>
> m_CommandBar.Visible = true;
>
> CreateButton();
>
> oStandardBar = null;
> oCommandBars = null;
> }
>
>
> public void OnBeginShutdown(ref System.Array custom)
> {
> }
>
> private void CreateButton()
> {
> int nNumberOfControls = this.m_CommandBar.Controls.Count;
>
> for( int i = 1; i <= nNumberOfControls; i++ )
> {
> if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
> {
> m_Button = ( CommandBarButton
> )this.m_CommandBar.Controls[ i ];
> break;
> }
> }
>
> if( m_Button == null )
> {
> object omissing = System.Reflection.Missing.Value;
>
> m_Button = ( CommandBarButton
> )this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
> omissing );
>
> m_Button.Enabled = true;
>
> m_Button.Caption = "Set Cell J10";
>
> m_Button.Style = MsoButtonStyle.msoButtonCaption;
>
> m_Button.Tag = "MyButton";
>
> m_Button.Visible = true;
>
> m_Button.Click += new
> Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler(
> this.T );
> }
> return;
> }
> private void T( CommandBarButton cmdBarbutton, ref bool cancel )
> {
> string s = "Test";
> // Get the active worksheet.
> object sheet = m_applicationObject.GetType().InvokeMember(
> "ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
> null );
>
> // Get cell J10
> object range = sheet.GetType().InvokeMember( "Range",
> BindingFlags.GetProperty, null, sheet, new object[] {
> "J10",Missing.Value } );
>
> try
> {
> // Set the value of cell J10
> range.GetType().InvokeMember( "Value",
> BindingFlags.SetProperty, null, range, new object[] { s } );
> }
> catch( Exception ex )
> {
> MessageBox.Show( ex.ToString() );
> }
>
> }
>
> #region COM Registration
> [ComRegisterFunctionAttribute]
> public static void RegisterFunctionAIRASF2( System.Type t )
> {
> Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
> ( "CLSID\\{" + t.GUID.ToString().ToUpper() +
> "}\\Programmable" );
> }
>
> [ComUnregisterFunctionAttribute]
> public static void UnregisterFunction( System.Type t )
> {
> Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
> ( "CLSID\\{" + t.GUID.ToString().ToUpper() +
> "}\\Programmable" );
> }
> #endregion
>
>
> }
> }
>
>
> "Frank M. Walter" wrote:
>
>> Sorry...
>> Exception has been thrown by the target of invocation.
>>
>> In debug-mode
>> range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
>> range, new object[] { s });
>>
>> It was this function...
>>
>>
>>

[Frank M. Walter]

Well, our customer do want it...

Frank

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:B0**********************************@microsof t.com...

I dont think it's a bug... It's probably a security issues, do you really
want a UDF to modify another cell automatically ? it could overwrite
data.

So I dont think your going to be able to modify a cell other than the one
calling the UDF... The way I got around this was to create a tool bar
button...
You could add an

"Frank M. Walter" wrote:

Hello Alan,
of course it is possible to set a value from out of sheet.
But what about the user function ?
Is it a bug?

BTW:
I am not able to put reference of Microsoft.Office.Interop.Excel in my
vs2005 project.
Microsoft.Office.Interop.Excel is in assambly cash.
It wont be listed in VS2005 Reference Listbox.
How to put Microsoft.Office.Interop.Excel to .NET listbox and get it in
Project?

Thanks
Frank

"Alan" <Al**@discussions.microsoft.com> schrieb im Newsbeitrag
news:74**********************************@microsof t.com...

> Ok, The problem appears to be caused by setting a cell other than the
> calling cell ...
>
> If you create a command bar button you can write to any cell that
> buttons
> click event ...
>
>
> namespace ExcelAddInFunc
> {
> using System;
> using System.Collections.Specialized;
> using System.Drawing;
> using System.Runtime.InteropServices;
> using System.Reflection;
> using System.Text;
> using Extensibility;
> using Microsoft.Office.Core;
> using Excel = Microsoft.Office.Interop.Excel;
> using System.Windows.Forms;
>
> #region Read me for Add-in installation and setup information.
> // When run, the Add-in wizard prepared the registry for the Add-in.
> // At a later time, if the Add-in becomes unavailable for reasons such
> as:
> // 1) You moved this project to a computer other than which is was
> originally created on.
> // 2) You chose 'Yes' when presented with a message asking if you
> wish
> to
> remove the Add-in.
> // 3) Registry corruption.
> // you will need to re-register the Add-in by building the
> ExcelAddInFuncSetup project,
> // right click the project in the Solution Explorer, then choose
> install.
> #endregion
>
> /// <summary>
> /// The object for implementing an Add-in.
> /// </summary>
> /// <seealso class='IDTExtensibility2' />
> [GuidAttribute("5FDC27C6-0146-4553-BB7E-5DC789DE7A2E"),
> ProgId("ExcelAddInFunc.Connect")]
> [ClassInterface( ClassInterfaceType.AutoDual )]
> [ComVisible( true )]
> public class Connect : Object, Extensibility.IDTExtensibility2
> {
>
> private CommandBar m_CommandBar;
> private CommandBarButton m_Button;
> private object m_applicationObject;
> private object addInInstance;
>
> public Connect()
> {
> }
>
> public void OnConnection(object application,
> Extensibility.ext_ConnectMode
> connectMode, object addInInst, ref System.Array custom)
> {
> m_applicationObject = application;
> addInInstance = addInInst;
> if( connectMode !=
> Extensibility.ext_ConnectMode.ext_cm_Startup
> && connectMode != Extensibility.ext_ConnectMode.ext_cm_AfterStartup )
> {
> OnStartupComplete( ref custom );
> }
> }
>
> public void OnDisconnection(Extensibility.ext_DisconnectMode
> disconnectMode, ref System.Array custom)
> {
> m_applicationObject = null;
>
> }
>
> public void OnAddInsUpdate(ref System.Array custom)
> {
> }
>
> public void OnStartupComplete( ref System.Array custom )
> {
> CommandBars oCommandBars = null;
> CommandBar oStandardBar = null;
>
> try
> {
>
> oCommandBars = ( CommandBars
> )m_applicationObject.GetType().InvokeMember(
> "CommandBars",
> BindingFlags.GetProperty,
> null,
> m_applicationObject,
> null );
> }
> catch { return; }
>
> // Set up a custom button on the "Standard" commandbar.
> try
> {
> oStandardBar = oCommandBars[ "Standard" ];
> }
> catch { return; }
>
> try
> {
> m_CommandBar = oCommandBars[ "AIR" ];
> }
> catch
> {
> m_CommandBar = oCommandBars.Add( "AIR", 1,
> System.Reflection.Missing.Value, false );
> }
>
> m_CommandBar.Visible = true;
>
> CreateButton();
>
> oStandardBar = null;
> oCommandBars = null;
> }
>
>
> public void OnBeginShutdown(ref System.Array custom)
> {
> }
>
> private void CreateButton()
> {
> int nNumberOfControls = this.m_CommandBar.Controls.Count;
>
> for( int i = 1; i <= nNumberOfControls; i++ )
> {
> if( this.m_CommandBar.Controls[ i ].Tag == "MyButton" )
> {
> m_Button = ( CommandBarButton
> )this.m_CommandBar.Controls[ i ];
> break;
> }
> }
>
> if( m_Button == null )
> {
> object omissing = System.Reflection.Missing.Value;
>
> m_Button = ( CommandBarButton
> )this.m_CommandBar.Controls.Add( 1, omissing, omissing, omissing,
> omissing );
>
> m_Button.Enabled = true;
>
> m_Button.Caption = "Set Cell J10";
>
> m_Button.Style = MsoButtonStyle.msoButtonCaption;
>
> m_Button.Tag = "MyButton";
>
> m_Button.Visible = true;
>
> m_Button.Click += new
> Microsoft.Office.Core._CommandBarButtonEvents_Clic kEventHandler(
> this.T );
> }
> return;
> }
> private void T( CommandBarButton cmdBarbutton, ref bool cancel )
> {
> string s = "Test";
> // Get the active worksheet.
> object sheet = m_applicationObject.GetType().InvokeMember(
> "ActiveSheet", BindingFlags.GetProperty, null, m_applicationObject,
> null );
>
> // Get cell J10
> object range = sheet.GetType().InvokeMember( "Range",
> BindingFlags.GetProperty, null, sheet, new object[] {
> "J10",Missing.Value } );
>
> try
> {
> // Set the value of cell J10
> range.GetType().InvokeMember( "Value",
> BindingFlags.SetProperty, null, range, new object[] { s } );
> }
> catch( Exception ex )
> {
> MessageBox.Show( ex.ToString() );
> }
>
> }
>
> #region COM Registration
> [ComRegisterFunctionAttribute]
> public static void RegisterFunctionAIRASF2( System.Type t )
> {
> Microsoft.Win32.Registry.ClassesRoot.CreateSubKey
> ( "CLSID\\{" + t.GUID.ToString().ToUpper() +
> "}\\Programmable" );
> }
>
> [ComUnregisterFunctionAttribute]
> public static void UnregisterFunction( System.Type t )
> {
> Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey
> ( "CLSID\\{" + t.GUID.ToString().ToUpper() +
> "}\\Programmable" );
> }
> #endregion
>
>
> }
> }
>
>
> "Frank M. Walter" wrote:
>
>> Sorry...
>> Exception has been thrown by the target of invocation.
>>
>> In debug-mode
>> range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null,
>> range, new object[] { s });
>>
>> It was this function...
>>
>>
>>