Что могут макросы в эксель

19.09.202219.09.2022 admin 0 Comments

Канал в Telegram

Вы здесь

Для чего нужны макросы в Excel?

Многие из Вас наверняка слышали термин «Макрос». Иногда при запуске некоторых документов Word, а чаще Excel, Вам выскакивало предупреждение: «Данный документ содержит макросы, которые могут нанести вред Вашему компьютеру. С целью безопасности макросы были отключены» и т.д. И многих это предупреждение пугает, хотя большая доля правды в этом предупреждении все ж присутствует. Но, это относится к документам, попавшим к Вам из неизвестных источников, а именно из сети, по почте и т.д. Чем же так опасен макрос и для чего они тогда нужны, если их использование небезопасно? Рассмотрим далее, а пока дадим определение макросу.

Что такое «Макрос»?

Как работают макросы?

Благодаря внедрению в MS Office языка Visual Basic, Microsoft удалось значительно расширить функционал своих продуктов. Макрокоманды, записанные пользователями или написанные программистами, способны автоматизировать большинство процессов и значительно сократить время на обработку данных. Тем самым, оптимизировать рабочее время сотрудников. Для выполнения макрокоманд необходима своя программная среда, в которой эти команды способны работать. Такой средой являются, например Excel, Word, Access. При написании сложных макросов, их запуск и выполнение, как правило, ограничивается рабочей станцией (ПК) на которой он был написан. Сложные, имеется ввиду, подключение дополнительных компонентов и объектов, которые попросту могут отсутствовать на другом ПК. Происходит это потому, что программный код не переводится в исполняемый машинный код и вдобавок к которому прикладываются необходимые модули и библиотеки, а выполняется через интерпретатор. Но, есть и преимущества в таком подходе, а именно, возможность произвести отладку сразу, «на месте», при возникновении каких либо ошибок, просмотреть код и дополнить его новыми инструкциями, расширив функционал макроса. Как правило, макросы написанные для Excel, не работают в Word или Access, только если не используется базовый набор процедур и функций, которые не затрагивают объекты приложения.

Как макросы помогают в работе?

При работе с большими объемами данных, которые необходимо тем, или иным образом обрабатывать, а так же при выполнении однообразных действий по обработке информации в таблицах (например, формирование сводных таблиц определенного формата), макросы незаменимы. Пример: Вам ежедневно необходимо получать данные из БД, производить с этими данными некоторые расчеты, затем из результатов свести сводную таблицу. На все у Вас уходит, допустим, 2 часа. Разработав и внедрив такой макрос, который будет все эти действия производить самостоятельно, у Вас время сократиться до пары минут, а точнее секунд т.к. остальное уйдет на открытие и получение данных. А если еще к расчетам «вручную» добавить допущенные механические ошибки, которые обязательно бываю, и которые в лучшем случае Вы обнаружите и будете искать, то это еще добавит время к тем двум часам. Макрос же при грамотной разработке и хорошо протестированный, позволяет избежать таких ошибок.

Это один из многих вариантов применения. Другой пример, на личном опыте. При работе с ежедневными накопительными данными, мне приходилось заполнять поле «Контрагент» (КА) соответствующим именем, имеющимся в базе. Название КА должно обязательно быть идентичным. Создание списка очень неудобно т.к. список состоит не из одной сотни КА. Я написал макрос, который запускается на исполнение, при нажатии в соответствующей колонке, правой кнопкой мыши. Открывается форма, со списком КА в которой также имеется «горячий поиск» в списке т.е. достаточно начать вводить сочетание символов содержащихся в названии и список тут же отфильтрует и оставит подходящих, далее остается выбрать из двух трех КА нужного. На все про все, не больше 5 сек. Вот как вариант. И примеров можно приводить кучу, в зависимости от специфики работы.

В чем заключается опасность макросов?

Варианты защиты от таких макросов:

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

P. S. Используя смекалку и фантазию на VBA можно написать простейшие и интересные игры. Если Вас заинтересовал такой вариант применения, то можете ознакомиться с играми, написанными для Excel (игры в Excel).

Источник

Работа с макросами Excel для чайников

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

Мы приведем некоторые примеры макросов Excel, которые станут верными помощниками в выполнении наиболее частых задач.

Понятие макроса

Термин «Макрос» слышало множество людей. Нередко при запуске таблицы появляется предупреждение: “Этот документ использует макросы, способные навредить данному компьютеру, поэтому они отключены с целью защиты от вредоносных действий”.

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

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

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

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

Макросы можно запускать по нажатию определенной комбинации клавиш. Например, если нажать Ctrl+J, можно запустить подпрограмму.

Интересный факт: известная программа бухгалтерского учета 1C изначально очень напоминала Excel, но потом ее функционал расширился до текущего.

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

Когда какой тип записи макросов использовать?

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

Если же необходимо программировать сложные действия, то тогда придется пользоваться встроенной средой VBA. Например, если необходимо записать в массив все элементы какого-то диапазона значений, определить его длительность, и при условии, что количество элементов массива не превышает определенного числа, выдавать какое-то сообщение. Здесь стандартного инструмента для записи макросов окажется недостаточно, необходимо изучать язык программирования и записывать команды в специальной среде. А интерпретатор в дальнейшем будет выполнять написанный код.

Пример использования макросов №1

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

‘ Подпрограмма для поиска ячеек с адресами A1-A100 текущего активного листа

‘ и поиска ячеек, в которых содержится требуемая строка

Sub Find_String(sFindText As String)

Dim i As Integer ‘ Целочисленная переменная, которая используется в цикле типа «For»

Dim iRowNumber As Integer ‘ Целочисленная переменная, предназначенная для сохранения результата

‘ Цикл через ячейки A1-A100 до тех пор, пока не будет найдена строка ‘sFindText’

If Cells(i, 1).Value = sFindText Then

‘ Совпадение обнаружено для заданной строки

‘ Сохранение текущего номера строки и выход из цикла

‘ Всплывающее сообщение, информирующее пользователя о найденной строке и ее номере

If iRowNumber = 0 Then

MsgBox «String » & sFindText & » not found»

MsgBox «String » & sFindText & » found in cell A» & iRowNumber

Пример 2

Эта процедура перечисляет все значения числовой последовательности Фибоначчи, вплоть до 1000. В этом примере приводятся следующие возможности макросов Excel:

‘ Подпрограмма для перечисления всех значений последовательности Fibonacci для всех значений ниже тысячи

Dim i As Integer ‘ счетчик для позиции в серии значений

Dim iFib As Integer ‘ сохраняет текущее значение в серии

Dim iFib_Next As Integer ‘ сохраняет следующее значение в серии

Dim iStep As Integer ‘ хранит размер следующего шага

‘ Инициализация переменных variables i и iFib_Next

‘ Цикл Do While, который исполняется до тех пор, пока номер

‘ числа в последовательности Фибоначчи меньше 1000.

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

‘ Подпрограмма, которая хранит значения колонки А текущего листа

Dim iRow As Integer ‘ сохраняется текущий номер строки

Dim dCellValues() As Double ‘ массив, в котором хранятся значения ячеек

ReDim dCellValues(1 To 10)

‘ Цикл Do Until, который извлекает значение каждой ячейки в столбце А

‘ активного листа до тех пор, пока ячейка не окажется пустой

Do Until IsEmpty(Cells(iRow, 1))

‘ Проверка, достаточно ли большой массив dCellValues

‘ Если нет, используется ReDim, чтобы увеличить размер массива на 10 элементов.

If UBound(dCellValues) Следующая процедура «Sub» читает содержимое ячеек из колонки А другого листа, имеющего название «Sheet2» и с этими значениями осуществляет арифметические операции. Результат вычислений пишется в колонке А текущего листа.

Этот пример показывает:

‘ Подпрограмма, запускающая цикл через значения в колонке А текущего листа

‘ «Sheet2», perform arithmetic operations on each value, and write the

‘ result into Column A of the current Active Worksheet («Sheet1»)

Dim dVal As Double

‘ Установить переменную Col в колонку А листа 2

Set Col = Sheets(«Sheet2»).Columns(«A»)

‘ Прохождение цикла через каждую ячейку колонки ‘Col’ до тех пор, пока

‘ не будет обнаружена пустая ячейка

Do Until IsEmpty(Col.Cells(i))

‘ Применение арифметических операций к значению текущей ячейки

dVal = Col.Cells(i).Value * 3 — 1

‘ Команда ниже копирует результат в колонку А

‘ текущего активного листа — без уточнения названия активного листа

Пример 5

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

‘ Код для отображения диалогового окна ячейки B1 текущего листа в случае, если она выбрана

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘ Проверка, выбрана ли ячейка B1

If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then

‘ При выборе ячейки B1 показать диалоговое окно

MsgBox «You have selected cell B1»

Пример 6

Следующая подпрограмма демонстрирует, как обрабатывать ошибки с помощью операторов OnError и Resume. Также в этом коде описывается, как открывать и читать данные с файла.

‘ Подпрограмма, для для установки определенных значений

‘ в ячейках A1 и B1 документа «Data.xls» на диске C:\

Sub Set_Values(Val1 As Double, Val2 As Double)

Dim DataWorkbook As Workbook

On Error GoTo ErrorHandling

‘ Открытие документа с данными

Set DataWorkbook = Workbooks.Open(«C:\Documents and Settings\Data»)

‘ Выбрать переменные Val1 and Val2 с данных в книге Excel

Val1 = Sheets(«Sheet1»).Cells(1, 1)

Val2 = Sheets(«Sheet1»).Cells(1, 2)

‘ Если файл не найден, предложить пользователю найти правильную директорию

‘ после чего продолжить выполнение подпрограммы

MsgBox «Data Workbook not found;» & _

«Please add the workbook to C:\Documents and Settings and click OK»

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

Выводы

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

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

Источник

Автоматизация задач с помощью средства записи макросов — Excel

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

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

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

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

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

Перед записью макросов полезно знать следующее:

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

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

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

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

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

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

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

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+ SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции «Отменить» в данном экземпляре Excel.

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

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

Чтобы начать запись макроса, нажмите кнопку ОК.

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

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

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Сведения о параметрах безопасности макросов и их значении.

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

С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

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

В поле Назначить макроса выберите макрос, который вы хотите назначить.

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

Вы можете назначать макросы формам и элементам ActiveX на листе.

Узнайте, как включать и отключать макросы в файлах Office.

Открытие редактора Visual Basic

Нажмите клавиши ALT+F11.

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.

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

Перед записью макросов полезно знать следующее:

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

Перейдите в Excel > параметры. > ленты & панель инструментов.

В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

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

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

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Как правило, макрос сохраняется в расположении Эта книга, но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите Личная книга макроса. При выборе личнойкниги макроса Excel создает скрытую личную книгу макроса (PERSONAL.XLSB), если она еще не существует, и сохраняет макрос в этой книге. Книги в этой папке открываются автоматически при Excel, и любой код, сохраненный в личной книге макроса, будет указан в диалоговом окну Макрос, которое объясняется в следующем разделе.

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

В поле Описание при необходимости введите краткое описание действий макроса.

Хотя поле «Описание» является необязательным, рекомендуется его заполнить. Полезно ввести содержательное описание со всеми сведениями, которые могут быть полезны вам или другим пользователям, которые будут запускать макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

Чтобы начать запись макроса, нажмите кнопку ОК.

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

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

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. При этом откроется диалоговое окно Макрос.

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Узнайте, как включать и отключать макросы в Excel для Mac.