Как использовать Личную Книгу Макросов
Если вы еще не знакомы с макросами в Excel, то я вам даже немного завидую. Ощущение всемогущества и осознание того, что ваш Microsoft Excel можно прокачивать почти до бесконечности, которые придут к вам после знакомства с макросами — приятные чувства.
Однако, эта статья для тех, кто уже «познал мощь» и начал использовать макросы (чужие или написанные самостоятельно — не важно) в своей повседневной работе.
Макрос — это код (несколько строк) на языке Visual Basic, которые заставляют Excel сделать то, что вам нужно: обработать данные, сформировать отчет, скопипастить много однообразных таблиц и т.п. Вопрос — где эти несколько строк кода хранить? Ведь от того, где макрос хранится будет потом зависеть где он сможет (или не сможет) работать.
Если макрос решает небольшую локальную проблему в отдельно взятом файле (например обрабатывает внесенные в конкретный отчет данные особым образом), то логично хранить код внутри этого же файла. Без вопросов.
А если макрос должен быть относительно универсален и нужен в любой книге Excel — как, например, макрос для конвертирования формул в значения? Не копировать же его код на Visual Basic каждый раз в каждую книгу? Кроме того, рано или поздно, почти любой пользователь приходит к мысли, что неплохо было бы сложить все макросы в одну коробку, т.е. иметь их всегда под рукой. И может быть даже запускать не вручную, а сочетаниями клавиш? И вот тут может здорово помочь Личная Книга Макросов (Personal Macro Workbook).
Как создать Личную Книгу Макросов
На самом деле, Личная Книга Макросов (ЛКМ) — это обычный файл Excel в формате двоичной книги (Personal.xlsb), который автоматически в скрытом режиме открывается одновременно с Microsoft Excel. Т.е. когда вы просто запускаете Excel или открываете любой файл с диска, на самом деле открываются два файла — ваш и Personal.xlsb, но второго мы не видим. Таким образом все макросы, которые хранятся в ЛКМ оказываются доступы для запуска в любой момент, пока открыт Excel.
Если вы еще ни разу не пользовались ЛКМ, то изначально файл Personal.xlsb не существует. Самый легкий способ его создать — это записать рекордером какой-нибудь ненужный бессмысленный макрос, но указать в качестве места для его хранения Личную Книгу — тогда Excel будет вынужден автоматически ее для вас создать. Для этого:
- Откройте вкладку Разработчик (Developer) . Если вкладки Разработчик не видно, то ее можно включить в настройках через Файл — Параметры — Настройка ленты (Home — Options — Customize the Ribbon) .
- На вкладке Разработчик нажмите кнопку Запись макроса (Record Macro) . В открывшемся окне выберите Личную книгу макросов (Personal Macro Workbook) как место для хранения записанного кода и нажмите OK:
Проверить результат можно, нажав на кнопку Visual Basic там же на вкладке Разработчик — в открывшемся окне редактора в левом верхнем углу на панели Project — VBA Project должен появиться наш файл PERSONAL.XLSB. Его ветку которого можно развернуть плюсиком слева, добравшись до Module1, где и хранится код только что записанного нами бессмысленного макроса:
Поздравляю, вы только что создали себе Личную Книгу Макросов! Только не забудьте нажать на кнопку сохранения с дискеткой в левом верхнем углу на панели инструментов.
Как использовать Личную Книгу Макросов
Дальше все просто. Любой нужный вам макрос (т.е. кусок кода, начинающийся на Sub и заканчивающийся End Sub ) можно смело копировать и вставлять либо в Module1, либо в отдельный модуль, добавив его предварительно через меню Insert — Module. Хранить все макросы в одном модуле или раскладывать по разным — исключительно вопрос вкуса. Выглядеть это должно примерно так:
Запустить добавленный макрос можно в диалоговом окне, вызываемом с помощью кнопки Макросы (Macros) на вкладке Разработчик:
В этом же окне, нажав кнопку Параметры (Options) , можно задать сочетание клавиш для быстрого запуска макроса с клавиатуры. Будьте внимательны: сочетания клавиш для макросов различают раскладку (русская или английская) и регистр.
Кроме обычных макросов-процедур в Личной Книге можно хранить и пользовательские макро-функции (UDF = User Defined Function) . В отличие от процедур, код функций начинаются с оператора Function или Public Function , а заканчиваются на End Function :
Код необходимо аналогичным образом скопировать в любой модуль книги PERSONAL.XLSB и затем можно будет вызвать функцию обычным образом, как любую стандарную функцию Excel, нажав кнопку fx в строке формул и выбрав функцию в окне Мастера Функций в категории Определенные пользователем (User Defined) :
Примеры таких функций можно в больших количествах найти в интернете или здесь же на сайте (сумма прописью, приблизительный текстовый поиск, ВПР 2.0, конвертация кириллицы в транслит и т.п.)
Где хранится Личная Книга Макросов
Если вы будете использовать Личную Книгу Макросов, то рано или поздно у вас возникнет желание:
- поделиться своими накопленными макросами с другими пользователями
- скопировать и перенести Личную Книгу на другой компьютер
- сделать ее резервную копию
Для этого нужно будет найти файл PERSONAL.XLSB на диске вашего компьютера. По умолчанию, этот файл хранится в специальной папке автозапуска Excel, которая называется XLSTART. Так что все, что нужно — это добраться до этой папки на нашем ПК. И вот тут возникает небольшая сложность, потому что местоположение этой папки зависит от версии Windows и Office и может различаться. Обычно это один из следующих вариантов:
- C:\Program Files\Microsoft Office\Office12\XLSTART
- C:\Documents and Settings\Computer\Application Data\Microsoft\Excel\XLSTART
- C:\Users\ имя-вашей-учетной-записи \AppData\Roaming\Microsoft\Excel\XLSTART
Как вариант, можно спросить о положении этой папки сам Excel с помощью VBA. Для этого в редакторе Visual Basic (кнопка Visual Basic на вкладке Разработчик) нужно открыть окно Immediate сочетанием клавиш Ctrl+G, ввести туда команду ? Application.StartupPath и нажать на Enter:
Полученный путь можно скопировать и вставить в верхнюю строку окна Проводника в Windows и нажать Enter — и мы увидим папку с нашим файлом Личной Книги Макросов:
Как сохранить макрос в excel для всех книг
В некоторых случаях вам может понадобиться использовать один макрос VBA несколько раз в будущем. Это возможно сохраните как-нибудь модуль VBA в любой новый документ чтобы он был доступен во всех книгах? Ответ положительный. В этом уроке мы познакомим вас с простым способом достижения вашей цели.
Сохраните и используйте код VBA во всех книгах
Например, вы хотите использовать код VBA для преобразовать числа в английские слова и сохранить модуль VBA во всех книгах на случай, если вы захотите использовать код VBA в будущем. Пожалуйста, сделайте следующее.
1. нажмите Alt + F11 ключи в Excel, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули и вставьте следующий макрос в окно модуля.
Код VBA: преобразование чисел в слова
3. Нажмите Save. значок в верхнем левом углу ленты или нажмите Ctrl + S для открытия Сохранить как окно.
4. в Сохранить как введите имя книги в поле Имя файла коробка. И выберите Надстройка Excel (*.xlam) вариант в Сохранить как выпадающий список.
5. Затем нажмите Save. кнопку, чтобы сохранить книгу с кодом VBA в качестве Надстройка Excel.
6. Вернемся к Excel, закройте пустую книгу, которая была сохранена как надстройка Excel.
7. Откройте новую книгу с данными, которые необходимо преобразовать. Введите формулу = Число слов (A2) в ячейке В2. # ИМЯ? будет возвращено значение ошибки, поскольку код VBA еще не был применен во всех книгах.
8. К Застройщик вкладку нажмите Надстройки Excel в Надстройки группа.
9. Добавить в всплывает диалоговое окно. Щелкните значок Приложения кнопку.
10. Выберите надстройку, которую вы только что сохранили, затем нажмите OK кнопку.
11. Затем Надстройка «Преобразование чисел в слова» настроенный вами вставлен и включен. Нажмите на OK кнопку, чтобы завершить настройку.
12. Теперь при вводе формулы = Число слов (A2) в ячейке B2 и нажмите кнопку Enter ключ, будут возвращены соответствующие английские слова. Перетащите маркер автозаполнения вниз, чтобы получить все результаты.
Заметки:
Если вам нужно запустить код вручную, нет никакого способа найти его из вышеперечисленных шагов. Пожалуйста, не волнуйтесь. Есть два пути запустить код.
- Вы можете добавить код в Быстрая панель инструментов и запускайте код каждый раз, когда на панели инструментов нажимается кнопка кода.
- Вы также можете напрямую нажать Alt + F11 чтобы открыть окно операции с кодом, найдите код и нажмите F5 бежать.
Прочие операции (статьи)
Код VBA для отображения всех надстроек в Excel
В Excel вы можете добавлять или вставлять надстройки для более удобной работы с данными. Как мы знаем, мы можем перейти в окно «Параметры», чтобы просмотреть все надстройки, но есть ли способ перечислить все надстройки на листе? Теперь в этом руководстве он предоставляет код VBA для перечисления всех надстроек в Excel.
Как запустить макрос VBA при открытии или закрытии книги?
В этой статье я расскажу вам, как запускать код VBA при каждом открытии или закрытии рабочей книги.
Как защитить/заблокировать код VBA в Excel?
Точно так же, как вы можете использовать пароль для защиты книг и рабочих листов, вы также можете установить пароль для защиты макросов в Excel.
Как использовать временную задержку после запуска макроса VBA в Excel?
В некоторых случаях вам может потребоваться установить задержку таймера для запуска макроса VBA в Excel. Например, если щелкнуть для запуска указанного макроса, он вступит в силу через 10 секунд. Эта статья покажет вам способ ее достижения.
Где хранить макросы
При создании своих первых макросов вы скорее всего соглашались с тем местом хранения макросов, которое предлагает Excel по умолчанию. Но вообще-то Excel предлагает три места сохранения макросов:
текущая открытая рабочая книга,
новая рабочая книга,
личная книга макросов.
По умолчанию макросы сохраняются в текущей рабочей книге. Это подразумевает, что макросы будут доступны только тогда, когда эта книга открыта. Если вы выбираете опцию сохранения макроса в новой рабочей книге, то эта книга будет создана автоматически. При желании можно сохранить макрос в личной книге макроса, эта книга также будет автоматически открыта или создана (если она не была создана ранее).
Создание личной книги макросов
Личная книга макросов — специальная скрытая рабочая книга, предназначенная для хранения макросов. При первом сохранении макросов в личной книге макросов создается новый файл с названием PERSONAL.XLS. После создания этого файла он автоматически открывается при каждой загрузке программы Excel. Так как личная книга постоянно открыта, то все макросы, сохраненные в ней, всегда доступны. Отсюда следует простой вывод — если вы хотите, чтобы какой-либо макрос был доступен во всех рабочих книгах, сохраните его в личной книге макросов.
Личная книга макросов — специальная скрытая рабочая книга, предназначенная для хранения макросов. При первом сохранении макросов в личной книге создается новый файл с названием PERSONAL.XLS. После создания этого файла он автоматически открывается при каждой загрузке программы Excel. Так как личная книга постоянно открыта, то все макросы, сохраненные в ней, всегда доступны. Отсюда следует простой вывод — если вы хотите, чтобы какой-либо макрос был доступен во всех рабочих книгах, сохраните его в личной книге макросов.
Личная книга макросов находится на жестком диске компьютера в папке C:\Windows\Application Data\Microsoft\Excel\XLStart.
Сохранение макросов в личной книге макросов
Процедура сохранения макроса в личной книге макросов ничем не отличается от аналогичной процедуры сохранения в текущей рабочей книге. Процесс сохранения макроса в личной книге рассмотрим на примере создания простого макроса, который применяет к содержимому выделенных ячеек курсивное и полужирное начертание. Выполните следующие действия.
Выберите команду Сервис > Макрос > Начать запись. Появится диалоговое окно Запись макроса.
Введите имя макроса, например ФорматТекст.
В раскрывающемся списке Сохранить в выберите опцию Личная книга макросов.
Щелкните на кнопке ОК. Вы перешли в режим записи.
Щелкните на кнопке Курсив панели инструментов форматирования. Если вы впервые сохраняете макрос в личной книге, то на несколько секунд указатель мыши примет вид песочных часов — происходит создание личной книги макросов.
Щелкните на кнопке Полужирный панели инструментов форматирования.
Остановите запись макроса.
Выполнение макросов из личной книги макросов
После сохранения макроса в личной книге макросов вы можете использовать его из любой рабочей книги. Для этого сделайте следующее.
Закройте все рабочие книги. (Это необходимо для того, чтобы удостовериться в том, что макрос сохранен именно в личной книге макросов.)
Рис. 2.2. Макрос, сохраненный в личной книге макросов, можно использовать в любой рабочей книге
Можно ли сделать макрос VBA Excel доступным для всех книг?
можно ли написать сценарий VBA в одной книге и использовать его в других книгах на компьютере? Наверное, я ищу способ написать пакет макросов.
EDIT
Следуя инструкциям в ссылке, предоставленной Лансом, мне удалось создать надстройку, но я не вижу ее на вкладке «макросы». Что мне здесь не хватает?
26.11.2022 8:37 2577
5 ответов
есть несколько способов сделать это, вот ссылка это объясняет, как создать надстройку Excel с кодом VBA. Вы также можете создавать другие типы надстроек другими способами, но это самый простой. Есть тысячи ссылок, которые вы можете искать, которые говорят о том, как это сделать.
аддон не требуется!
хранить его в специальной книге под названием Personal.xlsb
действия:
макросы можно сделать доступными во всех книгах, сохранив их в специальной книге, загружаемой Excel при каждом открытии.
при записи макроса его можно сохранить в личной книге макросов. Когда вы выходите из Excel, он спрашивает, Хотите ли вы сохранить его, как только вы нажмете да, макрос станет доступным для всех книг в Developer => Macros. См. также поддержка Microsoft
воспользуйтесь переменной среды UserProfile для aviod, которая отличается для каждого пользователя.
Так стандартизированной путь: %Профиль_пользователя%\AppData\роуминг\Майкрософт\в Excel\XLSTART
Как найти папку XLStart
-1.Нажмите кнопку Microsoft Office кнопку Office изображение кнопки и нажмите кнопку Параметры Excel.
-2.Щелкните Центр управления безопасностью, а затем в разделе Центр управления безопасностью Microsoft Office Excel щелкните параметры Центра управления безопасностью.
-3.Щелкните надежные расположения, а затем проверьте путь к папке XLStart в списке надежных расположений.