The advantages of a Automation Add-In are two-fold:

Security
Unlike an XLA add-in, an Automation Add-In is written in a language like Visual Basic or C++ and compiled to a DLL file, so you never distribute source code to the end users. You distribute only the DLL file. Your proprietary or otherwise valuable source code remains secure on your own machine. It is never distributed to the end user.

Performance
An Automation Add-In is compiled to native machine code. This is much faster than the interpreted code of an XLA type add-in.

Creating The Automation Add-In

Creating an Automation Add-In is very simple using Visual Basic 6. Procedures for writing an Automation Add-In in NET will appear later on this site. Start Visual Basic 6, choose New Project form the File menu, and choose ActiveX DLL. This will create a project with a single class module named Class1. Typically, your Automation Add-In will contain only one class contains the callable functions. However, you are not restricted to a single class. You can create multiple classes, each containing a family of related functions. In this example project, we will create two classes, ExcelMath and ExcelStrings.

First, you need to set up the environment for development. Go to the Project menu, choose References, and set a reference to the Microsoft Excel object library. While strictly speaking this is not required, you will need it if you are going to use any Excel object definitions such as a ranges or worksheets, or if you are going to use any of Excel's built-in worksheet function in your code. Change the name of the project from "Project1" to "ExampleAutomation" or any other meaningful name. Then change the name of the existing class module from "Class1" to "ExcelMath". Add a new class module from the Project menu, and name this class "ExcelStrings". In this example, we will create a single function in each class module. However, in the real world, your Automation Add-In class(s) will contain multiple functions.  The Instancing property of both classes should be set to 5 - Multiuse.

Put the following code in each class.

    Private XL As Excel.Application

    Private Sub Class_Initialize()
        On Error Resume Next
        Set XL = GetObject(, "Excel.Application")
    End Sub
    
    Private Sub Class_Terminate()
        Set XL = Nothing
    End Sub

Then define your functions. In ExcelMath, we will have a single function named Times4, which simply multiplies it input value by 4 and returns the result.

    Public Function Times4(D As Double) As Double
        Times4 = D * 4
    End Function

In ExcelStrings, we will have a single function named CountChars, which returns the number of times a specific character occurs in an input text string. Paste the following function in ExcelStrings:

    Public Function CountChars(Text As String, C As String, _
            Optional CompareMode As VbCompareMethod) As Variant
        Dim Ndx As Long
        Dim Counter As Long
        If Len(C) > 1 Then
            CountChars = CVErr(xlErrValue)
            Exit Function
        End If
        
        For Ndx = 1 To Len(Text)
            If StrComp(C, Mid(Text, Ndx, 1), CompareMode) = 0 Then
                Counter = Counter + 1
            End If
        Next Ndx
        
        CountChars = Counter
    End Function
    

Now you are ready to build the Add-In.  From the File menu, choose "Make ExampleAutomation.dll...".  If the compilation was successful, Visual Basic will automatically register the two classes in the System Registry (in the HKEY_CLASSES_ROOT section of the Registry). To use the functions, you must load the Automation Add-In classes from within Excel.

Registering The Automation Add-In With Windows

After you create your add-in, or if you add additional public classes containing new function to the project, you will need to unregister the existing library and then re-register the library with Windows. This ensures that all the classes containing function are available to other projects and workbooks. With Excel closed, go to the Windows Start menu and choose Run. In that dialog, enter the following to unregister the class library. Of course, change the file name to your specific file:

    RegSvr32 "C:\MyFolder\DLLFileName.dll"  /u

Then repeat this, omitting the /u, to register the library with Windows:

    RegSvr32 "C:\MyFolder\DLLFileName.dll"

Loading The Automation Add-In

Go to the Tools menu, choose Add-Ins (not COM Add-Ins) and in that dialog, click the Automation button. This will display a very long list of available automation servers. Scroll down in the list to find ExampleAutomation.ExcelMath. Click OK. Repeat this process for ExampleAutomation.ExcelStrings. Now, in the Add-Ins list, you should see ExampleAutomation.ExcelMath and ExampleAutomation.ExcelStrings checked in the list. You can now call the functions in these classes directly from worksheet cells. For example,

    =CountChars("ABCABC","B")

Calling Automation Add-In Functions In VBA

You can call the functions in your Automation Add-In from your own VBA code. The first step is to set a reference in your VBA Project to the DLL file of your Automation Add-In.  From the Tools menu, choose References to display the References dialog. In that dialog, click the Browse button and navigate to your Automation Add-In's DLL. Click Open. Once you've set the reference, you must create an object variable with a data type of your class library. Declare these variables as Public, project-wide variables, using the New keyword:

    Public XLMath As New ExampleAutomation.ExcelMath
    Public XLStrings As New ExampleAutomation.ExcelStrings

We use the New keyword in the variable declaration in order to ensure that the variable will be created as needed. Now you can call upon the function in the DLL with code such as the following:

    Sub AAA()
        Dim D As Double
        Dim Res As Double
        D = 123.456
        Res = XLMath.Times4(D)
        Debug.Print Res
    End Sub

 While I usual avoid using the 'New' keyword in a variable declaration (instead using code like Set XLMath = New ExampleAutomation.ExcelMath), I recommend using it in this situation to ensure that the XLMath and XLStrings variables will never become Nothing, due to editing existing VBA code or a procedure calling 'End'.  If the variables were to be reset to Nothing, the code would raise an error 91, "Object variable or With block variable not set".  Avoiding this error is especially important if you are writing a function that will be called from a worksheet cell.

Distributing Your Automation Add-In

You can distribute your to other users and other machines. Copy the DLL file from your computer to the user's computer. Then on the user's computer, go to the Windows Start menu, choose Run, and enter the following:

    RegSvr32 "C:\AutomationExample\ExampleAutomation.dll"

Of course, change the path and filename to your DLL file.

Then,  repeat the steps described in Loading The Automation Add-In above to make the add-in available in Excel on the user's machine.

That's it. That's all there is to writing Automation Add-Ins for Excel.

You can download the complete Visual Basic 6 project files here.