简述封装VBA自定义函数dll以及在VBA和工作表中的引用

时光鸟

说实话，EXCELHOME里高手如云，我写这个东西真有点班门弄斧的味道，高手们请忽略此贴！之所以决定写，一方面是自己学习总结的一点心得体会；另一方面与想学习这方面东西的筒子们一起分享交流。还有一个更重要的原因就是，我能找到的现有的文章中，关于封装、在VBA中引用及在工作表中引用这三个方面都谈到了的比较系统的介绍比较少，甚至没有。闲话不说了，开始正题：

一、用VB6.0封装自定义函数
1.VB6.0封装是最方便受限制最少的方法，当然用VS.net的C#也可以做来（这里不作介绍），但需要.net库及对应的版本支持大家都知道win7是VB6.0所能支持的最后一个系统，win8已经不再支持官方安装版的VB6.0了，要想在win8中用VB6.0也是可以的，那就是用精简版的VB6.0（只有6M左右），封装VBA足够用了。

2.以管理员身份运行VB6.0（一定要以管理员身份运行），在弹出新建工程中选择ActiveX dll


3.在工程管理器分别修改工程名称及类名称（改名是为了方便，如果仅测试不改名也行）
                 

4.工程名称及类名称改完后，把写好的VBA自定义函数复制到刚新建的工程的类模块中（本例是myClass）,复制过来的函数一定不能是private的，要么是public的，要么前面空出来，原因不言自明。


5.如果你复制过来的函数是经过测试没有问题的话，选择左上角“文件”--生成XXXX（本例是生成TransNumber.dll），在弹出的对话框中选择该.dll封装文件的存放位置即可（随便什么位置），如果没有错误提示的话，那么封装就算是完成了！！（如果不是管理员身份运行VB6.0时，此处会提示不能访问注册表的错误提示）


特别说明：
   如果你的VBA里不包含excel对象的话，可以按上述步骤封闭，如果包含excel对象的话，那么对应的工作就要麻烦一点了，在VB6.0中封装成dll前（也就是在进行上述第5步前），要先在vb6.0中添加引用Microsoft Office 15.0 Object Library和MicrosoftExcel 15.0 Object Library （不同的office版本，相应的版本号会不同，我这里是office2013，所以版本号是15.0）
  在所有的过程前加上这两句：自定义一个Excel.Application对象xlapp（起别的名字也行，随便）
Dim xlapp As Object  
Set xlapp = GetObject(, "Excel.Application")
然后在所有excel对象前面加上xlapp。比如,sheet,cells,range，workbook等都是excel的对象，都要改成xlapp.sheet,xlapp.cells,xlapp.range，xlapp.workbook的形式。当然这些改动最好在VBA工程里面测试无误后再复制到vb6.0里封装。
（鉴于很多朋友看东西速度快得惊人，以至于将这部分完全忽略，没办法，只好加红，如果还是忽略，我也没招了）

二、在工作表中引用已封装的自定义函数。

6.在标题栏点击“开发工具”（如果没有的话，在选项中自定义设置中自己调出来，如下图），选择COM加载项，在弹出窗口中选择添加，找到刚才生成的.dll(本例是TransNumber.dll)文件，然后再点确定。

   
      

         
  

7.接下来在标题栏点击“开发工具”选择“加载项”（注意：这里不是“COM加载项”，而是紧靠COM加载项的“加载项”），在弹出对话框中选择自动化，然后再在弹出的自动化服务器中选择我们刚才生成的函数（本例是transNumber.myClass）,找到后选择并确定，然后在刚才弹出的加载宏对话框中就多了我们添加的函数了，再点确定后就完成了所有设置，接下来就可以在工作表中像使用内置函数那样使用我们的自定义函数了。(如下图)

      
     






三、在VBA工程中引用已封装的自定义函数。
8.打开VBA工程，选择“工具”--“引用”，在弹出的引用对话框中点“浏览”，找到我们生成的.dll(本例是TransNumber.dll),点确定后回到刚才引用对话框里就多了我们的自定义函数transNumber，确定后完成设置。
   
     
        


9.在VBA中就可以用"工程名.类名称.函数名（)"的方式引用我们的自定义函数了，为简化代码，你也可以按下图方式操作。




至此，自定义函数的封装、在工作表的中引用及在VBA工程中的引用都介绍完毕了。有不对的地方，恳请指定，谢谢。

zyg365

支持一下：讲的比较细，但这种封装，需要在Excel中写代码才能使用，
这里有另一种封装方法，在Excel中不需要写代码就能使用，就像使用系统自带的函数一样使用
http://club.excelhome.net/thread-958823-1-1.html

时光鸟

zyg365 发表于 2012-12-31 20:40
支持一下：讲的比较细，但这种封装，需要在Excel中写代码才能使用，
这里有另一种封装方法，在Excel中不需 ...

上面谈到的这种方法在工作表中引用函数，不需要在excel中写代码，上面说得很清楚了

vbaplus

哎，总是在一个东西还没学会多少的时候，就已经成为过去时了
你说vb6在windows8中不支持，看来是要成为过去式了，就连大家天天讨论的VBA也会被VSTO逐渐取代的

yclotus

支持一下楼主的奉献精神。。。

wanghx8866

谢谢老师我需要拿走了

时光鸟

vbaplus 发表于 2013-1-1 20:42
哎，总是在一个东西还没学会多少的时候，就已经成为过去时了
你说vb6在windows8中不支持，看来是要成为过去 ...

追赶时髦伤不起，够用就好

网名

楼主你好，我点击生成dll文件是跳出此提示框，请帮助指点，谢谢~~~

时光鸟

网名 发表于 2013-1-6 17:23
楼主你好，我点击生成dll文件是跳出此提示框，请帮助指点，谢谢~~~

你大概是没仔细看或没看明白我的原文，再次复制一次回复你：

特别说明：   如果你的自定义函数里不包含excel对象的话，可以按上述步骤封闭，如果包含excel对象的话，那么对应的工作就要麻烦一点了，在VB6.0中封装成dll前（也就是在进行上述第5步前），要先在vb6.0中添加引用Microsoft Office 15.0 Object Library和MicrosoftExcel 15.0 Object Library （不同的office版本，相应的版本号会不同，我这里是office2013，所以版本号是15.0）  在所有的过程前加上这两句：自定义一个Excel.Application对象xlapp（起别的名字也行，随便）Dim xlapp As Object  Set xlapp = GetObject(, "Excel.Application") 然后在所有excel对象前面加上xlapp。比如,sheet,cells,range，workbook等都是excel的对象，都要改成xlapp.sheet,xlapp.cells,xlapp.range，xlapp.workbook的形式。当然这些改动就好在VBA工程里面测试无误后再复制到vb6.0里再封装。

你的问题就在于，sheets("XXXXX")这都属于工作表对象！

网名

谢谢楼主，我在按你的方面在设置，
但是设置到些处工作对象时又不知道如何设置 了
原代码是
Dim Wk As Workbook
按你的方面加了xlapp的定义后 修改成：Dim Wk As xlapp.Workbook
还是出现未定义的错误 。