Как сделать общий макрос 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) :

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

    Читать еще:  Смешанная ссылка в excel как сделать

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

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

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

    Для этого нужно будет найти файл 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 будет запускаться чуть медленнее, особенно на слабых ПК
    • стоит периодически очищать Личную Книгу от информационного мусора, старых и ненужных макросов и т.п.
    • у корпоративных пользователей бывают иногда сложности с использованием Личной Книги, т.к. это файл в системной скрытой папке

    Создание макросов в Microsoft Excel

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

    Способы записи макросов в Excel

    Макрос записывается двумя способами: автоматически и вручную. Воспользовавшись первым вариантом, вы просто записываете определенные действия в Microsoft Excel, которые выполняете в данный момент времени. Потом можно будет воспроизвести эту запись. Такой метод очень легкий и не требует знания кода, но применение его на практике довольно ограничено. Ручная запись, наоборот, требует знаний программирования, так как код набирается вручную с клавиатуры. Однако грамотно написанный таким образом код может значительно ускорить выполнение процессов.

    Вариант 1: Автоматическая запись макросов

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

    Когда все готово, приступаем к записи.

      Перейдите на вкладку «Разработчик». Кликните по кнопке «Запись макроса», которая расположена на ленте в блоке инструментов «Код».

  • После этого все ваши действия в данной книге (файле) Excel будут записываться в макрос до тех пор, пока вы сами не остановите запись.
  • Для примера запишем простейшее арифметическое действие: сложение содержимого трех ячеек (=C4+C5+C6).

    Когда алгоритм был выполнен, щелкаем на кнопку «Остановить запись». Эта кнопка преобразовалась из кнопки «Запись макроса» после включения записи.

    Запуск макроса

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

      Кликаем в том же блоке инструментов «Код» по кнопке «Макросы» или жмем сочетание клавиш Alt + F8.

  • Можно поступить еще проще и не вызывать даже окно выбора макросов, так как на первом этапе мы задали сочетание клавиш для быстрого вызова макроса. В нашем случае это Ctrl + М. Жмем данную комбинацию на клавиатуре, после чего он запускается.
  • Как видим, он выполнил в точности все те действия, которые были записаны ранее.

    Редактирование макроса

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

      Снова щелкаем на кнопку «Макросы». В открывшемся окне выбираем нужный и кликаем по кнопке «Изменить».

    Открывается «Microsoft Visual Basic» (VBE) – среда, где происходит их редактирование.

  • Запись каждого макроса начинается с команды Sub , а заканчивается командой End Sub . Сразу же после Sub указывается имя макроса. Оператор Range(«…»).Select указывает выбор ячейки. Например, при команде «Range(«C4»).Select» выбирается ячейка «C4». Оператор ActiveCell.FormulaR1C1 используется для записи действий в формулах и других расчетов.
  • Попытаемся немного изменить макрос, дописав выражение:

    Range(«C3»).Select
    ActiveCell.FormulaR1C1 = «11»
    Выражение ActiveCell.FormulaR1C1 = «=R[-3]C+R[-2]C+R[-1]C» заменим на ActiveCell.FormulaR1C1 = «= R[-4]C+R[-3]C+R[-2]C+R[-1]C» .

  • Закрываем редактор и запускаем макрос. Как видим, вследствие введенных нами изменений была добавлена дополнительная ячейка с данными. Она также была включена в расчет общей суммы.
  • В случае если макрос слишком большой, его выполнение может занять значительное время, но внесением ручного изменения в код мы можем ускорить процесс. Добавляем команду Application.ScreenUpdating = False . Она позволит сохранить вычислительные мощности, а значит, ускорить работу. Это достигается путем отказа от обновления экрана во время выполнения вычислительных действий. Чтобы возобновить обновление после выполнения макроса, в его конце пишем команду Application.ScreenUpdating = True .
  • Добавим также команду Application.Calculation = xlCalculationManual в начало кода, а в его конец дописываем Application.Calculation = xlCalculationAutomatic . Этим мы сначала отключаем автоматический пересчет результата после каждого изменения ячеек, а в конце макроса – включаем. Таким образом, Excel подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.

    Вариант 2: Написание кода макроса с нуля

    Продвинутые пользователи могут выполнять не только редактирование и оптимизацию записанных макросов, но и записывать их код с нуля.

    Читать еще:  Как сделать смешанный график в excel?

      Чтобы приступить к этому, нужно нажать на кнопку «Visual Basic», которая расположена в самом начале ленты разработчика.

    Откроется окно редактора VBE, которое уже быдло продемонстрировано в предыдущем варианте.

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Как создавать, изменять и выполнять макросы в Excel

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

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

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

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

    Метод 1: Записываем макрос в автоматическом режиме

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

    Узнать, как это можно сделать, можно в нашей статье – “Как включать и отключать макросы в Excel”.

    После того, как макросы включены, можно перейти к нашей основной задаче.

    1. Переключаемся во вкладку “Разработчик”. В группе инструментов “Код” щелкаем по значку “Записать макрос”.
    2. На экране появится окошко, в котором мы можем выполнить настройки записи. Здесь указывается:
      • имя макроса – любое, но должно начинаться с буквы (не с цифры), не допускаются пробелы.
      • комбинация клавиш, которая будет использоваться для запуска макроса. Обязательной является Ctrl, а вторую мы можем назначить в специальном поле. Допустим, пусть это будет клавиша “r”. Если нужно использовать заглавную букву, нужно зажать клавишу Shift, которая будет добавлена в сочетание клавиш.
      • место хранения макроса. Стандартно – это будет текущая книга, но, если требуется, можно выбрать новую книгу или личную книгу макросов. В нашем случае оставляем стандартный вариант.
      • При желании можно добавить описание к макросу.
      • По готовности кликаем OK.
    3. Теперь в созданный макрос будут записываться все действия, которые мы будем выполнить в книге. И так до тех пор, пока мы не остановим запись.
    4. Допустим, давайте давайте в ячейке D2 выполним умножение двух ячеек (B2 и B3): =B2*B3 .
    5. После того, как выражение готово, нажимаем клавишу Enter, чтобы получить результат. Затем останавливаем запись макроса, нажав на значок “Стоп” (появилась вместо кнопки “Записать макрос”) в группе инструментов “Код”.

    Запускаем выполнение макроса

    Чтобы проверить работу записанного макроса, нужно сделать следующее:

    1. В той же вкладке (“Разработчик”) и группе “Код” нажимаем кнопку “Макросы” (также можно воспользоваться горячими клавишами Alt+F8).
    2. В отобразившемся окошке выбираем наш макрос и жмем по команде “Выполнить”.Примечание: Есть более простой вариант запустить выполнение макроса – воспользоваться сочетанием клавиш, которое мы задали при создании макроса.
    3. Результатом проверки будет повторение ранее выполненных (записанных) действий.

    Корректируем макрос

    Созданный макрос можно изменить. Самая распространенная причина, которая приводит к такой необходимости – сделанные при записи ошибки. Вот как можно отредактировать макрос:

    1. Нажимаем кнопку “Макросы” (или комбинацию Ctrl+F8).
    2. В появившемся окошке выбираем наш макрос и щелкаем “Изменить”.
    3. На экране отобразится окно редактора “Microsoft Visual Basic”, в котором мы можем внести правки. Структура каждого макроса следующая:
      • открывается с команды “Sub”, закрывается – “End Sub”;
      • после “Sub” отображается имя макроса;
      • далее указано описание (если оно есть) и назначенная комбинация клавиш;
      • команда “Range(“…”).Select” возвращает номер ячейки. К примеру, “Range(“B2″).Select” отбирает ячейку B2.
      • В строке “ActiveCell.FormulaR1C1” указывается значение ячейки или действие в формуле.
    4. Давайте попробуем скорректировать макрос, а именно, добавить в него ячейку B4 со значением 3. В код макроса нужно добавить следующие строки:
      Range(«B4»).Select
      ActiveCell.FormulaR1C1 = «3»
    5. Для результирующей ячейки D2, соответственно, тоже нужно изменить начальное выражение на следующее:
      ActiveCell.FormulaR1C1 = «=RC[-2]*R[1]C[-2]*R[2]C[-2]» .Примечание: Обратите внимание, что адреса ячеек в данной строке (ActiveCell.FormulaR1C1) пишутся в стиле R1C1.
    6. Когда все готово, редактор можно закрывать (просто щелкаем на крестик в правом верхнем углу окна).
    7. Запускаем выполнение измененного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением “3”), а также, пересчитан результат с учетом измененной формулы.
    8. Если мы имеем дело с большим макросом, на выполнение которого может потребоваться немало времени, ручное редактирование изменений поможет быстрее справиться с задачей.
      • Добавив в конце команду Application.ScreenUpdating = False мы можем ускорить работу, так как во время выполнения макроса, изменения на экране отображаться не будут.
      • Если потребуется снова вернуть отображение на экране, пишем команду: Application.ScreenUpdating = True .
    9. Чтобы не нагружать программу пересчетом после каждого внесенного изменения, в самом начале пишем команду Application.Calculation = xlCalculationManual , а в конце – Application.Calculation = xlCalculationAutomatic . Теперь вычисление будет выполняться только один раз.

    Метод 2: создание макроса вручную

    Опытные пользователи Эксель в некоторых случаях предпочитают создавать макросы от начала до конца полностью вручную . План действий следующий:

    Читать еще:  Как сделать таблицу расписания уроков в excel?

    1. Во вкладке “Разработчик” нажимаем на значком “Visual Basic” (группа инструментов “Код”).
    2. В результате, на экране появится уже знакомое окно, которое мы рассмотрели выше.
    3. Здесь создается макрос и пишется ее код – полностью вручную.

    Заключение

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

    Как записать макросы в Microsoft Excel (Краткое руководство)

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

    При записи макроса средство записи макросов записывает все шаги в коде Visual Basic для приложений (VBA). Эти шаги могут включать в себя ввод текста или чисел, щелчок ячеек или команд на ленте или меню, форматирование ячеек, строк или столбцов или даже импорт данных из внешнего источника, такого как Microsoft Access. Приложение Visual Basic (VBA) является подмножеством мощного языка программирования Visual Basic и включено в большинство приложений Office. Хотя VBA дает вам возможность автоматизировать процессы внутри и между приложениями Office, вам не нужно знать код VBA или компьютерное программирование, если макрос устройства записи выполняет то, что вы хотите.

    Важно знать, что когда вы записываете макрос, Macro Recorder записывает почти каждое ваше движение. Поэтому, если вы допустили ошибку в своей последовательности, например, если вы нажали на кнопку, которую не хотели нажимать, Macro Recorder запишет ее. Разрешение состоит в перезаписи всей последовательности или изменении самого кода VBA. Поэтому всегда лучше записать что-то, записать процесс, который вы хорошо знаете. Чем плавнее вы записываете последовательность, тем эффективнее макрос будет во время воспроизведения.

    Макросы и инструменты VBA можно найти на вкладке «Разработчик», которая по умолчанию скрыта, поэтому сначала необходимо включить ее.

    Что может Excel сделать без макросов?

    Excel может делать все удивительные вещи без макросов. Откройте для себя мощные встроенные функции Excel, такие как

    • Условное форматирование
    • проверка достоверности данных
    • Сводные таблицы
    • Автофильтр и расширенные фильтры

    Исправление обновления февраля 2020 года:

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

    • Шаг 1: Скачать PC Repair & Optimizer Tool (Windows 10, 8, 7, XP, Vista — Microsoft Gold Certified).
    • Шаг 2: Нажмите «Начать сканирование”, Чтобы найти проблемы реестра Windows, которые могут вызывать проблемы с ПК.
    • Шаг 3: Нажмите «Починить все», Чтобы исправить все проблемы.


    (дополнительное предложение для Advanced System Repair Pro -> Cайт | Лицензионное соглашение | Политика Kонфиденциальности | Удалить)

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

    В этом подробном руководстве я расскажу обо всем, что вам нужно знать, чтобы начать запись и использование макросов в Excel.

    Как записать макрос в Excel Инструкции

    • Чтобы записать макрос в Excel, перейдите на вкладку «Вид» панели команд ленты.
    • Затем нажмите раскрывающуюся кнопку «Макросы» в группе кнопок «Макросы».
    • Затем выберите команду «Запись макроса…», чтобы открыть диалоговое окно «Запись макроса».
    • В диалоговом окне «Запись макроса» введите имя нового макроса в текстовом поле «Имя макроса».
    • Чтобы создать настраиваемое сочетание клавиш вместе с клавишей «Ctrl», введите нужную букву сочетания клавиш в текстовом поле рядом с именем «Ctrl +». Если вы решили сделать это, будьте осторожны, чтобы не перезаписать существующий ярлык!
    • Выберите имя рабочей книги, к которой должен быть прикреплен макрос, в раскрывающемся списке «Сохранить макрос в:».
    • Чтобы записать действия вашего макроса, нажмите кнопку «ОК» и затем выполните действия, которые нужно записать.
    • Чтобы использовать относительную ссылку на ячейку при записи макроса, щелкните раскрывающуюся кнопку «Макросы» в группе «Макросы».
    • Затем выберите команду «Использовать относительную ссылку».
    • Чтобы вернуться к абсолютной ссылке на ячейку при записи макроса, нажмите раскрывающуюся кнопку «Макросы» в группе «Макросы».
    • Затем снова выберите команду «Использовать относительные ссылки», чтобы отключить ее.
    • Чтобы остановить запись макроса, щелкните вкладку «Отображение ленты».
    • Затем нажмите раскрывающуюся кнопку «Макросы» в группе кнопок «Макросы».
    • Затем выберите команду «Остановить запись».

    Даже если вы совершенно новичок в мире Excel VBA, вы можете легко записать макрос и автоматизировать некоторые из ваших работ.

    CCNA, веб-разработчик, ПК для устранения неполадок

    Я компьютерный энтузиаст и практикующий ИТ-специалист. У меня за плечами многолетний опыт работы в области компьютерного программирования, устранения неисправностей и ремонта оборудования. Я специализируюсь на веб-разработке и дизайне баз данных. У меня также есть сертификат CCNA для проектирования сетей и устранения неполадок.

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