Коди макросів в visual basic. Як написати простий макрос в програмі Excel

Стаття призначена для людей, які хочуть навчитися писати програми на вбудованому в Excel Visual Basic (VBA), але абсолютно не знають що це таке.

Для початку - кілька слів про те, навіщо це потрібно. Засіб VBA в MS Excel, являє нам універсальний інструмент для швидкого і точного вирішення будь-яких індивідуальних призначених для користувача завдань в MS Excel. Можна звичайно використовувати і вбудовані в MS Excel функції яких безліч, проте вони далеко не завжди вирішують поставлене завдання.
Отже, створимо для прикладу найпростішу програму. Будемо використовувати MS Excel 2007. Відкрийте MS Excel, натисніть "зберегти як" та збережіть файл Вашої програми натиснувши "Книга ексель з підтримкою макросів".

Далі необхідно включити вкладку "Розробник". Для цього натискаємо "Параметри Excel"

Ставимо галочку на "Показувати вкладку" Розробник "на стрічці"

Після цього на стрічці, в верху листа Excel, з'явиться вкладка "Розробник", яка містить в собі інструменти для створення VBA макросів.
Уявімо собі невелику завдання - припустимо ми маємо 2 числа, нам необхідно їх скласти і по отриманої суми отримати значення з нашої таблиці.
Поставимо в осередку Ліста1 наступні значення:

на Лісте2 заповнимо осередку, створивши таблицю з 2 стовпців

Далі перейдемо на Лист1, натиснемо на вкладку "Розробник", "Вставити", на ній виберемо кнопку
і намалюємо кнопку на Лісте1, після чого відразу з'явиться вікно "Призначити макрос об'єкту", в якому вибираємо "Створити"

Після цього відкриється редактор Visual Basic, і автоматично напишется найменування процедури, яка буде виконуватися при натисканні кнопки. Під назвою процедури впишемо наступний код:

Код виконає такі завдання:

• MsgBox ( "Це мій перший Макрос!") - повідомлення
• Змінної q присвоюється значення осередки на Лісте1, з координатами 2 рядок, 2 стовпець
• Змінної w присвоюється значення осередки на Лісте1, з координатами 3 рядок, 2 стовпець
• У осередок на Лісте1, з координатами 4 рядок, 2 стовпець, записується сума q + w

Далі отримаємо значення стовпця В з Ліста2, яке розташоване на тому самому рядку де значення нашої суми збігається з значенням стовпця А.
Введемо наступний код:

і отримаємо при натисканні на кнопку наступний результат:

з результату видно що макрос підібрав число з таблиці на Лісте2 відповідно до нашої сумою.
Не буду вдаватися в подробиці цього хитрого коду, так як мета даної статті - почати писати макроси. Для VBA в інтернеті є маса ресурсів, з прикладами і поясненнями, хоча для автоматизації розрахунків цілком вистачить обсягу інформації в довідці.

Таким чином за допомогою VBA можливо автоматизувати розрахунок будь-якої складності і послідовності. Довідкові таблиці можна копіювати з різної літератури на окремі листи Excel і писати послідовний розрахунок з кнопками.

Тема 2.3. Програмні засоби презентацій і основи офісного програмування

Тема 2.4. Системи управління базами даних і експертні системи

2.4.11. Навчальна база даних з головною кнопковою формою "Training_students" - Завантажити

Програмування VBA і макроси

2.3. Програмні засоби презентацій і основи офісного програмування

2.3.7. Основи офісного програмування

Візуальна мова програмування Бейсік для додатків: Visual Basic for Applications (VBA)

VBA - це підмножина візуальної мови програмування Visual Basic (VB), яке включає майже всі засоби створення додатків VB.

VBA відрізняється від мови програмування VB тим, що система VBA призначена для безпосередньої роботи з об'єктами Office, в ній не можна створювати проект незалежно від додатків Office. Таким чином, в VBA мовою програмування є VB, а інструментальне середовище програмування реалізована у вигляді редактора VB, який може активізуватися з усіх програм MS Office.

Наприклад, для того, щоб відкрити редактор VBA з програми PowerPoint необхідно виконати команду Сервіс / Макрос / Редактор VBA. Повернутися з редактора в додаток можна, вибравши команду Microsoft PowerPoint в меню Вид або комбінацією клавіш Alt + F11.

За допомогою вбудованого в редактор VBA набору елементів управління і редактора форм користувач може створити призначений для користувача інтерфейс для розроблюваного проекту з екранної формою. Елементи управління є об'єктами, а для кожного об'єкта визначено ряд можливих подій (наприклад, клацання або подвійне клацання миші, натискання клавіші, перетягування об'єкта і т.д.).

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

Той факт, що система програмування VBA призначена для роботи з об'єктами Office, дозволяє ефективно її застосовувати для автоматизації діяльності, пов'язаної з розробкою різних типів документів.

Розглянемо алгоритм створення функцій користувача в VBA:

1. Викликати вікно редактора коду VBA за допомогою команди Сервіс / Макрос / Редактор Visual Basic або натиснути Alt + F11.

2. Виконати пункт меню редактора Insert / Module (Вставка / Модуля).

3. Далі виконати Insert / Procedure (Вставка / Процедура). У відкритому діалогом вікні Add Procedure ввести ім'я функції (наприклад, СУММА5) і встановити перемикачі: Type (тип) - в положення Function (функція); Scope (Область визначення) - в положення Public (Загальна) і клацнути ОК.

Рис. 1.

4. У вікні редактора для програмування VBA з'явиться заготовка функції: заголовок - Public Function СУММА5 () і закінчення - End Function, між якими треба помістити код тіла функції.

Рис. 2.

5. Далі вводимо список параметрів функції, наприклад, в дужках вказуємо (x, y, z, i, j), тип даних (для точних розрахунків) і тип повертається функцією значення (в даному прикладі не будемо вводити). Крім того, вводимо тіло функції, наприклад, СУММА5 \u003d x + y + z + i + j. В результаті отримаємо наступний текст програми:

Public Function СУММА5 (x, y, z, i, j)
СУММА5 \u003d x + y + z + i + j
End Function

6. Повертаємося у вікно програми Excel, в якому, наприклад, нам необхідно виконати підсумовування п'яти значень.

7. Виконуємо "Вставка / Функція" і в вікні, майстра функцій вибираємо категорію «Певні користувачем», а у вікні «Виберіть функцію» виділяємо СУММА5 і клацаємо ОК.

Рис. 3.

Рис. 4.

макроси

створення макросу

Створимо макрос для автоматичного підсумовування двох чисел в осередках A1, B1 і розміщення результату в осередку C1, а також заливки осередку C1 бірюзовим кольором.

Алгоритм створення макросу для поставленого завдання:

1. Виберіть Сервіс / Макрос, Почати запис.

2. У поле Ім'я макросу введіть ім'я для макросу. Першим символом імені макросу повинна бути буква. В імені макросу не допускаються пропуски; в якості роздільників слів можна використовувати знаки підкреслення.

3. Для того щоб запускати макрос за допомогою поєднання клавіш, введіть букву в поле Поєднання клавіш. Допускається використання сполучень CTRL + буква (для малих літер) або CTRL + SHIFT + буква (для великих літер), де буква - будь-яка буквена клавіша на клавіатурі. Не вибирайте стандартного поєднання клавіш, так як вбрання поєднання клавіш пригнічує стандартні сполучення клавіш Microsoft Excel на той час з цією книгою.

4. У полі "Зберегти" виберіть книгу, в якій потрібно зберегти макрос. Збережіть макрос в «Ця книга». Для створення короткого опису макросу, введіть необхідний текст в поле Опис. На скріншоті представлений приклад заповнення вікна діалогу «Запис макросу»

Рис. 5.

5. Натисніть кнопку OK.

6. Виконайте макрокоманди, які потрібно записати.

Рис. 6.

7. Натисніть кнопку Зупинити запис на плаваючій панелі інструментів «Ос» (Зупинити запис) або Сервіс / Макрос / Зупинити макрос.

Після закінчення запису макросу він з'являється у списку макросів під своїм ім'ям.

Редактор VBA автоматично записав послідовність макрокоманд або програму по виконаним діям (Рис. 7.).

Рис. 7.

Для виклику макросу необхідно виконати команду Сервіс / Макрос / Макроси. Після цього в діалоговому вікні зі списком макросів його можна знайти по імені і натиснути кнопку Виконати.

Призначення кнопки панелі інструментів для запуску макросу

Макрос можна запустити за допомогою кнопки на вбудованої панелі інструментів, для цього необхідно виконати:

1. Вибрати пункт Налаштування в меню Сервіс.
2. У вікні діалогу Налаштування вибрати вкладку Команди вибрати параметр Макроси в списку Категорії, в списку Команди виділити «настроюється, кнопка».
3. Зі списку Команди перетягнути за допомогою миші настроюється кнопку на панель інструментів.
4. Клацнути цю кнопку правою кнопкою миші і вибрати команду Призначити макрос в контекстному меню.
5. Ввести ім'я макросу в поле Ім'я макросу.

Призначення області графічного об'єкта для запуску макросу:

1. Створіть графічний об'єкт.
2. До виділеного графічного об'єкту, застосуєте контекстне меню.
3. Виберіть в контекстному меню команду Призначити макрос.
4. У вікні діалогу "Призначити макрос об'єкту", введіть назву макросу в поле Ім'я макросу, потім натисніть кнопку OK.

Редагування макросу здійснюється редактором VBA, для цього необхідно виконати наступне:

1. Вибрати команду Сервіс / Макрос / Макроси.
2. Вибрати ім'я макросу, який слід змінити, у списку Ім'я.
3. Натиснути кнопку Змінити, буде відкрито вікно Visual Basic, в якому можливе редагування команд обраного макросу, записаних на мові Visual Basic.

Видалення макросу:

1. У меню Сервіс виберіть пункт Макрос, а потім - команду Макроси.
2. У списку макросів поточної робочої книги необхідно вибрати макрос, який передбачається видалити і натиснути кнопку Видалити.

перейменування макросу

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

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

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

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

Виберіть пункт меню «Сервіс» -\u003e «Макрос» -\u003e «Почати запис» (в Office 2007 - «Вид» -\u003e «Макроси» -\u003e «Запис макросу»). У вікні «Запис макросу» задайте ім'я нового макросу, за замовчуванням воно коштує «Макрос1», але краще дати йому назву, особливо якщо макросів буде кілька. Максимальний розмір поля імені - 255 знаків, не допускається використання символів точки і пробілу.

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

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

Макрос, створений в Word і PowerPoint, буде діяти для всіх документів в подальшому. Щоб макрос в Excel був доступний для всіх документів, збережіть його у файлі personal.xls, який запускається автоматично при запуску програми. Виконайте команду «Вікно» -\u003e «Відобразити» і виділіть у вікні рядок з назвою файлу personal.xls.

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

Виконайте команду «Сервіс» -\u003e «Макрос» -\u003e «Зупинити запис». Ви створили об'єкт VBA, не написавши жодного рядка коду самостійно. Однак якщо все ж виникне необхідність внести зміни вручну, увійдіть в об'єкт через розділ «Макрос», команда «Змінити» або за поєднанням клавіш Alt + F8.

Можливості Excel не обмежуються набором вбудованих функцій. За допомогою написання макросів Ви можете створювати власні функції для виконання нестандартних завдань в Excel.

Наприклад, самостійно написаний макрос можна прив'язати до іконки і вивести на Стрічку меню. Або Ви можете створити для користувача функцію (UDF) і використовувати її точно так же, як і інші вбудовані функції Excel.

Макрос - це комп'ютерний код, написаний для Excel на мові програмування Visual Basic for Applications (VBA). Базові поняття мови програмування VBA розглядаються на нашому сайті в Підручнику по VBA. Однак перш ніж приступити до написання коду VBA, рекомендуємо познайомитися з уроками, в яких розглядається безпеку макросів Excel і редактор Visual Basic.

Налаштування дозволу для використання макросів в Excel

В Excel передбачена вбудований захист від вірусів, які можуть проникнути в комп'ютер через макроси. Якщо хочете запустити в книзі Excel макрос, переконайтеся, що параметри безпеки налаштовані правильно.

Редактор Visual Basic

запис макросів

Інструментарій Excel для запису макросів - це відмінний спосіб ефективно виконувати прості завдання, що повторюються. Також його можна використовувати, як допоміжний засіб при написанні більш складних макросів.

Підручник Excel VBA

Для тих, хто тільки починає освоювати мову програмування Excel VBA, пропонуємо невеликий вступний курс по Visual Basic for Applications.

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

При записи макросу записуються всі дії, описані в Visual Basic для додатків (VBA) коді. Ці дії можуть включати введення тексту або чисел, натискання осередків або команд на стрічці або в меню, форматування осередків, рядків або стовпців, а також імпорт даних із зовнішнього джерела, наприклад Microsoft Access. Додаток Visual Basic (VBA) - це підмножина потужного мови програмування Visual Basic, яке входить в більшість додатків Office. Незважаючи на те, що VBA забезпечує можливість автоматизації процесів між додатками Office, вам не потрібно знати код VBA або програмне програмування, якщо це потрібно.

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

Розробник, Яка за замовчуванням прихована, тому спочатку потрібно включити її. Додаткові відомості див. У статті Відображення вкладки "Розробник".

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

на вкладці Розробник клацніть макроси, Щоб переглянути макроси, пов'язані з книгою. Крім того, можна натиснути клавіші ALT + F8. При цьому відкриється діалогове вікно Макрос.

Увага:

		Відомості про параметри безпеки макросів і їх значенні.
	Макроси можна запускати різними способами, наприклад за допомогою поєднання клавіш, графічного об'єкта, панелі швидкого доступу, кнопки або навіть при відкритті книги.
	За допомогою редактора Visual Basic можна змінювати макроси, приєднані до книги.
	призначити макрос. В полі призначити макросу
	Дізнайтеся, як вмикати та вимикати макроси в файлах Office.
	натисніть клавіші ALT + F11.

Робота з записаним кодом в редакторі Visual Basic (VBE)

За допомогою редактора Visual Basic (VBE) ви можете додавати в записаний код власні змінні, керуючі структури і інші елементи, які не підтримує засіб запису макросів. Так як засіб запису макросів фіксує майже кожен крок, що виконується під час запису, можливо, потрібно буде видалити непотрібний код. Перегляд записаного коду - відмінний спосіб навчитися програмувати на VBA або відточити свої навички.

Приклад зміни записаного коду можна знайти в статті Початок роботи з VBA в Excel.

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

Перед записом макросів корисно знати наступне:

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

Якщо вам потрібно записати довгу послідовність завдань, радимо замість цього використовувати кілька дрібніших макросів.

У макросі можуть міститися і завдання, що не належать до Excel. Процес макросу може охоплювати інші додатки Office і інші програми, які підтримують Visual Basic для додатків (VBA). Наприклад, ви можете записати макрос, який спочатку оновлює таблицю в Excel, а потім відкриває Outlook для її відправки по електронній пошті.

Макроси і засоби VBA знаходяться на вкладці Розробник, Яка за замовчуванням прихована, тому спочатку потрібно включити її.

Перейдіть в розділ Налаштування\u003e Excel... Панель інструментів &\u003e стрічки.

Щоб записати макрос, дотримуйтесь інструкцій нижче.

Робота з макросами, записаними в Excel

на вкладці Розробник клацніть макроси, Щоб переглянути макроси, пов'язані з книгою. При цьому відкриється діалогове вікно Макрос.

Примітка: Макроси можна скасувати. Перш ніж вперше запускати записаний макрос, збережіть книгу або створіть її копію, щоб запобігти внесенню небажаних змін. Якщо вас не влаштовують результати виконання макросу, ви можете закрити книжку, не зберігаючи її.

Нижче наведені додаткові відомості про роботу з макросами в Excel.

		Дізнайтеся, як вмикати та вимикати макроси в Excel для Mac.
	Якщо книга містить макрос VBA, який потрібно використовувати будь-де ще, цей модуль можна скопіювати в іншу книгу за допомогою редактора Microsoft Visual Basic.
Призначення макросу об'єкту, фігурі або графічного елементу	На аркуші клацніть правою кнопкою миші об'єкт, малюнок, фігуру або елемент, якому потрібно призначити існуючий макрос, а потім виберіть команду призначити макрос. В полі призначити макросу виберіть макрос, який ви хочете призначити.
	Ви можете призначити макрос значку і додати його на панель швидкого доступу або стрічку.
	Ви можете призначати макроси формам і елементам ActiveX на аркуші.
Відкриття редактора Visual Basic	на вкладці Розробник клацніть Visual Basic або виберіть сервіс > Макрос > Редактор Visual Basic.
	Дізнайтеся, як знайти довідку за елементами Visual Basic.

додаткові відомості

Ви завжди можете надіслати електронною поштою питання Excel Tech Community, попросити допомоги в співтоваристві Answers community, а також запропонувати нову функцію або поліпшення на веб-сайті