How do I install an add-in to Excel's Automation Add-in List?

2
votes

I have a C# class library project for an automation add-in for which I have created a Visual Studio set-up project.

When I run the installer, I want the add-in to appear in the Excel automation add-in list (Tools-->Addins-->Automation Addin) so that I can directly include it in my Excel application.

How do I go about it?

I created a setup project following the link here http://dreamincode.net/forums/showtopic58021.htm, but the add-in does not appear in the Automation add-in list.

Am I missing something here?

1
installationadd-inexcel-2003
The link in your question points to a general article about using the Deployment facility of Visual Studio. It may help you get better answers if you specify what version of Excel and Visual Studio you are working with (and what versions of Excel you are required to be backwards-compatible with). Also suggest you read a basic tutorial on Excel Add-Ins in C# for Excel 2007 : support.microsoft.com/kb/302901 - BillW
I am working with VS 2008 and Excel 2003. Also, My automation addin calls a web-service within the code. I am hoping there are some dependancies there that are not getting taken care of. - Sandy
Hi, suggest you add the tags "web-service," "excel," "office automation" to your message. Good luck, - BillW

1 Answers

0
votes

Project properties -> Debug -> Enable the Visual Studio hosting process after run it, you will find addin that can be selected in Excel

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();

        }
    }
}