Excel где хранятся макросы Excelka.ru - все про Ексель

Excel где хранятся макросы

Как использовать Личную Книгу Макросов

Если вы еще не знакомы с макросами в Excel, то я вам даже немного завидую. Ощущение всемогущества и осознание того, что ваш Microsoft Excel можно прокачивать почти до бесконечности, которые придут к вам после знакомства с макросами — приятные чувства.

Однако, эта статья для тех, кто уже «познал мощь» и начал использовать макросы (чужие или написанные самостоятельно — не важно) в своей повседневной работе.

Макрос — это код (несколько строк) на языке Visual Basic, которые заставляют Excel сделать то, что вам нужно: обработать данные, сформировать отчет, скопипастить много однообразных таблиц и т.п. Вопрос — где эти несколько строк кода хранить? Ведь от того, где макрос хранится будет потом зависеть где он сможет (или не сможет) работать.

Если макрос решает небольшую локальную проблему в отдельно взятом файле (например обрабатывает внесенные в конкретный отчет данные особым образом), то логично хранить код внутри этого же файла. Без вопросов.

А если макрос должен быть относительно универсален и нужен в любой книге Excel — как, например, макрос для конвертирования формул в значения? Не копировать же его код на Visual Basic каждый раз в каждую книгу? Кроме того, рано или поздно, почти любой пользователь приходит к мысли, что неплохо было бы сложить все макросы в одну коробку, т.е. иметь их всегда под рукой. И может быть даже запускать не вручную, а сочетаниями клавиш? И вот тут может здорово помочь Личная Книга Макросов (Personal Macro Workbook).

Как создать Личную Книгу Макросов

На самом деле, Личная Книга Макросов (ЛКМ) — это обычный файл Excel в формате двоичной книги (Personal.xlsb), который автоматически в скрытом режиме открывается одновременно с Microsoft Excel. Т.е. когда вы просто запускаете Excel или открываете любой файл с диска, на самом деле открываются два файла — ваш и Personal.xlsb, но второго мы не видим. Таким образом все макросы, которые хранятся в ЛКМ оказываются доступы для запуска в любой момент, пока открыт Excel.

Если вы еще ни разу не пользовались ЛКМ, то изначально файл Personal.xlsb не существует. Самый легкий способ его создать — это записать рекордером какой-нибудь ненужный бессмысленный макрос, но указать в качестве места для его хранения Личную Книгу — тогда Excel будет вынужден автоматически ее для вас создать. Для этого:

  1. Откройте вкладку Разработчик (Developer) . Если вкладки Разработчик не видно, то ее можно включить в настройках через Файл — Параметры — Настройка ленты (Home — Options — Customize the Ribbon) .
  2. На вкладке Разработчик нажмите кнопку Запись макроса (Record Macro) . В открывшемся окне выберите Личную книгу макросов (Personal Macro Workbook) как место для хранения записанного кода и нажмите OK:

  • Остановите запись кнопкой Остановить запись (Stop Recording) на вкладке Разработчик (Developer)
  • Проверить результат можно, нажав на кнопку 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) :

    Читать еще:  Как в excel сделать фиксированную ячейку

    Примеры таких функций можно в больших количествах найти в интернете или здесь же на сайте (сумма прописью, приблизительный текстовый поиск, ВПР 2.0, конвертация кириллицы в транслит и т.п.)

    Где хранится Личная Книга Макросов

    Если вы будете использовать Личную Книгу Макросов, то рано или поздно у вас возникнет желание:

    • поделиться своими накопленными макросами с другими пользователями
    • скопировать и перенести Личную Книгу на другой компьютер
    • сделать ее резервную копию

    Для этого нужно будет найти файл PERSONAL.XLSB на диске вашего компьютера. По умолчанию, этот файл хранится в специальной папке автозапуска Excel, которая называется XLSTART. Так что все, что нужно — это добраться до этой папки на нашем ПК. И вот тут возникает небольшая сложность, потому что местоположение этой папки зависит от версии Windows и Office и может различаться. Обычно это один из следующих вариантов:

    • C:Program FilesMicrosoft OfficeOffice12XLSTART
    • C:Documents and SettingsComputerApplication DataMicrosoftExcelXLSTART
    • C:Users имя-вашей-учетной-записи AppDataRoamingMicrosoftExcelXLSTART

    Как вариант, можно спросить о положении этой папки сам Excel с помощью VBA. Для этого в редакторе Visual Basic (кнопка Visual Basic на вкладке Разработчик) нужно открыть окно Immediate сочетанием клавиш Ctrl+G, ввести туда команду ? Application.StartupPath и нажать на Enter:

    Полученный путь можно скопировать и вставить в верхнюю строку окна Проводника в Windows и нажать Enter — и мы увидим папку с нашим файлом Личной Книги Макросов:

    И несколько практических нюансов вдогон:

    • при использовании Личной Книги Макросов Excel будет запускаться чуть медленнее, особенно на слабых ПК
    • стоит периодически очищать Личную Книгу от информационного мусора, старых и ненужных макросов и т.п.
    • у корпоративных пользователей бывают иногда сложности с использованием Личной Книги, т.к. это файл в системной скрытой папке

    Создание и сохранение всех макросов в одной книге

    Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

    Если вы обнаружите, что они воссоздают один и тот же макрос, вы можете скопировать эти макросы в специальную книгу с именем Personal. xlsb , сохраненную на компьютере. Макросы, которые хранятся в личной книге, становятся доступными при каждом запуске Excel на том же компьютере.

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

    Чтобы создать личную книгу, необходимо сначала создать макрос и сохранить его в личной книге макросов. Прежде чем приступить к работе, убедитесь в том, что вкладка разработчик доступна на ленте Excel. Для получения дополнительных сведений ознакомьтесь с вкладкой «Разработчик» .

    Создание и обновление личной книги макросов

    На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

    В диалоговом окне запись макроса введите понятное имя макроса в поле имя макроса . Убедитесь в том, что имя не используется в качестве пробелов.

    В диалоговом окне сохранить макрос в выберите пункт Личная книга макросов.

    Нажмите кнопку ОК.

    Выполните действия, которые нужно записать.

    На вкладке Разработчик в группе Код нажмите кнопку Остановить запись.

    Отобразится сообщение с предложением сохранить изменения, внесенные в личную книгу макросов.

    Нажмите кнопку сохранить , чтобы сохранить книгу.

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

    Общий доступ к макросам

    Если вы хотите скопировать макросы из личной книги в другую книгу или наоборот, это можно сделать в редакторе Visual Basic (VBE). Чтобы запустить редактор Visual Basic в Excel, нажмите клавиши ALT + F11. Дополнительные сведения о копировании макросов из одной книги в другую см. в статье Копирование модуля макроса в другую книгу.

    Если вы хотите поделиться своим файлом личных файлов с другими пользователями, вы можете скопировать его в папку XLSTART на других компьютерах. В Windows 10, Windows 7 и Windows Vista эта книга сохраняется в папке c appdatalocaltemp. намеаппдаталокалмикрософтексцелкслстарт. В Microsoft Windows XP эта книга сохраняется в папке «C:Documents and Settings Намеаппликатион Датамикрософтексцелкслстарт». Книги, хранящиеся в папке XLStart, открываются автоматически при запуске Excel, а код, хранящийся в личной книге макросов, будет отображаться в диалоговом окне макрос.

    Читать еще:  Как в excel отфильтровать повторяющиеся значения

    Если у вас есть только один или несколько макросов, к которым вы хотите предоставить доступ другим пользователям, вы можете отправить им книгу, содержащую их, в сообщении электронной почты. Вы также можете сделать книгу доступной на общем сетевом диске или в библиотеке SharePoint Services.

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

    В меню Excel выберите пункт Параметры. _гт_ ленты _амп_ Toolbar. В категории настроить ленту в списке Основные вкладки установите флажок разработчик , а затем нажмите кнопку сохранить.

    Создание и обновление личной книги макросов

    Чтобы создать личную книгу, необходимо сначала создать макрос и сохранить его в личной книге макросов.

    На вкладке Разработчик нажмите кнопку Запись макроса.

    В диалоговом окне запись макроса введите понятное имя макроса в поле имя макроса . Убедитесь в том, что имя не используется в качестве пробелов.

    В диалоговом окне сохранить макрос в выберите пункт Личная книга макросов.

    Нажмите кнопку ОК.

    Выполните действия, которые нужно записать.

    На вкладке Разработчик щелкните Остановить запись.

    Сохраните изменения, закройте книгу, а затем закройте Excel.

    Отобразится сообщение с предложением сохранить изменения, внесенные в личную книгу макросов.

    Нажмите кнопку сохранить , чтобы сохранить книгу.

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

    Общий доступ к макросам

    Если вы хотите скопировать макросы из личной книги в другую книгу или наоборот, это можно сделать в редакторе Visual Basic (VBE). Чтобы запустить редактор Visual Basic в Excel, на вкладке разработчик нажмите кнопку Visual Basic . Дополнительные сведения о копировании макросов из одной книги в другую см. в статье Копирование модуля макроса в другую книгу.

    Если вы хотите поделиться своим файлом личных файлов с другими пользователями, вы можете скопировать его в корневую папку Excel на других компьютерах. Папка для Mac находится в домашней папке

    Если у вас есть только один или несколько макросов, к которым вы хотите предоставить доступ другим пользователям, вы можете отправить им книгу, содержащую их, в сообщении электронной почты. Вы также можете сделать книгу доступной на общем сетевом диске или в библиотеке SharePoint Services.

    Дополнительные сведения

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    Личная книга макросов в Excel и как ее использовать

    Большинство пользователей Excel знают, как создать и использовать макрос внутри файла одной рабочей книги. В случаи, когда необходимо использовать тот же макрос в других файлах рабочих книг, тогда можно сохранить его в личной книге макросов. У нее всегда одно название – Personal.xlsb. Она всегда открывается при запуске программы Excel, только скрыто. Данная книга не является доступной по умолчанию в Excel, поэтому ее нужно сначала подключить.

    Как сохранить макрос в личную книгу макросов

    Чтобы создать и схоронить код в личной книге макросов:

    1. Выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Запись макроса».
    2. В появившемся диалоговом окне «Запись макроса», из выпадающего списка «Сохранить в:» выберите опцию «Личная книга макросов». И нажмите на кнопку OK.
    3. Теперь выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Остановить запись».
    4. Откройте редактор Visual Basic: «РАЗРАБОТЧИК»-«Код»-«Visual Basic». Или нажмите комбинацию горячих клавиш ALT+F11. В окне «Project-VBAProject», в дереве проектов появиться доступная еще одна книга Personal.xlsb. Кликните на «плюсик» слева на против этой книги, чтобы раскрыть проект книги Personal.xlsb. А после двойным щелчком отройте ее Module1.
    5. В результате откроется окно кода модуля с зарегистрированным макросом. Удалите его код и введите код своего макроса. Выберите инструмент в редакторе макросов: «File»-«Save Personal.xlsb», а потом закройте редактор Visual Basic.

    

    Теперь у вас подключена скрытая книга для хранения макросов, к которым можно получить доступ из любой другой рабочей книги. Личная книга макросов где находится находиться в папке автозагрузки файлов Excel – XLSTART: C:Documents and SettingsUser_NameAppDataRoamingMicrosoftExcelXLSTARTPersonal.xlsb

    Примечание. XLSTART – это папка для автозагрузки файлов вместе с запуском программы Excel. Если сохранить файл в данную папку, то он будет открываться вместе с программой Excel. Для версий старше 2007 путь к папке автозагрузки будет следующим: C:Program FilesMicrosoft OfficeOffice12Xlstart.

    Excel где хранятся макросы

    7 вопросов о макросах

    1) Что такое макрос?

    Читать еще:  Диаграмма ганта в excel как сделать

    Макрос (другие названия: код, программа, подпрограмма, процедура, скрипт) — это набор команд (или одна команда), выполняющих заданные пользователем при написании кода макроса действий. Правильно называть все макросы Процедура, но в разговорном русском закрепилось название Макрос, поэтому мы будем далее все процедуры называть макросами.
    2) Зачем нужны макросы?

    Макросы используются в двух случаях:
    — Если необходимо автоматизировать часто повторяющиеся действия;
    — Если задачу невозможно решить обычными средствами Excel.
    3) На каком языке программирования пишутся макросы?

    До выхода Excel 95 разработчиками использовался язык макросов под названием XLM (Excel Macro). Начиная с версии Excel 95 стал использоваться язык VBA (Visual Basic for Applications). Этот язык используется и по сей день, но в будущем Microsoft планирует заменить его на VSTA (Visual Studio Tools for Applications) — инструментарий расширения функциональности приложений, основанный на Microsoft .NET.

    4) В каких программах работают макросы?
    Макросы поддерживаются во многих программах пакета MS Office, кроме Excel это например Word, Access, Outlook, PowerPoint и т.д. Кроме офисного пакета, макросы на VBA работают например в программе AutoCad.
    5) Безопасно ли использовать макросы?

    Нет, опасность существует. На VBA можно написать вредоносный код. Однако на практике такое встречается настолько редко, что такой возможностью можно пренебречь. Если Вы не уверены в том, как именно работает макрос, то лучше его не запускать. Для того что бы макросы не запускались сами, в MS Office по умолчанию макросы отключены. Если вы пишете макрос сами и не ставите целью испортить свой копьютер — то да, безопасно.

    6) Где можно писать макросы?

    Для написания макросов используется редактор VBE (Visual Basic Editor). Он встроен в программу Excel по умолчанию. Попасть в него можно несколькими способами. Например, нажав сочетание клавиш Alt+F11 или кликнув правой кнопкой по ярлыку любого листа и в появившемся контекстном меню выбрать пункт Исходный текст.

    7) Где хранятся макросы?

    Текст макроса хранится в специальном контейнере, называемым модуль. Модули бывают четырёх типов:
    Стандартный модуль (или просто модуль);
    Модуль листа;
    Модуль книги;
    Модуль класса.

    Excel где хранятся макросы

    Если вы пользуетесь возможностью записывать макросы VBA в Excel, то рано или поздно вас настигнет проблема под названием PERSONAL.XLSB . В один прекрасный момент все макросы, которые вы записываете, будут помещаться не в текущую книгу, а в PERSONAL.XLSB, от которого избавиться простому смертному пользователю не так-то просто.

    Что это вообще такое.

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

    На практике, однако, это не совсем удобно, так как обычно требуется, чтобы макросы работали не только у вас, но и у ваших коллег, а это означает, что макрос желательно разместить непосредственно в файле, данными которого макрос будет манипулировать. Это имеет свои недостатки, но сейчас не об этом.

    В общем суть в том, что однажды PERSONAL.XLSB у вас появляется на экране Visual Basic for Application и многие терпят его там годами, не зная, как от него избавиться.

    Как его удалить навсегда?

    Надеюсь, что у вас Windows 7 . В Windows XP и Windows 8 всё тоже должно быть похоже.

    Для начала надо проверить, что у вас в Проводнике отображаются скрытые файлы. Для этого войдите в меню Упорядочить Проводника и выберите пункт Параметры папок и поиска . Проверьте, что у вас сделана настройка.

    После этого в вашем профиле появится папка AppData . Ваш профиль обычно располагается по пути C:ПользователиИмя вашего пользователя или для англоязычной ОС C:UsersИмя вашего пользователя.

    Заметьте, что поиск PERSONAL.XLSB не работает, так как Windows не хочет по умолчанию искать в скрытых папках.

    Откройте папку AppDataRoamingMicrosoftExcelXLSTART и удалите оттуда PERSONAL.XLSB , предварительно закрыв Excel, так как тот этот файл держит открытым, что препятствует его удалению.

    И последний очень важный момент, на который многие не обращают внимание, и из-за которого Excel этот файл успешно пересоздаёт. При записи новых пользовательских макросов убедитесь, что на этом экране у вас нет настройки Личная книга макросов , а есть, скажем, Эта книга .

    Вот теперь вы от него избавились навсегда 🙂

    Ссылка на основную публикацию