Solved

Using a C# library in Excel (Calling C# functions in VBA or Excel Spreadsheet)

Posted on 2003-12-06
3
1,157 Views
Last Modified: 2012-05-04
I am porting a legacy pricing engine to C# and I would like to expose this newly created library to Excel spreadsheet users.  I've looked on the EE board and see that there have been questions like this in the past:

http://www.experts-exchange.com/Programming/Programming_Languages/C_Sharp/Q_20515723.html?query=excel+worksheet+function&searchType=topic

Lets assume for now that my class is very simple:

using System;

namespace FinLibary.FI
{
      public class Bond
      {
            private double _Coupon;

            public Bond()
            {
            
            }
            public double Coupon
            {
                  get
                  {
                        return(_Coupon);
                  }
                  set
                  {
                        _Coupon = value;
                  }
            }
      }
}

Could someone point me in the best direction for getting this class exposed to an Excel user?

I'd like to allow the user to call this library from VBA (so I'll need a declaration and a wrapper). For now, lets just assume that all I want to do is return the "Coupon" property from the Bond Class.  

I am more interested in the methodology/approach/syntax for bulilding/registering/referencing the DLL , declaring the C# class and Calling it from VBA.  I've done something similar in the past w/ C/C++, but not since .NET.  Its been awhile and I'm hoping that there is a simple approach out there.

I've looked at using the Microsoft Office System Project templates in VS2003 and the Extensibility project Templates.  Unlclear to me what value the Office Sytem Project templates have in this case.  It would be nice just to be able to have this class libary available for multiple purposes (calling it from a Web project, command line interface and Excel), so keeping the library clean is important to me.

I'd eventually like produce an XLL for pure spreadsheet users (beyond the scope of this question) as well as a DLL for VBA users, but for now I would settle for a DLL that I could have VBA users call.

I have a lot of legacy code here and I am eager to get moving on this, so I am setting the point value high to encourage those of you who have been through this to give me a hand.

Much appreciated.  Would be happy to clarify if anyone out there has questions.

Thanks.
0
Comment
Question by:dcr25568
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
vascov earned 500 total points
ID: 9893013
You can build your .NET code and place it in the GAC (Global Assembly Cache/Catalog). This will make your .NET code "findable" from multiple places.
To place your assembly in the GAC, you'll have to provide a strong name for it

To make it VBA friendly, you should be using the System.Runtime.InteropServices namespace in order to make it COM friendly.

Using your sample, here goes a possible solution:

using System;
using System.Reflection;

[ assembly: AssemblyKeyFile( "FinLibrary.snk" ) ]

namespace FinLibary.FI
{
     public class Bond
     {
          private double _Coupon;

          public Bond()
          {
         
          }
          public double Coupon
          {
               get
               {
                    return(_Coupon);
               }
               set
               {
                    _Coupon = value;
               }
          }
     }
}


Compile it:
sn -k FinLibrary.snk
csc /t:library /debug+ FinLibrary.cs

after compiling, we have to register this assembly with COM.
We'll use the default behavior of COM Interop, and extract and register COM info using:
regasm /tlb /FinLibrary.dll

Now, to make this callable from any Excel doc (or any other app), we have to put the assembly in the GAC
gacutil -i FinLibrary.dll


We're set with our lib.

Now, let's go to Excel.

Create an Excel sheet, and go to Visual Basic Editor.
Add the reference to our typelib. Tools -> References -> browse -> select the tlb from the dir you have just compiled the assembly

Add A Module

Declare a function that just makes some dummy calcs

Public Function GetBondValue( bondCode )
  Dim o as <yourAssemblyName>.Bond

  o.Coupon = 10

  GetBondValue = o.Coupon + bondCode
end Function


Now, back to excel, in a cell type in
= GetBondValue( A1 )

This will call your function, passing in the value of A1 (which i assume is an int), which will in turn instantiate a bond object, and interact with both properties.



Some final notes regarding Interop:
* if you want IntelliSense in Excel, you'll have to customize your object design (use interfaces) and make use of System.Interop.RuntimeServices COM attributes
* if you want a specific ClassId, ProgId, than make use of the attributes to specify those

HTH
0
 
LVL 1

Author Comment

by:dcr25568
ID: 9893058
Thanks.  This is a good start for me and I appreciate the quick turnaround.

You commented that "To make it VBA friendly, you should be using the System.Runtime.InteropServices namespace in order to make it COM friendly."

Curious if you meant that I should chage the namespace to System.Runtime.InteropServices or should I just add:

using System.Runtime.InteropServices;

i'd like to keep the namespace clean and keep it standard with the rest of my library.  Does it make more sense to write a wrapper in C# that uses the System.Runtime.InteropServices namespace and implement an interface to the FinLibrary.FI C# library?

Thanks.
0
 
LVL 12

Expert Comment

by:vascov
ID: 9893085
DCR, you can keep you namespaces :)

I meant to say that you could be using the attributes within the System.Runtime.InteropServices namespace. These include attributes to control what is visible or not, as well as which progids, Dual interfaces, event interfaces, etc... your com objects should have. There are also some more advanced attributes that can control how the data is "transformed" from COM <-> .NET.
(have a look at this page to see the attributes i'm speaking about:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemRuntimeInteropServices.asp?frame=true)

Rest assured, you can keep your namespaces :)

HTH
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Amazon S3 .Net error 5 78
aspx ascx, c# 7 43
Timeouts during development 3 28
Disable the weekends on datepicker control 6 52
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question