Розширюємо можливості макросів Excel за рахунок коштів Visual Basic

Нотатка написана Андрієм Макаренко

Усім, кому доводиться використовувати функції Excel, певному етапі стикаються з проблемою обмеженості запропонованого функціоналу. Перший вихід із цієї ситуації — все глибше вивчення вбудованих можливостей (знайомлячись з цією темою, не втомлюєшся вражати як багатство пропонованих Excel можливостей, так і неординарність рішень їх використання). Проте слід визнати, що обмеженість об'єктивна, т.к. "не можна осягнути неосяжне" з одного боку, і вивчення специфічних функцій Excel ніде крім Excel не буде потрібно з іншого.

Мал. 1. Діалогове вікно сортування надає величезний вибір параметрів; щоб збільшити зображення, клацніть на ньому правою кнопкою миші і виберіть Відкрити картинку в новій вкладці

Завантажити нотатку у форматі Word або pdf, приклади у форматі Excel (з макросами)

Автоматизувати можна і потрібно будь-яку дію, що часто повторюється. Візьмемо для прикладу сортування по заданому стовпцю - "AE" (ми вибрали віддалений стовпець навмисно, тому що його ручне виділення утруднене). Для її виконання проводяться такі дії (використовуємо Excel2010):

Виділяємо рядки, що підлягають сортуванню.
Викликаємо діалогове вікно Дані –>Сортування
У полі «Сортувати за» вибираємо стовпчик «AE»
Натискаємо Ok

У середньому операція триває 15 секунд. За день у нас вона повторюється разів 200. Разом 3000 секунд, або майже година тупого повторення. Т.к. операції ці робити нам, ми хочемо їх позбутися! Щоб не копатися в об'єктній моделі Excel (у нас немає мети стати великими програмістами) можна здорово спростити завдання використовуючи механізм макросів. Ми запишемо послідовність необхідних дій, а потімдоопрацюємо їх під наші завдання. Макроси в Excel за замовчуванням вимкнені (точніше, записати ви їх зможете, але зберегти або відкрити файл з макросами, ні). Щоб увімкнути можливість працювати з макросами, пройдіть по меню Файл –> Параметри –> Центр управління безпекою –> Параметри центру керування безпекою –> Параметри макросів –> Увімкнути всі макроси. Тепер можна зберегти вихідний файл із розширенням *.xlsm — файл Excel із підтримкою макросів.

Мал. 2. Включаючи можливість виконання макросів, ви переключаєте на себе контроль над захистом від вірусів.

Забезпечуємо зручний доступ до засобів програмування: Файл –> Параметри –> Панель швидкого доступу. Ставимо «галку» у правій частині (Налаштування стрічки) навпроти поля Розробник.

Мал. 3. Виводимо вкладку Розробник на стрічку

У стрічці з'явилася вкладка Розробник. Вибираємо її та в розділі Код натискаємо Запис макросу. Макрос можна також запустити, натиснувши кнопку Рядок стану Excel у нижньому лівому куті вікна (рис. 4). Після початку запису макросу ця кнопка змінює вигляд і тепер з її допомогою можна зупинити запис макросу.

Мал. 4. Кнопка в рядку стану, що активує початок запису макросу

Мал. 5. Після натискання на кнопку Запис макросу, її назва змінюється на Зупинити

Даємо ім'я макросу, присвоюємо клавіатурне скорочення Ctrl+q (рис. 6), і починаємо запис послідовності дій, що виконуються.

Мал. 6. Параметри макросу можна встановити та змінити надалі у діалоговому вікні Макроси

Мал. 7. Вікно Макрос дозволяє вибрати та відредагувати код макросу

Мал. 8. Код макросу

Очевидно, щоб програма працювала так, як нам потрібно, миповинні підставити до неї замість рядків із першої по шосту номери фактично виділених. Куди підставляти? Звернімо увагу на два рядки, в яких згадуються ці номери:

вказівка діапазону, за яким проводиться сортування Key:=Range(" AE1:AE6 " ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

вказівка діапазону, в якому проводиться сортування . SetRange Range("A1:AF6")

Тобто, підставляти потрібно на висловлювання в лапках: "AE1:AE6" і "A1:AF6". Що це за текст? A1:AF6 – це імена осередків лівого верхнього та правого нижнього кутів виділеного діапазону даних. AE1:AE6 – це імена верхньої та нижньої осередків, за якими ведеться сортування. Імена стовпців (A, AE та AF) залишатимуться незмінними, які б рядки ми не виділили, отже потрібно визначити лише номери першого та останнього виділених рядків та підставити їх замість 1 та 6. За справу!

Для початку оголошуємо змінні, яким надалі надамо шукані значення. Робиться це такими рядками:

Dim strLineBegin As String ' перший рядок сортованого діапазону Dim strLineEnd As String ' останній рядок сортованого діапазону

Дотримуємося у своїй наступних нескладних правил (необов'язкових, просто їх застосування полегшить нам життя надалі):

Присвоєння змінних виробляємо відразу після імені макросу, тобто. рядки Sub Сортування АЕ()
Ім'я змінної включає: перші три символи малими літерами скорочене позначення типу змінної; у разі str, тобто. String - текст; у практичному житті ще застосовуються типи Integer - ціле число, Single - число з плаваючою комою, Boolean - так/ні (докладніше див, наприклад, тут); далі з великої літери латинськими літерами власне ім'я

Таким чином,початок нашої програми набуває вигляду:

Sub Сортування АЕ() Dim strLineBegin As String 'перший рядок сортованого діапазону Dim strLineEnd As String 'останній рядок сортованого діапазону …

Далі Інтернет нам на допомогу та запитами типу «vba номер виділеного рядка excel» дізнаємося, що отримати номер рядка виділеного осередку можна простою командою:

Однак такий варіант не підходить при одночасному виділенні декількох рядків. Модифікуємо запит на «vbа номер першого та останнього рядка виділеного діапазону excel». Знаходимо наступну конструкцію:

strLineBegin = Selection.Row strLineEnd = strLineBegin + Selection.Rows.Count – 1

Підставляємо їх у наш код:

Sub Сортування АЕ() Dim strLineBegin As String ' перший рядок сортованого діапазону Dim strLineEnd As String ' останній рядок сортованого діапазону

strLineBegin = Selection.Row strLineEnd = strLineBegin + Selection.Rows.Count – 1 …

Мал. 9. Дізнатись поточне значення змінної можна, навівши на неї курсор миші

Робимо це і переконуємось, що все працює! Далі проходити код безглуздо, т.к. ми його ще не виправили. Перериваємо виконання програми, натиснувши на панелі інструментів кнопку Reset – синій квадратик.

Мал. 10. Управління виконанням макросу здійснюється за допомогою кнопок: виконати, пауза, стоп

Залишається підставити отримані значення команду сортування. Робимо це так:

Оголошуємо змінні з результуючим текстом

Dim strCol As String ' колонка, по якій проводиться сортування Dim strLine As String ' діапазон, який сортуємо

Формуємо її значення, використовуючи не фактичні значення, а змінні (нагадаю, що має вийтитекст "AE1:AE6" та "A1:AF6"):

strCol = "AE" & strLineBegin & ":AE" & strLineEnd strLine = "A" & strLineBegin & ":AF" & strLineEnd

Тут імена стовпців AE, A і AF - константи, нам їх міняти не потрібно, а знаки амперсанда (&) служать операторами, що склеюють частини тексту. Звертаємо увагу, що чистий текст береться в англійські лапки, тоді як назви змінних йде без них.

Підставляємо отриманий вираз за умови сортування:

Key:=Range(strCol) , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

Знову звертаємо увагу, що, т.к. текст у рядку замінено на ім'я змінної, то лапки не ставимо.

Команда – Rows("1:6").Select – із записаного макросу, що виділяє рядки нам не потрібна, ми будемо підставляти замість фіксованих значень, фактичні номери виділених рядків, прибираємо цей рядок з макросу (точніше, зберігаємо його у вигляді примітки, яка не виконується) частини коду). Разом залишається (рис. 11).

Мал. 11. Підсумковий код VBA, що дозволяє сортувати виділений діапазон стовпця АЕ за зростанням