ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 挑战你的Excel知识,一起测验下 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 72715|回复: 108

[原创] VBA 全自动使用DLL 的各种封装方法 - 完全公开源码和方法(如用VSTO就什么都不用学)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2011-7-10 23:03 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:封装
本帖最后由 灰袍法师 于 2011-12-10 20:36 编辑

强烈推荐论坛置顶帖 VBA收藏贴 http://club.excelhome.net/thread-721260-1-1.html

更新第10楼,靠VSTO的开发方法,直接在VB开发Excel工作簿,全自动把一切代码打包入DLL,连注册都不需要!
需要VS2007, office2007 .NET 3.5以上才可以用


很多筒子也许希望用VB的强大功能制作DLL,从而使VBA的速度加快,功能增多,或者保护代码,简单搜索一下论坛,你会找到许多封装DLL的例子。

以下介绍一个直接在 VBA引用VB 编译的DLL类库(class library)的方法,不是封装自定义函数哦,而是封装VBA直接使用的对象。
本方法编译出来的DLL可以放在任意目录,使用前无须人手点击VBA菜单的引用,也无需regsv*****.exe
不过需要.NET framework 2.0或以上,office2007或以上的支持,office 2003需要打补丁(附件压缩包有此补丁)

本方案用Visual studio 2010, 编译 32位DLL for anyCPU,附件有工程源码,熟练的筒子可以自行重新编译自己喜欢的版本。

重点有2个:
1 VB的类库必须要能够被regasm.exe注册,也就是说要有COM的注册信息,微软的官方网页有介绍
Walkthrough: Creating COM Objects with Visual Basic
http://msdn.microsoft.com/en-us/library/x66s8zcd.aspx

这里简单介绍步骤:
步骤一: VB2010里面新建项目,选择VisualBasic,类库
步骤二 :在解决方案资源管理器点击你的项目ClassLibrary1 (或者你刚才新建项目时候采用的项目名称),选择添加-新建项-COM类
这时候你将会看到出现一个ComClass1,这就是你要写你的自定义对象代码的地方,里面应该已经自动生成了以下代码
这些代码是注册COM类必需要用到的,GUID可以换成其他你喜欢的值,只要跟系统已经注册的其他项目没冲突就行(一般乱写一个也不会有冲突)

<ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
Public Class ComClass1

#Region "COM GUID"
    ' 这些 GUID 提供此类的 COM 标识
    ' 及其 COM 接口。若更改它们,则现有的
    ' 客户端将不再能访问此类。
    Public Const ClassId As String = "4061126d-7488-4dcd-a4f1-40d632e03a15"
    Public Const InterfaceId As String = "65aab5a1-a56b-4418-a8bf-35d247166e49"
    Public Const EventsId As String = "ae811cd9-1c21-4a89-99a5-ff4ef3edb396"
#End Region

    ' 可创建的 COM 类必须具有一个不带参数的 Public Sub New()
    ' 否则, 将不会在
    ' COM 注册表中注册此类,且无法通过
    ' CreateObject 创建此类。
    Public Sub New()
        MyBase.New()
    End Sub
End Class

步骤三: 写好你的对象代码,属性方法等等,编译即可得到一个DLL。
如果你只需要在自己的电脑用这个DLL,那么可以让编译器帮你自动注册
双击 My Project ,点击编译类别,勾选“为COM互操作注册”即可
但是这个做法并不能让你的DLL在其他操作系统,其他电脑运行,所以实际上没什么用。

2 要在VBA使用这个DLL,需要先注册这个DLL,注册方法是采用.NET framework 目录的regasm.exe
本附件的VBA已经做成靠Shell命令在运行时自动注册,自动注销。
详细的命令可以看VBA源码,或者自己搜索这个regasm.exe的用法。
要注意的是:这个注册方法要求安装了.NET framwork 2.0或以上,并且需要是office 2007或以上
office2003则需要打一个补丁(附件已提供此补丁简体中文版,也可去微软网站下载 http://www.microsoft.com/download/en/details.aspx?id=10624)

按上述做法,现在应该可以在VBA用createobject来引用这个DLL了。

附件的qsort.dll是编译好的类库
附件的.xls将引用这个类库,对数组排序,可以看出这个排序速度非常快,100万行也就0.3秒!
不过这个版本只能处理数值型的排序。。。。。。
因为VB没有 Variant 变体类型,只有Object类型,这种类型简直是龟速吖!!!
而且不能在一个数组里面混合处理数值和字符串,也不会自动识别数值型和字符串吖。太残了。。。。。。

所以我又编译了另外一个qsortstr.dll,这个dll把所有输入数据当作字符串来排序,除了文件名不同,其余用法跟qsort.dll一样。
100万行4字符长度排序,耗时1.3秒左右,看来排序的速度跟数值大小,和字符串长度都有很大关系吖。

打算封装VBA代码的筒子要知道的是:有好多不同的封装方法,互相不通用的!!! 详情看第10楼。

这个关于VB6.0封装DLL的链接不错,图文并茂而且很详细,强烈推荐:VB封装DLL实例讲解 by 江羽
http://www.accessoft.com/blog/ar ... userid=3967&Id=4563

如果是VS2010要封装自定义函数的话,参考:
chentx的VSTO4.0类库制作EXCEL自定义函数
http://club.excelhome.net/thread-674732-1-1.html
VB6.0参考:
Zamyi的封装自定义工作表函数为DLL
http://club.excelhome.net/viewth ... B7%E2%D7%B0%2Bzamyi

本帖附件补充一个跟Zamyi的帖一样但更详细更安全的封装方法,也就是用VB6制作excel的自定义函数DLL,可以在单元格直接使用
而且本帖附件的DLL可以放在任意目录,由VBA自动注册,引用,注销,VBA源码和VB工程全部公开,VB6做这个太简单了
关键是:
1 VB6的工程选择ActiveX DLL
2 VBA用regsv***** 去注册这个DLL,注意执行命令 regsv***** 的 /s 参数必需在最前面,否则无法静默注册,必须先自动取消之前的注册,否则目录一旦更改就无法新增最新的目录到注册表。
3 VBA用addins.add方法去引用这个DLL为加载宏,比较保险的方法是加上on error resume next,并且先取消之前的引用,然后再加。

[ 本帖最后由 灰袍法师 于 2011-8-10 20:36 编辑 ]
VB编译(COM)DLL.jpg

VBA引用 VB编译的类库 (DLL) 对一维二维数组快速排序.rar

329.88 KB, 下载次数: 1748

VB6开发EXCEL自定义函数加载宏(DLL全自动_注册_引用_注销.rar

13.97 KB, 下载次数: 2116

qsort.dll和qsortstr.dll的属性和方法说明.rar

805 Bytes, 下载次数: 1081

评分

参与人数 9鲜花 +19 技术 +1 收起 理由
jy03342543 + 2 太强大了
jygzcj + 3
ravinn + 2
veggieg + 2 感觉法师的功力是最深的!!:)
cxh2010 + 2 太强大了

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-7-11 04:39 | 显示全部楼层
虽然DLL的排序代码很高效

但是细心的筒子可以发现,把EXCEL工作表数据传到对象里面,再从对象里面把数据提取出来,耗时太长了,完全淹没了排序速度带来的提升

唉,有什么方法可以高速把数据传入DLL呢??? 目前只想到把EXCEL数据存入.txt文件,然后在DLL读入这个.txt

点评

用vba传数组给自定义函数不行吗?可以用XLL开发,速度快。 http://club.excelhome.net/thread-964658-1-1.html  发表于 2012-12-30 19:06

TA的精华主题

TA的得分主题

发表于 2011-7-11 07:28 | 显示全部楼层
我不记得在那里看到以
.csv格式写入,再重新调出,写入 .xls格式要比直接存为xls格式要快
在大师面前班门弄斧了......

[ 本帖最后由 335081548 于 2011-7-11 08:30 编辑 ]

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-7-11 17:05 | 显示全部楼层
原帖由 335081548 于 2011-7-11 07:28 发表
我不记得在那里看到以
.csv格式写入,再重新调出,写入 .xls格式要比直接存为xls格式要快
在大师面前班门弄斧了......


你太客气了,我不懂的东西也很多的。。。。。。

你说的对,csv格式会更快,我也是这么想的。

不过我又觉得如果还要依赖 读写.txt 来传递数据,那么干脆不要用DLL更好,直接把DLL换成.EXE,更加干净利落。

初步发现应该不是传递效率的问题,实际上按值传递一个100万元素的数组只需要0.6秒

而顶楼程序花了四秒以上,应该是VB内部对传递的对象当作数组来读取,就会比较慢,试一下 for each in obj 也许会好些

果然,用for each语句来导入数据,速度大大加快,现在只需要1.5秒,而导出到 VBA数组 则只需要0.3秒

附件是:字符串排序的DLL版本,附带VB工程源码。

[ 本帖最后由 灰袍法师 于 2011-7-12 05:37 编辑 ]

VBA引用VB编译的类库 (DLL) 对一维二维数组快速排序 - 快速导入数据版本.rar

47.55 KB, 下载次数: 744

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-7-25 04:10 | 显示全部楼层
奇怪了,为什么下述链接的两个帖一堆人追着问,我这个帖连下载的都很少?

照理说创建一个类库,跟创建一个addin或者函数库 没啥区别吧,一样可以在VBA当自定义函数使用嘛。

而且。。。。。。封装为对象不是比封装为自定义函数更加好用么???

Excel加载宏管理器-轻松加载卸载DLL封装函数、加载宏,一键搞定!
http://club.excelhome.net/thread-729100-1-1.html

COM加载宏封装自定义函数并全自动安装最简实例
http://club.excelhome.net/viewth ... p;extra=&page=1

[ 本帖最后由 灰袍法师 于 2011-7-25 04:13 编辑 ]

TA的精华主题

TA的得分主题

发表于 2011-7-25 11:46 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-7-25 11:57 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-7-26 21:14 | 显示全部楼层
本帖最后由 灰袍法师 于 2011-8-14 22:32 编辑

令我感到非常郁闷的是:

在VB 6.0上开发一个 ActiveX DLL,编译成.dll文件,然后在Excel里面当作加载宏使用

让单元格可以直接输入自定义函数,这是一件非常简单的事情。不用三分钟我就研究出来了。

而 VB2010里面,做这个事情似乎是不可能的,只能用C#去做,VB完全做不了!!!

补充:VB也可以做,不过要安装收费软件 addin-express,Add-in Express&#8482; 2010 for Microsoft&reg; Office and .net售价 349美元,废!
这个教程就是基于此软件的,其VB2010的创建工程画面,我的VB2010就看不到。
http://www.add-in-express.com/docs/net-excel-automation-addins.php

VB编译出来的类库在VBA使用是没有问题的(用顶楼的注册方法即可,需要.NET支持)

但没法做成自定义函数在单元格直接使用。

一个曲折的方法是:用VBA做个自定义函数,在此自定义函数内部调用VB类库的对象方法即可。

微软真是越升级越倒退了,各位有VB6的可千万别删除了吖,它至少有两个优点:不需要.NET,可以直接做出自定义函数加载宏。

[ 本帖最后由 灰袍法师 于 2011-8-2 19:25 编辑 ]

TA的精华主题

TA的得分主题

发表于 2011-8-7 22:33 | 显示全部楼层
原帖由 灰袍法师 于 2011-7-26 21:14 发表
令我感到非常郁闷的是:

在VB 6.0上开发一个 ActiveX DLL,编译成.dll文件,然后在Excel里面当作加载宏使用

让单元格可以直接输入自定义函数,这是一件非常简单的事情。不用三分钟我就研究出来了。

而 VB2 ...


法师老师的帖子看了感觉晕乎乎的,可能是我太愚钝了...封装DLL到现在都还没学会,更不知道老师说的几分钟学会封装自定义函数.........学习的路还长.
帖子比较深奥.所以回帖的人有些少

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-8-7 23:24 | 显示全部楼层
本帖最后由 灰袍法师 于 2011-8-14 22:32 编辑
原帖由 XZ19860527 于 2011-8-7 22:33 发表
法师老师的帖子看了感觉晕乎乎的,可能是我太愚钝了...封装DLL到现在都还没学会,更不知道老师说的几分钟学会封装自定义函数.........学习的路还长.
帖子比较深奥.所以回帖的人有些少


你说的对,我自己也觉得这个帖写得不够清楚。

VisualStudio 2010的封装方法,跟VB6.0的封装方法其实是不一样的,我也是看到论坛很多VB6的封装例子,但是VB2010的几乎没有才发此帖。
其实我觉得VB6.0用于封装DLL,反倒比VB2010简单得多

封装DLL有好几个不同做法,也许这也是许多新手搞不清东南西北的原因。

粗略分类一下分别是
1 VB6开发,封装为窗体,对象及其方法,要求在VBA代码里面使用
可以参考: 一个VB6.0的DLL,在VBA使用的实例 by yl1991,主要看第11楼和第16楼
http://club.excelhome.net/thread-746691-2-1.html

2 VB6开发,封装为自定义函数,要求在单元格直接输入公式就可以使用,这个简直太简单了,顶楼补充了做法和全部源码和VB工程。
论坛好多人发过帖介绍了,也可参考:
封装自定义工作表函数为DLL by Zamyi
http://club.excelhome.net/thread-572819-1-1.html
或者上面第5楼的两个帖的链接(注:此两帖的作者没有完全公布做法,而是靠自己制造的安装工具去实现自动注册引用)

3 VB2010开发,封装为窗体,对象及其方法,要求在VBA代码里面使用,这个也没啥难度,关键是开发一个对象总是比开发一个函数要麻烦一点而已
就是本帖顶楼的做法,因为写成了一个算是比较实用的排序类,所以代码比很多封装教学帖的超简单自定义函数要复杂得多。

4 VB2010开发,封装为自定义函数,要求在单元格直接输入公式就可以使用,这个要求我认为只能用C#,不能用VB,参考:
VSTO4.0类库制作EXCEL自定义函数 by chentx
http://club.excelhome.net/thread-674732-1-1.html
我自己倒觉得与其在解决方案里面多使用一门C#语言,还不如多装一个VB6.0算了。
补充:
VB也可以做,不过要安装收费软件 addin-express,Add-in Express&#8482; 2010 for Microsoft&reg; Office and .net售价 349美元,废!
这个教程就是基于此软件的,其VB2010的创建工程画面,我的VB2010就看不到。
http://www.add-in-express.com/docs/net-excel-automation-addins.php

5 开发类似 windows API的dll,也就是用 VBA代码的 public declare 来声明,使用时就像一个过程或者函数,这个做法现在比较少见,毕竟面向对象编程用起来要方便得多。所以我也没有研究这个做法,搜索一下论坛应该也有。
可以参考
VB6.0 自定义函数方法 by zhounie
http://club.excelhome.net/viewthread.php?tid=729841

6 微软新增的VSTO开发方法,可以把所有代码打包进DLL
你发布作品需要四个文件,附件例子是 ExcelWorkbook1.dll        ExcelWorkbook1.dll.manifest             ExcelWorkbook1.vsto   ExcelWorkbook1.xls
,用起来非常方便,连DLL注册都不需要!因为已经帮你全自动做好了,也就是说,根本不需要VBA了,直接就用VB完全代替VBA的所有功能,开发所有代码。
但是这样的开发需要VS2007,而且会导致安装.NET3.5,office2007以下的电脑没法使用,而且VB代码毕竟跟VBA代码不是完全一样,要学习一点新的东东。
微软的.NET真是一把双刃剑,它其实就是一个更先进的windowsAPI,学习起来,用起来都方便一万倍,但是在没装.NET的电脑或者.NET版本不对的电脑上,就无法运行程序。

附件是一个简单的第6种封装方法的实例,打开ExcelWorkbook1.xls文件将触发workbook open事件,显示hello,而修改任意单元格将触发worksheet change事件,在A1单元格显示改动的单元格地址。而所有的事件代码,都在ExcelWorkbook1.dll里面,VBA代码一句都没有。哈哈哈。。。。。。
可以参考:使用VSTO移植VBA by excelflower
http://club.excelhome.net/viewth ... ght=vsto&page=1

你如果把自己的DLL工程上传,说清楚自己的要求,应该很多人可以帮你完成封装。

[ 本帖最后由 灰袍法师 于 2011-8-10 20:37 编辑 ]

VS2010的VSTO开发方法 - 工程打包.rar

53.97 KB, 下载次数: 1843

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-9-23 17:14 , Processed in 0.093669 second(s), 14 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表