Самоучитель по макросам в Excel. Азы VBA

Благодаря использованию макросов в excel, т.е. языка программирования Visual Basic For Application (сокращенно VBA), легко можно автоматизировать однообразную работу. Данная мини программа выполнит за вас рутинную работу за долю секунды. Ниже приведены азы работы с vba, своего рода самоучитель по макросам в excel’е, освоив и, поняв которые, легко можно написать макрос самостоятельно.

Использование макросов является предпочтительным по причине решения большинства рутинных задач в процессе трудовой деятельности. А, как известно, работодатель старается защитить свою информационную сеть закрывая установку и использование стороннего программного обеспечения, за исключением Microsoft Office, частью которой и является VBA. Так что, используя макросы мы упрощаем себе жизнь и не нарушаем внутренние нормативные документы Компании.

Содержание:

  1. Быстрое перемещение со станицы на страницу
  2. Запуск макроса в excel
  3. Как разрешить использование макросов?
  4. Как вызвать редактор макросов?
  5. Процедура
  6. Функция
  7. Переменные
  8. Получить значение из ячейки / записать значение в ячейку
  9. Оператор With

Быстрое перемещение со станицы на страницу

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

'' функция перемещения к листу книги
Function go_list(book As String, list As String)
Workbooks(book).Worksheets(list).Select'' сделать лист активным
Workbooks(book).Worksheets(list).Range("A1").Select'' переместиться в начало листа
End Function

Функции достаточно передать полное наименование книги под аргументом book и наименование листа под аргументом list. На итог при выполнении функции произойдет перемещение в начало листа list книги book.

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

В excel запуск макроса vba осуществляется путем нажатия клавиши «F5» в окне редактора кода.

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

1. При открытии документа

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

Private Sub Worksheet_Activate()
'' код макроса или ссылка на функцию
End Sub

2. При нажатии на ячейку

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 2 And Cells(ActiveCell.Row, ActiveCell.Column).Value = "Обновить" And ActiveCell.Row = 3 Then
'' код макроса или ссылка на функцию
End If
End Sub

В приведенном примере стоит ограничительное условие на срабатывание макроса в случае нажатия на третью строку второго столбца (т.е. на ячейку «B3») если в ней указано «Обновить».

ActiveCell.Row '' номер строки
ActiveCell.Column '' номер столбца

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

образец кнопки запуска макроса vba
Пример кнопки выполнения макроса

Как разрешить использование макросов?

Зачастую на персональных компьютерах по умолчанию запрещено, либо ограничено использование макросов. Чтобы это исправить и разрешить их выполнение - нужно зайти в «Файл» -> «Параметры» -> «Центр управления безопасностью» -> «Параметры управления центром безопасности…» -> «Параметры макросов» -> поставить галочку напротив «Включить все макросы» -> нажать «Ок» -> закрыть и заново открыть книгу.




разрешить использование макросов
Включить макросы Excel

Как вызвать редактор макросов?

Чтобы открыть редактор макросов – достаточно в любом активном экселевском окне нажать сочетание клавиш «Alt» + «F11». В результате появится окно следующего вида:

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

Основные элементы управления расположены в отмеченных красным цветом областях. Область №1 – используется для запуска и остановки макроса. №2 – выбор книги excel и листа, к которому необходимо применить макрос. №3 служит для редактирования созданных кнопок, текста, форм и т.п. И №4 располагается сам код макроса vba.

Процедура

Процедура в vba используется для вызова макроса в случае выполнения какого-нибудь действия пользователем, например запуск книги Worksheet_Activate, внесение изменений в ячейку Worksheet_Change, нажатие на ячейку Worksheet_SelectionChange и т.п.

Пример процедуры:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

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

Процедура vba
Формирование процедуры vba

Функция

Функция в отличие от процедуры предназначена для расположения в ней часто применяемого кода и возможности его вызова в любом месте путем написания названия функции.

Пример функции:

Function new_function(argument1, argument2, …)
End Function

Функция состоит из самого наименования, в приведенном примере new_function. И аргументов argument1, argument2. Аргументы являются не обязательными, их количество может быть от нуля до необходимого количества. Аргументы функции предназначены для передачи функции необходимых значений, например значения, полученного после вызова другой функции или введенного пользователем.

Переменные

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

Синтаксис:

Dim Имя_переменной As Тип_данных

Пример переменной:

Dim Age As Integer

Переменная состоит из наименования Age, которое может называться как вам нравится, например, peremennaya, kak_hochu, super, и т.п. А также указывается тип данных, в приведенном примере Integer, т.е. переменная может хранить любое число от -32768 до 32767. Тип данных необходимо объявлять для уменьшения нагрузки на компьютер (чтобы код занимал меньше оперативной памяти) и соответственно более быстрого выполнения макроса. Типы данных, возможность хранения и занимаемая ими оперативная память приведена в таблице:

Тип данных
Типы данных с значениями

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

Age = 18

Т.е. переменной Age присвоено значение 18. Чтобы его получить – достаточно указать имя переменной:

MsgBox Age '' функция MgsBox выводит вспрывающее окно Excel с указанным значением Age

При выполнении данного кода получим значение «18», присвоенное переменной Age ранее:

Значение переменной Age

Получить значение из ячейки / записать значение в ячейку

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

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

Age = Range("A1")

Где переменной Age присваивается значение, записанное в ячейке «A1» активного листа. Чтобы получить значение, записанное в ячейке «A1» определенного листа книги в независимости от его активности, нужно использовать следующий код:

Age = Worksheets("Название_листа").Range("A1")

Чтобы получить значение, записанное в ячейке «A1» из другой (по отношению к коду vba) книги необходимо сделать так:

Age = Workbooks("Имя_книги.xlsm").Worksheets("Название_листа").Range("A1")

Поменяв код на противоположный, можно записать значение переменной Age в ячейку «A1»:

Workbooks("Имя_книги.xlsm").Worksheets("Название_листа").Range("A1") = Age
Worksheets("Название_листа").Range("A1") = Age
Range("A1") = Age

Оператор With

Оператор With предназначен для изменения разных свойств одного элемента.

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

Range("A1").Value = "123"'' записать значение «текст» в ячейку «A1»
Range("A1").Font.Color = -11480942'' изменить цвет текста ячейки «A1» на зеленый
Range("A1").Interior.ThemeColor = xlThemeColorLight1'' изменить заливку ячейки «A1» на черный

Как видно используется одна и та же ссылка Range("A1") на ячейку «A1», что затрудняеет как само восприятие кода, так и его создание. Но используя, оператор With, код можно значительно упростить:

With Range("A1")
.Value = "текст"
.Font.Color = -11480942
.Interior.ThemeColor = xlThemeColorLight1
End With

Т.е. ссылку на ячейку «A1» указываем единожды, а после перечисляем те свойства, которые хотим изменить, после закрываем оператор With, фразой End With.

Поделись с друзьями:
Комментарии 2

Мария Аверина
11 сентября 2020 в 06:23
Углубленное изучение excel даётся тяжело, но вот использование макросов вроде освоила успешно. На практике применяю )
© планеро.ru