C# and Excel

KSparks212
KSparks212 used Ask the Experts™
on
I need to know how to call an external Excel function from a class.  The excel formula works fine inside the main function but not in the separate class.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel;
 
namespace TestNormSInv
{
    public class optionMath
    {
        public double testDouble(double price)
        {
            double result = 0.0;
            //The line below produces the error !!!!!!!!!!!!!!!!!!!!!
            result = WorksheetFunction.NormSDist(price);
            return result;
        }
 
        public double testTriple(double price)
        {
            double result = 0.0;
            //The line below produces the error !!!!!!!!!!!!!!!!!!!!!
            result = WorksheetFunction.NormSDist(price);
            return result;
        }
    }
    class CalcExcel
    {
        static void Main(string[] args)
        {
            double result = 0.0;    
            Excel.Application oXL;
            try {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = false;
                oXL.UserControl = false;
 
                // This code works
                result = oXL.WorksheetFunction.NormSDist(1.04);
                Console.WriteLine("Normsdist 1.04: {0}", result);
                optionMath OM = new optionMath();
 
                //This does not work
                result = OM.testDouble(1.04);
            }
            catch (SqlException e)
            {
                // Display error
                Console.WriteLine("Error: " + e);
            }
            finally {
                Console.WriteLine("Connection closed.");
            }
        }
    }
}

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You cannot call methods on the WorksheetFunction because it is an Interface (not a class).  The only way to do what you want is to use the WorksheetFunction property from the Excel.Application class instance.  Because you are using the Excel Interop, you need to instantiate Excel.Application (run the Excel process).

This an example of your working class (I used a property to wrap the getting of the WorksheetFunction from the Excel.Application).  I also implemented IDisposable to quit excel after the class leaves scope.  You would use it like this:
                using (optionMath OM = new optionMath())
                {

                    //This does not work
                    result = OM.testDouble(1.04);
                }
public class optionMath :IDisposable
{
    Excel.Application oXL;
    public optionMath()
    {
        oXL = new Excel.Application();
        oXL.Visible = false;
        oXL.UserControl = false;
    }
    private WorksheetFunction WorksheetFunction
    {
        get { return oXL.WorksheetFunction; }
    }
    public double testDouble(double price)
    {
        double result = 0.0;
        //The line below produces the error !!!!!!!!!!!!!!!!!!!!!
        result = WorksheetFunction.NormSDist(price);
        return result;
    }
 
    public double testTriple(double price)
    {
        double result = 0.0;
        //The line below produces the error !!!!!!!!!!!!!!!!!!!!!
        result = WorksheetFunction.NormSDist(price);
        return result;
    }
 
    #region IDisposable Members
 
    public void Dispose()
    {
        if (this.oXL != null)
        {
            oXL.Quit();
            oXL = null;
        }
    }
 
    #endregion
}

Open in new window

Author

Commented:
The code submitted did not work.  Can you change the code so that once I compile it, it will run unmodified?  Or submit any example code that does work with any excel function.  Or direct me to a book or URL that at least explains how to make the necessary changes.
Here is the complete code:
(You need to add a reference to the Excel COM Interop object (I am using Office 2003's version))
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
 
namespace TestNormSInv
{
public class optionMath :IDisposable
{
    Excel.Application oXL;
    public optionMath()
    {
        oXL = new Excel.Application();
        oXL.Visible = false;
        oXL.UserControl = false;
    }
    private WorksheetFunction WorksheetFunction
    {
        get { return oXL.WorksheetFunction; }
    }
    public double testDouble(double price)
    {
        double result = 0.0;
        //The line below produces the error !!!!!!!!!!!!!!!!!!!!!
        result = WorksheetFunction.NormSDist(price);
        return result;
    }
 
    public double testTriple(double price)
    {
        double result = 0.0;
        //The line below produces the error !!!!!!!!!!!!!!!!!!!!!
        result = WorksheetFunction.NormSDist(price);
        return result;
    }
 
    #region IDisposable Members
 
    public void Dispose()
    {
        if (this.oXL != null)
        {
            oXL.Quit();
            oXL = null;
        }
    }
 
    #endregion
}
    public class CalcExcel
    {
        static void Main(string[] args)
        {
            double result = 0.0;
            Excel.Application oXL;
            try
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = false;
                oXL.UserControl = false;                
 
                // This code works
                result = oXL.WorksheetFunction.NormSDist(1.04);
                Console.WriteLine("Normsdist 1.04: {0}", result);
                oXL.Quit();
                oXL = null;
 
                using (optionMath OM = new optionMath())
                {
 
                    result = OM.testDouble(1.04);
                    Console.WriteLine("Normsdist (from optionMath) 1.04: {0}", result);
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: " + e);
            }
            finally
            {
                Console.WriteLine("Connection closed.");
            }
        }
    }
}

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial