Эксель макросы для чайников: Запуск и создание макросов в Excel для чайников

Самоучитель по работе с макросами в Excel

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 2 мин Опубликовано 09.11.2015

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

Например, самостоятельно написанный макрос можно привязать к иконке и вывести на Ленту меню. Либо Вы можете создать пользовательскую функцию (UDF) и использовать ее точно так же, как и остальные встроенные функции Excel.

Макрос — это компьютерный код, написанный для Excel на языке программирования Visual Basic for Applications (VBA). Базовые понятия языка программирования VBA рассматриваются на нашем сайте в Учебнике по VBA. Однако прежде чем приступить к написанию кода VBA, рекомендуем познакомиться с уроками, в которых рассматривается безопасность макросов Excel и редактор Visual Basic.

Содержание

1. Настройка разрешения для использования макросов в Excel
2. Редактор Visual Basic
3. Запись макросов
4. Учебник Excel VBA

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

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

Кликните эту ссылку, чтобы узнать больше о параметрах безопасности макросов в Excel

Редактор Visual Basic

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

Кликните эту ссылку, чтобы узнать больше о редакторе Visual Basic в Excel

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

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

Кликните эту ссылку, чтобы узнать больше о записи макросов в Excel

Учебник Excel VBA

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

Кликните эту ссылку, чтобы перейти к учебнику Excel VBA

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excelfunctions.net/Writing-Excel-Macros.html
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

Работа с макросами Excel для чайников (пошаговая инструкция)

Автор Мариям Жук На чтение 10 мин Опубликовано 23. 03.2020

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

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

Содержание

1. Понятие макроса
2. Когда какой тип записи макросов использовать?
3. Пример использования макросов №1
4. Пример 2
5. Пример 3
6. Пример 4
7. Пример 5
8. Пример 6
9. Рекомендации по использованию макросов
10. Выводы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Объявление переменных.
2. Указание ссылок на ячейки Excel.
3. Применение цикла типа For.
4. Применение условного оператора.
5. Отображение оповещения.

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

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

Sub Find_String(sFindText As String)

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

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

iRowNumber = 0

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

For i = 1 To 100

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

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

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

iRowNumber = i

Exit For

End If

Next i

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

If iRowNumber = 0 Then

MsgBox «String » & sFindText & » not found»

Else

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

End If

End Sub

Пример 2

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

1. Объявление переменных.
2. Цикл Do While.
3. Ссылки на ячейки текущего листа Excel.
4. Условный оператор.

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

Sub Fibonacci()

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

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

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

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

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

i = 1

iFib_Next = 0

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

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

Do While iFib_Next < 1000

If i = 1 Then

‘ Специальный случай для первой записи в серии

iStep = 1

iFib = 0

Else

‘ Сохраняется следующий размер шага, перед перезаписью

‘ текущей записи в серии

iStep = iFib

iFib = iFib_Next

End If

‘ Печать текущего значения последовательности Фибоначчи для столбца А 

‘ текущего листа

Cells(i, 1). Value = iFib

‘ Вычисление следующего значения последовательности и увеличение

‘ маркера позиции на 1

iFib_Next = iFib + iStep

i = i + 1

Loop

End Sub

Пример 3

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

1. Как объявлять переменные.
2. Работу динамического массива.
3. Цикл Do Until.
4. Ссылки на ячейки в текущем листе Excel.
5. Встроенную функцию Ubound, которая предназначена для определения размера массива.

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

‘ в массиве

Sub GetCellValues()

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

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

iRow = 1

ReDim dCellValues(1 To 10)

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

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

Do Until IsEmpty(Cells(iRow, 1))

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

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

If UBound(dCellValues) < iRow Then

ReDim Preserve dCellValues(1 To iRow + 9)

End If

‘ Сохраняется текущая ячейка в массиве CellValues

dCellValues(iRow) = Cells(iRow, 1).Value

iRow = iRow + 1

Loop

End Sub

Пример 4

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

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

1. Как объявлять переменные.
2. Объекты Excel.
3. Цикл Do Until. 
4. Доступ к листам электронных таблиц и диапазонам ячеек с текущей книги.

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

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

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

Sub Transfer_ColA()

Dim i As Integer

Dim Col As Range

Dim dVal As Double

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

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

i = 1

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

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

Do Until IsEmpty(Col.Cells(i))

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

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

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

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

Cells(i, 1) = dVal

i = i + 1

Loop

End Sub

Пример 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»

End If

End Sub

Пример 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)

DataWorkbook. Close

Exit Sub

ErrorHandling:

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

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

MsgBox «Data Workbook not found;» & _

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

Resume

End Sub

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

Рекомендации по использованию макросов

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

1. Перед тем, как записывать макрос с помощью рекордера, следует заранее продумать все свои действия, поскольку автоматизироваться будут все действия (в том числе, и ошибочные).
2. Не стоит торопиться, поскольку паузы при записи макросов не учитываются. Вполне можно начать продумывать по ходу какие-то действия. А все записанные операции будут обработаны в один момент.
3. Обязательно необходимо научиться использовать режим отладки макроса. Если возникают какие-то ошибки, он поможет обнаружить, в чем причина неполадки. На первых порах без ошибок не обойтись, потому что в реальной программе будет все не так идеально, как может показаться на первый взгляд.
4. Перед использованием макросов, сделанных другими людьми, нужно настроить антивирусную программу на их обнаружение. Как правило, эта опция установлена по умолчанию.
5. Если загружаются документы из сомнительных источников, следует выбрать опцию «Отключить макросы» при их открытии. И не рекомендуется менять настройки, которые выставлены по умолчанию в настройках безопасности Excel.

Выводы

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

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

Оцените качество статьи. Нам важно ваше мнение:

Excel Macros For Dummies Cheat Sheet

By: Michael Alexander and Dick Kusleika and

Updated: 03-01-2022

From The Book: Excel Macros For Dummies

Excel Macros For Dummies

Исследуйте книгу Купить на Amazon

Сочетания клавиш Excel позволяют выполнять определенные задачи, используя только клавиатуру. Идея заключается в том, что вы повышаете свою эффективность, ограничивая количество случаев, когда ваши руки должны перемещаться взад и вперед от клавиатуры к мыши. Выработав привычку использовать эти сочетания клавиш, вы сможете работать более эффективно при использовании редактора Visual Basic.

Клавиши быстрого доступа стандартного визуального базового редактора

Клавиши быстрого доступа для работы в окне кода VBE

Клавиши быстрого доступа для отладки кода

Клавиши быстрого доступа для навигации по окну проекта VBE

Об этой статье

Эта статья взята из книги:

• Макросы Excel для чайников,

Об авторе книги:

Дик Куслейка уже более 25 лет помогает пользователям получить максимальную отдачу от продуктов Microsoft Office через онлайн-форумы , блоги, книги и конференции.

Эту статью можно найти в категории:

• Excel ,

Книга макросов Excel для чайников

Обзор

Экономьте время и станьте мастером Excel с помощью ведущего в мире руководства по макросам Excel
Вы любите Excel и все, что вы можете с ним делать, но хотите работать… быстрее? Макросы Excel — автоматизированные рабочие процессы, которые экономят ваше время и энергию — могут быть именно тем, что вам нужно. В Макросы Excel для чайников вы узнаете более 70 самых продуктивных, экономящих время макросов за меньшее время, чем требуется для резервного копирования файлов на вашем компьютере!
Каждая глава книги содержит практическую информацию и упражнения, которые можно сразу же приступить к работе, а также пошаговые инструкции и рекомендации по настройке Excel в соответствии с вашими потребностями. Внутри вы найдете:

• Автоматизация, которая поднимает вашу производительность Excel на новый уровень и выше
• Полностью обновленные макросы, совместимые с новейшей версией Excel, включенной в Microsoft 365
• Тщательное объяснение основ, а также советы для опытных пользователей

Программа Excel Macros For Dummies предлагает что-то для всех. Это то средство повышения производительности, которого вы так долго ждали. Получите копию сегодня!

Сэкономьте время и станьте мастером Excel с помощью ведущего в мире руководства по работе с макросами Excel
Вы любите Excel и все, что вы можете с ним делать, но хотите работать… быстрее? Макросы Excel — автоматизированные рабочие процессы, которые экономят ваше время и энергию — могут быть именно тем, что вам нужно. В Макросы Excel для чайников вы узнаете более 70 самых продуктивных, экономящих время макросов за меньшее время, чем требуется для резервного копирования файлов на вашем компьютере!
Каждая глава книги содержит практическую информацию и упражнения, которые можно сразу же приступить к работе, а также

пошаговые инструкции и рекомендации по настройке Excel в соответствии с вашими потребностями. Внутри вы найдете:

• Автоматизация, которая поднимает вашу производительность Excel на новый уровень и выше
• Полностью обновленные макросы, совместимые с новейшей версией Excel, включенной в Microsoft 365
• Тщательное объяснение основ, а также советы для опытных пользователей

Программа Excel Macros For Dummies предлагает что-то для всех. Это то средство повышения производительности, которого вы так долго ждали. Получите копию сегодня!

Макросы Excel для чайников Шпаргалка

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

Статьи из книги

8 результатов

Статьи Excel

Макрос Excel для одновременного закрытия всех книг

Одной из наиболее неприятных вещей в Excel является одновременное закрытие многих книг. Для каждой открытой книги необходимо активировать работу, закрыть ее и подтвердить сохранение изменений. В Excel нет простого способа закрыть их все сразу. Этот маленький макрос позаботится об этом раздражении.

Как работает макрос

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

 Субмакрос1()
'Шаг 1: Объявите свои переменные
    Dim wb как рабочая книга
'Шаг 2. Прокрутите рабочие книги, сохраните и закройте
    Для каждого ББ в рабочих книгах
        wb. Close SaveChanges: = True
    Следующий бб
Конец суб 

Шаг 1 объявляет переменную Object, которая представляет объект Workbook. Это позволяет вам перебирать все открытые рабочие книги, запоминая их имена по мере продвижения.
Шаг 2 просто перебирает открытые книги, сохраняя и закрывая их. Если вы не хотите их сохранять, измените аргумент SaveChanges с True на False.

Как использовать макрос

Лучшее место для хранения этого макроса — ваша личная книга макросов. Таким образом, макрос всегда доступен для вас. Личная книга макросов загружается при каждом запуске Excel. В окне проекта VBE он называется personal.xlsb.

1. Активируйте редактор Visual Basic, нажав Alt+F11.

2. Щелкните правой кнопкой мыши файл personal.xlb в окне проекта.

3. Выберите «Вставка» → «Модуль».

4. Введите или вставьте код во вновь созданный модуль.

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

Чтобы записать макрос в личную книгу макросов, откройте диалоговое окно «Запись макроса». В раскрывающемся списке «Сохранить макрос в» выберите «Личная книга макросов». Затем просто запишите несколько щелчков по ячейке и остановите запись. Вы можете сбросить записанный макрос и заменить его этим.

Статьи Excel

Макрос Excel для открытия книг на определенной вкладке

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

Откройте рабочую книгу на листе Start Here.

Как работает макрос

Этот макрос использует событие Open книги для запуска книги на указанном листе при открытии книги:

 Частная подпрограмма Workbook_Open()
'Шаг 1: выберите указанный лист
     Листы("Начать здесь").Выбрать
Конец суб 

Макрос явно называет лист, на который рабочая книга должна перейти при ее открытии.

Как использовать макрос

Для реализации этого макроса его необходимо скопировать и вставить в окно кода события Workbook_Open. Размещение макроса здесь позволяет ему запускаться каждый раз при открытии книги:

1. Активируйте редактор Visual Basic, нажав Alt+F11.

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

3. Щелкните по этой книге.

4. В раскрывающемся списке Событие выберите событие Открыть.

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

   Введите свой код в событие открытия рабочей книги.

Статьи по Excel

10 вакансий для гуру макросов в Excel

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

Специалист по бухгалтерскому учету

• Записывает операционные операции, проводит отчеты на конец месяца и готовит финансовые отчеты

• Выполняет финансовый и бюджетный анализ, анализируя операционные данные и оценивая исторические тенденции

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