我正在尝试创建一个自定义的子程序和函数库,在网络位置中另存为 .txt 文件,我正在创建的工作簿的各个用户可以根据他们选择的用户表单函数导入它们。工作簿的用户只能通过用户表单使用工作簿。我不想要求他们修改其安全信任中心设置以使此导入库代码正常工作,因此我不想使用wb.VBproject.References.import
命令,或将它们添加到插件中。
我发现这种方法非常有效,但前提是没有其他工作簿打开。如果另一个工作簿打开,则此代码最终会在另一个工作簿中插入一个新模块,然后用户窗体调用就毫无意义。
我不明白这是怎么可能的,因为我在“With ThisWorkbook”语句中引用了所有这些代码。我究竟做错了什么?
Sub importLib(libName As String)
Application.DisplayAlerts = False
Path = "C:\Users\(username)\Desktop\excelLibraries\" 'Example only, my path is actually a network location
lib = Path & libName
Dim wb As Workbook
Set wb = ThisWorkbook
With wb
On Error Resume Next
Dim wbModules As Modules
Set wbModules = .Application.Modules 'wb=ThisWorkbook, but doesn't point to this workbook when other workbooks open???
'wb.VBProject.VBComponents.Import lib '---> library saved as .bas, but this requires the user to change their security settings
'wb.VBProject.References.AddFromFile lib '---> library saved as add-in reference, but this requires user to change security settings
'----This method works when no other workbooks are open
For Each a In wbModules 'Clear any previous Library Module
If a.Name = "Library" Then
a.Delete
Exit For
End If
Next
Set m = wbModules.Add 'Create a new Library Module
m.Name = "Library"
m.InsertFile lib 'Insert the code
On Error GoTo 0
End With
Application.DisplayAlerts = True
End Sub
Sub callsub()
importLib "library1.txt"
End Sub
进行以下更改似乎对我有用。
- Change
Dim wbModules As Modules
to Dim wbModules
。否则我会收到类型不匹配错误。
- 去除
.Application
in Set wbModules = .Application.Modules
.
EDIT:
As @马蒂厄·吉登 https://stackoverflow.com/users/1188513/mathieu-guindon已在评论中指出,但值得在问题正文中保留,Application
指的是Excel
应用程序实例,host. The Modules
集合包含所有打开的模块,无论工作簿如何。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)