Макрос в «Эксель» — небольшая программка (скрипт) написанная на языке VBA (Visual Basic for Applications) разновидности языка Basic (Бейсик).
В макросах, как правило, прописывают последовательность действий с данными в таблице Excel.
Это очень удобно, если Вам приходится по многу раз выполнять одну и ту же последовательность операций с данными. Достаточно один раз записать макро и запускать его каждый раз, когда хотите выполнить нужные действия. При запуске макроса программа сама выполнит все нужные расчеты и обработает информацию в таблице.
Создавать макросы в Excel можно двумя способами:
Самый простой способ «Запись макроса», его и рассмотрим.
Для начала необходимо включить вкладку «Разработчик», если она не включена.
Необходимо кликнуть правой кнопкой мыши на панели инструментов, выбрать «настройка ленты», в настройках ленты поставить галочку напротив панели «Разработчик» и нажать «ОК».
После того, как включили панель разработчика можно начинать запись макроса.
— при сохранении в «Эта книга» макрос будет работать только в текущем документе;
— при сохранении в «Личная книга» макрос будет работать во всех документах на Вашем компьютере.
Записанные макросы отображаются в книге макросов.
Чтобы их посмотреть следует нажать кнопку «макросы». В появившемся окне появится список макросов. Выберете нужный макрос и нажмите «Выполнить».
Макросы, находящиеся в книге можно редактировать. Для этого нужно выбрать макрос и нажать кнопку «Изменить». При нажатии на кнопку «Изменить» откроется редактор макросов с записанным на языке VBA скриптом.
Например, продолжить его еще на несколько ячеек.
После корректировок закрываем редактор и пробуем снова запустить макрос.
Если Вы не сильны в программировании, но, при этом, Вам необходимо реализовать какую-либо задачу, выходящую за рамки стандартных функций и команд MS Excel, можно поискать решение в интернете. Решение, скорее всего, найдется в виде VBA кода, который необходимо скопировать и вставить в Вашу рабочую книгу, потом каким-то образом заставить этот код работать на Вас, в этой статье я расскажу, как это сделать.
Рассмотрим два примера:
1. Ищем и используем команду
Например, нам необходима команда, которая бы вставляла в выделенные ячейки цифру "1". Запускаем поисковик, набираем поисковую фразу, получаем результаты, начинаем просматривать, находим код примерно в таком виде:
Sub Вставка1() Dim q As Object On Error Resume Next Set q = Cells For Each q In Selection q = 1 Next q End Sub
Выделяем данный код (без нумерации строк, начиная со слова Sub ) и нажимаем Ctrl+C . Переходим в свою рабочую книгу MS Excel и нажимаем сочетание клавиш Alt+F11 , у вас откроется окно редактора VBA:
В левом окне "Project - VBA Project" выбираем (щелкаем мышкой) нашу рабочую книгу, в которую необходимо вставить макрос, например, "VBAProject (Книга2)":
В пункте меню "Insert" выбираем "Module":
В левом окне "Project - VBA Project" у вас должна появиться новая папка "Modules" и в ней новый объект "Module1":
Переходим курсором в правое "Большое" поле для ввода и нажимаем Ctrl+V , скопированный макрос вставиться в модуль:
Макросы в MS Excel можно вставлять в следующие места:
Обычно, человек, который публикует код, указывает, куда его необходимо вставить, в модуль, в лист или книгу.
Чтобы вставить код в Рабочий лист, в левом окне редактора VBA выберите соответствующий лист, щелкните по нему два раза левой кнопкой мышки, переместите курсор в правое поле ввода и вставьте код.
Для вставки кода в Книгу, выберите "ЭтаКнига":
Давайте потренируемся. Вставьте код опубликованный ниже в "Лист1".
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo a If Target = 2 Then VBA.MsgBox ("Ячейка " & Target.Address & " = 2") End If a: Exit Sub End Sub
Данный макрос выводит информационное сообщение если вы введете в любую ячейку листа "2".
Вернитесь в рабочую книгу, перейдите в "Лист1" и введите в ячейку "А1" цифру "2" и нажмите Enter , после чего у вас должно появиться следующее сообщение:
Если вы видите это сообщение, то вы все сделали правильно. Если нет, то вы вставили код куда-то не туда, повторите попытку.
При вставке кода, необходимо внимательно следить за тем, куда вы его вставляете. Это можно сделать посмотрев на то, что написано в заголовке окна редактора VBA:
2. Ищем и используем функцию
Находим в интернете VBA код функции, которая подсчитывает, например, количество слов в ячейке:
Public Function КолСловВЯчейке(Ячейка As Range) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Ячейка.Value), " ") КолСловВЯчейке = UBound(q) + 1 End Function
Копируем код, нажимаем сочетание клавиш Alt+F11 , откроется редактор VBA:
Добавляем новый модуль в свою книгу и в этот модуль вставляем скопированный код:
Закрываем редактор VBA и переходим в свою рабочую книгу. Создаем новый лист (необязательно) в ячейку A1
вводим текст "мама мыла раму". Встаем в ячейку, в которой хотим получить результат (количество слов), в меню "Формулы" нажимаем кнопку "Вставить функцию":
В открывшемся окне "Вставка функции" в поле "Категория" выбираем "Определенные пользователем"
В списке доступных функций выбираем "КолСловВЯчейке", нажимаем "ОК":
Вводим необходимые аргументы и нажимаем "ОК":
Получаем результат:
Важно:
Если вы не сохраните книгу, в которую вставили макрос как "Книгу с поддержкой макросов", все модули с макросами удаляться и вам придется, потом, повторно проделывать всю эту работу.
Если при вставке макроса в модуль у вас вместо некоторого текста стоят красные знаки "???????? "
Это значит, что у вас проблема с кодировкой, которая наблюдается с копированием Кириллического текста из некоторых браузеров. Чтобы победить эту проблему, попробуйте вставить скопированный код в пустой лист MS Excel, как "Текст в кодировке Unicode". Для этого перейдите в книгу MS Excel, выберите или создайте пустой лист, встаньте в ячейку "A1" и нажмите сочетания клавишCtrl+Alt+V . Должно будет появиться меню "Специальной вставки", выберите пункт "Текст в кодировке Unicode" и нажмите "OK".
Код должен будет вставиться в рабочий лист без знаков вопроса:
После этого, еще раз скопируйте вставленный в лист код, и вставить его уже в модуль.
Если вы не видите в редакторе VBA окна "Project - VBA Project", перейдите во вкладку меню "View" и в списке выберите пункт "Project Explorer" или нажмите сочетание клавиш Ctrl+R :
Скачать файл, используемый в видеоуроке:
{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}
Многие люди думают, что макросы в Excel – это очень трудно, но это не так. В этой статье Вы найдёте несколько интересных и простых примеров, как можно автоматизировать Ваши повседневные задачи в Excel. Макрос – это программа на Visual Basic, созданная для того, чтобы автоматизировать задачи в Microsoft Office. В своих примерах я использую Excel 2010, но с таким же успехом Вы можете использовать Excel 2007.
Доступ к записанному макросу можно получить с помощью команды View Macros (Макросы), которая находится на вкладке View (Вид) в выпадающем меню Macros (Макросы). Откроется диалоговое окно Macro (Макрос), в котором Вы сможете выбрать нужный. Дважды кликните по имени макроса, чтобы выполнить программу.
Кроме этого, Вы можете связать макрос с кнопкой. Для этого:
Примечание: Если у вас включена вкладка Developer (Разработчик), то получить доступ к элементам управления формы можно с нее. Для этого перейдите на вкладку Developer (Разработчик), нажмите на иконку Insert (Вставить) и из раскрывающегося меню выберите нужный элемент.
Не знаете, как отобразить вкладку Developer (Разработчик)? Excel 2007: жмем на кнопку Office > Excel Options (Параметры Excel) > Popular (Основные) и ставим галочку напротив опции Show Developer tab in the Ribbon (Показывать вкладку “Разработчик” на ленте). Excel 2010: жмем по вкладке File (Файл) > Options (Параметры) > Customize Ribbon (Настройка ленты) и в правом списке включаем вкладку Developer (Разработчик).
В следующем примере Вы увидите, как использовать цикл FOR . Цикл FOR позволяет нам выполнить повторение цикла с разными значениями. Давайте посмотрим, как можно заполнить числами от 1 до 5 ячейки A1:A5 .
Для этого на вкладке Developer (Разработчик) нажмите Visual Basic . Дважды кликните по объекту из списка Microsoft Excel Objects , в котором должен быть сохранён макрос. Введите вот такой код:
Sub Macro1 () For n = 1 To 5 Cells(n, 1) = n Next n End Sub
Сохраните файл. Чтобы выполнить макрос, перейдите View > Macros > View Macros (Вид > Макросы > Макросы), выберите из списка название нужного макроса и нажмите Run (Выполнить).
Следующий код отображает фразу “Hello World ” в окне сообщений Windows.
Sub MacroName() MsgBox ("Hello World!") End Sub
В следующем примере мы создаём сообщение с выбором Yes (Да) или No (Нет). Если выбрать вариант Yes (Да), то значение ячейки будет удалено.
Sub MacroName() Dim Answer As String Answer = MsgBox("Are you sure you want to delete the cell values ?", vbQuestion + vbYesNo, "Delete cell") If Answer = vbYes Then ActiveCell.ClearContents End If End Sub
Давайте проверим этот код. Выделите ячейку и запустите макрос. Вам будет показано вот такое сообщение:
Если Вы нажмёте Yes (Да), значение в выделенной ячейке будет удалено. А если No (Нет) – значение сохранится.
В Microsoft Excel Вы также можете использовать конструкцию IF . В этом коде мы будем раскрашивать ячейки в зависимости от их значения. Если значение в ячейке больше 20 , то шрифт станет красным, иначе – синим.
Sub MacroName() Dim CellValue As Integer CellValue = ActiveCell.Value If CellValue > 20 Then With Selection.Font .Color = -16776961 End With Else With Selection.Font .ThemeColor = xlThemeColorLight2 .TintAndShade = 0 End With End If End Sub
Для проверки этого кода выберем ячейку со значением больше 20 :
Когда Вы запустите макрос, цвет шрифта изменится на красный:
При выполнении второго условия шрифт станет синим:
Вы также можете использовать конструкцию Case , чтобы связать выполнение действия с появлением определённого значения в ячейке. В следующем примере отображается сообщение, содержание которого зависит от указанного возраста человека.
Sub MacroName() Dim CellValue As Integer CellValue = ActiveCell.Value Select Case CellValue Case 60 To 200 MsgBox "The person is old" Case 30 To 59 MsgBox "The person is adult" Case 18 To 29 MsgBox "The person is young" Case 0 To 17 MsgBox "The person is a child" Case Else MsgBox "Unknown age" End Select End Sub
Чтобы протестировать этот пример, Вы должны выбрать ячейку со значением и запустить макрос. Если значение выбранной ячейки, к примеру, равно 44 , то будет показано вот такое сообщение.
Всем доброго времени суток. На связи Алексей Гулынин. В данной статье я бы хотел рассказать о том, как записать макрос . В своих примерах я буду пользоваться Microsoft Excel 2007 , вы же можете пользоваться любой (желательно, что версия была не ниже моей). Зайдите в Excel, выберите Вид — Макросы — Запись макроса :
В версиях 2010 и 2013 панель может отличаться, но незначительно. Далее должно открыться вот такое диалоговое окно, которое называется "Запись макроса" :
Имя макроса задавайте в том случае, если это будет конечное решение, и вы в дальнейшем им собираетесь пользоваться. Если же вы собираетесь записать макрос для того, чтобы подсмотреть что-то (что вы забыли), тогда можно имя оставить как есть.
В имени макроса использовать запрещено: пробелы, знаки препинания, цифры в начале имени. Можно использовать: английские и русские буквы, цифры (не в начале), знак подчеркивания. Также запрещено называть макрос, имя которого будет совпадать с любым существующим объектом в Excel.
Для макроса можно задать сочетание клавиш, при нажатии на которую, он будет запускаться. На все макросы не нужно вешать горячие клавиши, нужно вешать только на те, которые мы часто будем использовать. Вообще способов запустить макрос много, как вариант, можно разместить на ленте (начиная с 2010 версии), в 2007 версии лента есть, но её нельзя редактировать, можно разместить на листе. Также макрос можно запускать по какому-нибудь событию. У горячих клавиш есть чувствительность, как к регистру, так и к раскладке
, поэтому внимательно относитесь к тому, какую горячую клавишу назначить макросу. Можете ставить большие буквы, для этого придётся нажимать Ctrl + Shift + Ваша буква
.
Если макрос сохранить в эту книгу, то он физически будет находиться в данном файле. Поэтому, когда файл закрыт, данный макрос будет недоступен. В каких случаях это оправдано делать: макрос требуется только для расчетов в этой книге и больше ни в каких других. Если данный макрос нужно будет использовать в других файлах (книгах) Excel, то данный файл придётся всегда держать открытым или постоянно открывать и закрывать, что совсем неудобно. Здесь нужно выбирать другой способ сохранения: личная книга макросов .
Это почти обычный файл, и называется он Personal.xlsb
. Этот файл находится в папке XLStart
, и открывается он в скрытом режиме. Пока мы в этот файл ничего не записали — его не существует. Как только что-то запишем — он появится. Всё что находится в папке XLStart
запускается автоматически. Плюс здесь в том, что макрос, который вы записали в личную книгу будет всегда доступен (его не нужно будет постоянно открывать — закрывать). Обращаю ваше внимание на то, что он будет доступен только на данном компьютере.
Также можно к макросу добавить описание.
Давайте запишем следующий макрос, который будет создавать новый лист и в ячейку C4
будет записывать фразу Привет VBA
. Причем надпись должна быть красным цветом и стиль оформления: полужирный. Нажимаем ОК
.
Вставляем лист таким образом:
Теперь в ячейку С4 записываем фразу Привет VBA . Затем нажимаем Ctrl + Enter , чтобы остаться в этой же ячейке и применить к ней форматирование. Если мы нажмём просто Enter , то фокус перейдёт на другую ячейку (нам этого не нужно). Затем правой кнопкой щелкаем по ячейке и выбираем Формат ячеек и переходим на вкладку Шрифт , выбираем красный и цвет и делаем шрифт жирным:
Нажимаем OK . Теперь можно наш макрос завершать. Сделать это можно несколькими способами: Вид — Макросы — Остановить запись , также можно отключить в левом нижнем углу (в 2007 версии) в панели состояния (на 3 картинке видно). Был создан "Лист4". Давайте удалим его и с помощью макроса создадим новый лист. Для этого воспользуемся горячей клавишей, которую мы задали для макроса Ctrl + Shift + Ц . Убедитесь, что вы находитесь на русской раскладке. Если вы сделали всё правильно, то у вас должен был создаться новый лист с нужной нам записью.
