Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Макросы excel if then

Макросы excel if then

Выполнение разных макросов по изменению значений соответствующих ячеек

HeavyHard:
Доброго времени суток! Прошу помощи. Выложил книгу-пример, в ней необходимо чтобы при изменении на листе "Лист1" ячеек А1, А2 и А3 запускались соответствующие макросы (изменялись соответственно ячейки А1, А2 и А3 на листе "Лист2").
Необходимо поправить код:
Код: (vb)
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("A1,A2,A3"), Target) Is Nothing Then
If [A1] = "разновес" Then Макрос1
If [A1] <> "разновес" Then Макрос2
If [A2] = "разновес" Then Макрос3
If [A2] <> "разновес" Then Макрос4
If [A3] = "разновес" Then Макрос5
If [A3] <> "разновес" Then Макрос6
End If
Application.EnableEvents = True
End Sub

Дмитрий Щербаков(The_Prist):
Вы напишите хоть, что именно надо. А то соответствующие макросы у Вас и так запускаются. Единственное — переменную Target Вы не используете, а надо бы.
Чисто интуитивно предполагаю, что хочется изменить сами макросы, чтобы их было меньше:
Код: (vb)
Sub Макрос1(sAddr As String)
Range("Лист2!" & sAddr).FormulaR1C1 = "ррр"
End Sub
Sub Макрос2(sAddr As String)
Range("Лист2!" & sAddr).ClearContents
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("A1,A2,A3"), Target) Is Nothing Then
If Target = "разновес" Then
Call Макрос1(Target)
Else: Call Макрос2(Target)
End If
End If
Application.EnableEvents = True
End Sub

HeavyHard:
Так не работает (вложена книга).

А если применить такой код:Код: (vb)
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("A1,A2,A3"), Target) Is Nothing Then
If [A1] = "разновес" Then Макрос1
If [A1] <> "разновес" Then Макрос2
If [A2] = "разновес" Then Макрос3
If [A2] <> "разновес" Then Макрос4
If [A3] = "разновес" Then Макрос5
If [A3] <> "разновес" Then Макрос6
End If
Application.EnableEvents = True
End Sub
то начинает пересчитывать все указанные макросы. Ведь если макросы сложней, чем в моем примере, и их больше, тогда будет долго считать.
Что в ЭТОМ коде можно поправить, чтобы выполнялся только один "нужный" макрос?

Дмитрий Щербаков(The_Prist):
Код: (vb)
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Range("A1,A2,A3"), Target) Is Nothing Then
If Target = "разновес" Then
Call Макрос1(Target.Address)
Else: Call Макрос2(Target.Address)
End If
End If
Application.EnableEvents = True
End Sub

HeavyHard:
Откорректированный Вами макрос не хочет выполняться. Ошибка в макросе.

Учебник для начинающих по написанию макросов VBA в Excel (и почему вы должны учиться)

, Но макросы на самом деле довольно ограничены. С инструментом записи легко ошибиться, а процесс записи неудобен.

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

Что такое VBA?

VBA — это Visual Basic для приложений, язык программирования, который вы можете использовать во многих приложениях Microsoft. Visual Basic — это язык программирования, а VBA — его версия для конкретного приложения. (Microsoft прекратила выпуск Visual Basic еще в 2008 году, но VBA продолжает развиваться).

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

Читайте так же:
Как восстановить закрытую вкладку ворд

Тем не менее, VBA требует времени, чтобы привыкнуть.

Преимущества макросов VBA в Excel

Если VBA сложнее, чем запись макроса, зачем вам его использовать? Короткий ответ: вы получаете гораздо больше возможностей от макросов VBA.

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

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

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

Давайте посмотрим на простой макрос VBA, чтобы увидеть, как он работает.

Пример макроса VBA в Excel

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

Новичок

Этот макрос добавит квартальные продажи из каждого магазина и запишет эти итоги в ячейки электронной таблицы (если вы не уверены, как получить доступ к диалоговому окну VBA, ознакомьтесь с кратким описанием VBA здесь):

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

Объявление Sub

В начале модуля у нас есть «Sub StoreSales ()». Это определяет новую подпрограмму под названием StoreSales.

Вы также можете определять функции — разница в том, что функции могут возвращать значения, а подпрограммы не могут (если вы знакомы с другими языками программирования, подпрограммы эквивалентны методам). В этом случае нам не нужно возвращать значение, поэтому мы используем подпрограмму.

В конце модуля у нас есть «End Sub», который сообщает Excel, что мы закончили с этим макросом VBA.

Объявление переменных

Все первые строки кода в нашем скрипте начинаются с «Dim». Dim — команда VBA для объявления переменной.

Таким образом, «Dim Sum1» создает новую переменную под названием «Sum1». Однако нам нужно сообщить Excel, что это за переменная. Нам нужно выбрать тип данных. В VBA существует много разных типов данных, полный список которых можно найти в справочных документах Microsoft.

Поскольку наш макрос VBA будет работать с валютами, мы используем тип данных Currency.

Оператор «Dim Sum1 As Currency» указывает Excel создать новую переменную Currency с именем Sum1. Каждая переменная, которую вы объявляете, должна иметь оператор «As», чтобы сообщить Excel ее тип.

Начиная для цикла

Циклы являются одними из самых мощных вещей, которые вы можете создать на любом языке программирования. Если вы не знакомы с циклами, посмотрите это объяснение циклов Do-While.

, В этом примере мы используем цикл For, который также рассматривается в статье.

Вот как выглядит цикл:

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

, который является конкретным типом объекта в VBA. Когда мы используем его таким образом — Range («C2: C51») — это говорит Excel, что мы заинтересованы в этих 50 ячейках.

Читайте так же:
Минимальное значение кроме 0 в excel

«Для каждого» говорит Excel, что мы собираемся что-то сделать с каждой ячейкой в ​​диапазоне. «Следующая ячейка» идет после всего, что мы хотим сделать, и говорит Excel начать цикл с начала (начиная со следующей ячейки).

У нас также есть следующее утверждение: «Если IsEmpty (Cell), то Exit For».

Можете ли вы угадать, что он делает?

Замечания: Строго говоря, использование цикла While могло бы быть лучшим выбором

, Однако ради обучения я решил использовать цикл For с выходом.

If-Then-Else Заявления

Ядро этого конкретного макроса находится в операторах If-Then-Else. Вот наша последовательность условных выражений:

По большей части, вы можете догадаться, что делают эти заявления. Возможно, вы не знакомы с ActiveCell.Offset. «ActiveCell.Offset (0, -1)» указывает Excel взглянуть на ячейку, которая находится в одном столбце слева от активной ячейки.

В нашем случае это говорит Excel о необходимости обратиться к колонке с номером магазина. Если Excel находит 1 в этом столбце, он берет содержимое активной ячейки и добавляет его в Sum1. Если он находит 2, он добавляет содержимое активной ячейки в Sum2. И так далее.

Excel проходит все эти утверждения по порядку. Если условное утверждение

удовлетворен, он завершает утверждение Тогда. Если нет, он переходит к следующему ElseIf. Если он дойдет до конца и ни одно из условий не будет выполнено, он не предпримет никаких действий.

Комбинация цикла и условных выражений — это то, что движет этим макросом. Цикл предписывает Excel пройти через каждую ячейку в выделении, а условные выражения сообщают ему, что делать с этой ячейкой.

Запись значений ячеек

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

Используя «.Value» и знак равенства, мы устанавливаем в каждой из этих ячеек значение одной из наших переменных.

И это все! Мы сообщаем Excel, что закончили написание этого Sub с «End Sub», и макрос VBA завершен.

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

Новичок

Собираем строительные блоки VBA в Excel

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

Но с практикой вы создадите свой словарный запас VBA и сможете писать макросы быстрее, точнее и с гораздо большей мощностью, чем вы могли бы когда-либо записывать.

Когда вы застряли, запуск поиска Google — это быстрый способ получить ответы на ваши вопросы VBA. А справочник Microsoft по VBA для Excel может быть полезен, если вы хотите найти технический ответ.

Когда вы освоитесь с основами, вы можете начать использовать VBA для таких вещей, как отправка электронных писем из Excel, экспорт задач Outlook.

Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием

В данном примере написаны исходные коды VBA-макросов для проверки ячеек на наличие выпадающих списков (или любых средств, созданных инструментом «проверка данных»). А также код макроса для проверки и получения доступа к ячейкам, которые содержат условное форматирование.

Читайте так же:
Гистограмма в excel это

Макрос поиска ячейки с выпадающим списком

Допустим у нас имеется таблица Excel сформированная в результате экспорта журнала фактур из истории взаиморасчетов с клиентами фирмы, как показано ниже на рисунке:

Таблица с выпадающими списками.

Нам необходимо найти все выпадающие списки или определить каким ячейкам присвоена проверка вводимых данных, создана инструментом: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

Проверка данных.

В программе Excel по умолчанию есть встроенный инструмент для поиска ячеек с проверкой правил вводимых значений. Чтобы его использовать следует выбрать: ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек». В появившемся диалоговом окне следует отметить опцию «проверка данных» и нажать на кнопку ОК. Выделить группу ячеек.Но как всегда более гибким решением является написание своего специального макроса. Ведь в такие случаи всегда можно усовершенствовать инструмент и дописать много других полезных функций. А этот код макроса послужит прекрасным началом программы.

Откройте редактор макросов Visual Basic (ALT+F11) и создайте новый модуль выбрав в редакторе инструмент: «Insert»-«Module». В созданный модуль введите VBA код макроса:

Visual Basic ALT+F11.

Если нужно выделить все ячейки в таблице, которые содержат проверку вводимых значений включенной инструментом «Проверка данных», тогда выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvDan»-«Выполнить».

В результате выделились 14 ячеек в столбце G, для которых включена проверка данных в стиле выпадающего списка:

Проверка данных.

В данном коде мы сначала выделяем все ячейки на текущем листе с помощью инструкции:

После, определяем диапазон ячеек на листе, который использует исходная таблица и с которыми будет работать наш макрос. Чтобы определить диапазон таблицы на рабочем листе Excel, мы могли бы использовать свойство UsedRange при создании экземпляра объекта Range в переменной diapaz1. Данное свойство охватывает только непустые ячейки, а это может быть даже несмежный диапазон. Но таблица может содержать пустые ячейки для, которых присвоена проверка ввода значений. Чтобы наш макрос не игнорировал пустые ячейки внутри таблицы мы определяем смежный (неразрывный) диапазон, который начинается с ячейки A1 и заканчивается последней используемой ячейкой на рабочем листе Excel.

Set diapaz1 = Application.Range(ActiveSheet.Range(«A1»), ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))

Последняя ячейка находиться наиболее отдаленно от ячейки A1 (в данном примере – это G15) и была использована на листе (это обязательное условие). При чем использована в прямом смысле, она может даже не содержать значения, но иметь измененный числовой формат, другой цвет фона, другие границы, объединение и т.п. Чтобы найти последнюю используемую ячейку на листе стандартными средствами Excel, выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

Ячейки с выпадающими списками.

В появившемся окне следует выбрать опцию «последнюю ячейку». А после просто нажать ОК. Курсор клавиатуры сразу переместиться на последнюю используемую ячейку на рабочем листе Excel.

Можно даже при создании экземпляра объекта Range в переменной diapaz1 использовать диапазон целого листа. Для этого просто замените выше описанную инструкцию на:

Set diapaz1 = Selection

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

Читайте так же:
Изменение ширины столбца в excel

Далее в коде макроса перед циклом прописана инструкция для выключения обработки ошибок, выполняемых в коде.

On Error Resume Next

Но после конца цикла обработка ошибок снова включается.

On Error GoTo 0

Внутри цикла проверяться по отдельности все ячейки на наличие включенной проверки вводимых значений инструментом «Проверка данных». Если ячейка содержит проверку вводимых значений?

If IsError(diapaz1(i).Validation.Type) Then

Тогда она присоединяется к диапазону ячеек, находящихся в переменной diapaz2.

Set diapaz2 = Application.Union(diapaz2, diapaz1(i))

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

MsgBox «Найдено: » & diapaz2.Count & » ячеек!»

Макрос поиска ячейки с условным форматированием

Некоторые ячейки в исходной таблице содержат условное форматирование, а пользователю Excel необходимо их все найти и выделить. Очень часто нельзя визуально определить присвоено ли ячейке условное форматирование или нет. Чтобы найти и выделить ячейки с условным форматированием в Excel можно воспользоваться встроенным инструментом. Просто необходимо выбрать опцию в меню: «ГЛАВНАЯ»-«Редактирование»-«Найти и выделить»-«Выделить группу ячеек».

В появившемся окне отмечаем опцию «условные форматы» и нажимаем кнопку ОК.

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

Снова откройте редактор Visual Basic (ALT+F11) и в уже созданный модуль добавьте новый код для нового макроса:

Теперь чтобы проверить, посчитать и выделить все ячейки для которых было применено любое условное форматирование запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«ProvFormat»-«Выполнить».

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

Диапазон с условным форматированием.

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

Проверка ячеек на наличие условного форматирования выполняется с помощью свойства Cuont для объекта FormatConditions. Если данное свойство возвращает значение 0, то для текущей ячейки не применялось ни одно условное форматирование.

If diapaz1(i).FormatConditions.Count > 0 Then

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

Курс Макросы на VBA для Microsoft Excel 2010 для профессионалов: VBA10

На этом курсе слушатели освоят программирование на языке Visual Basic для приложений (VBA) в Microsoft Excel 2010, обучатся основным навыкам работы c редактором Visual Basic, научатся использовать свойства и методы объектов Excel. Слушатели так же научатся программировать циклические действия и пользовательские функции, познакомятся с техникой обработки ошибок, изучат основы отладки и тестирования программ.

ПОСЛЕ ИЗУЧЕНИЯ КУРСА МАКРОСЫ НА VBA ДЛЯ MICROSOFT EXCEL 2010 ВЫ СМОЖЕТЕ:

  • Работать с редактором Visual Basic
  • Создавать макросы на языке Visual Basic
  • Использовать свойства и методы объектов Excel
  • Программировать циклы и пользовательские функции
  • Выполнять тестирование и отладку программ
  • Настраивать параметры безопасности
  • Использовать элементы управления и события рабочих книг для запуска макросов
  • ул. Доброслободская, д.5
  • Бауманская
  • Комсомольская
  • Красные ворота
  • ул. Доброслободская, д.5
  • Бауманская
  • Комсомольская
  • Красные ворота
Читайте так же:
Можно ли заразиться вич через бритвенный станок
РАСПИСАНИЕ ЗАНЯТИЙ
Содержание курса
  • Что такое макросы
  • Сохранение книги с поддержкой макросов
  • Запись макроса
  • Запуск, изменение параметров и удаление макросов
  • Абсолютные и относительные ссылки
  • Добавление макроса на панель быстрого доступа
  • Редактор Visual Basic
  • Исправление недостатков программного кода, генерируемого автоматически
  • Пример исправления недостатков кода, сгенерированного автоматически
  • Разрешение на запуск макросов
  • Параметры безопасности макросов
  • Редактор VBE
  • Использование окна проекта
  • Работа с модулями
  • Настройка среды VBE
  • Работа с окнами кода
  • Ввод и редактирование программного кода
  • Создание, сохранение и запуск процедуры VBA
  • Запуск процедуры
  • Типы данных
  • Объявление переменных
  • Область действия переменных
  • Использование констант
  • Тип Строки (String)
  • Тип Дата (Date)
  • Пользовательские типы данных
  • Массивы (Array)
  • Объектные переменные
  • Конструкция If-Then-Else
  • Конструкция Select Case
  • Понятие объектно-ориентированного программирования
  • Модель объектов Excel
  • Ссылки на объекты
  • Коллекция Workbooks
  • Обзор объекта Workbook
  • Свойство Saved
  • Метод Close
  • Метод SaveAs
  • Метод SendMail
  • События Open и AfterSave
  • События SheetActivate и SheetChange
  • Коллекция Worksheets
  • Метод Add
  • Обзор объекта Worksheet
  • Свойства Cells и Range
  • Методы Copy и Move
  • Метод Paste
  • Метод Select
  • События BeforeDoubleClick и BeforeRightClick
  • События Change и SelectionChange
  • Обзор объекта Range
  • Свойство Address
  • Свойства Font и NumberFormat
  • Свойства Text и Value
  • Метод AutoFill
  • Методы Copy, Cut и Insert
  • Метод Find
  • Использование объектных переменных
  • Конструкция With … End With
  • Разновидности циклов в VBA
  • Циклы со счетчиком For. Next
  • Циклы с предусловием Do. Loop
  • Циклы с постусловием Do. Loop
  • Циклы с перечислением For Each. Next
  • Перечисление всех файлов в папке
  • Перечисление всех открытых книг и всех листов
  • Сохранение копий всех открытых книг в указанную папку
  • Переименование всех листов книги
  • Преобразование данных в диапазоне ячеек
  • Поиск по всем листам рабочей книги с использованием методов Find и FindNext
  • Пользовательские функции
  • Создание процедуры Function
  • Использование функций на листе Excel
  • Использование функций в процедурах
  • Параметры функции
  • Использование необязательных параметров
  • Использование параметра ParamArray Variant
  • Возвращение массива из функции
  • Возвращение ошибок из функции
  • Функция заполнения диапазона ячеек случайными числами
  • Функция получения имени текущего пользователя
  • Типы ошибок
  • Настройка параметров перехвата ошибок
  • Создание процедуры обработки ошибок периода выполнения
  • Объект Err
  • Метод Raise объекта Err
  • Оператор Resume
  • Оператор On Error и режимы обработки ошибок
  • Обработка пользовательских прерываний
  • Отладка и пошаговое тестирование программы
  • Использование точек останова
  • Трассировка вызовов процедур
  • Просмотр контрольных значений
  • Окна Quick Watch и Watch
  • Объект Debug
  • Безопасность макросов
  • Включение макросов
  • Настройка параметров безопасности
  • Включение предупреждений о ссылках и файлах с подозрительных веб-сайтов
  • Защита проекта VBA
  • Элементы управления
  • Элемент управления формы Кнопка
  • Назначение макросов элементам управления формы
  • Использование событий рабочих книг и листов для запуска макросов
Предварительная подготовка
  • Знание основ работы с персональным компьютером
  • Умение работать в среде ОС Windows XP или Windows 7
  • Желательно до начала обучения прослушать курс PC1 «Основы работы на персональном компьютере»
  • Хорошее знание основ работы в программе Microsoft Excel 2010
СЛУЧАЙНЫЙ ВОПРОС ПО КУРСУ

Скидка рассчитывается индивидуально при обучении 2 и более человек от компании, заполните, пожалуйста, форму ниже:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector