1、EXCEL加載宏制作攻略日期：2007-08-01 來源： 作者：QEE用 字體：大 中 小 一、概述1制作加載宏的一般步驟（1）創建一個新的工作薄，在其中添加代碼。（2）在“文件”菜單上單擊“屬性”。在“文件名 屬性”對話框中，單擊“摘要信息”選項卡，然后在“標題”框中為加載宏指定一個您希望在“加載宏”對話框中出現的名稱，在“備注”框中輸入您希望當用戶從“加載宏”對話框選擇該加載宏時在“加載宏”對話框下方出現的說明信息。（3）在“文件”菜單上單擊“另存為”。在“另存為”對話框中，從“保存類型”框中選擇“Microsoft Office Excel 加載宏（*.xla）”，然后再選擇保存路徑，

2、并可在“文件名”框中修改文件名。2加載宏的加載和卸載通過在“工具”菜單上單擊“加載宏”，可以加載制作好的加載宏文件，如果它不在列表中，可以在“加載宏”對話框中單擊“瀏覽”找到它，當某個加載宏名稱前的復選框被選中時，說明它已經加載，取消選中復選框即為卸載該加載宏。3加載宏的作用加載宏可以擴展EXCEL的功能，將代碼寫在加載宏中和寫在普通的工作簿中相比，有如下優點：（1）加載宏對所有打開的EXCEL文件都生效。普通工作簿的代碼一般只在特定的工作簿中才生效。（2）加載宏不受宏安全級的限制，也不會有宏運行的提示。即使將安全級設為“非常高”，加載宏就象EXCEL本身固有的功能一樣工作。同樣，它也不會被按

3、住的Shift屏蔽。特別提請不習慣使用加載宏而專門VBA代碼書寫的朋友們注意的是，加載宏的所有好處和優點，都是屬于EXCEL的常規操作者的，雖然加載宏中的代碼可以被其他工作簿（中的代碼）調用，但這樣做是繁瑣而低效的，千萬不要把它當成了C中的“頭”文件或鏈接庫。也許不會有很多人這樣認為，但筆者初學VBA的時候，真的這樣天真過。（3）加載宏在運行時其工作簿窗口是隱藏的，因此普通用戶并不會感覺到它的存在。二、處理加載宏函數加載宏函數似乎是制作加載宏中最簡單的，通常它只需要在模塊中寫一段Function代碼，它就會和EXCEL自身的函數一樣使用，并且出現在“插入函數”對話框的列表中。關于函數的制作，再

4、沒有其它的嗎？1為函數添加說明信息至少你可以通過對象瀏覽器設置“屬性”為函數添加一段說明信息（對需要換行的說明可以在編輯時使用組合鍵CTRL+ENTER），這些信息當用戶使用“插入函數”對話框時就會看到。需要說明的是，在對象瀏覽器中為函數添加說明信息必須在“另存為”加載宏之前，一旦文件名的后綴變為了XLA，函數的屬性設置將不被接受。關于為函數定制說明信息，請參見：圖示自定義函數描述信息及有關介紹。現在，請你新建一個工作簿，插入模塊，粘貼下面的代碼：1 / 9Function dx(n)' by gly1126 金額小寫轉換為大寫dx = Replace(Application.Text

5、(Round(n+0.00000001, 2), "DBnum2"), ".", "元")dx = IIf(Left(Right(dx, 3), 1) = "元", Left(dx, Len(dx) - 1) & "角" & Right(dx, 1) & "分", IIf(Left(Right(dx, 2), 1) = "元", dx & "角整", IIf(dx = "零", &qu

6、ot;", dx & "元整")dx = Replace(Replace(Replace(Replace(dx, "零元零角", ""), "零元", ""), "零角", "零"), "-", "負")End Function如果覺得必要，你可以設置文件屬性（見一、1（2）并為該函數添加說明，先把它做為普通的工作簿保存（我們后面還會繼續用它）再另存為XLA，然后感受一下它的效果吧。對已經成為XLA

7、文件的函數說明的添加及修改并不需要總是手工先還原為XLS文件，我們稍后就會提到MacroOptions方法。在此之前，先來說一下與加載宏相關的Workbook事件：（1）AddinInstall和AddinUninstall。這兩個事件分別發生在加載宏加載和卸載時的，因此使用這兩個事件的工作簿一定是XLA工作簿，你需要記住的是，它們在加載宏作用期內各自只會發生一次，加載宏加載后每次EXCEL的打開和關閉都不會觸發它們。（2）Open和BeforeClose。和上兩個事件不同，這兩個事件在加載宏作用期內會被多次觸發，除了分別發生在AddinInstall后和AddinUninstall前之外，每

8、次打開和關閉EXCEL時都會觸發它們。清楚了上述特性，你就應該知道如何合理安排加載宏的初始化代碼。2為函數分類默認情況下，當用戶使用“插入函數”時，加載宏函數會被分類放在“用戶定義”類別中。哦，沒有個性！有的朋友甚至還會有被歧視的感覺呢，怎樣才能和EXCEL自身的函數一樣被放在“財務”類呢？我們會想到MacroOptions方法：Application.MacroOptions Macro:="dx", Category:=1不熟悉這個方法不要緊，看完后面的例子你再去查看一下它的幫助，很快就會掌握的。現在的問題，上面的語句放在哪兒呢？首先把它放在AddinInstall事件

9、中看看，你會發現，在第一次加載時，目的確實達到了，但當你關閉EXCEL重新打開時，卻發現它又回到了“用戶定義”中，看來需要放在Open事件中：Private Sub Workbook_Open() ThisWorkbook.IsAddin = False Application.MacroOptions Macro:="dx", Category:=1 ThisWorkbook.IsAddin = True ThisWorkbook.Saved = TrueEnd Sub你會發現，除了我們需要的，又增加了3條語句，這是因為加載宏文件與普通EXCEL文件除了后綴形式上的區別外

10、，一個重要的內在區別就是IsAddin屬性，該屬性為True 說明是加載宏工作簿，MacroOptions方法對宏的設置不能在隱藏的工作簿中進行，需要先將還原為普通工作簿，加入宏設置后再改回。這兩次改變不會觸發AddinInstall和AddinUninstall事件，但EXCEL會記住普通EXCEL文件的修改，因此在完成設置后，還需要加一句ThisWorkbook.Saved = True以使EXCEL忘記這種改變。下面列出了EXCEL內部分類對應的Category參數的整數。1：財務 2：日期與時間 3：數學與三角函數4：統計 5：查找與引用 6：數據庫7：文本 8：邏輯 9：信息如果你不

11、想記住這些整數的含義，你可以直接寫成：Application.MacroOptions Macro:="dx", Category:= "財務"那么是否可以為加載宏函數增加一個新類別呢？很簡單！只需要將上面的Category參數改變一下就可以了：Application.MacroOptions Macro:="dx", Category:= "財務擴展函數"上句會在“插入函數”對話框中增加一個新類別“財務擴展函數”，并把dx函數放入其中。MacroOptions方法還有其它一些參數，可以幫助我們，包括為函數添加說明

12、，下面語句在分類的同時會為函數增加說明。Application.MacroOptions Macro:="dx", Description:="金額小寫轉換為大寫" & vbCr & "參數N：要轉換的金額。", Category:="財務擴展函數"讓我們再次回到Open事件中，看看這3句代碼： ThisWorkbook.IsAddin = False ThisWorkbook.IsAddin = True ThisWorkbook.Saved = True在加載宏文件中使用類似在無耐情況下才采用的

13、變通代碼，達到的目的僅僅是一般人并不注意的函數分類，我想我已經把你引入歧途，真的很抱歉。我該如何糾正呢？我無意保留一個沒有多大實用價值的技巧，我更希望由你說出來三、處理加載宏過程加載宏的另一個重用功能就是提供擴展的操作，這通常是寫在模塊中的Sub過程。設計加載宏過程應注意兩個方面，一是代碼實體，二是提供適當的接口方式以便用戶可以使用這些操作。在代碼實體的設計上，加載宏代碼與其它VBA代碼看上去也許沒有太多的差別，但它要求設計者更為密切地注意加載宏的運行環境，對象成員的使用也更應規范和嚴謹。比如，喜歡混用ThisWorkbook和ActiveWorkbook的朋友要好好看看幫助文檔中二者的差別了

14、。現在請在模塊中粘貼下面的代碼：Sub HVCenter()' 這段代碼的含義很簡單，讓選定區域文字水平垂直居中 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End WithEnd Sub在接口處理上，一般有3種處理方式。1快捷鍵為過程設置快捷鍵可以在另存為加載宏文件前在“工具”菜單上單擊“宏”-“宏”，在“宏”對話框中，單擊“選項”按鈕完成。也可使用前面介紹過的MacroOptions方法設置。Application.MacroOptions Macro:="&qu

15、ot;, HasShortcutKey:=True, ShortcutKey:="A"2菜單通過在系統菜單上增加菜單項可以將操作提供給使用者，加載宏是對EXCEL基本功能的擴充，一般不提倡使用自定義菜單來代替系統菜單。3工具欄可以使用EXCEL現有的工具欄上增加按鈕也可以通過新建工具欄來完成。下面代碼將前面的過程關聯到新建的菜單項和工具欄上。Private Sub Workbook_AddinInstall() On Error Resume Next '新建菜單 With Application.CommandBars(1).Controls.Add(Type:=

16、msoControlPopup) .Caption = "測試(&T)" With .Controls.Add(Type:=msoControlButton) .Caption = "居中" .OnAction = "HVCenter" End With End With '新建工具欄 With Application.CommandBars.Add(Name:="myCmdbar") .Position = msoBarTop With .Controls.Add .FaceId = 352 .C

17、aption = "居中" .OnAction = "HVCenter" End With .Visible = True End WithEnd Sub由于工具欄和菜單生成后不會隨EXCEL的關閉而消失，因此創建工具欄和菜單的代碼一般應寫在AddinInstall事件中，并且在加載宏卸載時隨之卸載。下面代碼卸載前面創建的工具欄和菜單。Private Sub Workbook_AddinUninstall() On Error Resume Next Dim ctl As CommandBarControl '卸載工具欄和菜單 Applicati

18、on.CommandBars("myCmdbar").Delete For Each ctl In Application.CommandBars(1).Controls If ctl.Caption = "測試(&T)" Then ctl.Delete Next ctlEnd Sub四、處理加載宏事件原則上講，加載宏事件應對所有打開工作簿發生的事件進行處理。加載宏事件的設計相對函數和過程的設計而言通常要復雜得多，它要求設計者具有一定的類知識和良好的代碼組織能力。具體設計主要應解決兩方面問題，一是事件代碼本身，二是將預作的“事件代碼”和對象關聯。

19、我們仍通過實例，繼續前面的設計來介紹。任務：讓加載宏實現當用戶在活動工作簿的活動工作表選擇某一區域時，狀態欄顯示該區域的范圍。1設計事件代碼（1）分析要用的對象和事件不難知道，本例需要Workbook對象的SheetSelectionChange的事件。首先使用WithEvents關鍵字定義一個Workbook對象。Dim WithEvents wkb As Workbook注意，WithEvents關鍵字不能在標準模塊中使用，因此，代碼一般寫在Thisworkbook中。（2）書寫事件代碼Private Sub wkb_SheetSelectionChange(ByVal Sh As Obj

20、ect, ByVal Target As Range) Application.StatusBar = "你選擇的區域:" & Replace(Target.Address, "$", "")End Sub2關聯對象（1）分析需要關聯的所有情況寫好了上面的代碼，如何將它與活動工作簿關聯呢？首先應該分析可能進行關聯的時機。以本例，大體我們將需要（改變）關聯，即活動工作簿發生變化的情況列示如下：a.當新建工作簿時，關聯對象為新建的工作簿b.當新打開工作簿時，關聯對象為新打開工作簿c.當在兩個工作簿之間切換時，關聯對象為新切換到的工

21、作簿（2）完成關聯代碼上面的分析知道，需要用到Application級的事件。定義Application對象，寫入相應的事件，然后在Open事件中將它關聯到當前的Application即可。匯合前面的代碼如下：Dim WithEvents app As ApplicationDim WithEvents wkb As WorkbookPrivate Sub app_NewWorkbook(ByVal Wb As Workbook) Set wkb = WbEnd SubPrivate Sub app_WorkbookOpen(ByVal Wb As Workbook) Set wkb = Wb

22、End SubPrivate Sub app_WorkbookActivate(ByVal Wb As Workbook) Set wkb = WbEnd SubPrivate Sub wkb_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.StatusBar = "你選擇的區域:" & Replace(Target.Address, "$", "")End SubPrivate Sub Workbook_Open() '關聯到Application Set app = ApplicationEnd Sub還有一個問題，上面處理的都是加載宏加載后的關聯，當加載宏加載時，調用加載宏的工作簿呢？你是否認為可以在上面的Open事件中加入：Set wkb =