VBA Excel: як користуватися об’єктами і застосувати макроси [2020]

Перша версія Excel з’явилася в 1985 році. З цього часу ПО продовжувати розвиватися, в результаті чого з’явився vba excel. Що це таке і для чого можна використовувати, варто розглянути більш докладно.

Що це таке

При створенні програми Excel використовувався спеціальний технічний мову Visual Basic for Application. Спочатку його встановлюють в процесор Microsoft. Технічні фахівці відзначають, що дана мова легко вивчити і використовувати в роботі.

Однак існує нюанс, про який повинен знати кожен користувач: не всі версії можуть бути сумісні. Це пов’язано з тим, що функціонально VBA пов’язана з новою версією продукту, при цьому в старих програмах таких можливостей немає. Крім того, код відкритий для сторонніх користувачів, тому його легко змінити. Однак за допомогою IBM Lotus Symphony можна задати комбінацію пароля, щоб переглядати документи зміг тільки один користувач.

Об’єкти, колекції, властивості і методи

Для користувачів, які планують в майбутньому використовувати VBA, необхідно розібратися з ключовими поняттями і дізнатися, що таке об’єкт. Звернувшись до програми Excel, тут в даному випадку об’єктами є:

  • Комірка
  • лист
  • створена книга
  • Обраний діапазон.

Всі об’єкти створюють певну ієрархію, в якій підпорядковуються один одному. Наявний Application повинен відповідати продукту Excel. Так, наприклад, щоб використовувати певну осередок, користувач повинен задати певний шлях до неї.

колекція – це група об’єктів, що належать до того ж класу з форматом запису ChartObjects. Окремі складові також належать категорії об’єктів.

Ще одне поняття, з яким важливо ознайомитися, це «властивості». Кожен об’єкт має обов’язкову характеристику. Для Range, як правило, Value і Formula.

методи – це задаються команди, які потрібно здійснити користувачеві. Якщо потрібно заповнити команду в формі VBA, то в обов’язковому порядку потрібно проставляти крапки, відокремлюючи об’єкт. В якості прикладу: Cells (1,2) .Select. Дана команда передбачає, що потрібно вибрати комірку під номером 1.2. Якщо значення було задано невірно або буде потрібно видалити весь вміст комірки, то необхідно задати команду Selection.ClearContents.

Stop Recording

Як почати цим користуватися

Для початку необхідно створити файл у форматі Excel і зберегти на робочому столі. Важливо, щоб файл підтримував макроси. Далі потрібно перейти до VB. Для цього буде потрібно затиснути комбінацію з клавішею «Alt + F1». Потім потрібно слідувати інструкції:

  • Поруч з Excel у верхній частині можна знайти спеціальне меню;
  • З перерахованих функцій вибрати «Mudule»;
  • Зберегти і натиснути на зображення з написом «floppy disk»;
  • Вписуємо код.

Формат кодування повинен виглядати в такому форматі: Sub program-‘код-End Sub.

Увага: Рядок, в якій повинен прописуватися код буде виділена зеленим кольором. Перед тим, як прописувати кодування, вкрай важливо поставити апостроф. Він означає, що після шифру повинен проставлятися певний коментар.

Це все, що знадобитися, щоб сформувати інструмент VBA. Для користувачів, які мають уявлення про те, як використовувати Visual Basic, весь процес займе мінімум часу. Але процес теж не передбачає складнощів для тих користувачів, які раніше не стикалися з рішенням такого завдання.

Макроси в Excel

Макросом називають спеціальний код, який записується на спеціальній мові VBA. Їх можна створювати в ручному або автоматичному режимі. Для цього буде потрібно використовувати спеціальний макроредактор.

Програма, яка записує кожну дію, вироблене в програмі Excel, перетворює їх в спеціальну мову VBA. Код, який створюється за допомогою макрорекодера, легко піддається редагуванню і зручний у використанні. Важливо відзначити, що макрос можна використовувати необмежену кількість разів. Навіть якщо користувач не знає, яким чином потрібно звертатися з VBA, він може максимально автоматизувати весь процес.

Щоб самостійно створити макрос, рекомендується використовувати наступну інструкцію:

  • Натиснути правою кнопкою миші на створену стрічку і тиснути на функцію настройки. З його допомогою можна відкрити параметри діалогового вікна:

Налаштування стрічки

  • У вікні з’явиться параметр настройки стрічки. У правій частині знаходяться основні функції, де потрібно поставити галочку навпроти розробника:

Розробник

  • Підтвердити дію, натискаючи кнопку «ОК».

Якщо всі дії виконані в правильній послідовності, в основний меню повинно з’явитися додаткове меню Розробник, як зазначено на скріншоті:

Додаткове меню

Щоб скористатися розширеними функціями таблиці Excel, варто спробувати самостійно записати макрос. Він буде самостійно вибирати осередок і формувати в ній заданий текст. Для цього буде потрібно зробити наступні кроки:

  • Перейти в створену вкладку розробника;
  • В меню коду вибрати функцію запису. Повинно відкритися окреме вікно

запис макросу

  • У вікні запису потрібно задати ім’я для майбутнього макросу. Однак важливо відзначити, що важливо виконати кілька правил, щоб правильно поставити ім’я: не можна використовувати пробіли. Тому краще ставити злите назву, навіть якщо воно складається з двох слів. також дозволяється використовувати нижній дефіс.

ім’я макросу

  • Якщо виникає необхідність, то можна задати комбінацію з клавіш. У такому випадку потрібно одночасно затиснути наступне поєднання: «Ctrl + Shift + N». Важливо запам’ятати, що дана комбінація при використанні здатна скасувати абсолютно будь-які задані дії за допомогою гарячих клавіш. Якщо раніше для автоматичного збереження використовувалося поєднання «Ctrl + S», то більш для цієї функції його не можна використовувати.

Швидкий доступ

  • Перед тим, як зберегти документ, потрібно перевірити, чи правильно задана опція. Це необхідно для того, щоб упевнитися в тому, що макрос є складовою обраної книги. Він буде присутній навіть при автоматичному збереженні або якщо буде потрібно з кимось поділитися файлом.

зберегти в

  • Як доповнення можна придумати опис до файлу. Найчастіше це потрібно, якщо користувач планує створити кілька макросів поспіль, щоб в майбутньому не заплутатися, який з них за що відповідає;
  • залишається натиснути «OK». Після цього макрос почне автоматично записувати інформацію в книзі. Якщо потрібно зупинити запис, то натискаємо відповідну кнопку поруч з меню розробника;
  • Вибираємо іншу клітинку, наприклад А2;
  • Вводимо будь-який текст і натискає кнопу «Enter»
  • Далі припиняємо запис.

зупинити запис

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

Щоб ознайомитися з отриманими результатами, досить перейти в VB в Excel:

  • Відкрити вкладку розробника
  • В меню кодування відкрити Visual Basic.

Щоб перейти до кодуванні VBA або його редактор, можна використовувати комбінацію з клавіш Alt і F11. Для ознайомлення, яким чином виглядає сам редактор, представлений наступний скріншот:

Меню Visual Basic

  • Панель, на яку має меню представляє основні команди, які можна здійснювати за допомогою встановленого редактора;
  • Панель з інструментами нагадує ту, яку можна знайти в програмі Excel. При необхідності можна задати додаткові кнопки для окремого виведення, якщо вони використовуються частіше, ніж інші;
  • кодова вікно. У ньому розміщується коду VBA. Для кожного об’єкта є спеціальне вікно, яке вказується в провіднику. Якщо все правильно зроблено, то макрос повинен висвітлюватися у вікні провідника;
  • властивості. Використовуючи дане вікно, користувач зможе переглянути властивості по кожному об’єкту окремо.

Коли записується новий макрос, повинні відбуватися такі зміна:

  • Поява нового модуля
  • Поява набраного назви
  • Додається нова функція.

Щоб здалося вікно введення, залишається двічі клікнути на модуль, щоб з’явився наступний запис, як показано на скріншоті:

Важливо взяти до відома, що самостійно записаний код ефективний в певних областях застосування (фінансової або математичної). Крім того, часто макрорекодер може до наявного інструментарію пропонувати абсолютно нові і непотрібні функції.

функції VBA

На прикладі інструкцій, які були наведені раніше, можна зробити висновок про те, що використання VBA і макросів не припускав великих складнощів. Загальна кількість функцій, які були створені спеціально для програмування на мові VBA, становить 160 можливостей. Всі функції поділяються на кілька груп:

  1. математична. Застосовуючи її для аргументу, можна обчислювати косинуси і логарифми, що найчастіше потрібно для студентів технічних вузів і займаються програмуванням. Функція позбавляє від необхідності вести математичні підрахунки вручну і прискорює процес;
  2. фінансова. Ця функція завдяки наявності допомагає запускати програмування для фінансових операцій. Ці опції допоможуть складати грамотний фінансовий і бухгалтерський облік;
  3. Робота з масивами за допомогою L, U Bound, Array, IsArray;
  4. Окрема функція VBA для ведення рядків. Варто відзначити, що група представлена ​​численними здібностями. Так, наприклад, серед найпоширеніших – Space, яка задається в рядку з певною кількістю прогалин. Функція Asc потрібно для запису кодування в ANSI. Дані функції користуються великою популярністю серед користувачів і дають можливість повноцінної роботи з файлами Excel;
  5. Перетворення функцій і введення даних. Використання CVar допомагає повернути початкове значення аргументу і перетворити його в інший формат – Variant
  6. Зміна і коригування дат. Вони допомагають розширити спектр наявних можливостей. Щоб повернути вихідне назва або день, використовується WeekdayName. Щоб дізнатися скільки секунд пройшло з настання 12-ї ночі, застосовується функція Timer;
  7. Щоб перетворити число в спеціальні системи можна звернутися до можливості Oct, яка допоможе уявити число, що складається з 8 чисел;
  8. форматування. Основний є Format. З її допомогою здійснюється повернення значення у вигляді Variant, яке далі форматується відповідно до встановлених правил.

Рішення завдання з використанням програмування на VBA

Щоб прорахувати можливі відхилення, потрібно ввести позначення і отримати задану формулу. Для того, щоб прорахувати процентне позначення потрібно використовувати наступну формулу: (F-P) розділити на P * 100, сумарне значення має вийти при дії (F-P). Отримані результати рекомендується відразу вводити в таблицю.

Щоб сформувати в належному вигляді прогноз, потрібно застосувати наступну формулу: ItogP = ItogP + P, ItogF = ItogF + F. Для прорахунку відхилень потрібно використовувати (ItogF – ItogP) / ItogP * 100. Як тільки всі дії будуть виконані, значення вносяться в осередку, а значить привласнювати змінні немає необхідності.

Щоб здійснити запуск, в першу чергу потрібно зберегти створену книгу. Також варто звертати увагу і на інші умови. Так, наприклад, додавати рядки доведеться кожного разу після введення нових позначень в таблиці.

Якщо потрібно створити таблицю для звітності, то по даній функції достатньо натиснути все раз. Як тільки робота підійде до завершення, потрібно натиснути на закінчити, а потім переключитися в відповідне вікно програми Excel.

висновок

Вище були вивчені всі аспекти, яким чином можна використовувати макроси, для чого потрібен VBA в Excel і яким чином його застосовувати. Варто відзначити, що завдяки простим інструкціям, навчитися цим діям зможе будь-який досвідчений користувач, який раніше не мав можливість вивчати Visual Basic. Подібні можливості допомагають в організації фінансової звітності та проведення математичних функцій.

Ссылка на основную публикацию