none
How to Create User Defined Functions in Excel 2010 Using Visual Studio 2010 ?

    Question

  • #Region "Imports directives"
    Imports Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Imports Microsoft.Win32
    Imports System.Reflection
    #End Region
    
    
    <ClassInterface(ClassInterfaceType.AutoDual)> _
    Public Class ThisAddIn
    
     Private Sub ThisAddIn_Startup() Handles Me.Startup
    
     End Sub
    
     Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
    
     End Sub
    #Region "User Defined Functions"
    
     'Private Sub Application_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeSave
     ' Dim activeWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, Excel.Worksheet)
     ' Dim firstRow As Excel.Range = activeWorksheet.Range("A1")
     ' firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
     ' Dim newFirstRow As Excel.Range = activeWorksheet.Range("A1")
     ' newFirstRow.Value2 = "This text was added by using code"
     'End Sub
    
    
     Public Function xfMinusNumbers(ByVal num1 As Object, Optional ByVal num2 As Object = Nothing, _
              Optional ByVal num3 As Object = Nothing) _
              As Double
    
      Dim result As Double = num1
      If Not TypeOf num2 Is Missing And Not TypeOf num2 Is Double Then
       Dim r2 As Excel.Range = TryCast(num2, Excel.Range)
       result = (result - Convert.ToDouble(r2.get_Value2))
      End If
      If Not TypeOf num3 Is Missing And Not TypeOf num3 Is Double Then
       Dim r3 As Excel.Range = TryCast(num3, Excel.Range)
       result = (result - Convert.ToDouble(r3.get_Value2))
      End If
      Return result
    
     End Function
    
    #End Region
    
    #Region "Registration of Automation Add-in"
    
     ''' This is function which is called when we register the dll
     <ComRegisterFunction()> _
     Public Shared Sub RegisterFunction(ByVal type As Type)
    
      ' Add the "Programmable" registry key under CLSID
      Registry.ClassesRoot.CreateSubKey(GetCLSIDSubKeyName( _
               type, "Programmable"))
    
      ' Register the full path to mscoree.dll which makes Excel happier.
      Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey( _
      GetCLSIDSubKeyName(type, "InprocServer32"), True)
      key.SetValue("", (Environment.SystemDirectory & "\mscoree.dll"), _
          RegistryValueKind.String)
    
     End Sub
     ''Private Shared Function GetCLSIDSubKeyName(ByVal type As Type, ByVal p2 As String) As String
     '' Throw New NotImplementedException
     ''End Function
    
    
     ''' This is the function which is called when we unregister the dll
     <ComUnregisterFunction()> _
     Public Shared Sub UnregisterFunction(ByVal type As Type)
    
      ' Remove the "Programmable" registry key under CLSID
      Registry.ClassesRoot.DeleteSubKey( _
      GetCLSIDSubKeyName(type, "Programmable"), False)
    
     End Sub
    
     ''' Assistant function used by RegisterFunction/UnregisterFunction
     Private Shared Function GetCLSIDSubKeyName( _
     ByVal type As Type, ByVal subKeyName As String) As String
    
      Dim s As New StringBuilder
      s.Append("CLSID\{")
      s.Append(type.GUID.ToString.ToUpper)
      s.Append("}\")
      s.Append(subKeyName)
      Return s.ToString
    
     End Function
    
    #End Region
    
    End Class
    
    

    I can't seem to get the Excel 2010 Application Add-in feature to work properly in Visual Studio 2010, when it comes to developing a function in Visual Basic 2010 that will be recognized in Excel 2010 as a User-defined function (UDF).  My code compiles, and the DLL shows up as a COM add-in in Excel, yet I cannot find the functions in the function list.  The non-UDF-related aspects of the DLL work fine.  I have read many articles and have spent many hours trying to get this to work, but have not had any success. 

    The VB 2010 code I attached is essentially code I found on the Internet which ostensibly addressed this issue, yet it doesn't work for me.

    It's very important for me to be able to create custom functions in VB 2010 for Excel, because I develop complex actuarial functions.  I have developed many using VBA, but the calculation speed is too slow.

    Any help would be much appreciated !!

     

     

    • Edited by jtwade2010 Friday, November 26, 2010 1:37 PM
    • Moved by Kee Poppy Tuesday, November 30, 2010 8:43 AM (From:Visual Basic General)
    Friday, November 26, 2010 5:18 AM

Answers

  • As an alternative to the options mentioned by Bruce and Cindy, you may want to take a look at ExceDNA. It's a very nice open source framework, which works great if you want to write .NET code and expose it as UDF's in Excel.

    http://exceldna.codeplex.com/

    • Marked as answer by jtwade2010 Sunday, December 05, 2010 1:14 PM
    Wednesday, December 01, 2010 6:50 AM

All replies

  • jtwade,

    Not my problem, but to speed it up.

    In the way you state it it, this question get mostly only answers in a way as:

    Wrong forum.

    To show what you are doing you have to give some code which shows which of the endless Excel solutions you are using in VB Net.

     


    Success
    Cor
    Friday, November 26, 2010 5:35 AM
  • #Region "Imports directives"
    Imports Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices
    Imports Microsoft.Win32
    Imports System.Reflection
    #End Region
    
    
    <ClassInterface(ClassInterfaceType.AutoDual)> _
    Public Class ThisAddIn
    
      Private Sub ThisAddIn_Startup() Handles Me.Startup
    
      End Sub
    
      Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
    
      End Sub
    #Region "User Defined Functions"
    
      'Private Sub Application_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeSave
      '  Dim activeWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, Excel.Worksheet)
      '  Dim firstRow As Excel.Range = activeWorksheet.Range("A1")
      '  firstRow.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
      '  Dim newFirstRow As Excel.Range = activeWorksheet.Range("A1")
      '  newFirstRow.Value2 = "This text was added by using code"
      'End Sub
    
    
      Public Function xfMinusNumbers(ByVal num1 As Object, Optional ByVal num2 As Object = Nothing, _
                        Optional ByVal num3 As Object = Nothing) _
                        As Double
    
        Dim result As Double = num1
        If Not TypeOf num2 Is Missing And Not TypeOf num2 Is Double Then
          Dim r2 As Excel.Range = TryCast(num2, Excel.Range)
          result = (result - Convert.ToDouble(r2.get_Value2))
        End If
        If Not TypeOf num3 Is Missing And Not TypeOf num3 Is Double Then
          Dim r3 As Excel.Range = TryCast(num3, Excel.Range)
          result = (result - Convert.ToDouble(r3.get_Value2))
        End If
        Return result
    
      End Function
    
    #End Region
    
    #Region "Registration of Automation Add-in"
    
      ''' This is function which is called when we register the dll
      <ComRegisterFunction()> _
      Public Shared Sub RegisterFunction(ByVal type As Type)
    
        ' Add the "Programmable" registry key under CLSID
        Registry.ClassesRoot.CreateSubKey(GetCLSIDSubKeyName( _
                         type, "Programmable"))
    
        ' Register the full path to mscoree.dll which makes Excel happier.
        Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey( _
        GetCLSIDSubKeyName(type, "InprocServer32"), True)
        key.SetValue("", (Environment.SystemDirectory & "\mscoree.dll"), _
               RegistryValueKind.String)
    
      End Sub
      ''Private Shared Function GetCLSIDSubKeyName(ByVal type As Type, ByVal p2 As String) As String
      ''  Throw New NotImplementedException
      ''End Function
    
    
      ''' This is the function which is called when we unregister the dll
      <ComUnregisterFunction()> _
      Public Shared Sub UnregisterFunction(ByVal type As Type)
    
        ' Remove the "Programmable" registry key under CLSID
        Registry.ClassesRoot.DeleteSubKey( _
        GetCLSIDSubKeyName(type, "Programmable"), False)
    
      End Sub
    
      ''' Assistant function used by RegisterFunction/UnregisterFunction
      Private Shared Function GetCLSIDSubKeyName( _
      ByVal type As Type, ByVal subKeyName As String) As String
    
        Dim s As New StringBuilder
        s.Append("CLSID\{")
        s.Append(type.GUID.ToString.ToUpper)
        s.Append("}\")
        s.Append(subKeyName)
        Return s.ToString
    
      End Function
    
    #End Region
    
    End Class
    
    
    Friday, November 26, 2010 1:19 PM
  • I have to agree with Cor -- there must be a better forum for this question since this is not so much a VB question as it is about Office development.  Perhaps you have looked for the appropriate forum and didn't find it.  The best I could find is the VSTO forum (http://social.msdn.microsoft.com/Forums/en-US/vsto/threads) but that says it is for "managed code solutions" - which I'm not sure your COM stuff falls under...  but there is a far more likelihood that someone there will have experience in what you are are attempting.

    Good luck!

    EDIT:  Have looked in the Excel for Developers forum?  http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    Saturday, November 27, 2010 2:15 AM
  • O.K.  Thanks anyway !
    Saturday, November 27, 2010 4:57 AM
  • Hi jtwade,

    I am moving this thread to "Visual Studio Tools for Office" forum for better support.

     

    Best Regards,

    Kee


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, November 30, 2010 8:41 AM
  • Hi jtwade

    You can't include a UDF as part of a VSTO add-in (and I can tell this is a VSTO add-in from the ThisAddin_Startup event procedure). VSTO "wraps" things up and also doesn't register the UDF particularly, so Excel doesn't "see" it.

    the UDF needs to be a separate DLL.

    Here are some helpful resources. In the first article, pay special attention to the links provided for additional information

    http://stackoverflow.com/questions/540643/create-udf-using-vsto-and-no-vba
    http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx


    Cindy Meister, VSTO/Word MVP
    Tuesday, November 30, 2010 8:55 AM
    Moderator
  •  

    jtWade,

    My intention was not that you should be moved to an other newsgroup.

    (Not blaming Kee Poppy for doing that, I lost the thread somehow last week).

    You are now using the way like it was done in versions before 2008.

    But you wrote, you have Visual Studio 2010 (and not VB express 2010).

    Did you try already in VB 2010

    Project, New Project -> Visual Basic -> Office -> Excel Add In, that is meant to create a managed code Add In for Excel

    In the designer part is than automatically for you created.

     


     

     

    '------------------------------------------------------------------------------
    ' <auto-generated>
    '  This code was generated by a tool.
    '  Runtime Version:4.0.30319.1
    '
    '  Changes to this file may cause incorrect behavior and will be lost if
    '  the code is regenerated.
    ' </auto-generated>
    '------------------------------------------------------------------------------
    
    Option Strict Off
    Option Explicit On
    
    
    
    '''
    <Microsoft.VisualStudio.Tools.Applications.Runtime.StartupObjectAttribute(0), _
     Global.System.Security.Permissions.PermissionSetAttribute(Global.System.Security.Permissions.SecurityAction.Demand, Name:="FullTrust")> _
    Partial Public NotInheritable Class ThisAddIn
     Inherits Microsoft.Office.Tools.AddInBase
    
     Friend WithEvents CustomTaskPanes As Microsoft.Office.Tools.CustomTaskPaneCollection
    
     Friend WithEvents VstoSmartTags As Microsoft.Office.Tools.SmartTagCollection
    
     <Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0")> _
     Friend WithEvents Application As Microsoft.Office.Interop.Excel.Application
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Public Sub New(ByVal factory As Global.Microsoft.Office.Tools.Excel.ApplicationFactory, ByVal serviceProvider As Global.System.IServiceProvider)
      MyBase.New(factory, serviceProvider, "AddIn", "ThisAddIn")
      Globals.Factory = factory
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Protected Overrides Sub Initialize()
      MyBase.Initialize()
      Me.Application = Me.GetHostItem(Of Microsoft.Office.Interop.Excel.Application)(GetType(Microsoft.Office.Interop.Excel.Application), "Application")
      Globals.ThisAddIn = Me
      Global.System.Windows.Forms.Application.EnableVisualStyles()
      Me.InitializeCachedData()
      Me.InitializeControls()
      Me.InitializeComponents()
      Me.InitializeData()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Protected Overrides Sub FinishInitialization()
      Me.OnStartup()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Protected Overrides Sub InitializeDataBindings()
      Me.BeginInitialization()
      Me.BindToData()
      Me.EndInitialization()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Private Sub InitializeCachedData()
      If (Me.DataHost Is Nothing) Then
       Return
      End If
      If Me.DataHost.IsCacheInitialized Then
       Me.DataHost.FillCachedData(Me)
      End If
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Private Sub InitializeData()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Private Sub BindToData()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)> _
     Private Sub StartCaching(ByVal MemberName As String)
      Me.DataHost.StartCaching(Me, MemberName)
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)> _
     Private Sub StopCaching(ByVal MemberName As String)
      Me.DataHost.StopCaching(Me, MemberName)
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)> _
     Private Function IsCached(ByVal MemberName As String) As Boolean
      Return Me.DataHost.IsCached(Me, MemberName)
     End Function
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Private Sub BeginInitialization()
      Me.BeginInit()
      Me.CustomTaskPanes.BeginInit()
      Me.VstoSmartTags.BeginInit()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Private Sub EndInitialization()
      Me.VstoSmartTags.EndInit()
      Me.CustomTaskPanes.EndInit()
      Me.EndInit()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Private Sub InitializeControls()
      Me.CustomTaskPanes = Globals.Factory.CreateCustomTaskPaneCollection(Nothing, Nothing, "CustomTaskPanes", "CustomTaskPanes", Me)
      Me.VstoSmartTags = Globals.Factory.CreateSmartTagCollection(Nothing, Nothing, "VstoSmartTags", "VstoSmartTags", Me)
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Private Sub InitializeComponents()
     End Sub
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Advanced)> _
     Private Function NeedsFill(ByVal MemberName As String) As Boolean
      Return Me.DataHost.NeedsFill(Me, MemberName)
     End Function
    
     '''
     <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
      Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0"), _
      Global.System.ComponentModel.EditorBrowsableAttribute(Global.System.ComponentModel.EditorBrowsableState.Never)> _
     Protected Overrides Sub OnShutdown()
      Me.VstoSmartTags.Dispose()
      Me.CustomTaskPanes.Dispose()
      MyBase.OnShutdown()
     End Sub
    End Class
    
    '''
    <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
     Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0")> _
    Partial Friend NotInheritable Class Globals
    
     '''
     Private Sub New()
      MyBase.New()
     End Sub
    
     Private Shared _ThisAddIn As ThisAddIn
    
     Private Shared _factory As Global.Microsoft.Office.Tools.Excel.ApplicationFactory
    
     Private Shared _ThisRibbonCollection As ThisRibbonCollection
    
     Friend Shared Property ThisAddIn() As ThisAddIn
      Get
       Return _ThisAddIn
      End Get
      Set(ByVal value As ThisAddIn)
       If (_ThisAddIn Is Nothing) Then
        _ThisAddIn = value
       Else
        Throw New System.NotSupportedException()
       End If
      End Set
     End Property
    
     Friend Shared Property Factory() As Global.Microsoft.Office.Tools.Excel.ApplicationFactory
      Get
       Return _factory
      End Get
      Set(ByVal value As Global.Microsoft.Office.Tools.Excel.ApplicationFactory)
       If (_factory Is Nothing) Then
        _factory = value
       Else
        Throw New System.NotSupportedException()
       End If
      End Set
     End Property
    
     Friend Shared ReadOnly Property Ribbons() As ThisRibbonCollection
      Get
       If (_ThisRibbonCollection Is Nothing) Then
        _ThisRibbonCollection = New ThisRibbonCollection(_factory.GetRibbonFactory)
       End If
       Return _ThisRibbonCollection
      End Get
     End Property
    End Class
    
    '''
    <Global.System.Diagnostics.DebuggerNonUserCodeAttribute(), _
     Global.System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Tools.Office.ProgrammingModel.dll", "10.0.0.0")> _
    Partial Friend NotInheritable Class ThisRibbonCollection
     Inherits Microsoft.Office.Tools.Ribbon.RibbonCollectionBase
    
     '''
     Friend Sub New(ByVal factory As Global.Microsoft.Office.Tools.Ribbon.RibbonFactory)
      MyBase.New(factory)
     End Sub
    End Class
    

     

     

    Cor

    Tuesday, November 30, 2010 9:31 AM
  • Hi Jtwade2010,

                Have you resolved your issue yet, could you let us know whether the suggestions help you and we are happy to help with you.

                After reading your post, I know that you want to create an Excel2010 UDF via VS2010.

    As for the problem, I agree with the option of Cindy’s, you can use a standalone .net library to register UDF in Excel2010. I have done before via VS2010 by following the steps on the article:

    http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx which Cindy has also  provided.

                Besides, you can download the samples from Onecode website, http://blogs.msdn.com/b/codefx/archive/2010/11/26/all-in-one-office-development-code-samples.aspx?wa=wsignin1.0#Download

    Choose the sample named CSExcelAutomationAddIn to download, it is about creating an UDF in Excel2010.

                I hope it can help you and feel free to follow up after you have tried the solution.

    Best Regards,

    Bruce Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 01, 2010 5:02 AM
  • As an alternative to the options mentioned by Bruce and Cindy, you may want to take a look at ExceDNA. It's a very nice open source framework, which works great if you want to write .NET code and expose it as UDF's in Excel.

    http://exceldna.codeplex.com/

    • Marked as answer by jtwade2010 Sunday, December 05, 2010 1:14 PM
    Wednesday, December 01, 2010 6:50 AM
  • Matthias,

    In my idea does the code from Bruce and Cindy not answer the question from the OP. 

    He wrote

    I can't seem to get the Excel 2010 Application Add-in feature to work properly in Visual Studio 2010

    What we always see is that there is given direct answers in the old interop way for VB6 and C++ '98 (and C++ unmanaged))

    Not so clever from the OP is that he showed a sample which was also Interop and not around the Visual Studio application Add IN, which he copied from Internet, so the thread was moved.

    In my idea he can better create a new question in the VB general forum with 

    How to use the Visual Studio 2010 Excel 2010 Application Add-in feature template to work properly in Visual Basic (Not Interop)

    His problem is that there is few knowledge about this feature because everybody keeps showing the Interop way and moderators seems to be currently over active to move threads to other forums.


    Success
    Cor
    Wednesday, December 01, 2010 7:44 AM
  • In my idea does the code from Bruce and Cindy not answer the question from the OP. 

    He wrote

    I can't seem to get the Excel 2010 Application Add-in feature to work properly in Visual Studio 2010


    Hi Cor

    I disagree. You cut off the quote too soon. The main point in the OP's question is about integrating a UDF in a VSTO add-in:

    " can't seem to get the Excel 2010 Application Add-in feature to work properly in Visual Studio 2010, when it comes to developing a function in Visual Basic 2010 that will be recognized in Excel 2010 as a User-defined function (UDF).  "

    My answer is very much on-topic, and correct. The OP's message was moved to the correct forum.


    Cindy Meister, VSTO/Word MVP
    Wednesday, December 01, 2010 10:58 AM
    Moderator
  • Hi Jtwade2010,

                Have you resolved your issue yet, and do the suggestions help you? If you have any concern on the thread, feel free to follow up. We are happy to help with you.

    Best Regards,

    Bruce Song


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, December 03, 2010 2:16 AM
  • Thanks for all your responses ... I really appreciate it !  I apologize for not responding sooner but have been tied up with some projects at work.

    So far, I've had the most success with ExcelDNA - I can't seem to get anything else to work.   I still haven't been able to convert all my functions over from VBA yet,  but I'm confident that I'll be able to, after writing to Govert van Drimmelen:  

    Here was my question to him:

    I’ve been trying to use ExcelDNA to convert some actuarial user-defined functions I developed in VBA (for Excel), to user defined functions based on VB 2010.  Given the complexity of some of my functions, they calculate very slowly under my current implementation, which is as an add-in based on VBA.  I was hoping that by compiling my functions, they would run much faster.   I’ve already tried implementing my functions using the Application Add-in template in Visual Studio, which creates a COM Add-in, but have not had any luck in getting the functions to work.   I have also tried creating a DLL and referencing my functions using a Declare statement in my Excel VBA module, but have not had any luck. 

    So, I’ve been searching the Internet looking for what could be a solution to my problem, and I finally came across ExcelDNA which appears to give me the best chance yet of doing what I want to do.  Unfortunately, I’ve come across a few problems with it that I was hoping you could address.

    First, Excel does not appear to recognize my function when any of the arguments are defined as Type BYTE or Type SINGLE.  This is not a big problem; however, it also does not recognize my function when I have an array for an argument.  This is a big problem, because my Excel UDFs need to be able to reference a range of values representing the yield curve, or an array or mortality decrements.  

    Do you have any suggestions as to how I might get your XLL to recognize an array parameter in my function procedure? 

    Here was his response:

    Hi John,

    I’m sure Excel-Dna is the right tool for you. However, getting started will take some patience. I’m happy to help you on the way.

     

    You are right about the Byte and Single types (and Long, which means Int64 in VB.NET), not being supported. Excel uses Double for all numbers. There is a Reference.txt file in the Distribution directory that lists the accepted types.

     

    Array are supported, but your arguments must be object(,) or double(,). There is also support for one-dimensional arrays Object() and Double(), with some detection to deal with both rows and columns.

    In particular, string arrays are not currently supported – you have to do the conversion yourself. They will be some day.

     

    I paste a .dna file below that defines some functions you can check.

     

    Please let me know if you have more questions, or post to the Google group at http://groups.google.com/group/exceldna.

     

    Regards,

    Govert 

    <DnaLibrary Name="ExcelDna TestVBArrays">

    <![CDATA[

     

    Public Module Test

     

        Function AddThemAll(values(,) As Double) as Double

            Dim i As Integer

            Dim j As Integer

           

            For i = 0 To values.GetLength(0) - 1

                For j = 0 To values.GetLength(1) - 1

                    AddThemAll = AddThemAll + values(i,j)

                Next j

            Next i

        End Function

       

        Function ConcatThem(values(,) As Object) as String

            Dim i As Integer

            Dim j As Integer

           

            ConcatThem = ""

            For i = 0 To values.GetLength(0) - 1

                For j = 0 To values.GetLength(1) - 1

                    ConcatThem = ConcatThem & values(i,j).ToString()

                Next j

            Next i

        End Function

     

        Function ReturnAnArray() as Object(,)

            Dim result(2,3) as Object

           

            result(0,0) = 123

            result(0,1) = "asd"

            result(0,2) = DateTime.Now

            result(1,0) = ExcelError.ExcelErrorValue

            result(1,1) = Nothing

            result(1,2) = "end"

           

            ReturnAnArray = result

        End Function

       

    End Module

    ]]>

    </DnaLibrary>

     

     

    Sunday, December 05, 2010 1:12 PM
  •  

    " can't seem to get the Excel 2010 Application Add-in feature to work properly in Visual Studio 2010, when it comes to developing a function in Visual Basic 2010 that will be recognized in Excel 2010 as a User-defined function (UDF).  "

     

    Cindy,

    Are you aware of the new special template in Visual Studio 2010 for creating Excel Add In's by Visual Basic and C# 2010

    That one does not use direct the classic Interop methods from VBA and Visual Studio 2010 which I see in the code of the by you given replies.

    The problem is, that everybody who asks for help for those features is sent direct to VBA forums and then get again the in the case of .Net old samples which is VBA created in a kind of interop way for Net

    But that is not the Visual Studio 2010 Excel Add In template solutions, but it is of course an UDF for Visual Studio 2002, 2003, and 2005 and then of course as well able to be uses in 2008 and 2010. But that is not the same.

    I'am among those who once want to see a sample of done by somebody who knows very good the possibilities from current Net development and from Excel.

    All what I see currently seems for me to be done by a VB6 developer. (I'm for Excel currently simply a dummie again).




    Success
    Cor
    Sunday, December 05, 2010 4:26 PM
  • Hi Cor

    I'm afraid I'm having difficulty following what you're trying to say:

    <<Are you aware of the new special template in Visual Studio 2010 for creating Excel Add In's by Visual Basic and C# 2010

    That one does not use direct the classic Interop methods from VBA and Visual Studio 2010 which I see in the code of the by you given replies.>>

    Please be specific. I see no new template in Visual Studio for Excel. The only change I know of is that .NET 4.0 can embed the parts of the type libraries used by the solution, making the solution PIA (and thus version) independent. And with C# it's now possible to work with optional parameters. But that doesn't change what is and is not possible in VSTO.

    <<The problem is, that everybody who asks for help for those features is sent direct to VBA forums and then get again the in the case of .Net old samples which is VBA created in a kind of interop way for Net>>

    People are not being sent to VBA forums. The Excel Developer forum supports the Excel application, regardless of the programming language used. And VSTO is specifically VB.NET and C#.

    <<But that is not the Visual Studio 2010 Excel Add In template solutions, but it is of course an UDF for Visual Studio 2002, 2003, and 2005 and then of course as well able to be uses in 2008 and 2010. But that is not the same.>>

    I don't understand what it is you're saying, here. Perhaps if you provide some links to illustrate what you mean...


    Cindy Meister, VSTO/Word MVP
    Monday, December 06, 2010 10:24 AM
    Moderator
  • Hi Cor

    I'm afraid I'm having difficulty following what you're trying to say:

    <<Are you aware of the new special template in Visual Studio 2010 for creating Excel Add In's by Visual Basic and C# 2010

    That one does not use direct the classic Interop methods from VBA and Visual Studio 2010 which I see in the code of the by you given replies.>>


    Correct Cindy, that is the problem, the OP asked for the ADD IN and then later he pasted in the code he found on Internet for Interop but was also telling that it was not what he meant, He has changed a lot during that time.

    And now I find a sample to that

    http://msdn.microsoft.com/en-us/library/cc668197.aspx

     


    Success
    Cor
    Tuesday, December 07, 2010 8:07 AM
  • Hi Cor

    I'm afraid your point is still unclear and I fear your concerns are at cross-purposes with the actual content of the question. You seem to be worried that people aren't answering at the "VSTO level". I assure you, I'm a VSTO MVP and know quite a bit about Office development, on many levels and in many programming languages. I understand what the OP was asking and my and mathias's answers were directly on-target.

    First off, in the original question the OP specifically requested combining a UDF into a VSTO add-in. This is simply not possible, due to the design of the VSTO add-in. I haven't looked at the OP's code; my response has nothing to do with his code.

    Secondly, the link you give is on how to create a VSTO workbook. This has nothing to do with an Add-in and nothing to do with a UDF.

    At this point, the question has been answered and I think it better we drop this side-discussion. We can pick it up in the MVP forums - when I find the time to drop by there again.


    Cindy Meister, VSTO/Word MVP
    Tuesday, December 07, 2010 8:49 AM
    Moderator