Навігація
Головна
 
Головна arrow Інформатика arrow Інформатика для економістів

Застосування VBA для вирішення практичних завдань

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

Приклад 12.1. Створимо в Microsoft Excel функцію користувача, яка в зазначеному діапазоні підраховує кількість осередків, значення яких відповідають заданим шаблоном.

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

Таблиця 12.33

Символи шаблону

Символ шаблону

Символ рядка

?

Будь одиночний символ

*

Будь-яка кількість символів або відсутність символу

#

Будь-які одинарні цифра (0-9)

[перелік]

Будь одиночний символ, що входить до списку

[!перелік]

Будь одиночний символ, що не входить в список

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

Public Function СчетШаблон (Діапазон As Range, _

Шаблон As String)

Dim k As Integer, r

k = 0

For Each r In Діапазон

If r Like Шаблон Then k = k + 1

Next

СчетШаблон = k

End Function

У нашій функції ми скористалися операцією порівняння рядків Like, наявної в VBA. Шаблон задається відповідно до синтаксисом VΒA. У результаті вийшла проста, але дуже корисна для статистичної обробки текстових даних функція.

Щоб додати опис функції, яке виводиться у вікні Майстер функцій, відкрийте вікно Макрос (вкладка Розробник), введіть назву функції (СчетШаблон) у полі Назва макросу, клацніть на кнопці Параметри. Введіть опис функції в поле Опис.

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

На ріс.12.1 показано використання функції на робочому листі. Рядок шаблону в даному випадку має вигляд "Мо * 17" "*". Серед найменувань товарів ми шукаємо монітори з довжиною діагоналі 17 ". Щоб задати символ" "" всередині текстової константи, потурбувалися вказати його двічі, щоб було назадній, що це не кінець рядка.

Використання функції користувача

Мал. 12.1. Використання функції користувача

Приклад 12.2. Нехай на робочому листі Excel починаючи з клітинки А1 розташована таблиця з результатами іспитів. Напишемо макрос в Excel, який створить новий документ Word, помістить в нього заголовок "Відомість" і таблицю, скопійовану з Excel.

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

Public Sub PrintToWord ()

Dim wrd As Word.Application

Set wrd = CreateObjectfWord.Application ")

With wrd

.Visible = True

.Documents. Add

End With

With wrd.Selection

.ParagraphFormat.Alignment = WdAlignParagraphCenter

Font.Bold = wdToggle

.Font.Size = 16

.TypeText Text: = "Відомість"

.TypeParagraph

End With

Range ("A1"). CurrentRegion.Copy

With wrd.Selection

.TypeParagraph

.Paste

.Tables (1) .AutoFormat Format: = wdTableFormatGrid3

.Tables (1) .Select

.Font.Size = 12

.ParagraphFormat.Alignment = WdAlignParagraphLeft

.ParagraphFormat.FirstLinelndent = _

CentimetersToPoints (0.44)

.Columns.Width = LnchesToPoints (1.5)

.Rows (1) .Select

.Font.Bold = True

End With

Set wrd = Nothing

End Sub

Особливістю цього макросу є те, що ми в Excel використовуємо об'єкти іншого додатка (Word). Щоб це стало можливим, виконайте команду Tools / Refercnces, у вікні, знайдіть рядок Microsoft Word: 14.0 Object Library (замість 14.0 на вашому комп'ютері може бути інша версія), поставте поруч з нею галочку (клацніть мишею) і натисніть кнопку ОК.

Усі звернення до об'єктів Word повинні починатися з головного об'єкта Word.Application. У нашому випадку його опускати не можна, так як за замовчуванням буде використовуватися об'єкт Excel. Application з іншими властивостями і методами. При роботі з об'єктами Excel об'єкт Excel.Application можна не вказувати, оскільки макрос створений в Excel. Об'єкт Word.Application в макросі створює функція CreateObject. Вона запускає новий екземпляр програми Word і повертає посилання на створений об'єкт, яка зберігається у змінній wrd. Саме ця змінна використовується в програмі для доступу до об'єктів Word.

Інформація передається з робочої книги в документ Word, як зазвичай, через буфер обміну. У макросі для цієї мети використовуються методи Сору і Paste відповідних об'єктів.

Методи TypeText і TypeParagraph об'єкта Selection вставляють текст і порожній абзац у виділений фрагмент документа.

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

Щоб виконати макрос, перейдіть на лист з даними і виконайте команду Розробник / Макроси. Знайдіть у списку ім'я макросу PrintToWord і натисніть Виконати. Якщо в цьому вікні вибрати Параметри, то макросу можна призначити поєднання клавіш, яке буде використовуватися для виклику макросу.

Приклад 12.3. У робочій книзі є таблиця з інформацією про одержувачів, що складається їх двох стовпців: Одержувач і Адреса (Лист1), і таблиця, що містить назви всіх вулиць (Аркуш2). Дані, що знаходяться на цих аркушах, показані на рис. 12.2.

Створимо діалогове вікно для введення інформації в таблицю одержувачів. Вид вікна приведений на рис. 12.3.

Дані, що знаходяться на аркушах Лист1 і Лист2

Мал. 12.2. Дані, що знаходяться на аркушах Лист1 і Лист2

Діалогове вікно для введення даних в таблицю

Мал. 12.3. Діалогове вікно для введення даних в таблицю

Щоб створити таке вікно, перейдіть в редактор Visual Basic. Додайте до проекту модуль форми. У нашому проекті ім'я цієї форми - UserForml. Додайте у формі елементи управління: чотири поля для введення прізвища (TextBoxl), будинки (TextBox2), корпусу (TextBox3) і квартири (TextBox4); поле зі списком для введення назви вулиці (ComboBoxl); п'ять елементів Label, які використовуються для виведення пояснювальних написів поруч з попередніми елементами; кнопки Додати (Command Button 1) і Вихід (CommandButton2).

У вікно коду форми помістіть наступний текст:

Private Sub CommandButton1_Click ()

ActiveCell.Value = TextBoxl.Text If Right (ComboBox1 .Text, 4) = "nep." Or _

Right (ComboBox1.Text, 6) = "бульв." Then

S = ComboBoxl .Text

Else

S = "вул." & ComboBoxl .Text

End If

S = S & ", будинок" & TextBox2.Text

If TextBox3.Text <> "" Then S = S & ", корп. "& TextBox3.Text

S = S & ", kb." & TextBox4.Text

ActiveCell.Next. Value = S

Cells (ActiveCell.Row + 1, ActiveCell.Column) .Activate

TextBoxl .Text = ""

TextBox2.Text = ""

TextBox3.Text =

TextBox4.Text = ""

End Sub

Private Sub CommandButton2_Click ()

UserForml.Hide

End Sub

Процедура CommandButtonl_Click виконується, коли буде натиснута кнопка Додати. Вона переносить дані з форми на робочий лист. Вміст поля TextBoxl записується в активну комірку робочого аркуша, сформований адреса - в сусідню з нею осередок праворуч (для доступу до цього вічка використовується властивість Next). Курсор встановлюється в осередок, що знаходиться в наступному рядку, і всі нуля очищаються.

Процедура CommandButton2_Click виконується, коли буде натиснута кнопка Вихід. За допомогою методу Hide форма забирається з екрану.

Щоб форму було зручно викликати при роботі з таблицею, поступимо таким чином. У програмному модулі (не в модулі форми!) Створимо наступний макрос:

Public Sub Adrlnput ()

UserForml .ComboBoxI .RowSource = _

"Аркуш2!" & Ліст2.Range ("A3"). CurrentRegion.Address

UserForml.Show

EndSub

У макросі Adrlnput спочатку елемент ComboBoxI заповнюється значеннями, а потім форма виводиться на екран. Для заповнення списку ComboBox1 використовується властивість RowSource, якому присвоюється посилання на діапазон робочого аркуша Лист2 з назвами вулиць (у вигляді текстового рядка). Так як макрос знаходиться не в модулі форми, то для доступу до елементу ми спочатку повинні вказати ім'я форми, а потім, після точки, ім'я елемента. За умовою задачі відомо, що назви вулиць розташовуються починаючи з клітинки А3. Використовуючи для цього діапазону властивість CurrentRegion, отримуємо новий діапазон, що містить всі назви вулиць. Такий прийом дозволяє міняти список вулиць (додавати, видаляти), програма не залежить від його розміру. Властивість Address повертає текстовий рядок, відповідну посиланню на обчислений діапазон, наприклад "А3: А15". Додавши за допомогою операції конкатенації до отриманої рядку ім'я аркуша, ми отримали потрібне значення властивості. Метод Show виводить форму на екран.

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

Детально питання практичного використання VBA висвітлені в книзі "Інформатика для економістів: практикум" під ред. В. П. Полякова, В. П. Косарєва (, 2012).

 
Якщо Ви помітили помилку в тексті позначте слово та натисніть Shift + Enter
< Попередня   ЗМІСТ   Наступна >
 
Дисципліни
Агропромисловість
Аудит та Бухоблік
Банківська справа
БЖД
Географія
Документознавство
Екологія
Економіка
Етика та Естетика
Журналістика
Інвестування
Інформатика
Історія
Культурологія
Література
Логіка
Логістика
Маркетинг
Медицина
Нерухомість
Менеджмент
Педагогіка
Політологія
Політекономія
Право
Природознавство
Психологія
Релігієзнавство
Риторика
Соціологія
Статистика
Техніка
Страхова справа
Товарознавство
Туризм
Філософія
Фінанси
Пошук