none
How to add UDF functions RRS feed

  • Question

  • I am new to programming so please forgive me if I don't know all the right terms. :-)

    I have tried searching around and found many confusing answers from different corners of the web and many old posts but can not seem to find the answer.

    My company asked me to create an excel add-in for them. Using Visual studio 2010 I created an add-in using the VB.NET excel 2007 add-in template. This was great and I have made all the forms and ribbon buttons needed and I could write it in VB.NET as I have not learn't C or C#....yet :-)

    Now they want me to add the old VBA functions they have into it so that it can be packaged and sent to everyone in the company. This is where I fail.

    The UDF's must not be able to be seen by anyone with a little bit of knowhow as they are sensitive functions.

    How can I add them in? Or if that is not possible then can I somehow bundle them together as a dll (or is it xll) library that can be called from the cells and included in the setup?

    P.S. Some contain Excel functions such as lookup and index as well as straight number crunching would this be possible?

    Please any help would be greatly appreciated.

    Monday, March 5, 2012 11:38 AM

Answers

  • Hi Slyloki,

    Thanks for posting in the MSDN Forum.

    I hope this snippet can help you. Please set Properties->Complie->Register COM Interop on before you complie it. And this action need administrator right.

    Imports System.Runtime.InteropServices
    Imports Microsoft.Win32
    Imports System.Text
    
    <ClassInterface(ClassInterfaceType.AutoDual)>
    <ComVisible(True)>
    <Guid("207DF2C7-9773-4FB1-A50C-2A2515C3F8FA")>
    Public Class VBModule
        Public Function MyFunction(ByVal Width As Integer, ByVal Layes As Integer, _
                                   ByVal UponRubber As Double, _
                                   ByVal ButtomRubber As Double, _
                                   ByVal Length As Double) As Double
            MyFunction = Width / 1000 * (Layes + (UponRubber + ButtomRubber) _
                                         / 1.5) * Length
        End Function
    
        <ComRegisterFunctionAttribute()> Public Shared Sub RegisterFunction(ByVal Type As Type)
            Dim rk As RegistryKey
    
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(Type, "Programmable"))
            rk = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(Type, "InprocServer32"), True)
            rk.SetValue("", Environment.SystemDirectory + "\mscoree.dll", RegistryValueKind.String)
        End Sub
    
        <ComUnregisterFunctionAttribute()> Public Shared Sub UnregisterFunction(ByVal Type As Type)
            Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(Type, "Programmable"), False)
        End Sub
    
        Private Shared Function GetSubKeyName(ByVal Type As Type, ByVal SubKeyName As String) As String
            Dim b As StringBuilder = New StringBuilder
    
            b.Append("CLSID\{")
            b.Append(Type.GUID.ToString().ToUpper())
            b.Append("}\")
            b.Append(SubKeyName)
            GetSubKeyName = b.ToString()
        End Function
    End Class

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Bruce Song Wednesday, April 4, 2012 3:08 AM
    Thursday, March 8, 2012 7:35 AM
    Moderator