Благодаря использованию макросов в excel, т.е. языка программирования Visual Basic For Application (сокращенно VBA), легко можно автоматизировать однообразную работу. Данная мини программа выполнит за вас рутинную работу за долю секунды. Ниже приведены азы работы с vba, своего рода самоучитель по макросам в excel’е, освоив и, поняв которые, легко можно написать макрос самостоятельно.
Использование макросов является предпочтительным по причине решения большинства рутинных задач в процессе трудовой деятельности. А, как известно, работодатель старается защитить свою информационную сеть закрывая установку и использование стороннего программного обеспечения, за исключением Microsoft Office, частью которой и является VBA. Так что, используя макросы мы упрощаем себе жизнь и не нарушаем внутренние нормативные документы Компании.
Содержание:
Для перемещения на нужный лист книги необходимо реализовать небольшую функцию 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 запуск макроса vba осуществляется путем нажатия клавиши «F5» в окне редактора кода.
Как правило, такой метод используется разработчиком с целью отладки. А как запустить макрос в excel обычному пользователю, не заходя в редактор? Макрос можно запустить различными способами:
Чтобы выполнить макрос непосредственно после открытия документа, необходимо поместить его код или ссылку на функцию в следующую процедуру:
Private Sub Worksheet_Activate()
'' код макроса или ссылка на функцию
End Sub
Чтобы выполнить макрос после нажатия на ячейку необходимо воспользоваться следующим кодом:
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 '' номер столбца
При этом у вас есть возможность поместить в выбранную ячейку любой текст и стиль. Это позволит сделать уникальную кнопку на выполнения макроса. Например:
Зачастую на персональных компьютерах по умолчанию запрещено, либо ограничено использование макросов. Чтобы это исправить и разрешить их выполнение - нужно зайти в «Файл» -> «Параметры» -> «Центр управления безопасностью» -> «Параметры управления центром безопасности…» -> «Параметры макросов» -> поставить галочку напротив «Включить все макросы» -> нажать «Ок» -> закрыть и заново открыть книгу.
Чтобы открыть редактор макросов – достаточно в любом активном экселевском окне нажать сочетание клавиш «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 и достаточно лишь выбрать лист экселевской книги, в котором необходимо выполнить процедуру. После во всплывающем списке выбрать элемент и действие, которое должно вызвать выполнение макроса. После вышеперечисленных манипуляций наименование процедуры автоматически сформируется.
Функция в отличие от процедуры предназначена для расположения в ней часто применяемого кода и возможности его вызова в любом месте путем написания названия функции.
Пример функции:
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 = 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 предназначен для изменения разных свойств одного элемента.
Например, необходимо в ячейку «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.
