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 !!

 

 

Answer 1

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

Answer 2

#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

Answer 3

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

Answer 4

O.K.  Thanks anyway !

Answer 5

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.

Answer 6

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

Answer 7

 

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

Answer 8

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.

Answer 9

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/

Answer 10

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

Answer 11

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

Answer 12

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.

Answer 13

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>

 

 

Answer 14

 

" 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

Answer 15

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

Answer 16

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

Answer 17

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