Incorrect UDF call

[GSerjo]

Hi All,

I’ve created com automation add-in for Excel 2007 in Visual Studio 2008(I
use com automation add-in because I’ve to create UDFs)

All works fine, but udf function is called before user is pressed Ok in
“Function Arguments†window, i.e. the function is called when all function
arguments are entered. How I can change this, i.e. the function should be
called ONCE and ONLY after “Ok†button is pressed.

Here is the code


[ComVisible(true)]
public interface IUDFs

{

void Test(object value1, object value2);

}



[ClassInterface(ClassInterfaceType.None)]
[ComVisible(true)]
[Guid("6D3442CB-5DCB-44d1-8AFD-2693A8ACF465")]
[ProgId("AutomationAddin.UDFs")]
public class UDFs : IUDFs
{
public UDFs()
{

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

}

#region Implementation of IUDFs

public void Test(object value1, object value2)
{
if (value1 is Missing || value2 is Missing)
return;
//Do Work
}

#endregion

#region Register UnRegister Add-in

[ComRegisterFunction]
public static void RegisterFunction(Type type)
{

Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type,
"Programmable"));

RegistryKey key =
Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), true);

key.SetValue(string.Empty, Environment.SystemDirectory +
@"\mscoree.dll", RegistryValueKind.String);

}



[ComUnregisterFunction]
public static void UnregisterFunction(Type type)

{

Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type,
"Programmable"), false);

}



private static string GetSubKeyName(Type type, string subKeyName)
{

var subKey = new StringBuilder();
subKey.Append(@"CLSID\{");
subKey.Append(type.GUID.ToString().ToUpper());
subKey.Append(@"}\");
subKey.Append(subKeyName);
return subKey.ToString();

}
#endregion Register UnRegister Add-in

}

Thanks in advance for any help!

Thanks,
GSerjo

 

[Charles Williams]

See http://www.decisionmodels.com/calcsecretsj.htm#FuncWiz for a VBA
solution you may be able to adapt : I think this still works in Excel 2007.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[GSerjo]

Hi Charles,

Thanks a lot for the answer, but the article does not contain answer

See http://www.decisionmodels.com/calcsecretsj.htm#FuncWiz for a VBA
solution you may be able to adapt : I think this still works in Excel 2007.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

Hi All,

I've created com automation add-in for Excel 2007 in Visual Studio 2008(I
use com automation add-in because I've to create UDFs)

All works fine, but udf function is called before user is pressed Ok in
"Function Arguments" window, i.e. the function is called when all function
arguments are entered. How I can change this, i.e. the function should be
called ONCE and ONLY after "Ok" button is pressed.

Here is the code


[ComVisible(true)]
public interface IUDFs

{

void Test(object value1, object value2);

}



[ClassInterface(ClassInterfaceType.None)]
[ComVisible(true)]
[Guid("6D3442CB-5DCB-44d1-8AFD-2693A8ACF465")]
[ProgId("AutomationAddin.UDFs")]
public class UDFs : IUDFs
{
public UDFs()
{

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

}

#region Implementation of IUDFs

public void Test(object value1, object value2)
{
if (value1 is Missing || value2 is Missing)
return;
//Do Work
}

#endregion

#region Register UnRegister Add-in

[ComRegisterFunction]
public static void RegisterFunction(Type type)
{

Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type,
"Programmable"));

RegistryKey key =
Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"),
true);

key.SetValue(string.Empty, Environment.SystemDirectory +
@"\mscoree.dll", RegistryValueKind.String);

}



[ComUnregisterFunction]
public static void UnregisterFunction(Type type)

{

Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type,
"Programmable"), false);

}



private static string GetSubKeyName(Type type, string subKeyName)
{

var subKey = new StringBuilder();
subKey.Append(@"CLSID\{");
subKey.Append(type.GUID.ToString().ToUpper());
subKey.Append(@"}\");
subKey.Append(subKeyName);
return subKey.ToString();

}
#endregion Register UnRegister Add-in

}

Thanks in advance for any help!

Thanks,
GSerjo