KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как сохранить и использовать макросы VBA во всех книгах Excel?

АвторZhoumandyДата изменения

Существует множество ситуаций, когда один и тот же макрос VBA приходится многократно использовать в разных рабочих книгах Excel — будь то автоматизация повторяющихся вычислений, форматирование данных или создание пользовательских функций, например для преобразования чисел в слова. По умолчанию макросы сохраняются только в той книге, где они созданы, и не доступны в новых документах. Однако Excel предлагает несколько гибких способов сделать макрос VBA глобально доступным, избавляя от необходимости каждый раз копировать код при создании новой рабочей книги. В этом руководстве подробно описаны различные подходы, которые обеспечивают удобный доступ к макросам VBA во всех книгах и значительно повышают продуктивность и эффективность вашей работы.

Снимок экрана, показывающий диалоговое окно надстроек в Excel

Сохранение и использование кода 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, чтобы открыть диалоговое окно «Сохранить как».
Снимок экрана, показывающий параметр «Сохранить» в окне VBA

4. В окне «Сохранить как» введите желаемое имя файла в поле «Имя файла». Обязательно выберите в раскрывающемся списке «Тип файла» формат Надстройка Excel (*.xlam).
Снимок экрана диалогового окна «Сохранить как» с выбором типа файла «Надстройка Excel (*.xlam)»

5. Нажмите кнопку «Сохранить», чтобы сохранить книгу как файл надстройки Excel и получить многократно используемую надстройку, которую можно подключать к любой книге в любое время.
Снимок экрана книги, сохранённой как надстройка Excel

6. После сохранения вернитесь в Excel и закройте книгу, которую вы только что преобразовали в надстройку.

7. Откройте новую или существующую книгу, в которой вы хотите использовать макрос, и введите пользовательскую формулу в нужную ячейку (например, B2):

=NumberstoWords(A2)
Примечание: на данном этапе может появиться ошибка «#ИМЯ?». Это ожидаемо, поскольку надстройка с функцией макроса ещё не загружена в Excel глобально. Выполните приведённые ниже действия, чтобы активировать ваш макрос во всех книгах.
Снимок экрана с ошибкой #ИМЯ? до применения сохранённого макроса VBA

Перейдите на вкладку Разработчик и нажмите кнопку Надстройки Excel в группе «Надстройки».
Снимок экрана, показывающий пункт «Надстройки» на вкладке «Разработчик» в Excel

9. В появившемся диалоговом окне «Надстройки» выберите Обзор.
Снимок экрана диалогового окна надстроек в Excel

10. Найдите и выберите ранее сохранённый файл надстройки, затем нажмите ОК.
Снимок экрана с выбором пользовательского файла надстройки в Excel

11. Ваша пользовательская надстройка, например «Надстройка преобразования чисел в слова», теперь должна появиться в списке надстроек. Убедитесь, что она отмечена, и нажмите ОК, чтобы включить её.
Снимок экрана с пользовательской надстройкой в диалоговом окне надстроек Excel

12. Теперь снова введите пользовательскую функцию в целевую ячейку (например, B2) и нажмите Enter. Вы увидите, что формула корректно преобразует число в английские слова.

=NumberstoWords(A2)

13. Чтобы быстро применить формулу преобразования к нескольким числам, просто перетащите маркер автозаполнения ячейки вниз — и функция автоматически скопируется в другие ячейки.

Снимок экрана с окончательным результатом преобразования чисел в слова

Советы и примечания:

  • Сохранение макроса в виде надстройки позволяет использовать одни и те же пользовательские функции, код или автоматизацию во всех книгах — это экономит время и обеспечивает согласованность.
  • Если Excel закрыт или надстройка впоследствии отключена, функции из неё могут временно отображаться как «#ИМЯ?», пока надстройка снова не будет загружена. Чтобы избежать путаницы, всегда держите надстройку включённой в диспетчере надстроек, когда она вам нужна.
  • Некоторые пользователи по умолчанию не видят вкладку «Разработчик». Чтобы отобразить её, щёлкните правой кнопкой мыши по ленте, выберите «Настроить ленту» и установите флажок рядом с пунктом «Разработчик».
  • Рекомендуем хранить надстройки в постоянной папке, чтобы избежать потери ссылок при перемещении или переименовании файлов.

Если вы предпочитаете запускать код вручную, это также возможно и иногда полезно при отладке или для разового использования:

  1. Вы можете назначить макрос на панель быстрого доступа, чтобы запускать его одним щелчком в любой открытой книге. Для этого щёлкните правой кнопкой мыши по панели быстрого доступа, выберите «Настроить панель быстрого доступа» и добавьте нужный макрос.
    Снимок экрана, показывающий, как добавить макрос VBA на панель быстрого доступа
  2. Также можно нажать Alt + F11, чтобы открыть редактор VBA, вручную выбрать макрос и нажать F5, чтобы при необходимости выполнить код.

Преимущества: Это решение позволяет создавать и распространять мощные многократно используемые макросы, которые работают всегда — пока активна надстройка.
Недостатки: Пользователи должны помнить о загрузке надстройки, а при совместной работе с книгами — дополнительно передавать файл надстройки и описание функций. Кроме того, надстройки недоступны в Excel Online.

Метод личной книги макросов

Ещё один чрезвычайно практичный способ всегда иметь под рукой ваши любимые или наиболее часто используемые макросы в любой сессии Excel — независимо от того, какая книга открыта — это личная книга макросов (PERSONAL.XLSB). Это специальный скрытый файл 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

Другие операции (статьи)

Код VBA для вывода списка всех надстроек в Excel
В Excel можно подключать различные надстройки, чтобы работать с данными ещё эффективнее. Как известно, все надстройки отображаются в окне параметров — но можно ли вывести их список прямо на лист? В этом руководстве вы найдёте готовый код VBA для отображения всех надстроек в Excel.

Как запускать макрос VBA при открытии или закрытии книги?
В этой статье вы узнаете, как автоматически выполнять код VBA каждый раз при открытии или закрытии книги.

Как защитить / заблокировать код VBA в Excel?
Так же, как вы можете использовать пароль для защиты книг и листов, вы можете установить пароль для защиты макросов в Excel.

Как добавить временную задержку после выполнения макроса VBA в Excel?
Иногда возникает необходимость вставить таймерную задержку перед запуском макроса VBA в Excel. Например, после нажатия кнопки запуска определённого макроса его выполнение должно начаться спустя 10 секунд. В этой статье описан способ реализации такой задержки.

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…

Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу

  • Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
  • Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!

Все надстройки Kutools — один установщик

Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек