用VBA編寫Excel加載宏田華兵（國家電力公司成都勘測設(shè)計研究院 成都 610072）
加載宏的思想很簡單，就是把執(zhí)行特定功能的模塊保存在磁盤中，用戶可以方便地隨時加載該模塊并使用其中的功能，不需要時簡單地卸載即可。如果你有錄制宏、修改宏的經(jīng)驗，編寫加載宏就是一件非常簡單的事。把包含宏的工作簿以“.xla”格式保存，該工作簿即成為一個加載宏，其中的工作表自動變?yōu)椴豢梢?，工作表?#8220;IsAddIn”屬性也會被自動設(shè)置為“True”。加載宏的存放位置一般是Microsoft Office\Office文件夾下的"Library"文件夾或其子文件夾,也可以是Windows所在文件夾下的"Profiles\用戶名\Application Data\Microsoft\AddIns"文件夾或其他用戶可以存取的地方。此后,用戶就 可以通過Excel工具菜單中的“加載宏”命令來加載、卸載它，一旦加載后，其使用方式與內(nèi)部命令無異。
與普通工作簿或個人工作簿中的宏相比，加載宏有諸多優(yōu)越之處。首先，加載宏能方便地提供給他人使用（簡單的拷貝文件），讓宏的編制者與使用者可以完全分離，因此，專業(yè)人員可以為某一目的編寫包含大量復(fù)雜處理、功能完備的加載宏，提供專業(yè)級或企業(yè)級的解決方案，而用戶卻只需要按Excel命令的標(biāo)準(zhǔn)方式進(jìn)行操作；其次，加載宏的按需加載、卸載機(jī)制，有利于系統(tǒng)內(nèi)存的有效利用；此外，加載、卸載事件還為動態(tài)處理菜單、工具條等界面元素提供了時機(jī)，可以做到與Excel系統(tǒng)本身無縫連接。
事實上，Excel系統(tǒng)的許多附加功能就是以加載宏的形式提供的，如，“與 Access 鏈接”、“自動保存”、“規(guī)劃求解”、“分析工具庫”，等等。這與Autodesk的AutoCAD以lisp文件方式向用戶提供附加功能有些類似，但Excel加載宏對系統(tǒng)的控制能力似乎更強(qiáng)。
制作一個簡單的加載宏并不復(fù)雜，甚至可以把系統(tǒng)錄制的宏直接保存為加載宏。但要實現(xiàn)功能較為齊備的加載宏，不經(jīng)過編碼是不可能完成的。加載宏中的宏過程，可以與動態(tài)添加的自定義菜單（工具條）命令連接，也可以實現(xiàn)為自定義的工作表函數(shù)。加載宏中的自定義工作表函數(shù)與普通工作簿中完全一致。若是以命令方式提供，則須動態(tài)處理菜單（工具條）。這可以在Auto_Open()和Auto_Close()過程中實現(xiàn)，他們分別在加載宏的工作簿被打開和關(guān)閉時自動執(zhí)行。另外，Excel97還專門提供了工作簿的AddinInstall和AddinUninstall事件，分別是在工作簿作為加載宏被加載、卸載時發(fā)生。自然，編寫加載宏還必須熟悉 Excel的Automation接口，即對象體系。
在Microsoft Excel 97 Developer's Kit中，加載宏被劃分為兩種類型。即，與Excel內(nèi)置功能協(xié)同工作的提交加載宏（committee add-in）和用自定義命令完全取代Excel命令的接管加載宏（dictators add-in）。從自解釋的命名中即可看出他們之間的區(qū)別，其編制原理是完全一樣的。簡單地說，如果在加載提交加載宏時，把全部的Excel界面元素卸載并換上自定義的界面，同時屏蔽內(nèi)置命令、函數(shù)，則可得到接管加載宏。
以上介紹的是在Excel97及其以前兩個版本中被廣泛應(yīng)用的加載宏形式，如果你使用的是Office 2000版本，那么你擁有更多的選擇余地。Office 2000除了向后兼容，繼續(xù)支持普通加載宏外，它還支持更為全面、功能更為強(qiáng)大的COM加載項形式。COM加載項是一個完全意義上的ActiveX Automation組件，它實現(xiàn)了IDTExtensibility接口，作為標(biāo)準(zhǔn)ActiveX服務(wù)器被Excel或/和其他Offfice應(yīng)用程序連接和使用。由于COM加載項是在其他編程環(huán)境中完成而被Office套件使用的，在此不涉及，如果你編寫過Visual Basic或Visual Studio環(huán)境中的Add In程序，則明白他們實質(zhì)上是一回事，只是變換了客戶而已。
4 一個簡單的實例這是一個完整的小型加載宏，當(dāng)它加載后，在工具菜單的末尾將增加一個菜單項，點按該菜單將對當(dāng)前選定區(qū)域求平均值、小值平均值、大值平均值，并以對話框形式顯示結(jié)果。另外，你也可以以工作表函數(shù)形式使用其中的宏，只是略有限制。
限于篇幅，本程序中未處理異常。
'當(dāng)工作簿打開時執(zhí)行
Sub Auto_Open()
'在工具菜單中添加按鈕
With Application.CommandBars("Tools").Controls.Add(msoControlButton, 1)
.Caption = "大小平均值" '按鈕文本
.Tag = "Example" '按鈕標(biāo)簽
.OnAction = ThisWorkbook.Name & "!AverageMM" '按鈕命令宏
End With
End Sub
'當(dāng)工作簿關(guān)閉時執(zhí)行
Sub Auto_Close()
Dim colMyButtons As CommandBarControls
Dim ctrMine As CommandBarButton
'查找該工程添加的菜單按鈕及其復(fù)制件
Set colMyButtons = Application.CommandBars.FindControls(msoControlButton, 1, "Example")
'如果已被用戶手工刪除則退出過程
If colMyButtons Is Nothing Then Exit Sub
'刪除所有與該工程相關(guān)的菜單按鈕
For Each ctrMine In colMyButtons
ctrMine.Delete
Next
End Sub
'實現(xiàn)功能的宏函數(shù)
Function AverageMM(Optional RA As Range = Nothing, Optional OP As Integer = 0)
Dim RAG As Range, CL As Range
Dim A As Single, MA As Single, MI As Single
Dim T As Integer, TMA As Integer, TMI As Integer
Dim MSG As String
If RA Is Nothing Then
Set RAG = Selection
Else
Set RAG = RA
End If
For Each CL In RAG
If CL.Value <> "" And IsNumeric(CL.Value) Then
T = T + 1
A = A + CL.Value
End If
Next
If T <> 0 Then
A = A / T
Else
Exit Function
End If
For Each CL In RAG
If CL.Value <> "" And IsNumeric(CL.Value) Then
If CL.Value - A > 0.001 Then
TMA = TMA + 1
MA = MA + CL.Value
ElseIf CL.Value - A < -0.001 Then
TMI = TMI + 1
MI = MI + CL.Value
End If
End If
Next
If TMA = 0 Then
MA = A
Else
MA = MA / TMA
End If
If TMI = 0 Then
MI = A
Else
MI = MI / TMI
End If
If RA Is Nothing Then
MSG = "有效單元共：" & T & "個" & vbCrLf & "平均值：" & Space(2) & _
A & vbCrLf & "大值平均：" & MA & vbCrLf & "小值平均：" & MI & Space(20)
MsgBox MSG, vbInformation, "平均值"
Else
Select Case OP
Case -1
AverageMM = MI
Case 0
AverageMM = A
Case 1
AverageMM = MA
End Select
End If
End Function
