Как сохранить и использовать свои макросы VBA во всех книгах Excel?
Существует множество ситуаций, когда вам может понадобиться многократно использовать один и тот же макрос VBA в разных книгах Excel для таких задач, как автоматизация повторяющихся вычислений, форматирование данных или пользовательские функции, например, преобразование чисел в слова. Общая проблема заключается в том, что по умолчанию макросы хранятся только в книге, где они были созданы, а это значит, что к ним нельзя легко получить доступ или переиспользовать их в новых документах. Однако Excel предлагает несколько гибких методов, чтобы сделать макрос VBA доступным глобально, исключая необходимость перекопировать код каждый раз при создании новой книги. Это руководство предоставляет подробные инструкции для различных подходов, чтобы гарантировать, что ваши макросы VBA будут легко доступны во всех книгах, повышая вашу продуктивность и улучшая рабочий процесс.
Сохранение и использование кода VBA во всех книгах
Метод личной книги макросов
Например, предположим, что вы хотите иметь возможность преобразовать числа в их эквивалент на английском языке с помощью пользовательского кода VBA и обеспечить, чтобы эта функция всегда была доступна независимо от того, в какой книге вы работаете. С правильным подходом вы можете сохранить свои модули VBA так, чтобы они были многоразовыми в любое время, когда они вам нужны в Excel. Это особенно полезно для пользовательских функций или автоматизации, которые вы хотите иметь доступными каждый раз, без дублирования кода в нескольких файлах.
Для этого вы можете упаковать свой код VBA в виде пользовательского дополнения Excel. Это дополнение можно включить в Excel, и оно будет предоставлять ваши пользовательские функциональности как функцию, доступную глобально.
Выполните следующие шаги:
1. Нажмите Alt + F11 в Excel, чтобы открыть окно «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. Ваша пользовательская надстройка, такая как «Преобразовать число в слова», теперь должна появиться в списке надстроек. Убедитесь, что она отмечена, и нажмите ОК чтобы включить её.
12. Теперь снова введите пользовательскую функцию в целевую ячейку (например, в B2) и нажмите Enter. Вы должны увидеть, что формула возвращает правильные английские слова для числа.
=NumberstoWords(A2)
13. Чтобы быстро применить формулу преобразования к нескольким числам, перетащите маркер автозаполнения ячейки вниз, чтобы скопировать функцию в другие ячейки.

Советы и Примечания:
- Сохранение вашего макроса как надстройки позволяет вам использовать одни и те же пользовательские функции, код или автоматизацию во всех ваших книгах, экономя время и улучшая согласованность.
- Если Excel закрыт или надстройка отключена позже, функции из надстройки могут временно показывать «#ИМЯ?» до тех пор, пока надстройка не будет загружена снова. Чтобы избежать путаницы, убедитесь, что надстройка всегда включена в менеджере надстроек, если она нужна.
- Некоторые пользователи могут не видеть вкладку Разработчик по умолчанию. Чтобы включить её, щелкните правой кнопкой мыши ленту, выберите «Настроить ленту» и отметьте параметр «Разработчик».
- Хорошей практикой является хранение надстроек в постоянной папке, чтобы избежать потери ссылок, если файлы перемещаются или переименовываются.
Если вы предпочитаете запускать код вручную, это также возможно и иногда полезно при отладке или для одноразового использования:
- Вы можете назначить макрос на панель быстрого доступа для выполнения одним щелчком в любой видимой книге. Для этого щелкните правой кнопкой мыши панель быстрого доступа, выберите «Настроить панель быстрого доступа», затем добавьте свой макрос.
- Вы также можете нажать 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
Код VBA для вывода всех надстроек в Excel
В Excel вы можете добавить или вставить некоторые надстройки для лучшей обработки данных. Как известно, мы можем перейти в окно Параметры, чтобы просмотреть все надстройки, но есть ли способ вывести все надстройки на лист? Теперь, в этом руководстве представлен код VBA для вывода всех надстроек в Excel.
Как запустить макрос VBA при открытии или закрытии книги?
В этой статье я расскажу вам, как запускать код VBA при каждом открытии или закрытии книги.
Как защитить / заблокировать код VBA в Excel?
Так же, как вы можете использовать пароль для защиты книг и листов, вы также можете установить пароль для защиты макросов в Excel.
Как использовать временную задержку после запуска макроса VBA в Excel?
В некоторых случаях вам может потребоваться таймер задержки для запуска макроса VBA в Excel. Например, при нажатии для запуска определенного макроса, он вступит в силу через 10 секунд. Эта статья покажет вам метод для достижения этого.
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!