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