Skip to main content

如何在 Excel 的所有工作簿中保存並使用您的 VBA 宏?

Author: Zhoumandy Last Modified: 2025-07-21

在許多情況下,您可能需要在不同的 Excel 工作簿中重複使用相同的 VBA 宏來執行自動化任務,例如自動進行重複計算、數據格式化或自訂函數(如將數字轉換為文字)。常見的挑戰是,默認情況下,宏僅存儲在創建它們的工作簿中,這意味著您無法輕鬆地在新文檔中訪問或重用它們。然而,Excel 提供了幾種靈活的方法,可以讓 VBA 宏全局可用,從而避免每次開始新工作簿時都重新複製代碼。本教程提供了多種方法的詳細說明,以確保您的 VBA 宏能夠跨所有工作簿輕鬆訪問,提高您的生產力和工作效率。

A screenshot showing the Add-ins dialog in Excel

在所有工作簿中保存和使用 VBA 代碼
個人宏工作簿方法


在所有工作簿中保存和使用 VBA 代碼

例如,假設您希望能夠使用自定義的 VBA 代碼將數字轉換為其對應的英文單詞,並且確保此功能不管在哪個工作簿中都能隨時可用。通過正確的方法,您可以保存您的 VBA 模塊,以便在需要時隨時重用。這對於您希望每次都能訪問的自定義函數或自動化流程特別有幫助,而無需在多個文件中重複代碼。

要做到這一點,您可以將 VBA 代碼打包成一個自定義的 Excel 加載項。這個加載項可以在 Excel 中啟用,並將您的自定義功能作為全局可用的功能展示出來。

請按照以下步驟操作:

1. 在 Excel 中按 Alt + F11 打開「Microsoft Visual Basic for Applications」窗口。

2. 在 VBA 編輯器中,點擊 插入 > 模塊,並將以下宏粘貼到新創建的模塊窗口中。

VBA 代碼:將數字轉換為文字

Function NumberstoWords(ByVal MyNumber)
'Update by ExtendofficeDim xStr As StringDim xFNum As IntegerDim xStrPointDim xStrNumberDim xPoint As StringDim xNumber As StringDim xP() As VariantDim xDPDim xCnt As IntegerDim xResult, xT As StringDim xLen As IntegerOn Error Resume NextxP = Array("", "Thousand ", "Million ", "Billion ", "Trillion ", " ", " ", " ", " ")
xNumber = Trim(Str(MyNumber))
xDP = InStr(xNumber, ".")
xPoint = ""
xStrNumber = ""
If xDP >0 ThenxPoint = " point "
xStr = Mid(xNumber, xDP +1)
xStrPoint = Left(xStr, Len(xNumber) - xDP)
For xFNum =1 To Len(xStrPoint)
xStr = Mid(xStrPoint, xFNum,1)
xPoint = xPoint & GetDigits(xStr) & " "
Next xFNumxNumber = Trim(Left(xNumber, xDP -1))
End IfxCnt =0xResult = ""
xT = ""
xLen =0xLen = Int(Len(Str(xNumber)) /3)
If (Len(Str(xNumber)) Mod3) =0 Then xLen = xLen -1Do While xNumber <> ""
If xLen = xCnt ThenxT = GetHundredsDigits(Right(xNumber,3), False)
ElseIf xCnt =0 ThenxT = GetHundredsDigits(Right(xNumber,3), True)
ElsexT = GetHundredsDigits(Right(xNumber,3), False)
End IfEnd IfIf xT <> "" ThenxResult = xT & xP(xCnt) & xResultEnd IfIf Len(xNumber) >3 ThenxNumber = Left(xNumber, Len(xNumber) -3)
ElsexNumber = ""
End IfxCnt = xCnt +1LoopxResult = xResult & xPointNumberstoWords = xResultEnd FunctionFunction GetHundredsDigits(xHDgt, xB As Boolean)
Dim xRStr As StringDim xStrNum As StringDim xStr As StringDim xI As IntegerDim xBB As BooleanxStrNum = xHDgtxRStr = ""
On Error Resume NextxBB = TrueIf Val(xStrNum) =0 Then Exit FunctionxStrNum = Right("000" & xStrNum,3)
xStr = Mid(xStrNum,1,1)
If xStr <> "0" ThenxRStr = GetDigits(Mid(xStrNum,1,1)) & "Hundred "
ElseIf xB ThenxRStr = "and "
xBB = FalseElsexRStr = " "
xBB = FalseEnd IfEnd IfIf Mid(xStrNum,2,2) <> "00" ThenxRStr = xRStr & GetTenDigits(Mid(xStrNum,2,2), xBB)
End IfGetHundredsDigits = xRStrEnd FunctionFunction GetTenDigits(xTDgt, xB As Boolean)
Dim xStr As StringDim xI As IntegerDim xArr_1() As VariantDim xArr_2() As VariantDim xT As BooleanxArr_1 = Array("Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
xArr_2 = Array("", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
xStr = ""
xT = TrueOn Error Resume NextIf Val(Left(xTDgt,1)) =1 ThenxI = Val(Right(xTDgt,1))
If xB Then xStr = "and "
xStr = xStr & xArr_1(xI)
ElsexI = Val(Left(xTDgt,1))
If Val(Left(xTDgt,1)) >1 ThenIf xB Then xStr = "and "
xStr = xStr & xArr_2(Val(Left(xTDgt,1)))
xT = FalseEnd IfIf xStr = "" ThenIf xB ThenxStr = "and "
End IfEnd IfIf Right(xTDgt,1) <> "0" ThenxStr = xStr & GetDigits(Right(xTDgt,1))
End IfEnd IfGetTenDigits = xStrEnd FunctionFunction GetDigits(xDgt)
Dim xStr As StringDim xArr_1() As VariantxArr_1 = Array("Zero ", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ")
xStr = ""
On Error Resume NextxStr = xArr_1(Val(xDgt))
GetDigits = xStrEnd Function

3. 現在,點擊窗口左上角的「保存」圖標,或者直接按 Ctrl + S 以打開「另存為」對話框。
A screenshot showing the Save option in the VBA window

4. 在「另存為」窗口中,在「文件名」字段中輸入所需的文件名。在「保存類型」下拉菜單中,務必選擇 Excel 加載項 (*.xlam).
A screenshot showing the Save As dialog box with the selection of Excel Add-in (*.xlam) as the save type

5. 點擊「保存」按鈕,將您的工作簿保存為 Excel 加載項文件。這樣就創建了一個可重用的加載項,可以隨時在任何工作簿中啟用。
A screenshot showing the workbook saved as an Excel Add-in

6. 保存後,返回 Excel 並關閉剛才轉換為加載項的工作簿。

7. 打開一個新的或現有的工作簿,在其中您想使用該宏。在適當的單元格中輸入自定義公式(例如,在 B2 中):

=NumberstoWords(A2)
注意:在此階段可能會出現「#NAME?」錯誤。這是正常的,因為包含宏功能的加載項尚未被全局加載到 Excel 中。按照以下步驟啟用您的宏,使其在所有工作簿中可用。
A screenshot of the #NAME? error before applying the saved VBA macro

8. 轉到 開發工具 選項卡,然後點擊 Excel 加載項 按鈕(位於「加載項」組中)。
A screenshot showing the Add-ins option under the Developer tab in Excel

9. 在彈出的加載項對話框中,選擇 瀏覽.
A screenshot of the Add-ins dialog box in Excel

10. 找到並選擇您之前保存的加載項文件,然後點擊 確定.
A screenshot showing the selection of a custom Add-in file in Excel

11. 您的自定義加載項(如「將數字轉換為文字加載項」)現在應該會出現在加載項列表中。確保它已被勾選,然後點擊 確定 以啟用它。
A screenshot showing the custom add-in the Add-ins dialog box in Excel

12. 現在,在目標單元格(例如 B2)中再次輸入自定義函數,然後按 Enter。您應該會看到公式返回該數字對應的正確英文單詞。

=NumberstoWords(A2)

13. 若要快速將轉換公式應用於多個數字,拖動單元格的自動填充手柄向下複製函數到其他單元格。

A screenshot showing the final result of the converted numbers to words

提示與注意事項:

  • 將您的宏保存為加載項,可以讓您在所有工作簿中使用相同的自定義函數、代碼或自動化功能,節省時間並提高一致性。
  • 如果 Excel 關閉或稍後禁用了加載項,加載項中的函數可能會暫時顯示「#NAME?」,直到加載項再次加載。為避免混淆,請確保在需要時始終在加載項管理器中啟用該加載項。
  • 某些用戶可能默認看不到開發工具選項卡。要啟用它,右鍵點擊功能區,選擇「自定義功能區」,然後勾選「開發工具」選項。
  • 最好將加載項存儲在永久文件夾中,以免文件移動或重命名後找不到引用。

如果您更喜歡手動運行代碼,也是可行的,並且在調試或臨時使用時有時很有幫助:

  1. 您可以將宏分配給快速訪問工具欄,以便在任何可見的工作簿中一鍵執行。要做到這一點,右鍵點擊快速訪問工具欄,選擇「自定義快速訪問工具欄」,然後添加您的宏。
    A screenshot showing how to add the VBA macro to the Quick Access Toolbar
  2. 您也可以按 Alt + F11 打開 VBA 編輯器,手動選擇您的宏,然後按 F5 鍵根據需要運行代碼。

優點: 此解決方案讓您可以創建和共享豐富的、可重用的宏功能,只要加載項啟用,這些功能就會一直可用。
缺點: 用戶必須記住加載該加載項;如果共享工作簿,還需要分享加載項文件和功能詳情。此外,加載項無法在 Excel Online 中使用。


個人宏工作簿方法

另一種非常實用的方法是使用個人宏工作簿(PERSONAL.XLSB),確保您最喜歡或最常用的宏在每次 Excel 會話中都可用,無論哪個工作簿處於打開狀態。這是一個特殊的隱藏 Excel 文件,每次啟動 Excel 時都會自動加載,允許存儲在其中的任何宏都可以在所有打開的工作簿中訪問。

適用場景:適用於個人自動化、例行格式化腳本或不需要作為正式 Excel 加載項共享的工具函數。存儲在 PERSONAL.XLSB 中的宏在您的電腦上不論打開哪個文件都可以使用。

優點: 宏在本地 Excel 個人配置文件中全局可用,無需安裝加載項或額外文件。
缺點: 以這種方式存儲的宏只能在存在 PERSONAL.XLSB 文件的電腦和賬戶上使用。與他人共享需要手動導出和導入模塊。

  • 要使用此方法,首先需要記錄或創建一個宏,並確保它保存到個人宏工作簿中。

請按照以下步驟操作:

  1. 打開 Excel。在 檢視 選項卡中,點擊 ,然後選擇 錄製宏
  2. 在對話框中,於「存儲宏於」部分選擇 個人宏工作簿。完成錄製(如果不需要,可以立即停止)。
  3. Alt + F11 進入 VBA 編輯器,您會看到 PERSONAL.XLSB 專案。在這裡,插入一個新模塊或粘貼您想要的宏代碼。
  4. 保存您的更改。Excel 會自動在其啟動文件夾中創建並維護 PERSONAL.XLSB 工作簿。
  5. 存儲在 PERSONAL.XLSB 中的宏可以通過 對話框(Alt + F8)運行,分配到功能區或工具欄按鈕,或從 VBA 中調用。

故障排除與維護:如果 PERSONAL.XLSB 中的宏不可用,請檢查 Excel 是否在安全模式下啟動,或者宏安全性設置是否設置為「禁用所有宏」。此外,PERSONAL.XLSB 默認為隱藏;如果未保存或意外刪除,您可能需要重新錄製一個宏來重新生成它。

提示: 定期備份您的 PERSONAL.XLSB 文件。您可以在系統配置文件夾中找到它,通常是:
C:\Users\[YourUserName]\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

其他操作(文章)

列出 Excel 中所有加載項的 VBA 代碼
在 Excel 中,您可能添加了一些加載項來更好地處理數據。我們知道,可以通過選項窗口查看所有加載項,但是否有辦法將所有加載項列在一個工作表中呢?本教程提供了一段 VBA 代碼,用於列出 Excel 中的所有加載項。

如何在打開或關閉工作簿時運行 VBA 宏?
在本文中,我將告訴您如何在每次打開或關閉工作簿時運行 VBA 代碼。

如何保護 / 鎖定 Excel 中的 VBA 代碼?
正如您可以使用密碼保護工作簿和工作表一樣,您也可以設置密碼來保護 Excel 中的宏。

如何在 Excel 中運行 VBA 宏後設置時間延遲?
在某些情況下,您可能需要設置計時器延遲來觸發 Excel 中的 VBA 宏。例如,當點擊運行指定宏時,它將在 10 秒後生效。本文將向您展示實現這一功能的方法。

最佳辦公效率工具

🤖 Kutools AI 助手:基於智能執行方式革新數據分析:智能執行   |  生成代碼  |  創建自訂公式  |  分析數據並生成圖表  |  調用 Kutools 函數
熱門功能查找、標記重複值或識別重複項   |  刪除空行   |  合併列或單元格而不丟失數據   |   四捨五入無需公式 ...
高級 LOOKUP多條件 VLookup    多值 VLookup  |   多表查找   |   模糊查找 ....
高級下拉列表快速創建下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
列管理器添加特定數量的列  |  移動列  |  切換隱藏列的可見狀態  |  比較區域和列 ...
特色功能網格聚焦   |  設計檢視   |   增強編輯欄    工作簿與工作表管理器   |  資源庫(自動文本)   |  日期提取器   |  合併資料   |  加密/解密儲存格    按列表發送電子郵件   |  超級篩選   |   特殊篩選(篩選粗體/斜體/刪除線...) ...
頂級 15 種工具集12 個文本工具添加文本刪除特定字符、...)   |   50+ 圖表 類型甘特圖、...)   |   40+ 實用 公式基於生日計算年齡、...)   |   19 個插入工具插入QR碼根據路徑插入圖片、...)   |   12 個轉換工具金額轉大寫匯率轉換、...)   |   7 個合併與分割工具高級合併行分割儲存格、...)   |   ... 還有更多

使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。  點擊這裡獲取您最需要的功能...


Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
  • 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
  • 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!