My First User Defined Function in Excel.

With Visual Studio 2005 and Microsoft Office 2007 or 2003, I have created my own formula.



Open Visual Studio -> New -> Project.
Select "Class Library"

Your project name will show in Excel's Formula dialogbox.

Now you have a "Class1.vb" in your Solution Explorer.

Now right down this code in your Class file....

'imports required name space
Imports System.Runtime.InteropServices
Imports Microsoft.Win32


<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)< _
Public Class Class1

'this is for automatically register the function in excel.
<ComUnregisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type))
End Sub

'this is for to unregister the function from excel.
<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubKeyName(type), False)
End Sub

Private Shared Function GetSubKeyName(ByVal type As Type) As String
Dim s1 As String
s1 = "CLSID\{" & type.GUID.ToString().ToUpper() & "}\Programmable"
Return s1.ToString()
End Function


'this is Excel custom formula "myTestFormula"
Public Function myTestFormula(ByVal anyNumber As Double) As Double
Dim result As Double = anyNumber * 2
Return result
End Function

End Class

------------------
ok, open project properties form Project menu.

click on "Compile" Tab. and check a "Register for COM imterop" option.

now save all file of the project. and build solution pressing F6.

Ok, user defined function has been created. now we have to give reference to Excel for this formula.

• Open Microsoft Excel 2007
• open "Excel Options" from office button icon.
• click on "Add-Ins" tab.
• Click on "Go" button under Manage Excel Add-ins list.
• It will show Add-Ins dialog box.

• Click on Automation button.
• Under Automation Services dialogbox fine out ProjectName.ClassName.
• here I have "myExcelFormula.myFunctions" or like that.
  

click on OK.
it done.

now type your formula and pass it argument and you will get the result.

here you will get the result 10 (5*2)

it's g. using this method I am creating a Bill for a form. This is a print preview of a Bill using user defined function where database is MS Access, Software is Created in VB6.0 and this formula created from Visual Studio 2005.