Как сделать код в экселе

Обновлено: 04.07.2024

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

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

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

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

Создать макрос в Excel с помощью макрорекордера

Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.

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

Этот способ очень полезен тем, кто не владеет навыками и знаниями работы в языковой среде VBA. Но такая легкость в исполнении и записи макроса имеет свои минусы, как и плюсы:

Записать макрорекордер может только то, что может пощупать, а значит записывать действия он может только в том случае, когда используются кнопки, иконки, команды меню и всё в этом духе, такие варианты как сортировка по цвету для него недоступна;
В случае, когда в период записи была допущена ошибка, она также запишется. Но можно кнопкой отмены последнего действия, стереть последнюю команду которую вы неправильно записали на VBA;
Запись в макрорекордере проводится только в границах окна MS Excel и в случае, когда вы закроете программу или включите другую, запись будет остановлена и перестанет выполняться.

Для включения макрорекордера на запись необходимо произвести следующие действия:

в версии Excel от 2007 и к более новым вам нужно на вкладке «Разработчик» нажать кнопочку «Запись макроса»
в версиях Excel от 2003 и к более старым (они еще очень часто используются) вам нужно в меню «Сервис» выбрать пункт «Макрос» и нажать кнопку «Начать запись».

Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса», где:

поле «Имя макроса» — можете прописать понятное вам имя на любом языке, но должно начинаться с буквы и не содержать в себе знаком препинания и пробелы;
поле «Сочетание клавиш» — будет вами использоваться, в дальнейшем, для быстрого старта вашего макроса. В случае, когда вам нужно будет прописать новое сочетание горячих клавиш , то эта возможность будет доступна в меню «Сервис» — «Макрос» — «Макросы» — «Выполнить» или же на вкладке «Разработчик» нажав кнопочку «Макросы» Sub MyMakros()
Dim polzovatel As String
Dim data_segodnya As Date
polzovatel = Application.UserName
data_segodnya = Now
MsgBox "Макрос запустил пользователь: " & polzovatel & vbNewLine & data_segodnya
End Sub

Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.

Настройка разрешения для использования макросов в Excel

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

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

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

Подробнее: Включение и отключение макросов в Microsoft Excel

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

«Разработчик»

«Запись макроса»

«Код»

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

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

«Код»

«Макросы»

Alt + F8

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

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

«Макросы»

«Изменить»

Создание кнопки для запуска макросов в панели инструментов

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

Кнопка в панели инструментов в MS Excel 2003 и более старше. Вам нужно в меню «Сервис» в пункте «Настройки» перейти на доступную вкладку «Команды» и в окне «Категории» выбрать команду «Настраиваемая кнопка» обозначена жёлтым колобком или смайликом, кому как понятней или удобней. Вытащите эту кнопку на свою панель задач и, нажав правую кнопку мыши по кнопке, вызовите ее контекстное меню, в котором вы сможете отредактировать под свои задачи кнопку, указав для нее новую иконку, имя и назначив нужный макрос.
Кнопка в панели вашего быстрого доступа в MS Excel 2007 и более новее. Вам нужно клацнуть правой кнопкой мышки на панели быстрого доступа , которое находится в верхнем левом углу окна MS Excel и в открывшемся контекстном меню выбираете пункт «Настройка панели быстрого доступа». В диалоговом окне настройки вы выбираете категорию «Макросы» и с помощью кнопки «Добавить» вы переносите выбранный со списка макрос в другую половинку окна для дальнейшего закрепления этой команды на вашей панели быстрого доступа.

Создание графической кнопки на листе Excel

Данный способ доступен для любой из версий MS Excel и заключается он в том, что мы вынесем кнопку прямо на наш рабочий лист как графический объект. Для этого вам нужно:

В MS Excel 2003 и более старше переходите в меню «Вид», выбираете «Панель инструментов» и нажимаете кнопку «Формы».
В MS Excel 2007 и более новее вам нужно на вкладке «Разработчик» открыть выпадающее меню «Вставить» и выбрать объект «Кнопка».

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

Чтобы записать макрос, следует:

Войти во вкладку «разработчик».
Выбрать запись макроса.
Выбрать имя макроса (в имени нельзя использовать пробелы и дефисы);
Можно выбрать сочетание клавиш, при нажатии которых будет начинаться запись макроса;
Выбрать место сохранения:

— при сохранении в «Эта книга» макрос будет работать только в текущем документе;

— при сохранении в «Личная книга» макрос будет работать во всех документах на Вашем компьютере.

Можно добавить описание макроса, оно поможет Вам вспомнить, какие действия совершает макрос.
Нажать «Ок».
Если вы не указали сочетание клавиш, запись начнется сразу после нажатия кнопки «Ок».
Когда идет запись, Вы должны совершать требуемую последовательность действий.
Когда закончите, нажимайте кнопку остановить запись.

Записанные макросы отображаются в книге макросов.

Чтобы их посмотреть следует нажать кнопку «макросы». В появившемся окне появится список макросов. Выберете нужный макрос и нажмите «Выполнить».

Макросы, находящиеся в книге можно редактировать. Для этого нужно выбрать макрос и нажать кнопку «Изменить». При нажатии на кнопку «Изменить» откроется редактор макросов с записанным на языке VBA скриптом.

Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
Нажмите «ОК».

Абсолютная и относительная запись макроса

Теперь давайте запишем макрос в режиме относительных ссылок:

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

Вот код, который записал макрорекодер:

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

Что нельзя сделать с помощью макрорекодера?

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

Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).

Редактор Visual Basic

В Excel есть встроенный редактор Visual Basic , который хранит код макроса и взаимодействует с книгой Excel. Редактор Visual Basic выделяет ошибки в синтаксисе языка программирования и предоставляет инструменты отладки для отслеживания работы и обнаружения ошибок в коде, помогая таким образом разработчику при написании кода.

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

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

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

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

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

При ведении учета в торговле, создании личных каталогов используются штрих-коды. Для их последующего считывания существуют сканеры. А создать можно с помощью программных продуктов разработанных специально для Excel.

Как сделать штрих-код в Excel

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

Самый простой способ это установить в Windows шрифты штрих кодов и тогда они буду доступны в Excel на панели "Главная"-"Шрифт".

Пару шрифтов-штрихкодов можно скачать в конце статьи

Перед началом работы убедитесь, что в программе Excel доступен Visual Basic. А в настройках безопасности – сняты ограничения для макросов.

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

Еще один способ формирования штрих-кодов – написать макрос (реализовано тут).

Чтобы добавить штрих-код в Excel 2007, нужно выполнить следующую последовательность:

Нажать кнопку «Файл» - «Параметры». В основных параметрах – поставить галочку «Показывать вкладку «Разработчик» на ленте.
Вкладка «Разработчик». Находим кнопку «Вставить» - «Элементы управления ActiveX». Выбираем «Другие элементы управления».
В открывшемся меню находим StrokeScribe Objekt. Нажимаем ОК. Рисуем мышью прямоугольник в любом месте страницы.
Появляется штрих-код. Открываем меню правой кнопки мыши. «Свойства». Alphabet – для выбора типа штрих-кода.

Чтобы нарисованный код «заработал» (адаптировался под введенные пользователем цифры), нужно писать макрос.

Как подключить сканер штрих-кода к Excel

Процесс подключения зависит от модели сканера и его возможностей. Иногда ничего делать не нужно. Достаточно подключить сканер к компьютеру, используя включение «разъем клавиатуры», и открыть во время работы книгу Excel (должен быть установлен USB-драйвер сканера).

Сканер не воспринимает штрих-код как графический объект – он преобразует его в набор цифр. Во время работы в активную ячейку вставляется числовое значение, считываемое устройством, и срабатывает перевод строки. Детектор посылает символ Enter, что заставляет курсор перейти в следующую строку.

Ряд моделей сканеров работает как клавиатура. При вводе кода данные сразу попадают в активную ячейку листа.

Для ряда устройств требуется специальная надстройка: стандартное меню не справится с задачей.

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

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

В следующем примере показаны четыре типа разделов кода формата:

Формат положительных чисел

Формат отрицательных чисел

Если указан только один раздел кода формата, он будет использоваться для всех чисел. Если указаны два раздела кода формата, первый раздел кода будет использоваться для положительных чисел и нулей, а второй — для отрицательных чисел. Если требуется пропустить какой-либо раздел кода в числовом формате, необходимо добавить точку с запятой для каждого пропущенного раздела. Для объединения (или сцепления) двух значений можно использовать текстовый оператор амперсанд (&).

Создание кода пользовательского формата

На вкладке Главная щелкните Числовой формат и выберите Другие числовые форматы.

В диалоговом окне Формат ячеек в разделе Категория выберите пункт Другой.

В списке Тип выберите числовой формат, который нужно настроить.

Выбранный формат отображается вверху списка в поле Тип.

В поле Тип внесите необходимые изменения в выбранный числовой формат.

Советы по созданию кодов форматирования

Чтобы в ячейке отображался текст и числа, заключите текстовые знаки в двойные кавычки (" ") или поставьте перед отдельным символом обратную косую черту (\). Добавьте знаки в нужный раздел кода формата. Например, введите формат "Избыток "0,00 ₽;"Дефицит "-0,00 ₽, чтобы положительные значения отображались в формате "Избыток 125,74 ₽", а отрицательные — в формате "Недостаток -125,74 ₽".

Для отображения символов, перечисленных в следующей таблице, использовать кавычки не требуется:

Левая круглая скобка

Правая круглая скобка

Левая фигурная скобка

Правая фигурная скобка

Чтобы создать числовой формат, содержащий текст, который вводится в ячейке, вставьте в текстовом разделе кода числового формата символ "@" в том месте, где в ячейке должен будет отображаться введенный текст. Если в текстовом разделе числового формата отсутствует символ "@", в ячейке не будет отображаться введенный текст (будут видны только числа). Вы также можете создать числовой формат, который объединяет определенные текстовые символы с текстом, вводимым в ячейке. Для этого необходимые текстовые символы следует ввести перед символом "@", после символа "@" или и перед ним, и после него. Затем введенные текстовые символы нужно заключить в двойные кавычки (" "). Например, чтобы вставить текст перед текстом, вводимым в ячейке, введите "валовой доход для "@ в текстовом разделе кода числового формата.

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

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

Код VBA можно определить как код, который вводится в окне Visual Basic для выполнения набора инструкций или действий в Excel и предоставления результатов. С помощью кода VBA мы можем сократить время на выполнение повторяющейся задачи, не требуется большого вмешательства человека, кроме как для запуска программы. Теперь мы увидим, как открыть окно Visual Basic, как написать в нем код, как запустить и как его сохранить.

Как открыть окно Visual Basic?

Если вы хотите написать код Visual Basic, мы должны написать его в окне Visual Basic. Вопрос здесь в том, где находится окно Visual Basic в Excel и как его открыть. Ответ заключается в выборе меню « Разработчик» и в меню разработчика выберите « Visual Basic», как показано на рисунке ниже.

Который откроет пустое окно, как показано ниже.

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

Шаг 1: Нажмите на опцию файла .

Шаг 2: Появится раскрывающийся список, нажмите на вкладку « Параметры ».

Шаг 3: После того, как вы нажмете «Параметры», появится диалоговое окно, как показано ниже, и выберите параметр « Настроить ленту» .

Шаг 4: Когда мы перетаскиваем параметры настройки ленты, мы находим параметр для разработчика (Custom), нам нужно установить этот флажок, который позволит нам использовать VBA в Excel.

Еще один простой способ открыть окно Visual Basic - нажать Alt + F11.

Где написать код в Excel VBA?

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

Если мы добавим несколько листов в нашу книгу, это позволит использовать разные модули листов.

Код модуля листа: независимо от того, какой код события мы напишем в модуле листа, он будет применяться только к этому листу.

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

Модуль ThisWorkbook

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

Модуль: Обычно мы будем писать код в модулях на VBA. Нам нужно вставить модуль, щелкнув по меню «Вставка» и выбрав опцию модуля, чтобы вставить модуль.

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

Примеры написания и запуска кода в Excel VBA

Ниже приведены различные примеры написания и запуска кода с использованием кода VBA в Excel.

Вы можете скачать этот шаблон VBA Code Excel здесь - Шаблон VBA Code Excel

Пример № 1 - Макрокод с использованием обычного модуля

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

Выполните следующие шаги, чтобы написать код в Excel VBA.

Шаг 1: Любая программа в VBA начинается с дополнительного ключевого слова и имени программы с открытыми и закрытыми скобками, как показано ниже. Когда мы введем после скобок автоматически появится «End Sub».

Код:

Шаг 2: между ними мы должны написать наш код.

Код:

В приведенном выше макросе я написал пример кода, например, когда я запускаю макрос, первые три ячейки должны заполниться именем, возрастом и адресом. Cells (1, 1) .value представляет ячейку со значением row1 и column1.

Шаг 3: Мы можем запустить код, нажав F5 или щелкнув по кнопке запуска вверху, которая отмечена черным цветом.

Шаг 4: Как только мы нажмем на запуск, всплывающее окно попросит выполнить запуск, как показано ниже. Если доступно несколько макросов, необходимо выбрать правильный макрос и нажать кнопку запуска.

Шаг 5: перейдите в Excel и проверьте, появляются ли результаты, как показано ниже.

Пример №2 - Присоединение макроса к фигуре

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

Шаг 1: Перейдите на «Вставка» и выберите фигуру по вашему желанию.

Шаг 2: После выбора формы нарисуйте это на своем рабочем листе.

Шаг 3: Теперь мы можем написать текст, например, «нажмите здесь» или «запустить макрос» в этой форме. Для добавления текста просто щелкните правой кнопкой мыши на фигуре и выберите Редактировать текст

Шаг 4: Добавьте слово согласно вашему желанию. Я добавил слово « Run Macro ».

Шаг 5: Теперь нам нужно назначить макрос для этой фигуры, для этого выберите фигуру, щелкните правой кнопкой мыши и выберите « Назначить макрос ».

Шаг 6: появится всплывающее окно с запросом выбора макроса. Выберите имя макроса и нажмите кнопку ОК.

Шаг 7: Теперь, если мы нажмем на форму, макрос запустится и выдаст результат, как показано ниже.

Как сохранить макрокоманду?

Мы все знаем, как сохранить рабочую книгу. Сохранение макроса также не изменилось, но есть одно небольшое изменение. У нас есть два разных способа сохранить наши макро-файлы.

Книга с поддержкой макросов
Макрос включен шаблон

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

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

То, что нужно запомнить

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