VBA.Excel

VBA.Excel

https://www.youtube.com/c/ExcelVBABillK/featured
На Пикабу
поставил 8 плюсов и 2 минуса
отредактировал 1 пост
проголосовал за 3 редактирования
Награды:
самый сохраняемый пост недели самый сохраняемый пост недели более 1000 подписчиков
48К рейтинг 4483 подписчика 4 подписки 56 постов 53 в горячем

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке)

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

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке) Microsoft Excel, Отчет, Видео, Длиннопост, Лайфхак, Пособие

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


Итак, для того чтобы сделать такое разделение ячейки, сперва вписываем в требуемую ячейку оба заголовка специальным образом. А именно таким:


1. [СИМВОЛ ПРОБЕЛА]

2. ЗАГОЛОВОК 1

3. [ПЕРЕХОД НА ВТОРУЮ СТРОКУ ALT+ENTER]

4. ЗАГОЛОВОК 2

5. [СИМВОЛ ПРОБЕЛА]

6. [ТОЧКА]


В рассматриваемом примере это выглядит в итоге вот так:

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке) Microsoft Excel, Отчет, Видео, Длиннопост, Лайфхак, Пособие

Подтвердив ввод значений и выбрав ячейку, переходим в режим её форматирования. Проще всего это сделать, использовав комбинацию клавиш CTRL+1. В открывшемся окне во вкладке «Граница» сперва выбираем внешнюю (1) и также диагональную границы (2):

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке) Microsoft Excel, Отчет, Видео, Длиннопост, Лайфхак, Пособие

Затем во вкладке «Выравнивание» выбираем в первом выпадающем списке «равномерно (отступ)» (3), а в поле ниже «по центру» (4). Подтверждаем настройки нажиманием на «ОК» (5):

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке) Microsoft Excel, Отчет, Видео, Длиннопост, Лайфхак, Пособие

Почти всё готово:

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке) Microsoft Excel, Отчет, Видео, Длиннопост, Лайфхак, Пособие

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

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке) Microsoft Excel, Отчет, Видео, Длиннопост, Лайфхак, Пособие

Вот и всё, всё готово:

Excel: Диагональное разделение ячейки (два заголовка в одной ячейке) Microsoft Excel, Отчет, Видео, Длиннопост, Лайфхак, Пособие

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


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

Показать полностью 6 1

Многоуровневая сортировка массивов в VBA: разбор примера

Для сортировки массивов в VBA можно писать собственные функции и процедуры основанные, например, на методах пузырьковой сортировки, сортировки подсчётом, сортировки Хоара и т.д. Тем не менее, подобные решения будут представлять из себя достаточно сложные макросы даже в том случае, если предназначаться они будут для сортировки лишь одномерных массивов.


В случае же многоуровневой сортировки массивов (например, сперва сортировка значений «x» по возрастанию, а затем сортировка значений «у» по убыванию) написание подобных процедур фактически почти теряет смысл, так как уровень сложности написания макросов для подобных целей возрастает экспоненциально и итоговые макросы при этом однозначно не будут оптимальными по производительности, так как самой производительным методом сортировки в VBA является сортировка средствами Excel.


Пример многоуровневой сортировки:

Многоуровневая сортировка массивов в VBA: разбор примера Vba, Microsoft Excel, Видео, Длиннопост

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


1. Экспорте значений из массива на рабочий лист

2. Многоуровневой сортировке значений на рабочем листе средствами Excel

3. Обратном импорте уже просортированных значений в массив


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

Показать полностью 1 1

Поиск неправильных записей – Задача в Excel

Получил вот такой вопрос:

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

И хочу предложить один из вариантов решения этой задачи с помощью стандартных инструментов Excel (в следующих постах также рассмотрим решение отдельно с помощью Power Query и отдельно с помощью VBA).


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


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


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

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

Теперь я создам этот самый упомянутый в постановке задачи лист записи. То есть рандомно заполняю столбец ФИО лицами из нашей исходной таблички (добавляю около трёхсот строк), и в случае пяти строк преднамеренно вношу неправильный номер паспорта, симулируя ошибку.

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

Если также хочешь потренироваться  в решении этой задачи, то вот ссылка на скачивание файла, показанного выше:

https://drive.google.com/file/d/1-y1erQDwHdAMId-juqpJ0KGMtVF...


Итак, теперь собственно процесс поиска внесённых ошибок (естественно, представляем, что не знаем их).


В первую очередь статично пронумеруем все строки, кому-как удобно (формулой и вставкой в виде значений или автозаполнением):

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

Теперь будем удалять дубликаты по первым двум столбцам. Для этого выбираем одну из ячеек таблицы и идём во вкладку «Данные», где затем щелкаем по «Удалить дубликаты».

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

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

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

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


Добавляем столбец «Подсчёт», и прописываем в нём функцию СЧЁТЕСЛИ. В первом аргументе указываем столбец со всеми ФИО, а во втором аргументе ссылаемся на ФИО текущей строки таблицы:

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

При этом я здесь работаю с умной таблицей, поэтому у меня были вставлены так называемые структурные ссылки ([ФИО], [@ФИО] и т.д.). Если тебе приходится проделывать эту операцию с обычным диапазоном на рабочем листе, то процесс имеет мельчайшее отличие (просто важно не забывать закреплять ссылки).


Вот что в итоге выходит в столбце «Подсчёт». Функция СЧЁТЕСЛИ считает, сколько раз встречается имя каждой текущей строки во всей таблице. Если имя встречается всего один раз, то из этого следует, что все записи с этим именем в таблице имели одинаковый номер паспорта. Там же где мы видим любое другое число нам становится понятно, сколько вариантов номера паспорта была внесено для одного и того же человека.

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

Так что всё что остаётся — это профильтровать значения. Убираю в фильтре столбца «Подсчёт» единицы, и мы получаем следующий результат:

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

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


Ну и конечно же можно еще сделать итоговый результат попривлекательнее (условное форматирование, сортировка и так далее):

Поиск неправильных записей – Задача в Excel Microsoft Excel, Задача, Видео, Длиннопост

Вот такое решение поставленной задачи с помощью стандартных инструментов Excel. Если хочешь более подробно узнать о всём, что было проделано в этом примере, предлагаю посмотреть следующее видео (в нём, помимо прочего, подробно рассказываю о примененном условном форматировании, о удалении дубликатов нескольких столбцов и т.д.):

Ну а в одном из следующих столбцов мы разберем более интересное и более оптимальное решение этой же задачи – с помощью Power Query!

Показать полностью 9 1

Визуализация принципа работы ячеек в Excel

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


Итак, пустой рабочий лист:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Первое с чего начнём, это внесём статичное значение 8 в ячейку A1. Просто выбираем её и, нажав на клавишу 8 на клавиатуре вписываем это значение в ячейку:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Под статичным значением понимается именно такое – внесенное с помощью клавиатуры, которое не изменяется.


Теперь в соседней ячейке B1 впишем уже динамически вычисляемое значение – то есть формулу. Выбрав ячейку, вписываем в ней следующее:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Когда Excel видит знак равенства, то понимает, что прописанное далее выражение нужно вычислить. Поэтому, когда мы подтверждаем ввод, по итогу видим всё тоже значение восемь, хоть и помним, что на самом деле внутри ячейки вписана вычисляемая формула:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Это первый пункт, который нужно понять. Ячейки в Excel – это своего рода контейнеры, в которые помещаются либо формулы, либо статичные значения. По итогу, после «просчёта» всех контейнеров, на поверхности ячейки Excel показывает нам либо внесённое статичное значение, либо значение, вычисленное формулой. Формула же при этом всё также остаётся внутри «контейнера».


Кроме того, в формулах могут быть ссылки на другие ячейки (они выделяются другим цветом). Вот пример:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

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

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Конечно же, ссылки и статичные значения можно комбинировать:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Получается мы уже рассмотрели два уровня отображения ячеек. Первый уровень – это то, что мы видим, находясь непосредственно «внутри» наших ячеек, то есть формулы и статичные значения. Второй уровень – это результат вычисления – то есть всё тоже статичное значение или результат вычисления формулы.


И нам остаётся рассмотреть лишь последний уровень, своеобразную линзу – уровень форматирования.

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Выберем ячейки из первого столбца и поменяем их формат на денежный (Главная -> Число -> Денежный):

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Теперь, как видишь, в названных столбцах также отображаются: разделитель десятичной части (запятая), десятичная часть числа (00 копеек), а также символ валюты (Рубль).


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


То есть таким образом форматирование — это дополнительная линза, наложенная поверх рабочего листа, которая меняет для нас лишь отображение значения.


В этом можно кстати еще раз убедиться вот так: скопировав область значений, попробуем вставить их ниже. Щелчок ПКМ по А4 выводит следующее контекстное окно:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

1 – Вставка в качестве значений: форматирование теряется, и все значения вставляются в статичном виде (то есть формулы заменяются результатами вычисления этих формул):

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

2 – Вставка функций: форматирование теряется, формулы вставляются формулами (следим за сдвигом ссылок! Поможет здесь F4), а статичные значения – всё также статичными значениями:

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

3 – Копирование форматирования: копируется лишь эта итоговая «линза» форматирования (здесь значения оставлены с предыдущей вставки):

Визуализация принципа работы ячеек в Excel Microsoft Excel, Формула, Видео, Длиннопост

Вот такое небольшое резюме и визуализация того, как работают ячейки в Excel.


В видео ниже я рассказал об описанном выше принципе работы ячеек в виде 3D-визуализации, а также перечислил и показал все самые важные особенности и фишки профессиональной работы с формулами и функциями в Excel, так что предлагаю также посмотреть его:

Показать полностью 13 1

Почему с возрастом время идет быстрее?

В детстве летние каникулы длились очень долго и 90 тёплых дней казались большим, длинным приключением, поделенным на три части – июнь, июль и август. Сегодня же, будучи взрослыми, мы зачастую и не замечаем, как проносится всё лето, а в сентябре лишь удивляемся, как быстро теплая летняя атмосфера начинает сдавать позиции и отступать перед осенним похолоданием.


И это чувство, ощущение ускорения хода времени присуще очень многим. Вот только все ли понимают, и задаются вопросом, почему у нас это чувство может возникать? Почему месяц для ребёнка может казаться вечностью, а для взрослого лишь мигом?


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


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


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

Почему с возрастом время идет быстрее? Молодость, Старение, Философия, Саморазвитие, Психология, Мысли, Видео, Длиннопост

Как видишь, с каждым прожитым годом, доля этого года уменьшается, достигая, например, к 30 годам всего примерно 3 процента, к 45 годам около 2 процентов и так далее.


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


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


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


Так что предлагаю тебе посмотреть видео, в котором рассказывается, как мы можем выйти из-под контроля времени, как единого мерила темпа нашей жизни:

P.S. Предложение решения начинается с 1:50 😊


Хороших выходных!

Показать полностью 1 1

Функция Split в VBA

Всем хорошо знаком формат CSV - Comma-separated values, то есть значения, разделённые запятыми. CSV – это текстовый формат, в котором отдельные значения каждой строки таблицы разделяются запятыми.


Вот простейший пример одной строчки с данными, разделенными однотипным символом – запятой:

Франция,Германия,Канада,Испания,США

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


Итак, у функции Split есть четыре параметра – один обязательный (Expression) и три опциональных (Delimiter, Limit, Compare):

Функция Split в VBA Vba, Microsoft Excel, Видео, Длиннопост

Если с прогнать показанный выше код и, закинув переменную в Watches Window, посмотреть её содержимое, то можно будет увидеть следующую картину:

Функция Split в VBA Vba, Microsoft Excel, Видео, Длиннопост

То есть VBA извлекает отдельные значения и помещает их в указанный одномерный массив.


При этом, разделитель может быть и буквой или даже целым словом, а также любым другим

символом (двоеточие, тире и т.д.). Вот пример, в котором разделителем выступает буква z:

Функция Split в VBA Vba, Microsoft Excel, Видео, Длиннопост

В случае с буквами также важно учитывать настройку Compare. Она определяет, нужно ли учитывать при поиске в тексте разделителей также и регистр написания букв. В Excel для нас важны две настройки Compare – это vbBinaryCompare и vbTextCompare.


vbBinaryCompare учитывает регистр написания, соответственно z и Z – это разные вещи, и нужно обязательно следить за тем, прописная или же строчная буква написана в качестве разделителя. vbTextCompare регистр не различается, то есть z и Z для VBA при этой настройке грубо говоря являются одним и тем же. Именно поэтому, при этой настройке Split извлек бы из следующего текста: 100z200z300z400Z500z600 точно также 6 чисел, заметив одну из прописных Z.


Ну и последний параметр – Limit. Этот параметр определяет максимальное число ячеек в массиве. Обрати при этом внимание на то, что если число элементов в текстовой строке превышает заданное число, то элементы, оставшиеся без «собственной ячейки», помещаются в последнюю. Вот наглядный пример:

Функция Split в VBA Vba, Microsoft Excel, Видео, Длиннопост

И да, значение параметра Limit по умолчанию = -1. Оно означает, что в массиве создаётся столько «ячеек», сколько нужно.


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

В этом видео, мы более подробно рассмотрим как все параметры функции Split, так и узнаем, как легко запомнить, за что отвечают vbBinaryCompare и vbTextCompare. Кроме того, в нём я также покажу тебе, какая функция выполняет противоположные функции Split действия (спойлер – это функция Join).


В конце концов, в этом видео есть также и интересная задача. Её я, пожалуй, даже упомяну и в этом посте:


Итак, задача следующая, скопируй вручную путь к любому файлу на твоём рабочем листе. При этом именно к файлу, а не к рабочей папке. Делается это очень просто – зажимаешь шифт и щелкаешь ПКМ по нужному файлу, затем в контекстом окне выбираешь функцию «Копировать как путь». Я скопировал в качестве примера путь к следующему файлу:
"C:\Users\User1\Desktop\Тестовый файл.xlsx"
Задача такая. Выдели с помощью функции Split название файла без расширения. То есть в моем это было бы название «Тестовый Файл».
P.S. Решение этой задачи есть в видео 😊 Еще раз с новым годом – желаю всего самого лучшего!

Показать полностью 4 1

VBA: Составление списка уникальных значений с помощью коллекций

Основным инструментом для отбора уникальных значений в VBA являются Dictionaries. Тем не менее, зачастую намного проще и быстрее получить список уникальных значений можно с помощью коллекций (простейший банальный плюс – в случае коллекций не надо включать отдельные библиотеки для того, чтобы работала функция Intellisense). Тем не менее, в подходе с коллекциями есть свои особенности, так что хотелось бы коротко представить этот подход.


Итак, в качестве примера предположим, что нам нужно из столбца «Страна» следующей таблицы извлечь список уникальных значений с помощь коллекции:

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

Для начала объявим все требуемые переменные:

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

Следующей строкой кода определим, где заканчиваются данные столбца С (используем для этого прыжок от последней ячейки к следующей заполненной сверху – эквивалент CTRL +↑):

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

Теперь, определив и сохранив номер последней строки в переменную lngLastRow, мы можем привязывать переменную rgDataColumn к нужному диапазону:

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

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

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

Теперь пропишем команду добавления элемента в коллекцию. Значение каждой ячейки будем вносить как в качестве значения элемента коллекции, так и в качестве его Key. Обрати при этом внимание на то, что Key всегда должен быть типа данных String, поэтому «оборачиваем» значение ячейки в функцию CStr, которая конвертирует значения в текстовый тип данных:

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

Теперь важный момент. Данная процедура, в её текущем состоянии, сразу же выдаст ошибку в случае встречи первого дубликата, так как ключи, эти самые Keys, должны быть обязательно уникальными. Поэтому перед циклом «выключаем» выведение ошибок с помощью команды On Error Resume Next, а после цикла, снова включаем с помощью On Error GoTo 0:

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

Вот и всё! Поставив Break-Point, прогоняю процедуру и проверяем, что у нас было внесено в коллекцию:

VBA: Составление списка уникальных значений с помощью коллекций Vba, Microsoft Excel, Видео, Длиннопост

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


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

Приятного просмотра и с наступающим! 😊

Показать полностью 8 1

On Error в VBA

С помощью оператора On Error в VBA мы можем регулировать, как программа должна реагировать на появление ошибок в коде. Вот в этом видео я рассказал о видах ошибок в VBA и том, как с каждым из этих видов следуют себя вести:

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

Итак, оператор On Error имеет четыре настройки:

1. On Error GoTo 0

2. On Error Resume Next

3. On Error GoTo [Метка]

4. On Error GoTo -1


On Error GoTo 0

Настройка On Error GoTo 0 является настройкой по умолчанию. При ней выполнение кода останавливается на строке с ошибкой и выводится сообщение:

On Error в VBA Microsoft Excel, Vba, Видео, Длиннопост

Продолжение работы кода возможно лишь после вмешательства в него. Поэтому, в выведенном сообщении мы может либо оставить выполнение кода нажатием на кнопку «End», либо приступить к его поправке, нажав на «Debug».

On Error Resume Next

При настройке Resume Next VBA, не смотря на ошибку, продолжает выполнение кода, не выводя при этом сообщений об ошибке. Ошибочные строки кода просто игнорируются, и программа их «перепрыгивает».

Для переключения режима работы VBA на настройку Resume Next нужно прописать указание «On Error Resume Next» с той строки, с которой эта настройка требуется:

On Error в VBA Microsoft Excel, Vba, Видео, Длиннопост

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

On Error в VBA Microsoft Excel, Vba, Видео, Длиннопост

При этом важно сказать, что лишь в некоторых ситуациях есть реальная польза в использовании настройки On Error Resume Next. В качестве примера можно назвать выделение лишь уникальных значений при помощи коллекций. В остальных же ситуациях лучше сперва искать другие методы решения поставленных задача. Особенно не стоит использовать Resume Next, не выключая эту настройку по итогу снова указанием On Error GoTo 0. В таком случае можно получить и вовсе непредсказуемое поведение итоговой процедуры.


On Error GoTo [Метка]

Данная настройка заставляет VBA производить прыжок к определенному месту в коде, которое помечается с помощью метки. Метка при этом указывается с помощью двоеточия:

On Error в VBA Microsoft Excel, Vba, Видео, Длиннопост

Важно при этом помнить, что после первого «прыжка» к указанной метке настройка On Error возвращается в исходное положение – то есть GoTo 0. Следовательно, если в части кода после метки также возникнет ошибка, будет выведено сообщение об ошибке и VBA попросит либо поправить код, либо остановить выполнение кода.

On Error в VBA Microsoft Excel, Vba, Видео, Длиннопост

On Error GoTo -1

С помощью последней настройки GoTo -1 мы можем встраивать несколько меток в код. Для этого сразу после первой метки надо прописать On Error GoTo -1, а затем вписать вторую настройку с меткой.

On Error в VBA Microsoft Excel, Vba, Видео, Длиннопост

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

Показать полностью 6
Отличная работа, все прочитано!