加載宏制作攻略

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

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

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

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

5、.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 = 零, , dx & 元整)dx = Replace(Replace(Replace(Replace(dx, 零元零角, ), 零元, ), 零角, 零), -, 負(fù))End Function如果覺得必要，你可以設(shè)置文件屬性（見一、1（2）并為該函數(shù)添加說明，先把它做為普通的工作簿

6、保存（我們后面還會(huì)繼續(xù)用它）再另存為XLA，然后感受一下它的效果吧。對(duì)已經(jīng)成為XLA文件的函數(shù)說明的添加及修改并不需要總是手工先還原為XLS文件，我們稍后就會(huì)提到MacroOptions方法。在此之前，先來說一下與加載宏相關(guān)的Workbook事件：（1）AddinInstall和AddinUninstall。這兩個(gè)事件分別發(fā)生在加載宏加載和卸載時(shí)的，因此使用這兩個(gè)事件的工作簿一定是XLA工作簿，你需要記住的是，它們?cè)诩虞d宏作用期內(nèi)各自只會(huì)發(fā)生一次，加載宏加載后每次EXCEL的打開和關(guān)閉都不會(huì)觸發(fā)它們。（2）Open和BeforeClose。和上兩個(gè)事件不同，這兩個(gè)事件在加載宏作用期內(nèi)會(huì)被多次觸

7、發(fā)，除了分別發(fā)生在AddinInstall后和AddinUninstall前之外，每次打開和關(guān)閉EXCEL時(shí)都會(huì)觸發(fā)它們。清楚了上述特性，你就應(yīng)該知道如何合理安排加載宏的初始化代碼。2為函數(shù)分類默認(rèn)情況下，當(dāng)用戶使用“插入函數(shù)”時(shí)，加載宏函數(shù)會(huì)被分類放在“用戶定義”類別中。哦，沒有個(gè)性！有的朋友甚至還會(huì)有被歧視的感覺呢，怎樣才能和EXCEL自身的函數(shù)一樣被放在“財(cái)務(wù)”類呢？我們會(huì)想到MacroOptions方法：Application.MacroOptions Macro:=dx, Category:=1不熟悉這個(gè)方法不要緊，看完后面的例子你再去查看一下它的幫助，很快就會(huì)掌握的。現(xiàn)在的問題，上

8、面的語句放在哪兒呢？首先把它放在AddinInstall事件中看看，你會(huì)發(fā)現(xiàn)，在第一次加載時(shí)，目的確實(shí)達(dá)到了，但當(dāng)你關(guān)閉EXCEL重新打開時(shí)，卻發(fā)現(xiàn)它又回到了“用戶定義”中，看來需要放在Open事件中：Private Sub Workbook_Open() ThisWorkbook.IsAddin = False Application.MacroOptions Macro:=dx, Category:=1 ThisWorkbook.IsAddin = True ThisWorkbook.Saved = TrueEnd Sub你會(huì)發(fā)現(xiàn)，除了我們需要的，又增加了3條語句，這是因?yàn)榧虞d宏文件與普通

9、EXCEL文件除了后綴形式上的區(qū)別外，一個(gè)重要的內(nèi)在區(qū)別就是IsAddin屬性，該屬性為True 說明是加載宏工作簿，MacroOptions方法對(duì)宏的設(shè)置不能在隱藏的工作簿中進(jìn)行，需要先將還原為普通工作簿，加入宏設(shè)置后再改回。這兩次改變不會(huì)觸發(fā)AddinInstall和AddinUninstall事件，但EXCEL會(huì)記住普通EXCEL文件的修改，因此在完成設(shè)置后，還需要加一句ThisWorkbook.Saved = True以使EXCEL忘記這種改變。下面列出了EXCEL內(nèi)部分類對(duì)應(yīng)的Category參數(shù)的整數(shù)。1：財(cái)務(wù) 2：日期與時(shí)間 3：數(shù)學(xué)與三角函數(shù)4：統(tǒng)計(jì) 5：查找與引用 6：數(shù)據(jù)庫

10、7：文本 8：邏輯 9：信息如果你不想記住這些整數(shù)的含義，你可以直接寫成：Application.MacroOptions Macro:=dx, Category:= 財(cái)務(wù)那么是否可以為加載宏函數(shù)增加一個(gè)新類別呢？很簡(jiǎn)單！只需要將上面的Category參數(shù)改變一下就可以了：Application.MacroOptions Macro:=dx, Category:= 財(cái)務(wù)擴(kuò)展函數(shù)上句會(huì)在“插入函數(shù)”對(duì)話框中增加一個(gè)新類別“財(cái)務(wù)擴(kuò)展函數(shù)”，并把dx函數(shù)放入其中。MacroOptions方法還有其它一些參數(shù)，可以幫助我們，包括為函數(shù)添加說明，下面語句在分類的同時(shí)會(huì)為函數(shù)增加說明。Applicatio

11、n.MacroOptions Macro:=dx, Description:=金額小寫轉(zhuǎn)換為大寫 & vbCr & 參數(shù)N：要轉(zhuǎn)換的金額。, Category:=財(cái)務(wù)擴(kuò)展函數(shù)讓我們?cè)俅位氐絆pen事件中，看看這3句代碼： ThisWorkbook.IsAddin = False ThisWorkbook.IsAddin = True ThisWorkbook.Saved = True在加載宏文件中使用類似在無耐情況下才采用的變通代碼，達(dá)到的目的僅僅是一般人并不注意的函數(shù)分類，我想我已經(jīng)把你引入歧途，真的很抱歉。我該如何糾正呢？我無意保留一個(gè)沒有多大實(shí)用價(jià)值的技巧，我更希望由你說出來三、處理加載

12、宏過程加載宏的另一個(gè)重用功能就是提供擴(kuò)展的操作，這通常是寫在模塊中的Sub過程。設(shè)計(jì)加載宏過程應(yīng)注意兩個(gè)方面，一是代碼實(shí)體，二是提供適當(dāng)?shù)慕涌诜绞揭员阌脩艨梢允褂眠@些操作。在代碼實(shí)體的設(shè)計(jì)上，加載宏代碼與其它VBA代碼看上去也許沒有太多的差別，但它要求設(shè)計(jì)者更為密切地注意加載宏的運(yùn)行環(huán)境，對(duì)象成員的使用也更應(yīng)規(guī)范和嚴(yán)謹(jǐn)。比如，喜歡混用ThisWorkbook和ActiveWorkbook的朋友要好好看看幫助文檔中二者的差別了?，F(xiàn)在請(qǐng)?jiān)谀K中粘貼下面的代碼：Sub HVCenter() 這段代碼的含義很簡(jiǎn)單，讓選定區(qū)域文字水平垂直居中 With Selection .HorizontalAlig

13、nment = xlCenter .VerticalAlignment = xlCenter End WithEnd Sub在接口處理上，一般有3種處理方式。1快捷鍵為過程設(shè)置快捷鍵可以在另存為加載宏文件前在“工具”菜單上單擊“宏”-“宏”，在“宏”對(duì)話框中，單擊“選項(xiàng)”按鈕完成。也可使用前面介紹過的MacroOptions方法設(shè)置。Application.MacroOptions Macro:=, HasShortcutKey:=True, ShortcutKey:=A2菜單通過在系統(tǒng)菜單上增加菜單項(xiàng)可以將操作提供給使用者，加載宏是對(duì)EXCEL基本功能的擴(kuò)充，一般不提倡使用自定義菜單來代替系

14、統(tǒng)菜單。3工具欄可以使用EXCEL現(xiàn)有的工具欄上增加按鈕也可以通過新建工具欄來完成。下面代碼將前面的過程關(guān)聯(lián)到新建的菜單項(xiàng)和工具欄上。Private Sub Workbook_AddinInstall() On Error Resume Next 新建菜單 With Application.CommandBars(1).Controls.Add(Type:=msoControlPopup) .Caption = 測(cè)試(&T) With .Controls.Add(Type:=msoControlButton) .Caption = 居中 .OnAction = HVCenter End Wit

15、h End With 新建工具欄 With Application.CommandBars.Add(Name:=myCmdbar) .Position = msoBarTop With .Controls.Add .FaceId = 352 .Caption = 居中 .OnAction = HVCenter End With .Visible = True End WithEnd Sub由于工具欄和菜單生成后不會(huì)隨EXCEL的關(guān)閉而消失，因此創(chuàng)建工具欄和菜單的代碼一般應(yīng)寫在AddinInstall事件中，并且在加載宏卸載時(shí)隨之卸載。下面代碼卸載前面創(chuàng)建的工具欄和菜單。Private Sub

16、Workbook_AddinUninstall() On Error Resume Next Dim ctl As CommandBarControl 卸載工具欄和菜單 Application.CommandBars(myCmdbar).Delete For Each ctl In Application.CommandBars(1).Controls If ctl.Caption = 測(cè)試(&T) Then ctl.Delete Next ctlEnd Sub四、處理加載宏事件原則上講，加載宏事件應(yīng)對(duì)所有打開工作簿發(fā)生的事件進(jìn)行處理。加載宏事件的設(shè)計(jì)相對(duì)函數(shù)和過程的設(shè)計(jì)而言通常要復(fù)雜得多，它

17、要求設(shè)計(jì)者具有一定的類知識(shí)和良好的代碼組織能力。具體設(shè)計(jì)主要應(yīng)解決兩方面問題，一是事件代碼本身，二是將預(yù)作的“事件代碼”和對(duì)象關(guān)聯(lián)。我們?nèi)酝ㄟ^實(shí)例，繼續(xù)前面的設(shè)計(jì)來介紹。任務(wù)：讓加載宏實(shí)現(xiàn)當(dāng)用戶在活動(dòng)工作簿的活動(dòng)工作表選擇某一區(qū)域時(shí)，狀態(tài)欄顯示該區(qū)域的范圍。1設(shè)計(jì)事件代碼（1）分析要用的對(duì)象和事件不難知道，本例需要Workbook對(duì)象的SheetSelectionChange的事件。首先使用WithEvents關(guān)鍵字定義一個(gè)Workbook對(duì)象。Dim WithEvents wkb As Workbook注意，WithEvents關(guān)鍵字不能在標(biāo)準(zhǔn)模塊中使用，因此，代碼一般寫在Thisworkb

18、ook中。（2）書寫事件代碼Private Sub wkb_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.StatusBar = 你選擇的區(qū)域: & Replace(Target.Address, $, )End Sub2關(guān)聯(lián)對(duì)象（1）分析需要關(guān)聯(lián)的所有情況寫好了上面的代碼，如何將它與活動(dòng)工作簿關(guān)聯(lián)呢？首先應(yīng)該分析可能進(jìn)行關(guān)聯(lián)的時(shí)機(jī)。以本例，大體我們將需要（改變）關(guān)聯(lián)，即活動(dòng)工作簿發(fā)生變化的情況列示如下：a.當(dāng)新建工作簿時(shí)，關(guān)聯(lián)對(duì)象為新建的工作簿b.當(dāng)新打開工作簿時(shí)，關(guān)聯(lián)對(duì)象為新打開工作簿

19、c.當(dāng)在兩個(gè)工作簿之間切換時(shí)，關(guān)聯(lián)對(duì)象為新切換到的工作簿（2）完成關(guān)聯(lián)代碼上面的分析知道，需要用到Application級(jí)的事件。定義Application對(duì)象，寫入相應(yīng)的事件，然后在Open事件中將它關(guān)聯(lián)到當(dāng)前的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

20、 As Workbook) Set wkb = WbEnd 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 = 你選擇的區(qū)域: & Replace(Target.Address, $, )End SubPrivate Sub Workbook_Open() 關(guān)聯(lián)到Application Set app = ApplicationEnd Sub還有一個(gè)問題，上面處理的都是加載宏加載后的關(guān)聯(lián)，當(dāng)加載宏加載時(shí)，調(diào)用加載宏的工作簿呢？你是否認(rèn)為可以在上面的Open事件中加入：Set wkb = ActiveWorkbook就可以了呢？答案是否定的。正確的做法是使用屬性過程和Auto_Open，首先在ThisWorkb