如何将加载项安装到 Excel 的自动化加载项列表？

Question

我有一个用于自动化插件的 C# 类库项目，我已为其创建了一个 Visual Studio 设置项目。

当我运行安装程序时，我希望加载项出现在 Excel 自动化加载项列表中（工具-->加载项-->自动化加载项），以便我可以将其直接包含在我的 Excel 应用程序中。

我该怎么办？

我按照此处的链接创建了一个设置项目 http://dreamincode.net/forums/showtopic58021.htm，但该加载项未出现在自动化加载项列表中。

我在这里错过了什么吗？

Answer 1

项目属性->调试->启用 Visual Studio 托管进程
运行后，你会发现可以在Excel中选择的addin

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace LongAddin
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ComVisible(true)]
    public class Functions
    {
        public Functions()
        {
        }

        //cumulative normal distribution function
        private double CND(double X)
        {
            double L = 0.0;
            double K = 0.0;
            double dCND = 0.0;
            const double a1 = 0.31938153;
            const double a2 = -0.356563782;
            const double a3 = 1.781477937;
            const double a4 = -1.821255978;
            const double a5 = 1.330274429;
            L = Math.Abs(X);
            K = 1.0 / (1.0 + 0.2316419 * L);
            dCND = 1.0 - 1.0 / Math.Sqrt(2 * Convert.ToDouble(Math.PI.ToString())) *
                Math.Exp(-L * L / 2.0) * (a1 * K + a2 * K * K + a3 * Math.Pow(K, 3.0) +
                a4 * Math.Pow(K, 4.0) + a5 * Math.Pow(K, 5.0));

            if (X < 0)
            {
                return 1.0 - dCND;
            }
            else
            {
                return dCND;
            }
        }

        //function phi
        private double phi(double x)
        {
            double phi = 0.0;

            phi = Math.Exp(-x * x / 2) / Math.Sqrt(2 * Math.PI);
            return phi;
        }

        //implied volatility using Newton-Raphson method
        public double blsimpvCall(double Price, double Strike, double Rate, double Time, double Value, double Yield)
        {
            const double ACCURACY = 1.0e-6;

            double ComputedVolatility = Math.Pow(Math.Abs(Math.Log(Price / Strike) + Rate * Time) * 2 / Time, 0.5); // initial value of volatility
            double ComputedValue = blsCall(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            double Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);

            while (Math.Abs(Value - ComputedValue) > ACCURACY)
            {
                ComputedVolatility = ComputedVolatility - ((ComputedValue - Value) / Vega);
                ComputedValue = blsCall(Price, Strike, Rate, Time, ComputedVolatility, Yield);
                Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            }

            return ComputedVolatility;
        }
        public double blsimpvPut(double Price, double Strike, double Rate, double Time, double Value, double Yield)
        {
            const double ACCURACY = 1.0e-6;

            double ComputedVolatility = Math.Pow(Math.Abs(Math.Log(Price / Strike) + Rate * Time) * 2 / Time, 0.5); // initial value of volatility
            double ComputedValue = blsPut(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            double Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);

            while (Math.Abs(Value - ComputedValue) > ACCURACY)
            {
                ComputedVolatility = ComputedVolatility - ((ComputedValue - Value) / Vega);
                ComputedValue = blsPut(Price, Strike, Rate, Time, ComputedVolatility, Yield);
                Vega = blsvega(Price, Strike, Rate, Time, ComputedVolatility, Yield);
            }

            return ComputedVolatility;
        }
        //Call pricer
        public double blsCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double Call = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);

            Call = Price * Math.Exp(-Yield * Time) * CND(d1) - Strike * Math.Exp(-Rate * Time) * CND(d2);
            return Call;
        }

        //Put pricer
        public double blsPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double Put = 0.0;


            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);

            Put = Strike * Math.Exp(-Rate * Time) * CND(-d2) - Price * Math.Exp(-Yield * Time) * CND(-d1);
            return Put;
        }

        //delta for Call
        public double blsdeltaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));

            return Math.Exp(-Yield * Time) * CND(d1);
        }

        //delta for Put
        public double blsdeltaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));

            return Math.Exp(-Yield * Time) * CND(d1) - 1;
        }

        //gamma is the same for Put and Call
        public double blsgamma(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));

            return Math.Exp(-Yield * Time) * phi(d1) / (Price * Volatility * Math.Sqrt(Time));
        }

        //vega is the same for Put and Call
        public double blsvega(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            return Price * Math.Exp(-Yield * Time) * phi(d1) * Math.Sqrt(Time);
        }

        //theta for Call
        public double blsthetaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return -Math.Exp(-Yield * Time) * Price * phi(d1) * Volatility / (2 * Math.Sqrt(Time)) - Rate * Strike * Math.Exp(-Rate * Time) * CND(d2) + Yield * Price * Math.Exp(-Yield * Time) * CND(d1);
        }

        //theta for Put
        public double blsthetaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return -Math.Exp(-Yield * Time) * Price * phi(d1) * Volatility / (2 * Math.Sqrt(Time)) + Rate * Strike * Math.Exp(-Rate * Time) * CND(-d2) - Yield * Price * Math.Exp(-Yield * Time) * CND(-d1);
        }

        //rho for Call
        public double blsrhoCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return Strike * Time * Math.Exp(-Rate * Time) * CND(d2);
        }

        //rho for Put
        public double blsrhoPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return -Strike * Time * Math.Exp(-Rate * Time) * CND(-d2);
        }

        //volga is the same for Call and Put
        public double blsvolga(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            return Price * Math.Exp(-Yield * Time) * phi(d1) * Math.Sqrt(Time) * d1 * d2 / Volatility;

        }

        //vanna is the same for Call and Put
        public double blsvanna(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double vanna = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);

            vanna = -Math.Exp(-Yield * Time) * phi(d1) * d2 / Volatility;

            return vanna;
        }

        //charm for Call
        public double blscharmCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double charmC = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);

            charmC = -Yield * Math.Exp(-Yield * Time) * CND(d1) + Math.Exp(-Yield * Time) * phi(d1) * (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) / (2 * Time * Volatility * Math.Sqrt(Time));
            return charmC;
        }

        //charm for Put
        public double blscharmPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double charmP = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);

            charmP = Yield * Math.Exp(-Yield * Time) * CND(-d1) - Math.Exp(-Yield * Time) * phi(d1) * (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) / (2 * Time * Volatility * Math.Sqrt(Time));
            return charmP;
        }

        //color is the same for Call and Put
        public double blscolor(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double color = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);

            color = -Math.Exp(-Yield * Time) * (phi(d1) / (2 * Price * Time * Volatility * Math.Sqrt(Time))) * (2 * Yield * Time + 1 + (2 * (Rate - Yield) * Time - d2 * Volatility * Math.Sqrt(Time)) * d1 / (2 * Time * Volatility * Math.Sqrt(Time)));
            return color;
        }

        //dual delta for Call
        public double blsdualdeltaCall(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double ddelta = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            ddelta = -Math.Exp(-Rate * Time) * CND(d2);
            return ddelta;
        }

        //dual delta for Put
        public double blsdualdeltaPut(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double ddelta = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);
            ddelta = Math.Exp(-Rate * Time) * CND(-d2);
            return ddelta;
        }

        //dual gamma is the same for Call and Put
        public double blsdualgamma(double Price, double Strike, double Rate, double Time, double Volatility, double Yield)
        {
            double d1 = 0.0;
            double d2 = 0.0;
            double dgamma = 0.0;

            d1 = (Math.Log(Price / Strike) + (Rate - Yield + Volatility * Volatility / 2.0) * Time) / (Volatility * Math.Sqrt(Time));
            d2 = d1 - Volatility * Math.Sqrt(Time);

            dgamma = Math.Exp(-Rate * Time) * phi(d2) / (Strike * Volatility * Math.Sqrt(Time));
            return dgamma;
        }

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

            Registry.ClassesRoot.CreateSubKey(

              GetSubKeyName(type, "Programmable"));

            RegistryKey key = Registry.ClassesRoot.OpenSubKey(

              GetSubKeyName(type, "InprocServer32"), true);

            key.SetValue("",

              System.Environment.SystemDirectory + @"\mscoree.dll",

              RegistryValueKind.String);
        }
        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(Type type) 
        { 
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type)); 
        }
        [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; } 

        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(Type type)
        {

            Registry.ClassesRoot.DeleteSubKey(

              GetSubKeyName(type, "Programmable"), false);
        }

        private static string GetSubKeyName(Type type,

          string subKeyName)
        {

            System.Text.StringBuilder s =

              new System.Text.StringBuilder();

            s.Append(@"CLSID\{");

            s.Append(type.GUID.ToString().ToUpper());

            s.Append(@"}\");

            s.Append(subKeyName);

            return s.ToString();

        }
    }
}