On the development of Excel (IV.) Excel custom functions

Related articles:

On Excel Development (1) Overview of Excel Development

On Excel Development (II.) Excel menu system

On the development of Excel (III.) Excel object model

We know that there are many built-in functions in Excel, such as summation, averaging, string manipulation functions, financial functions, and so on. In some cases, these functions may not meet our requirements in conjunction with business requirements, such as I want a function to get the latest price of a stock from a webservice; I want a function that can get the current weather conditions that we can write with Excel custom functions (User Define function, UDF), so that we can call our custom functions directly in Excel to meet specific business requirements, and generally because the granularity of this custom function is relatively small, we could write many basic custom functions based on business requirements. Then, based on these custom functions, write a variety of complex analysis reports.

There are a variety of ways to write a UDF, for example, write custom functions directly in VBA; If you are familiar with C + +, you can write custom functions into XLL, or you can use the. NET technology with Exceldna, or you can compile your code into XLL, if you are familiar with. NET, Use C # to write a custom function class library, and then register the class library as a COM component or call it in Excel. The following is a brief introduction to these methods and their advantages and disadvantages.

1. Implement Excel custom functions using C # class library registration

I am more familiar with. NET, so I first introduce this in. NET can be used in Excel Custom Function development mode, this method is relatively simple. Before the beginning, or back to our previous plans for yy Plug-ins, our yy plug-ins have weather, finance, maps and other functions, now we assume that the need for a weather custom function, through this function can get a City one day weather conditions, such as temperature.

First we need to create a simple C # class library, the following figure, which is named YYWEATHERUDF.

Then, we create a base class UDFBase.cs of all the custom functions, in which we put some of the basic registration COM components required and the way to block objects of object so that they don't appear in the Excel UDF function. One thing to note is that when registering and canceling registration as a COM component, to prevent Excel from finding Mscoree.dll, you need to write all of its paths to the registry, which is the following code to implement this functionality.

Public abstract class Udfbase {///<summary>///Troubleshoot problems with Excel prompts in some machines cannot find mscoree.dll///here in the registry, note the DLL's path Register, this method is invoked when the class library is registered as a Com component///using regasm///</summary>///<param name= "type" ></param> [Com Registerfunctionattribute] public static void Registerfunction (type type) {Registry.ClassesRoot.CreateSub
        Key (Getsubkeyname (Type, "programmable"));
        RegistryKey key = Registry.ClassesRoot.OpenSubKey (Getsubkeyname (Type, "InprocServer32"), true); Key.
    SetValue ("", System.Environment.SystemDirectory + @ "\mscoree.dll", registryvaluekind.string); [Comunregisterfunctionattribute] public static void Unregisterfunction (type type) {registry.cl
    Assesroot.deletesubkey (Getsubkeyname (Type, "programmable"), false); private static string Getsubkeyname (type type, string subkeyname) {return string. Format ("Clsid\\{{{0}}}\\{1} ", type. Guid. ToString ().
    ToUpper (), subkeyname); }
}