Створення макросів, функцій користувача, форм з елементами управління у додатках Microsoft Office

End with

End with

With Font

Або ще так

End with

Можна записати

При роботі з властивостями і методами одного й того ж об’єкта потрібно повторювати при кожному зверненні одне й теж ім’я об’єкта. Між тим існує оператор with, який дозволяє спростити написання програми і водночас покращити читабельність тексту програми. Наприклад замість коду

Якщо і вказаний робочий аркуш є активним, то посилання можна скоротити ще: Range(„А3”).

Worksheets(„Аналіз”). Range(„А3”).

Якщо вказана книга є активною, то посилання можна ще скоротити

WorkBooks(„Облік”).Worksheets(„Аналіз”). Range(„А3”).

Кожного разу застосовувати таке посилання досить обтяжливо і не завжди в цьому є потреба. Натомість в багатьох випадках застосовують неявне посилання на об’єкт. Якщо посилання використовується у програмі, яка буде виконуватись у середовищі Ms Excel, то достатньо застосувати відносне посилання у вигляді

Range(Cells(1, 2), Cells(3, 7).

Повне та неявне посилання на об’єкт. Повне посилання на об’єкт складається з переліку імен вкладених один в одного об’єктів. Наприклад, повне посилання на комірку А3 робочого аркуша з ім’ям Аналіз робочої книги Облік має вигляд:

Application. WorkBooks(„ Облік”). Worksheets(„ Аналіз”). Range(„ А3”)

Range(“A3”).Value=32

Range(“A3”).Font.Italic=True

Range(“A3”).Font.Color=vbRed

with Range(“A3”)

.Value=32

.Font.Italic=True

.Font.Color=vbRed

with Range(“A3”)

.Value=32

.Italic=True

.Color=vbRed

Цей приклад показує як виділити клітинку та помістити туди число, або формулу, або діапазон

Private Sub CommandButton1_Click()

' Вказуємо робочий файл

With Application.Workbooks.Item("M4_1_1.xls")

’ Надаємо значення клітинці А2

Range("A2") = 2+RND

’ Надаємо значення клітинці А3

Range("A3") = 3+RND

’ Задаємо у клітинку А4 формулу

Range("A4") = "=A2+A3" ' формула

’ Надаємо значення діапазону

Range("b2:b5") = 2+RND ' діапазон

MsgBox "Перейти на Лист2?"

’ Відкриваємо "Лист2"

Worksheets("Лист2").Activate

’ Надаємо нове значення клітинці А2 цього листа

Worksheets("Лист2").Range("A3") = 5+RND

MsgBox "Повернутися на Лист1?"

’ Відкриваємо "Лист1"

Worksheets("Лист1").Activate

’ Закінчуємо роботу з блоком With

End With

End Sub

Макроси. Автоматизація роботи в Microsoft Excel. Перед тим, як перейти до програмування на мові Visual Basic, познайомимося з найпростішими прикладами використання можливостей Visual Basic. Таким прикладом є макроси, для створення яких мову Visual Basic знати необов’язково. Макроси використовуються досить часто, вони дозволяють як спростити процес обробки документів, так і автоматизувати цей процес.

Макрос — це послідовність команд і функцій яка зберігається в модулі Visual Basic.
Над макросами можна виконувати такі операції:
- запис, або створення макросів;
- запуск макросів;
- керування макросами;
- забезпечення безпеки макросів.

Макроси часто використовуються з такою метою:

- для прискорення часто виконуваних операцій редагування або форматування;

- для об'єднання складних команд;

- для спрощення доступу до параметрів у діалогових вікнах;

- для автоматизації складних послідовних дій у завданнях на редагування.

Автоматизація виконання завдань за допомогою макросів MS Word. Якщо у Word необхідно багаторазово виконувати яке-небудь завдання, то найкраще це зробити за допомогою макроса. Замість того, щоб вручну робити повторювані дії, що забирають багато часу, можна створити і запускати один макрос, який буде виконувати ці дії.

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

Після присвоєння макросу кнопки панелі інструментів, команди меню або сполучення клавіш для його виконання буде достатньо вибрати команду в меню, натиснути кнопку на панелі інструментів або натиснути сполучення клавіш. Щоб запустити макрос, можна також вибрати команду Макрос у меню Сервис, команду Макросы, а потім - ім'я макроса, який потрібно виконати.

Макроси зберігаються в шаблонах або документах. За згодою макроси зберігаються в шаблоні «Обычный», щоб вони були доступні всім документам. Якщо макрос, збережений у шаблоне «Обычный», використовується тільки в документах визначеного типу, можна скопіювати цей макрос у шаблон цього типу і видалити його із шаблона «Обычный». Для копіювання, видалення або перейменування макросів використовується діалогове вікно Организатор. Щоб відкрити діалогове вікно Организатор, треба вибрати команду Макрос у меню Сервис, а потім - команду Макросы і натиснути кнопку Организатор.

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

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

Інший спосіб створити макрос - ввести його код на мові Visual Basic.

Запис макроса здійснюється так:

- вибрати команду Макрос у меню Сервис, потім - команду Начать
запись;

- в поле Имя макроса ввести ім'я нового макроса;

- вибрати в полі Макрос доступен для шаблон або документ, у якому буде зберігатися макрос. За згодою макрос зберігається в шаблоне Обычный;.

- можна ввести стислий опис макроса в поле Описание;

- для присвоєння макросу кнопки на панелі інструментів або команди меню натиснути кнопку Панели. Вибрати записуваний макрос у списку Команды і перетягнути його на панель інструментів або в меню. Натиснути кнопку Закрыть, для запису макросу;

- щоб присвоїти макросу сполучення клавіш, треба натиснути кнопку Клавишам. Вибрати записуваний макрос у списку Команды, ввести сполучення клавіш у поле Новое сочетание клавиш, а потім натиснути кнопку Закрыть для запису макроса;

- натиснути кнопку OK, щоб почати запис макроса. Під час запису Word виконує такі дії:

■ виводить поруч із покажчиком символ касети;

■ виділяє напівжирним шрифтом індикатор ЗАП (REC) у рядку стану;

■ відображає на екрані панель інструментів запису макроса;

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

- щоб закінчити запис макроса, натиснути кнопку Остановить запись на панелі інструментів запису макроса.

Якщо новому макросу буде присвоєне ім'я вже існуючого макроса, то останній буде замінений. Щоб переглянути список вбудованих макросів Word, вибрати команду Макрос у меню Сервис, а потім - команду Макросы. Вибрати Команд Word із списку Макросы из….

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

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

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

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

- щоб припинити запис макроса, треба натиснути кнопку Пауза на панелі інструментів запису макроса;

- виконати дії, що не повинні бути записані;

- для поновлення запису натиснути кнопку Возобновить запись.

Написання макроса на мові Visual Basic:

- вибрати команду Макрос у меню Сервис, а потім - команду Макросы;

- вибрати у списку Макросы из шаблон або документ, у якому буде
зберігатися макрос;

- у поле Имя макроса ввести ім'я нового макроса;

- натиснути кнопку Создать, щоб викликати редактор Visual Basic.

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

- вибрати команду Макрос у меню Сервис, а потім - команду Макросы;

- вибрати ім'я макроса, що потрібно видалити, у списку Имя;

- якщо імені потрібного макроса немає в списку, вибрати у полі Макросы из інший список макросів;

- натиснути кнопку Удалить.

Щоб видалити відразу кілька макросів, треба виділити їх у списку Имя, утримуючи натиснутою клавішу CTRL, а потім натиснути кнопку Удалить.

Виконання макроса:

- вибрати команду Макрос у меню Сервис, а потім - команду Макросы;

- вибрати ім'я макроса, що потрібно виконати, у списку Имя;

- якщо потрібного макроса немає в списку, вибрати у полі Макросы из інший список макросів;

- натиснути кнопку Выполнить.

Зміна макроса:

- вибрати команду Макрос у меню Сервис, а потім - команду Макросы;

- вибрати ім'я макроса, що потрібно відредагувати, у списку Имя;

- якщо імені потрібного макроса немає в списку, вибрати у полі Макросы из інший список макросів;

- натиснути кнопку Изменить.

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

Копіювання макроса

Для копіювання макроса в інший шаблон або документ використовується діалогове вікно Организатор:

- вибрати команду Макрос у меню Сервис, а потім - команду Макросы;

- натиснути кнопку Организатор;

- вибрати потрібні макроси в одному із списків, а потім натиснути кнопку Копировать.

Макроси, що використовуються в активному документі, виводяться в списку зліва, а стилі в шаблоні «Обычный» - у списку справа.

Перейменування макросів:

- вибрати команду Шаблоны и надстройки в меню Сервис;

- натиснути кнопку Организатор;

- вибрати вкладку Макросы;

- у лівому списку вибрати елемент, що треба перейменувати, а потім
натиснути кнопку Переименовать;

- у діалогове вікно Переименование ввести нове ім'я елемента;

- натиснути кнопку OK, а потім - кнопку Закрыть.

Автоматизація виконання завдань за допомогою макросів MS Excel.

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

Перед тим як написати макрос, необхідно спланувати кроки і команди, що він буде виконувати. Якщо при записі макроса була допущена помилка, її виправлення буде також записано. Щораз при записі макроса, він зберігається в новому модулі, приєднаному до книги. Редактор Visual Basic дозволяє змінювати макроси, а також копіювати їх або з одного модуля в інший, або між різноманітними книгами. Крім того, можна перейменовувати модулі, у яких зберігаються макроси, або перейменовувати самі макроси.

Запис макросів. При записі макросу Microsoft Excel зберігає інформацію про кожен крок виконання послідовності команд. Наступний запуск макросу викликає повторення (відтворення) команд. Якщо при записі макросу була допущена помилка, зроблені виправлення також будуть записані. Кожен макрос зберігається в окремому модулі, прикріпленому до книги. Запис макросів відбувається за допомогою редактора Visual Basic.

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

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

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

Безпека макросів. Microsoft Excel забезпечує захист від комп'ютерних вірусів, що можуть передаватися макросами. Якщо макроси використовуються разом з іншими користувачами, макроси можуть бути позначені цифровим підписом – електронним підписом, що підтверджує дійсність макросу або документа. Наявність цифрового підпису підтверджує, що макрос або документ був отриманий від власника підпису і не був змінений. Так що користувачі можуть перевірити надійність джерела макросів. Кожен раз при відкритті книги, що містить макроси, можна перевірити джерело макросів.

Створення функції користувача. В загальному вигляді функція користувача має наступний вигляд:

Синтаксис:

Function Ім’яФункції (СписокПараметрів)

[ Інструкції ]

End Function

СписокПараметрів – це список параметрів, від яких залежить функція. Розділителем в списку параметрів є кома.

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

Для створення функцій користувача потрібно виконати дії:

· якщо в проекті немає модуля, то створити його можна, виконавши команду меню редактора VB Вставка/Модуль;

· виконати команду меню редактора VB Вставка/Процедура;

· у діалоговому вікні, що відкрилося, Вставка процедури встановити перемикач Функція (рис.14);

· у вікні Ім'я ввести ім'я функції;

· встановити відповідний перемикач Область визначення;

· клацнути на OK. Після виконання цих дій у вікні редагування модуля з'явиться заготівка функції (заголовок і закінчення), між якими потрібно помістити код тіла функції;

· ввести список параметрів функції, їх типів даних, а також вказати тип повертаємого функцією значення;

· використовуючи команду меню Вид/Перегляд об'єктів або натиснувши клавішу F2, викликати вікно Перегляд об'єктів;

· розкрити список верхнього лівого вікна (Список проектів) і вибрати з нього VBA Project. У вікні Класи відобразяться елементи поточного проекту;

· вибрати в цьому вікні модуль, в якому створена функція, - у вікні Компоненти відобразяться елементи, які містяться в цьому модулі (рис.15);

· виділити у вікні Компоненти елемент з ім'ям створеної функції і включити контекстне меню (рис. 16);

· виконати команду контекстного меню Властивості - відкриється вікно Параметри компоненту (рис. 17).

· у полі Опис цього вікна ввести текст короткого опису функції; якщо необхідно, то вказати файл довідки і ідентифікатор.

Рис. 4.1.2.

Рис. 4.1.3.

Рис. 4.1.4.

Рис. 4.1.5.

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

Даємо ім’я процедури: MyFirstProgram, вказуємо тип процедури, наприклад Sub, натискуємо ОК.

На листі отримуємо:

Public Sub MyFirstProgram()

[інструкції]

Дата добавления: 2014-12-07