Excel VBA代码封装成Dll(VB6.0)：将VBA操作封装为DLL组件并在EXCEL中调用详细步骤How to make a ActiveX DLL in VB 6.0 step by step

Sub Test()
    On Error Resume Next
    Range("C1") = Cells(1, 1) + Cells(1, 2)
End Sub

You could use Visual Basic (not Visual Basic for Applications) to create a DLL and then reference them in your macros.

You can also use .NET to create an unmanaged export library as mentioned in this post and then reference it in your macros. Although not necessary, I'd recommend using Visual Studio to build a DLL.

An ActiveX DLL’s code is executed within the main program’s address space.

Start Visual Basic.
Create a new 'ActiveX DLL' project.
Rename the 'Class1' class to the name of your class.
Rename the project to something sensible.

Add reference: excel Microsoft EXCEL Office15 Object Library

Paste the code from Excel into the class code window.
Save the project and its files.
Build the .dll (File -> Make...).

d:\tihirdprogram\microsoft Office12\Office14\EXCEL.EXE

Microsoft EXCEL Office15 Object Library
D:\Thirdprogram\Microsoft Office2013\Office15\EXCEL.EXE

EXCEL2000（Microsoft Office 9.0）
EXCEL2002（Microsoft Office 10.0），即ExcelXP
EXCEL2003（Microsoft Office 11.0）
EXCEL2007（Microsoft Office 12.0）
EXCEL2010（Microsoft Office 14.0）
EXCEL2013（Microsoft Office 15.0）->Microsoft EXCEL Office15 Object Library ->D:\Thirdprogram\Microsoft Office2013\Office15\EXCEL.EXE

Microsoft Add-In Designer ( C:\Program Files\Common Files\DESIGNER\MSADDNDR.DLL)
Microsoft Office Object Library (C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL)
Microsoft PowerPoint Object Library (C:\Program Files\Office 2003\OFFICE11\msppt.olb)
The Excel object library is the executable:
Office 2013:C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE
Office 2016:C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE

If the machine has Microsoft Office installed, you can go into the built-in Visual Basic for Applications IDE and load the object there:

Tools menu -> Macro -> Visual Basic Editor (or press Alt+F11)

Then when inside the VBA window, Tools menu -> References -> Browse to locate your DLL

and View menu -> Object Browser to view the object's content (or press F2)

Regsvr32.exe is a program that you can use to register and unregister dynamic-link libraries (DLLs) and ActiveX controls (formerly called OLE Custom Controls) in the registry.
Regsvr32.exe is installed in the System folder. On a 64-bit version of a Windows operating system, there are two versions of the Regsv32.exe file:
 
- The 64-bit version is %systemroot%\System32\regsvr32.exe.
 
Path: C:\Windows\System32\regsvr32.exe
 
- The 32-bit version is %systemroot%\SysWoW64\regsvr32.exe.
 
Path: C:\Windows\SysWOW64\regsvr32.exe
Regsvr32 is the command-line tool that registers DLL files as command components in the registry.
Regsvr32 is used for registering a COM-based DLL. Regsvr32 generates, registers, and installs a type library into a specified COM+ 1.0 application.
To be used with regsvr32, a DLL must export the functions DllRegisterServer and DllUnregisterServer.
Regsvr32 will load the library and try to call the DllRegisterServer() from that library. It doesn't care what DllRegisterServer() actually does – it just calls that function and checks the returned value.
You use it to register COM servers in unmanaged DLLs.
It can't generate a .tlb file.
Most often, RegSvr32.exe fails because the LoadLibrary, DllRegisterServer, or DllUnregisterServer function fails.
LoadLibrary can fail because:
 
- If the DLL is not in the specified path, or if the specified path is incorrect.
 
- If one of the dependencies of the DLL that you are trying to load is not met; in other words, if a dependent DLL is not present or is not in the specified path.
You can use the Depends.exe tool to check whether or not all of the dependencies of your DLL are met.
Your DLL must implement DllRegisterServer and DllUnregisterServer, which contain the logic that is necessary to add or delete the required registry entries for the COM component. RegSvr32.exe finds the entry point to these functions and calls them appropriately.
If you use the Microsoft Active Template Library (ATL), Wizard, to create the COM DLL, the Wizard generates the necessary code for DllRegisterServer and DllUnregisterServer.


If you have a 64-bit Windows, there is a 32-bit version of Regsrv32 and a 64-bit version of Regsrv32. The 64-bit version of Regsrv is in the System32 folder, while the 32-bit version is in the SysWOW64 folder. By default, the 64-bit version of Regsrv will run.

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ 

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\VBADLL.ClassModule1_Bar20190801]
@="VBADLL.ClassModule1_Bar20190801"

[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\VBADLL.ClassModule1_Bar20190801\Clsid]
@="{4A8119F2-25D0-4C95-8152-95379BCF85A2}"

Else u can do:

In a *.reg file paste the folowing:

REGEDIT4

; ActiveX DLLs

[HKEY_CLASSES_ROOT\.dll]
@="dllfile"

[HKEY_CLASSES_ROOT\dllfile\shell\regdll]
@="Register ActiveX DLL"

[HKEY_CLASSES_ROOT\dllfile\shell\regdll\command]
@="regsvr32.exe \"%L\""

[HKEY_CLASSES_ROOT\dllfile\shell\unregdll]
@="Unregister ActiveX DLL"

[HKEY_CLASSES_ROOT\dllfile\shell\unregdll\command]
@="regsvr32.exe /u \"%L\""

; ActiveX Controls

[HKEY_CLASSES_ROOT\.ocx]
@="ocxfile"

[HKEY_CLASSES_ROOT\ocxfile\shell\regocx]
@="Register OCX Control"

[HKEY_CLASSES_ROOT\ocxfile\shell\regocx\command]
@="regsvr32.exe \"%L\""

[HKEY_CLASSES_ROOT\ocxfile\shell\unregocx]
@="Unregister OCX Control"

[HKEY_CLASSES_ROOT\ocxfile\shell\unregocx\command]
@="regsvr32.exe /u \"%L\""

; ActiveX EXEs

[HKEY_CLASSES_ROOT\.exe]
@="exefile"

[HKEY_CLASSES_ROOT\exefile\shell\regexe]
@="Register ActiveX EXE"

[HKEY_CLASSES_ROOT\exefile\shell\regexe\command]
@="cmd /c \"%L\" /regserver"

[HKEY_CLASSES_ROOT\exefile\shell\unregexe]
@="Unregister Active EXE"

[HKEY_CLASSES_ROOT\exefile\shell\unregexe\command]
@="cmd /c \"%L\" /unregserver"

Com本质就是一个动态链接库，在windows中是靠DLL来实现的。
这个DLL实现了IUnknown接口等，同时实现了 DllRegisterServer、 DllRegisterServer、 DllCanUnloadNow 、DllGetClassObject等函数。
计算机为每个DLL分配一个GUI识别码，然后利用DllRegisterServer函数将Dll信息写入注册表，利用DllUnregisterServer来卸载DLL

Register Components without using Regsvr32.exe

NOTE: Check the improved version of this at:


Before you can use an DLL or an OCX you must register it. Registering a component places information about the control in the system registry. Once the control has been registered, applications and development environments can search the registry to determine which components have been installed.

Most developers use the Package and deployment wizard to register their components. However, it is occasionally useful to make your own setup kit. The most common method of doing this usually involves shelling Regsvr32.exe. The main problem with shelling Regsvr32.exe is that it is relatively difficult to see if the component was successfully registered. 

The following code shows how to register DLL/Ocx components (including ActiveX EXE's):

Option Explicit

Private Declare Function LoadLibraryA Lib "kernel32" (ByVal lLibFileName As String) As Long
Private Declare Function CreateThread Lib "kernel32" (lThreadAttributes As Any, ByVal lStackSize As Long, ByVal lStartAddress As Long, ByVal larameter As Long, ByVal lCreationFlags As Long, lThreadID As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal lMilliseconds As Long) As Long
Private Declare Function GetProcAddress Lib "kernel32" (ByVal hModule As Long, ByVal lProcName As String) As Long
Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As Long, ByVal hwnd As Long, ByVal Msg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function FreeLibrary Lib "kernel32" (ByVal hLibModule As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function GetExitCodeThread Lib "kernel32" (ByVal hThread As Long, lExitCode As Long) As Long
Private Declare Sub ExitThread Lib "kernel32" (ByVal lExitCode As Long)

'Purpose   :    This function registers and Unregisters OLE components
'Inputs    :    sFilePath                       The path to the DLL/OCX or ActiveX EXE
'               bRegister                       If True Registers the control, else unregisters control
'Outputs   :    Returns True if successful
'Notes     :    This is the API equivalent of RegSvr32.exe.
'Example   :
'               If RegisterComponent("C:\MyPath\MyFile.dll") = True Then
'                   Msgbox "Component Successfully Registered"
'               Else
'                   Msgbox "Failed to Registered Component"
'               End If
'Revisions :    1/Jan/2002. Updated to include code for registering ActiveX Exes.

Function RegisterComponent(ByVal sFilePath As String, Optional bRegister As Boolean = True) As Boolean
    Dim lLibAddress As Long, lProcAddress As Long, lThreadID As Long, lSuccess As Long, lExitCode As Long, lThread As Long
    Dim sRegister As String
    Const clMaxTimeWait As Long = 20000     'Wait 20 secs for register to complete
    
    On Error GoTo ErrFailed
    If Len(sFilePath) > 0 And Len(Dir(sFilePath)) > 0 Then
        'File exists
        If UCase$(Right$(sFilePath, 3)) = "EXE" Then
            'Register/Unregister ActiveX EXE
            If bRegister Then
                'Register EXE
                Shell sFilePath & " /REGSERVER", vbHide
            Else
                'Unregister ActiveX EXE
                Shell sFilePath & " /UNREGSERVER", vbHide
            End If
            RegisterComponent = True
        Else
            'Register/Unregister DLL
            If bRegister Then
                sRegister = "DllRegisterServer"
            Else
                sRegister = "DllUnRegisterServer"
            End If
            
            'Load library into current process
            lLibAddress = LoadLibraryA(sFilePath)
            
            If lLibAddress Then
                'Get address of the DLL function
                lProcAddress = GetProcAddress(lLibAddress, sRegister)
                If lProcAddress Then
                    lThread = CreateThread(ByVal 0&, 0&, ByVal lProcAddress, ByVal 0&, 0&, lThread)
                    If lThread Then
                        'Created thread and wait for it to terminate
                        lSuccess = (WaitForSingleObject(lThread, clMaxTimeWait) = 0)
                        If Not lSuccess Then
                            'Failed to register, close thread
                            Call GetExitCodeThread(lThread, lExitCode)
                            Call ExitThread(lExitCode)
                            RegisterComponent = False
                        Else
                            'Successfully registered component
                            RegisterComponent = True
                            Call CloseHandle(lThread)
                        End If
                    End If
                    Call FreeLibrary(lLibAddress)
                Else
                    'Object doesn't expose OLE interface
                    Call FreeLibrary(lLibAddress)
                End If
            End If
        End If
    End If
    Exit Function

ErrFailed:
    Debug.Print Err.Description
    Debug.Assert False
    On Error GoTo 0
End Function

怎样去掉"工程-引用"中曾经引用的自制的DLL历史记录?

在注册表的 HKEY_CLASSES_ROOT\TypeLib\ 分支中查找“数据”等于“Test”(需要删掉的历史记录)，然后会找到一个键值，该键值的数据等于“Test”，看看这个分支下面的数据，是否包含你 DLL的位置等信息，如果确定。则删除这个键值所在HKEY_CLASSES_ROOT\TypeLib\下的{xxxxxxxx-xxxx-xxxx- xxxx-xxxxxxxxxxxx}分支。（xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx根据你的实际情况是不同的16 进制）

删除引用窗口中引用项目的方法

在开始运行中输入regedit打开注册表，在注册表中搜索“工程名称.dll"，把对应的项删掉便可。要小心，不要删除错了。

For example
REG_SZ D:\developer\src\vbadll\VBADLL.dll

Sub DLLtest()
    Dim ABC As New VBAtest      '定义ABC为新类，即为DLL文件中的类模块VBAtest
    ABC.Test    '调用DLL中提供的过程，来完成原来在VBA中的功能，起到隐藏代码的效果
    Set ABC = Nothing   '释放类资源
End Sub