受欢迎的博客标签

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

Published

Table of Content

 
how to fixed Run-time error '429': ActiveX component can't create object step by step?
OFFICE2013 excel 64bit 引用dLL 提示“错误429 ActiveX部件不能创建对象(Run-time error '429': ActiveX component can't create object)
1、Microsoft Office Excel 2007
2、Microsoft Visual Basic 6.0
3、Windows server 2016
 
案例:在工作表的C1单元格得出A1单元格+B1单元格的值。
设计的VBA代码:
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.

 
第一部分、使用VB6.0制作DLL文件
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...).
 
一、Creating the ActiveX DLL Project(启动VB6.0,新建一个ActiveX DLL工程)
1)On the File menu, click New Project.
2)In the New Project dialog box, double-click the ActiveX DLL icon.
 
 
 
二、 引用:在VB中对Excel的引用
Office 2010 32bit
d:\tihirdprogram\microsoft Office12\Office14\EXCEL.EXE
Office 2013 64bit
Microsoft EXCEL Office15 Object Library
D:\Thirdprogram\Microsoft Office2013\Office15\EXCEL.EXE
 
 
 
 不同版本的EXCEL在“引用”窗口里显示的版本号也不同:
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
 
三、 修改ActiveX DLL的工程名称和类模块名称
 
 
四、编写代码:
在代码窗口输入代码,过程名称为Test:
Sub Test()
    On Error Resume Next
    Dim VBt, YB    '定义变量VBt
    Set VBt = GetObject(, "Excel.Application")    '使VBt表示为EXCEL对象
    Set YB = VBt.ActiveSheet    '使YB表示为EXCEL的当前工作表
 
    '注意要在对象前加上YB变量以表示是EXCEL当前工作表的对象
    YB. Range("C1")  = YB.Cells(1, 1).Value + YB.Cells(1, 2).Value
End Sub
 
五、设置工程属性 (为使开发的程序更规范,可以对工程属性加以描述【非必要设置,可以省略】):
 
 
六、保存工程、测试、生成DLL文件:
1、保存工程:保存本工程以作为将来修改代码和升级程序的需要;
2、测试工程:执行快捷工具栏上的“启动”按钮,检查是否存在错误;
七、Make a  DLL(生成DLL文件:制作DLL文件)
1)If the TestThing project is still in run mode, click the Close button on Form1 to return to design mode.
2)In the Project Explorer window, click ThingDemo to select the project.
3)On the File menu, click Make ThingDemo.dll to open the Make Project dialog box. Click OK to build the .dll file.
 
VB6 做出来的ActiveX DLL,都会有四个函数
DllRegisterServer、DllUnregisterServer、DllGetClassObject、DllCanUnloadNow 
 
 
第二部分、调用DLL文件
 
一、在VBE中调用DLL文件
调用DLL文件,要分两步走:先注册DLL,再使用DLL。
1、 注册DLL(添加引用)Add Reference
注册DLL也可以使用代码来做,但那样比较复杂,也存在很多问题,建议一般使用手工来注册。
(这里应该先设计好DLL文件放在硬盘的位置,因为后面引用DLL文件的代码也应该是指向这个位置的。)  
先打开EXCEL,再打开“Visual Basic 编辑器”
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)
 
 
2、引用DLL(这样每次打开打开文件时,就不必再去那个引用列表里打个勾了)
①DLL文件放在与EXCEL文件同一个文件夹内
在ThisWorkbook中添加如下代码:
Private Sub Workbook_Open() '打开文件时加载要引用的DLL文件
shell "Regsvr32 /s " & Chr(34) & ThisWorkBook.path & "\VBADLL.dll"& Chr(34)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)'关闭文件之前卸载引用的DLL文件
shell "Regsvr32 /s /u " & Chr(34) & ThisWorkBook.path & "\VBADLL.dll"& Chr(34)
End Sub
'/s参数是防止出现确认窗口;/u参数为取消引用。
 

How to Register and Unregister a DLL or ActiveX controls using Regsvr32.exe

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.
Excel are registered in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\ 
 
detail:
How to Register and Unregister a DLL or ActiveX Controls Using Regsvr32.exe
 
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
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文件放在固定文件夹内,如果你有多个XLS文档需要使用到同一个DLL文件,但这些XLS文档又不可能都与DLL文件放在同一个文件夹,则需要将DLL文件放置到一个固定文件夹,这样,只需要将上面的代码 ThisWorkBook.path & "\VBADLL.dll" 换成一个固定位置即可。
 
注意:有时间可能出现某些错误,如提示“变量类型未定义”等,可能是引用后改变了文件的位置或改变了文件的名称,即使再重新再改回来也可能会出现这些问题,所以,在正确注册及引用以后,最好不要再去修改这个DLL文件。如果出现问题,重新注册一次,保证注册的DLL文件的位置正确即可。
 
清除引用
怎样去掉"工程-引用"中曾经引用的自制的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
 
 
 
二、新建一个模块,输入调用DLL文件程序的过程:
Sub DLLtest()
    Dim ABC As New VBAtest      '定义ABC为新类,即为DLL文件中的类模块VBAtest
    ABC.Test    '调用DLL中提供的过程,来完成原来在VBA中的功能,起到隐藏代码的效果
    Set ABC = Nothing   '释放类资源
End Sub
 
 
给模块改名称
在模块的属性里面改。点击选择模块,打开属性窗口。在左边。  如果没有属性窗口就自己打开它。方法:选择模块->菜单->视窗->属性窗口
 
三、在工作表中运行DLLtest宏即可实现调用:
 


综上所述,封装DLL的主要步骤是:
①在VB中引用EXCEL;
②编辑代码(要在对象前面加上定义的EXCEL变量);
③在VBE中引用该DLL文件;

④在VBE的代码中调用DLL中的过程。

 

 

第三部分  关于VB中ACtivex DLL类得初始化

 

1. 如果想只在类生成的第一次执行代码,可以在类中添加一个module,在该module中添加main函数,然后将project的属性中的启动设为main.此时,main中的代码仅在第一次生成类时执行一次。
 
给工程添加模块,编写sub main(),在其内全局变量的定义和初始化,最后将工程属性中的“启动对象”该为sub main().

src/vbadll/VBADLL.vbp

Class=ClassModule1_BarTenderLabel; ClassModule1_BarTenderLable.cls
Class=ClassModule2_ScanData; ClassModule_ScanData.cls
Class=ClassModule3_ReisterCode; ClassModule3_ReisterCode.cls
-Startup="(None)"
+Module=Module1; Module1.bas
+Startup="Sub Main"
HelpFile=""
Title="Project1"
ExeName32="VBADLL.dll"

在VB中首先定义了一个类class1。

dim A as new class1

new 一开始,就会给相应的类创建实例,即为它分配内存。会引发类的initialize事件

而且,每创建一个新实例,都会分配内存。现在的vb使用的是apart thread的模式。即每一个类的实例独享自己的数据控件。

 
 
 

Useful links

1.vb 窗体的封装【[VBA代码封装过程.doc】 

https://m.book118.com/html/2017/0106/80522650.shtm

Register/Unregister DLL at Runtime VBA (Visual Basic for Applications)

 

https://blog.csdn.net/qwlovedzm/article/details/44261101

Microsoft office document:https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-basic-6/aa229332(v=vs.60)

Creating an ActiveX DLL vb6.0

 

 

第四部分  vbadll 的发布

1.

开发时:在工程中引用Microsoft Excel类型库,有智能提示

2:发布时:因为office 版本的不同,在代码写完之后,去掉引用 Microsoft Excel 9.0 Object Library(EXCEL2000

调用 excel 对象之前先创建

    比如:

   Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")

 

这样就可以避免因为版本的不同,出现问题了

VBA在32位下没问题,到64位异常报错

 

VB程序运行时产生的错误号码的中文解释

 

考场编排系统下载地址vba+call vb dll

使用Excel制作的仓库物料进销存管理系统,非常简单易用

 

运行时错误(429):ActiveX部件不能创建对象

Run-time error '429': ActiveX component can't create object

how to fixed Run-time error '429': ActiveX component can't create object step by step?

https://www.get-itsolutions.com/run-time-error-429/

Answer

In Visual Basic, there are several causes of error 429. The error occurs if any of the following conditions is true:

There is a mistake in the application.
There is a mistake in the system configuration.
There is a missing component.
There is a damaged component.

64位office不支持activex控件

detail:https://support.microsoft.com/en-us/topic/you-receive-run-time-error-429-when-you-automate-office-applications-b6070e15-ea78-4349-2751-72bdb5f186f7