他山之石——封装函数与COM加载宏

继续学习VBA!

'如果想用com加载宏的形式替代在excel的写EXCEL对象事件，可以在VB里编写好事件程序,封装到dll里，然后注册dll
'和函数封装不同，com加载宏需要在VB里添加"外接程序”
  'AddinInstance_OnConnection相当于excel里的open事件，随时程序的打开而自动加载
  'AddinInstance_OnDisconnection事件相当于close事件，在程序关闭时运行。
  
'Option Explicit
'Public WithEvents elevent As Excel.Application
'Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
'    Set elevent = Application
'End Sub
'Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
'    Set elevent = Nothing
'End Sub
'Private Sub elevent_NewWorkbook(ByVal Wb As Excel.Workbook)
'  MsgBox Wb.name
'End Sub


 '第1步：打开VB6.0,添加一个外接程序。把默认添加的窗体通过右键菜单删除掉。右键设置属性：
 
          '外接菜单显示名称: 自定义
          '外接程序描:自定义
          '应用程序:Microsoft Excel
          '应用程序版本:选取最高的一个应用版本
          '初始化加载形为;startup
 '第2步: 设计器右键--查看代码,设计器的代码窗口,工程菜单--引用:引用microsoft Excel....
 
 
 '第3步: 代码窗口中输入以下几段代码.和
  
      '1 添加公共变量;
           
'          Public xlapp                  As New Excel.Application
'          Dim mycombar                  As Office.CommandBarControl  '声明命令栏对象
'          Dim mybar                     As Office.CommandBarButton '声明命令按钮
         ' Public WithEvents mybarevent  As CommandBarEvents 'Office.CommandBarButton
   Dim mycombar As CommandBarPopup
   Dim mybar1 As CommandBarButton
   Dim mybar2 As CommandBarButton
   Dim cevent(1 To 2) As New 类1
Sub 添加命令()

   Set mycombar = CommandBars(1).Controls.Add(Type:=msoControlPopup)
   Application.CommandBars(1).Controls("Com加载命令").Delete
    With mycombar
        .Caption = "Com加载命令"
        .Visible = True
      Set mybar1 = .Controls.Add
        With mybar1
          .Caption = "在A1中输入1"
          .Tag = "C1"
          .FaceId = 30
          .BeginGroup = True
        End With
      Set mybar2 = .Controls.Add
        With mybar2
          .Caption = "删除输入的数字"
          .Tag = "C2"
          .FaceId = 20
          .BeginGroup = True
        End With
    End With
    Set cevent(1).bar = mybar1
    Set cevent(2).bar = mybar2
End Sub
Sub 删除添加的菜单()
  On Error Resume Next
  Application.CommandBars(1).Controls("Com加载命令").Delete
End Sub


'我们可以用VB编写自定义函数，然后封装成dll文件，最后在excel中使用该自定义函数，具体步骤如下：

'封装可以在工作表中使用的自定义函数和79集一样，只是加载时多了一个步骤
'1 创建步骤省略,下面是要封装的自定义函数
 Function OnlyCount(rg As Excel.Range)
    Dim d As Object
    Dim arr, sr, r
    Set d = CreateObject("scripting.dictionary")
    arr = rg
    For Each r In arr
      d(r) = ""
    Next r
    OnlyCount = d.Count
 End Function


'2 加载dll中的自定义函数
   
   '注册dll 控件。regsvr32 functionfeng.dll
   '工具--宏--加载宏--自动化。找到在VB中命名的工程名称和类模块名组合成的文字，选取它确定。回到加载宏对话框中，已出现该函数项目。
   
'3 使用方法。和一般的excel函数使用方法相同
 '