如何在 Excel 的所有工作簿中保存並使用您的 VBA 宏?
在許多情況下,您可能需要在不同的 Excel 工作簿中重複使用相同的 VBA 宏來執行任務,例如自動化重複計算、數據格式化或自訂函數(如將數字轉換為文字)。一個常見的挑戰是,默認情況下,宏僅存儲在創建它們的工作簿中,這意味著您無法輕鬆地在新文檔中訪問或重用它們。然而,Excel 提供了幾種靈活的方法,讓 VBA 宏可以全局使用,消除了每次啟動新工作簿時重新複製代碼的需要。本教程提供了各種方法的全面指導,以確保您的 VBA 宏能夠輕鬆地在所有工作簿中訪問,從而提高您的生產力和工作流程效率。
例如,假設您希望能夠使用自訂的 VBA 代碼將數字轉換為對應的英文單詞,並確保無論在哪個工作簿中操作,此功能始終可用。通過正確的方法,您可以保存 VBA 模組,以便在 Excel 中隨時重用它們。這對於您希望每次都可訪問的自定義函數或自動化特別有幫助,無需在多個文件中重複代碼。
要做到這一點,您可以將 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 以打開 "另存為" 對話框。
4. 在 "另存為" 視窗中,在 "文件名" 欄位中輸入所需的文件名。在 "保存類型" 下拉選單中,務必選擇 Excel 加載項 (*.xlam).
5. 點擊 "保存" 按鈕,將您的工作簿保存為 Excel 加載項文件。這樣會創建一個可重用的加載項,任何工作簿都可以隨時啟用。
6. 保存後,返回 Excel 並關閉剛才轉換為加載項的工作簿。
7. 打開一個新或現有的工作簿,您希望在其中使用您的宏。在適當的單元格中輸入自定義公式(例如,在 B2 中):
=NumberstoWords(A2)

8. 轉到 開發工具 標籤,然後點擊 Excel 加載項 按鈕(位於 "加載項" 組中)。
9. 在彈出的 "加載項" 對話框中,選擇 瀏覽.
10. 找到並選擇之前保存的加載項文件,然後點擊 確定.
11. 您的自定義加載項,例如 "Convert Number To Words Add-in",現在應該出現在加載項列表中。確保它被勾選,然後點擊 確定 以啟用它。
12. 現在,在目標單元格(例如 B2)中再次輸入自定義函數,然後按 Enter。您應該會看到該公式返回數字對應的正確英文單詞。
=NumberstoWords(A2)
13. 若要快速將轉換公式應用於多個數字,拖動單元格的自動填充手柄向下複製函數到其他單元格。

提示與注意:
- 將您的宏保存為加載項,使您能夠跨所有工作簿使用相同的自定義函數、代碼或自動化功能,節省時間並提高一致性。
- 如果 Excel 關閉或稍後禁用了加載項,則來自該加載項的功能可能會暫時顯示 "#NAME?",直到再次加載該加載項。為避免混淆,請確保在需要時始終在加載項管理器中啟用加載項。
- 一些用戶可能默認看不到 "開發工具" 標籤。要啟用它,請右鍵點擊功能區,選擇 "自定義功能區",然後勾選 "開發工具" 選項。
- 最好將加載項存放在永久文件夾中,以避免因文件移動或重命名而導致引用遺失。
如果您更喜歡手動運行代碼,這也是可行的,並且在調試或臨時使用時有時很有幫助:
- 您可以將宏分配到快速訪問工具欄,以便在任何可見的工作簿中一鍵執行。要做到這一點,右鍵點擊快速訪問工具欄,選擇 "自定義快速訪問工具欄",然後添加您的宏。
- 您也可以按下 Alt + F11 打開 VBA 編輯器,手動選擇您的宏,然後按 F5 鍵根據需要運行代碼。
優點: 這種解決方案讓您可以創建和分享豐富的、可重用的宏功能,只要加載項啟用,它就能一直正常工作。
缺點: 用戶必須記住加載加載項,如果共享工作簿,還需要共享加載項文件和函數詳細信息。此外,加載項不能在 Excel Online 中使用。
另一個非常實用的方法是,確保您最喜歡或最常用的宏在每個 Excel 會話中都準備就緒,無論哪個工作簿打開,都可以使用個人宏工作簿(PERSONAL.XLSB)。這是一個特殊的隱藏 Excel 文件,每次啟動 Excel 時都會自動加載,允許存儲在其內部的任何宏都能夠在所有打開的工作簿中訪問。
適用場景:適用於個人自動化、日常格式化腳本或不需要作為正式 Excel 加載項分享的工具函數。PERSONAL.XLSB 中的宏在您的電腦上無論打開哪個文件都可以使用。
優點: 宏在本地 Excel 配置文件中全局可用,無需安裝加載項或額外文件。
缺點: 以這種方式存儲的宏只能在存在 PERSONAL.XLSB 的電腦和帳戶上使用。與他人共享需要手動導出和導入模組。
- 要使用此方法,您首先需要記錄或創建一個宏,並確保它保存到個人宏工作簿中。
請按照以下步驟操作:
- 打開 Excel。在 檢視 標籤中,點擊 宏,然後選擇 錄製宏。
- 在對話框中,於 "存儲宏於" 下拉選單中選擇 個人宏工作簿。完成錄製(如果不需錄製,可立即停止)。
- 按下 Alt + F11 進入 VBA 編輯器,您將看到 PERSONAL.XLSB 專案。在此處,插入一個新模組或粘貼您所需的宏代碼。
- 保存更改。Excel 會自動在啟動文件夾中創建並維護 PERSONAL.XLSB 工作簿。
- PERSONAL.XLSB 中的宏可以通過 宏 對話框(Alt + F8)運行,分配給功能區或工具欄按鈕,或從 VBA 中調用。
故障排除與維護:如果 PERSONAL.XLSB 中的宏不可用,請檢查 Excel 是否在安全模式下打開,或者宏安全性設置是否設置為 “禁用所有宏”。此外,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 秒後生效。本文將向您展示一種實現它的方法。
最佳 Office 辦公效率工具
🤖 | Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入... | |
高級 LOOKUP:多條件查找|多值查找|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)... | |
前15 大工具集:12 款文本工具(添加文本,刪除特定字符,...)|50+ 種圖表 類型(甘特圖,...)|40+ 實用 公式(基於生日計算年齡,...)|19 款插入工具(插入QR码,按路徑插入圖片,...)|12 款轉換工具(金額轉大寫,匯率轉換,...)|7 款合併和分割工具(高級合併行,分割儲存格,...)| ...以及更多 |
利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...
Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆
- 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
- 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
- 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!