Excel UDF 'not a valid addin' error

Question

I am trying to create a custom vb.net Excel 2007 function (UDF) using VS 2010 and have gotten to this stage (borrowing heavily from Eric Carter's example at http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx):

Namespace AutomationAddin
    <Guid("1aeeb1b5-e099-4f7f-aeb0-3e9f19b64f62")>
    <ClassInterface(ClassInterfaceType.AutoDual)>
    <ComVisible(True)>
    Public Class MyFunctions
        Public MyFunctions()
        Public Function MultiplyNTimes(ByVal number1 As Double, ByVal number2 As Double, ByVal timesToMultiply As Double) As Double
            Dim result As Double = number1
            For i As Integer = 0 To timesToMultiply - 1
                result = result * number2
            Next
            Return result
        End Function
        <ComRegisterFunctionAttribute()>
        Public Shared Sub RegisterFunction(ByVal type As Type)
            Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type, "Programmable"))
            Dim key As RegistryKey = Registry.ClassesRoot.OpenSubKey(GetSubKeyName(type, "InprocServer32"), True)
            key.SetValue("", (System.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 s As System.Text.StringBuilder = New System.Text.StringBuilder
            s.Append("CLSID\{")
            s.Append(type.GUID.ToString.ToUpper)
            s.Append("}\")
            s.Append(subKeyName)
            Return s.ToString
        End Function
    End Class
End Namespace

However, when I build it using VS 2010 and try to load it in Excel 2007 using the Addin Manager>Automation I find it listed as AutomationAddin.AutomationAddin.MyFunctions and click OK only to get the error "AutomationAddin.AutomationAddin.MyFunctions is not a valid add-in." I have set the Build settings to Register for COM interop.

I've had a look online and tried following this article How to get COM Server for Excel written in VB.NET installed and registered in Automation Servers list? but to no avail. I checked my registry (after I built my project) and under CLSID/{myGuid}/InprocServer32/Default the data is set to C:\WINDOWS\system32\mscoree.dll and CLSID/{myGuid}/Programmable already exists.

I am not quite sure what I am doing wrong and would appreciate any guidance or suggestions on the topic.

Cheers,

Ben

score 0 · Answer 1 · answered Jun 26 '14 at 20:20

I don't know if this is relevant to the question (especially after all this time) but I originally started with a COM add-in (created using Visual Studio 2010's Excel 2010 add-in project builder). I then added an automation add-in (for UDFs) by hand in the same project using the Eric Carter blog and other examples. The two worked fine independently. It was only after combining the two in the same namespace (for some obsessively tidy reason) that I started getting the "... is not a valid add-in" error. Spent a day tearing my hair out and then separated the namespaces again - problem went away.

Excel UDF 'not a valid addin' error

1 Answers1