Using Excel UDF's from a VB6 ActiveX dll
 
To use functions written in a VB6 ActiveX dll (an ActiveX .exe should work similar) in Excel (Excel 2002 or higher) you need to do the following:
 

• Create a ActiveX dll project
  
• Add a class file to the project (e.g. called class1)
  
• Set the properties of this class to instancing=6 - GlobalMultiuse
  This ensures the functions inside the class will be available outside the class as well without declaration.
  
• Write your functions inside the class.
  Example testing function:
  Public Function MyTest() as Variant
    MyTest = "Hi"
  End Function
  
• Compile the project to create the ActiveX dll
  
• In Excel add the COM addin: tools->Addins->Automation
  
• Select yourproject.class1
  

 
That's it. Now you should be able to type the function into an Excel cell:
=MyTest()
And it should display: Hi