Форматування: зробити текст жирним

Введення

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

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

Суть мови заключається в оперуванні об'єктами (що відносить її до об'єктно-орієнтованого програмування).

Щоб працювати з VBA кодом, потрібен редактор (Visual Basic Editor), який вже встановлений в Excel по замовчуванню. Ви можете відкрити його, натиснувши комбінацію клавіш "ALT+F11".

VBA є мовою об'єктно-орієнтованного програмування, тобто вона працює з об'єктами Excel, використовуючи властивості і методи об'єктів.

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

У середовищі VBA можна створювати:

· програми (вони називаються макроси або модулі), що запускаються через системне меню. Ці програми оформляються як процедури без параметрів;

· функції користувача, що визиваються так само, як і вбудовані стандартні функції Excel;

· процедури з параметрами, що визиваються в програмах спеціальним оператором;

· форми - діалогове вікно для спілкування з користувачем і виконання програм. Для запуску форм звичайно створюється макрос, закріплений за кнопкою.

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

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

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

2 Об'єкти Excel, їхні властивості і методи …………………...…... 6

Об'єкт - це елемент, структурна частинка Excel, а саме: книга, аркуш, діапазон, комірка. Дані об'єкти мають ієрархію, тобто підпорядковуються один одному. Схематично структуру ієрархії Excel можна зобразити наступним чином:

Найважливішими є такі візуальні об'єкти:

· Application - програма Excel,

· Workbook - робоча книга,

· Worksheet - робочий лист,

· Range - діапазон клітин,

· Chart - діаграма,

· UserForm - форма користувача.

Розрізняють прості об'єкти і сімейства. Сімейство являє собою об'єкт, що містить декілька інших об'єктів одного типу. Наприклад, сімейство Workbooks (робочі книги) містить усі відкриті об'єкти Workbook. Кожний елемент сімейства нумерується і має ім'я. Щоб звернутися до нього вказують ім'я сімейства, а в дужках або номер об'єкта в сімействі або ім'я (в апострофах). Наприклад, Worksheets(1) - перший робочий лист активної книги, а Worksheets(“Лист1”) - робочий лист активної книги з ім'ям Лист1.

Об'єктна бібліотека VBA містить більше 100 різноманітних об'єктів, що знаходяться на різноманітних рівнях ієрархії. Ієрархія визначає зв'язок між об'єктами і показує шляхи доступу до них.

Повне посилання на об'єкт складаються з ряду імен вкладених послідовно друг у друга об'єктів. Роздільниками імен об'єктів у цьому ряду є точки. Ряд починається з об'єкта Application і закінчується ім'ям самого об'єкта. Наприклад, посилання на клітину А1 робочого листа Лист1 робочої книги з ім'ям Архів має вид:

Application. Workbooks(“Архів”). Worksheets(“Лист1”). Range(“A1”)

Приводити щораз повне посилання не обов'язково. Активні в даний момент об'єкти можна опускати. Якщо додаток Excel і робоча книга Архів є активними, то посилання можна записати:

Worksheets(“Лист1”). Range(“A1”).

Для роботи з об'єктами потрібно знати, які дії можна чинити над об'єктом, і якими характеристиками він володіє.

Метод являє собою дію, що виконується над об'єктом. Синтаксис застосування методу:

Об'єкт . Метод список_параметрів

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

1) задавати тільки значення параметрів, строго дотримуючи порядок, зазначений в оголошенні методу, і розділяючи значення комою;

2) задавати ім'я параметра := значення параметра, …
такий засіб завдання параметрів називається завданням по імені, дозволяє задавати не всі параметри та у довільному порядку, але необхідно знати імена параметрів. Імена параметрів зазначені в довідковій системі VBA для кожного методу.

Приведемо приклад виклику методу, що сортує список по стовпчику А за зростанням, а потім по стовпчику С за спаданням (складне сортування), двома зазначеними засобами:

1) Range(“A1:D28”). Sort Range(“A1”), xlAscending,_ Range(“C1”), xlDescending

2) Range(“A1:D28”). Sort key1 := Range(“A1”),_ Order1:=xlAscending, key2:=Range(“C1”), Order2:=xlDescending

Властивість- являє собою атрибут об'єкта, що визначає його характеристики, такі як зміст, розмір, колір. Щоб змінити характеристики об'єкта, треба просто змінити значення його властивостей. Це робиться в операторі присвоювання. Синтаксис зміни значення властивості:

Об'єкт . Властивість = ЗначенняВластивості

Подія - являє собою дію, що розпізнавана об'єктом (наприклад, щиголь мишею - Click або натискання клавіші), для якого можна запрограмувати відгук. Події виникають у результаті дій користувача або програми, або ж вони можуть бути викликані системою. Суть програмування на VBA саме і полягає в цих двох поняттях: подія і відгук на нього. Як відгук виконується код створеної користувачем процедури.

2. 1 Об'єкт Workbook і сімейство Workbooks ………………….… 8

Об'єкт Worksheet і сімейство Worksheets.У ієрархії об'єктів об'єкт Workbook - робоча книга, йде відразу після об'єкта Application і являє собою файл робочої книги. Властивості і методи робочої книги дозволяють працювати з файлами.

Властивості:

ActiveSheet - повертає активний лист книги;

Наприклад, оператор

MsgBox “Ім'я активного листа” & ActiveSheet . Name

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

Sheets - повертає сімейство всіх листів книги;

Worksheets -повертає сімейство усіх робочих листів книги.

Методи:

Activate - активізує робочу книгу так, що її перший лист стає активним; наприклад,

Workbooks(“Книга1”) . Activate

· Add - створює новий об'єкт для сімейства Workbooks;

· Open - відкриття існуючої робочої книги;

· Close - закриття робочої книги;

· Save - зберігання робочої книги;

· SaveAs - зберігання робочої книги в іншому файлі;

Наприклад,

ActiveBook . SaveAs Filename : = ”НоваВерсія . xls”

Властивості:

Name - повертає ім'я робочого листа; наприклад, привласнити нове ім'я першому робочому листу можна оператором:

Worksheets(1). Name=”Результати за грудень”

UsedRange - повертає діапазон, тобто об'єкт Range, що містить дані;

ActiveCell - повертає активну клітину активного робочого листа.

Методи:

Activate - активізує зазначений робочий лист;

Наприклад,

Worksheets(“Лист2”) . Activate

Add - створює новий робочий лист;

Наприклад,

ActiveWorkbook . Worksheets . Add

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

Delete - видаляє робочий лист;

Наприклад,

WorkSheets(“Результати за грудень”) . Delete

2.2 Об'єкт Worksheet і сімейство Worksheets ……………….….. 9

2.3 Об'єкти Range, Cells і Selection …….………………………... 9

Об'єкт Range - це клітина або діапазон клітин, він є одним із ключових об'єктів VBA. Адреса клітини або діапазону вказується в дужках як символьний рядок. Наприклад,

Range(“A1”) - клітина A1;

Range(“B2:D28”) - діапазон клітин.

Об'єкт Cells(s1, s2) - указує на клітину, номер рядка котрої s1, номер стовпчика - s2. У якості s1 і s2 можна використовувати або числа або перемінні. Наприклад, Cells(3,2) - указує на клітину B3. Цей об'єкт особливий тим, що в якості s1 і s2 можна вказувати перемінні, що дозволяє організовувати цикли по діапазонах клітин для обробки даних.

Наприклад, до клітини В3 можна звернутися і так:

i=3 : j=2 : Cells(i,j). Select

Об'єкт ActiveCell - це клітина, що є в даний момент активною. Активізується клітина звичайно в результаті роботи методу Select.

Об'єкт Selection (вибір) - це клітина або діапазон клітин, що відзначені або обрані користувачем як результат роботи методу Select.

Наприклад, виділяємо потрібний діапазон і змінюємо колір клітин виділеного діапазону:

Range(B2:C28”). Select

Selection. Interior. ColorIndex=15

Об'єкт Rows - звичайно застосовується до діапазону і позначає рядки діапазону.

Об'єкт Columns - звичайно застосовується до діапазону і позначає стовпчики діапазону.

Властивості:

· Name - задає ім'я діапазону клітин; наприклад,
Range(“A2:F12”) . Name=”Продажі”

· Value - задає значення клітині; наприклад, оператори
Range(“A2”). Value=”Товар”

Range(“C3”). Value=123

у зазначені клітини заносять значення, а оператор

a=Range(“C2”). Value

заносить у змінну а значення з клітини С2;

· Count - повертає кількість об'єктів у наборі; у прикладі перемінної k присвоюється значення, рівне числу рядків діапазону A1:D4

k = Range(“A1:D4”). Rows. Count

· CurrentRegion - повертає поточний діапазон. Поточним є діапазон, обмежений порожніми рядками і стовпчиками, що містить даний елемент.

Діапазон повинний бути оформлений як список. Наприклад, оператор

z=Range(“A1”). CurrentRegion. Rows. Count

у змінну z записує число рядків поточного діапазону.

· EntireRow, EntireColumn - повертає стовпчик і рядок відповідно. У прикладі очищається рядок і виділяється стовпчик з активною клітиною:

ActiveCell. EntireRow. Clear

ActiveCell. EntireColumn. Select

Formula - задає формулу в клітині; формула задається як рядок символів, записаний по правилах Excel. Наприклад,

Range(“D3”). Formula = ”=C3*0,25”

Range(“C10”). Formula = ”=sum(C3:C9)”

Функції, що використовуються у формулах, повинні мати

англійські імена.

FormulaR1C1 - задає формулу в клітині, у якій адреси клітин записуються в стилі R1C1, тобто записується буква R, а після неї - номер рядка, потім буква С и після неї - номер стовпчика. Наприклад, R3C2 відповідає клітині B3. Посилання на клітини можуть бути абсолютними і відносними. Відносні записуються в квадратних дужках як зсув щодо клітини, у якій записується формула. Наприклад,

ActiveCell. Value=24 ‘в активну клітину записуємо 24

ActiveCell. Offset(1,0). Value=7 ‘в клітину під активною - число 7

ActiveCell. Offset(2,0). Select ‘активізуємо клітину на 2 нижче

ActiveCell. FormulaR1C1= ” =R[-2]C[0]+R[-1]C[0] ”

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

· FormulaLocal - повертає російськомовні формули у форматі А1, тобто при запису таких формул необхідно вказувати російські імена функцій Excel.

Наприклад, наступна інструкція вводить в клітину В2 формулу:

Range(“B2”). FormulaLocal = ”=СУММ(C1:C4)”

Text - повертає інформацію, що в клітині, в текстовому форматі.

Offset(зсув_по_рядку, зсув_по_стовпчику) - повертає діапазон, усунутий щодо даного на розміри, зазначені в аргументах;

Наприклад,

ActiveCell. Offset(1,0). Select

активізує клітину, розташовану під активною, тобто усунуту на один рядок униз;

Font - задає властивості шрифту, яким відображається вміст клітини. Має такі власні властивості:

· Name - ім'я шрифту; (Arial, Times New Roman, …);

· Size – розмір шрифту;

· Bold - може бути True або False у залежності від жирності шрифту;

· Italic - курсив, якщо властивість встановлена в True;

· Underline - підкреслення.

Наприклад, змінимо шрифт, розмір та жирність тексту в клітині:

Range(“A1”). Font. Name = ”Arial”

Range(“A1”). Font. Size = 18

Range(“A1”). Font. Bold = True

· Borders - задає межу, що обмережує клітину або діапазон клітин. Має такі власні властивості:

· LineStyle - стиль лінії, може приймати значення:

· xlContinuous (суцільна), xlDouble (подвійна), xlDash (пунктирна);

· ColorIndex - колір лінії, указується цілим числом від 1 до 49; приведемо деякі кольори:

· Weight - товщина лінії, може приймати значення: xlThin (тонка), xlMedium (середня), xlThick (товста).

Наприклад,

Range(“B2:D14”). Borders. LineStyle = xlDouble

Range(“B2:D14”). Borders. Weight = xlMedium

Проводить межу навколо діапазону B2:D14 подвійною лінією середньої товщини.

Якщо необхідно задати не всі межі, а тільки частину з них або проводити межі різними лініями, то використовують властивість Borders(параметр). Параметр указує, яка межа задається, і може приймати значення: xlEdgeTop (угорі), xlEdgeBottom (знизу), xlEdgeLeft (зліва), xlEdgeRight (справа).

Наприклад, оператори

Range(“B2:D14”). Borders(xlEdgeTop). LineStyle = xlContinuous

Range(“B2:D14”). Borders(xlEdgeBottom). LineStyle = xlDouble

Range(“B2:D14”). Borders(xlEdgeLeft). LineStyle = xlDash

Range(“B2:D14”). Borders(xlEdgeRight). LineStyle = xlDash

Проводять зверху, знизу, зліва і справа зазначеного діапазону різні типи ліній.

Методи

· Copy - копіює діапазон в інший діапазон або в буфер обміну. Має один аргумент destination , що визначає діапазон, куди копіюється вихідний. Якщо аргумент опущений, діапазон копіюється в буфер. Наприклад, копіюємо діапазон А1:С4 з одного листа на інший:

Worksheets(“Лист1”). Range(“A1:C4”). Copy

Destination := Worksheets(“Лист2”). Range(“E5”)

· Cut -копіює діапазон із видаленням у зазначений діапазон або буфер обміну. Якщо аргумент destination не зазначений, то діапазон копіюється в буфер обміну.

· Delete - видаляє діапазон. У даному прикладі видалиться третій рядок активного робочого листа:

Rows(3). Delete

· Columns, Rows - повертає відповідно сімейства стовпчиків і рядків, із яких складається діапазон. Наприклад, у перемінні i та j записуються кількості стовпчиків і рядків у виділеному діапазоні відповідно:

i=Selection. Columns. Count

j= Selection. Rows. Count

· Insert - вставка клітини або діапазону клітин відповідно. У прикладі вставляється новий рядок перед четвертим рядком:

Worksheets(“Лист1”). Rows(4). Insert

· Select - виділяє діапазон. У прикладі активізується клітина, розташована на три рядка нижче і на два стовпчики лівіше щодо попередньої активної клітини:

ActiveCell. Offset(3,2). Select

· PasteSpecial - спеціальна вставка з буфера обміну. Аргумент Paste визначає ту частину вмісту буфера обміну, що повинна бути вставлена в діапазон. Припустимі значення:

· XlPasteAll - усе;

· XlPasteFormulas - формули;

· XlPasteValues - значення;

· XlPasteFormats - формати.

У прикладі діапазон А1:Е5 заповнюється цілими випадковими числами від 0 до 200 (в клітинах записані формули), а потім на інший лист копіюються тільки значення з клітин:

Worksheets(“Лист1”).Range(“A1:E5”). Formula = “=int(rand()*200))”

Worksheets(“Лист1”). Range(“A1:E5”). Copy

Worksheets(“Лист2”). Range(“A1:E5”). PasteSpecial _

paste:=xlPasteValues

· AutoFill - автоматично заповнює клітини діапазону елементами послідовності. Вручну цей метод еквівалентний протягуванню маркера заповнення уздовж діапазону, що заповнюється. Аргументи:

§ Destination - діапазон, що заповнюється. Повинний містити в собі вихідний діапазон із початковими даними;

§ Type - тип заповнення, може приймати одне зі значень: xlFillDefault, xlFillSeries, xlFillCopy, xlFillValues, xlFillDays.

У прикладі клітини В2:В12 заповнюються непарними числами від 1 до 21:

Range(“B2”). Value=1

Range(“B3”). Value=3

Range(“B2:B3”). AutoFill destination:=Range(“B2:B12”)

§ Find - використовується для пошуку клітини, що містить зазначену інформацію. Повертає покажчик на знайдену клітину або значення Nothing, якщо не знайдено. Вручну метод Find визивається через пункти меню Правка, Найти. Аргументи:

· What - указує, що шукати в діапазоні;

· After - указується перша клітина, після якої провадиться пошук; якщо аргумент опущений, то пошук провадиться у всьому діапазоні;

· Lookin - область пошуку; припустимі значення: xlFormulas - формули, xlValues - значення;

· LookAt - припустимі значення: xlWhole – збіг клітини цілком, xlPart – збіг тільки частини клітини.

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

Range(“E6”). Formula = “=Min(B2:E5)”

Range(“B2:E5”).Find(Range(“E6”).Value, , xlValues, xlWhole).Select

· Sort - метод здійснює сортування рядків списків і баз даних, а також стовпчиків робочих листів з урахуванням до трьох критеріїв, по яких провадиться сортування. Синтаксис:

Об'єкт. Sort (key1,order1,key2,order2, key3,order3)

Key - посилання на поле, по якому провадиться сортування;

Order - задає порядок сортування. Припустимі значення:

XlAscending - зростаючий порядок; XlDescending - убутний порядок;

Наприклад, діапазон А1:С20 робочого листа Лист1 сортується в порядку зростання так, що початкове сортування відбувається по першому стовпчику цього діапазону, а другорядне - по другому:

Range(“A1:C20”). Sort key1:=Range(“A1”), key2:=Range(“B1”)

Найголовніший об'єкт це Application, що відповідає самій програмі Excel. Далі іде Workbooks (книга), Worksheets (аркуш), Range (діапазон, або окрема комірка).

Наприклад, щоб звернутися до комірки "A1" на аркуші, нам потрібно буде прописати наступний шлях з врахуванням ієрархії:

Application.Workbooks("Архів").Worksheets("Лист1").Range("A1").

Колекції (Collections).В свою чергу об'єкти мають "колекції". Колекція - це група об'єктів однакового класу. Окремі елемети колекції є також об'єктами. Так, об'єкти Worksheets є елементами колекції об'єкту Worksheet, який містить також й інші колекції та об'єкти:

· ChartObjects (елемент колекції об'єкту ChartObject)

· Range

· PageSetup

· PivotTables (елемент колекції об'єкту PivotTable).

Властивості (Properties).Кожен об'єкт має властивості. Наприклад, об'єкт Range має властивість Value або Formula.

Worksheets(“Sheet1”).Range(“A1”).Value або

Worksheets(“Sheet1”).Range(“A1”).Formula

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

Також, через властивість Formula можна не тільки отримати формулу, але і записати її:

MsgBox Range(“A1”).Formula - отримаємо повідомлення з формулою в комірці "А1";

Range(“B12”).Formula = “=2+6*100” - записуємо формулу =2+6*100 в комірку B12.

Методи (Methods).Розглянемо, яким чином ми можемо керувати вмістом діапазону або комірки. Для цього в VBA існують методи (команди "що зробити"). При написанні коду методи відділяються від об'єкта крапкою, наприклад:

Range("A1").Select або

Cells(1, 1).Select

Даний метод вказує вибрати (Select) комірку "A1". Щоб видалити значення в даній комірці потрібно записати наступний код.

Selection.ClearContents

Тут програма віділяє (Selection) та видаляє його вміст (ClearContents).

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

Відкрийте Visual Basic Editor (Alt+F11). Створіть в ньому модуль та пропишіть наступний код:

1. Sub LearningDebug()

2. Dim A As Long, B As Long, C As Long, D As Long

3.

4. D = 0

5. A = 10

6. Debug.Print "A = " + Trim(Str(A))

7. B = 15

8. Debug.Print "B = " + Trim(Str(B))

9. C = A + B

10. Debug.Print "C = " + Trim(Str(C))

11. C = Round(C / 5)

12. Debug.Print "С ділимо на 5: C = " + Trim(Str(C))

13. C = Round(C / D) ' тут буде помилка, оскільки на нуль ділити неможна (а D = 0)

14. End Sub

Натисніть Ctrl+G, при цьому внизу з'явиться вікно Immediate. У цьому вікні можна в ході роботи макросу змінювати значення змінних, виконувати різний код на VBA, не написаний в модулі раніше.

Якщо стати курсором всередину коду модуля і натиснути F5, він виконається цілком. Якщо натиснути F8 - виконається тільки один рядок коду.

Наступне натискання F8 виконає наступний рядок коду і т.д. можна виконати покроково весь код.

За допомогою кнопки F9 можна створити точку зупинки. Якщо потім ви запустите код за допомогою кнопки F5, код буде виконаний до зазначеного рядка і потім призупиниться. Далі його можна буде продовжити за допомогою кнопок F5 або F8, описаних раніше.

Якщо уважно розглянути код, ви побачите, що в змінні A, B, C, D присвоюються числа.

Рядки Debug.Print "текст" друкують зазначений нами текст у відкрите раніше вікно Immediate, щоб бачити, що відбувається із змінною. Str (A) перетворює число в текстове значення. А Trim () прибирає з нього праворуч і ліворуч прогалини. Оператор Round (С) округлює значення за правилами арифметики до цілого числа (тому результат обчислень ми присвоюємо в змінну цілого типу Long, значить він повинен бути цілим). В кінці коду ми спеціально створили помилкову ситуацію, щоб потренуватися використовувати дебагер.

Станьте всередину коду і натисніть F8 чотири рази. Жовтим буде відзначено рядок, який буде виконаний наступним. Наведіть курсор на різні змінні. При цьому з'явиться спливаюче віконце, в якому побачите їх значення. Змінні, яким ще не були привласнені значення дорівнюють нулю.
Натисніть ще раз F8. У вікні Immediate з'явиться рядок:
А = 10
Тепер ми можемо змінити значення змінної A одразу у вікні Immediate (наприклад, змінимо його на 8). Для цього в новому рядку вікна Immediate напишіть:
А = 8
і натисніть Enter. Тепер наведіть на змінну A курсор і побачите, що його значення дорівнює 8. щоб побачити значення, не наводячи курсор, можна змусити його відобразитися у вікні Immediate. Робиться це так, введіть в порожньому рядку вікна Immediate:
?A
і натисніть Enter. Знак ? у вікні Immediate означає те ж саме, що і Debug. Print в коді. Просто так зручніше і коротше писати. Відразу ви побачите число - результат вашого запиту.

Станьте курсором на останній рядок коду (C = Round (C / D)) і натисніть F9. З'явиться точка зупинки. Тією ж кнопкою її можна прибрати, але ми її прибирати поки не будемо. Натисніть F5, програма виконає всі рядки і зупиниться на останній. У вікні Immediate з'являться, повідомлення про присвоєння значень змінним. Ми дісталися до останнього рядка коду. Він повинен викликати помилку, так як містить ділення на нуль.

Натисніть F8 і переконаєтеся в цьому. У вікні, що з'явиться, натисніть Debug. Якщо ви натиснете End, виконання програми зупиниться, а ми хочемо довести її до кінця.

Виправити ситуацію можна, змінивши значення змінної D. Зараз вона дорівнює нулю. У вікні Immediate введіть в порожньому рядку текст:
D = 2
і натисніть Enter. Тепер останнім рядком коду ми ділимо не на нуль, а на 2, таким чином уникаючи помилки. Натисніть F5 і програма завершить свою роботу без помилок.

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

Колекція Sheets.Дана колекція представляє собою набір аркушів (Sheets) у книзі (Workbooks). Давайте подивимось, які дії ми можемо робити над аркушами. Наприклад, як порахувати кількість аркушів в книзі?

Спочатку спробуємо узнати скільки аркушів має наша книга:

1. Sub Test() ' moonexcel.com.ua

2. MsgBox (Str(Application.Workbooks.Item("Test.xls").Sheets.Count))

3. End Sub

Даним кодом ми визвали повідомлення на екран (MsgBox), яке відобразило кількість аркушів (Sheets.Count) в книзі (Workbooks) "Test.xls".

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

Як додати аркуш в книгу.В колекції аркушів також є можливість добавляти свої листи, для цього існує метод Add. Цей метод має 4 параметри Add(Before, After, Count, Type). Всі ці параметри необов'язкові. Перші два відповідають за місце вставки аркуша. Далі, кількість аркушів, що вставляються Count і тип аркуша Type. Типи можуть бути, наприклад, такі xlWorkSheet для розрахункового листа та xlChart для діаграми. Якщо місце розташування не вказувати, то лист буде вставлятися відносно поточного аркуша.

1. Sub Test() 'moonexcel.com.ua

2. Sheets.Add After:=Worksheets("Аркуш3"). Count:=4

3. End Sub

Таким чином ми вставили 4 аркуші (Count:=4) після листа "Аркуш3". Також можна вставити лист в самий кінець книги:

1. Sub Test() 'moonexcel.com.ua

2. Worksheets.Add

3. ActiveSheet.Move After:=Sheets (ActiveWorkbook.Sheets. Count)

4. End Sub

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

1. Sub Test() 'moonexcel.com.ua

2. ActiveWorkbook.Sheets("Аркуш3").Visible = False

3. End Sub

Робота з діапазонами (Range).Розглянемо яким чином ми можемо працювати з діапазоном. Для цього в Excel існує об'єкт Range, що включає в себе як діапазони комірок, так і одну окрему комірку. Давайте подивимось, які дії ми можемо робити над діапазоном.

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

1. Sub Test2() 'moonexcel.com.ua

2. Dim cur_range As Range 'об'явимо змінну типу Range

3. Set cur_range = Selection 'об'єкт Range включає виділений діапазон

4. 'відобразимо адресу діапазону, кількістьть колонок та рядків у вікні Immediate

5. Debug.Print cur_range.Address

6. Debug.Print cur_range.Columns.Count

7. Debug.Print cur_range.Rows.Count

8. End Sub

Даним кодом ми присвоїли нашому діапазону cur_range виділені комірки. Далі, за допомогою функції Debug.Print відобразили параметри діапазону у вікні попереднього огляду значень Immediate.

Бачимо, що адреса діапазону $C$1:$E$5, кількістьть колонок - 3, кількість рядків - 5.

Виділення діапазону за допомогою .UsedRange.Розглянемо як можна виділити наш діапазон іншим способом. Для цього скористаємось .UsedRange.

1. Sub Test() 'moonexcel.com.ua

2. Dim cur_range As Range

3. Set cur_range = ActiveSheet.UsedRange

4. Debug.Print cur_range.Address

5. End Sub

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

Властивості (Properties). Напишемо VBA код, щоби керувати вмістом комірок, самими комірками та листами.

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

1. Sub Properties() 'moonexcel.com.ua

2. Range ("A1")

3. End Sub

Ми звернулися до комірки A1. Тепер спробуємо керувати даною коміркою. Щоби побачити, що ми можемо зробити, додамо крапку після Range ("A1").

Виберіть значення Value та натисніть Tab. Отримаємо такий код:

1. Sub Properties() 'moonexcel.com.ua

2. Range ("A1").Value

3. End Sub

Значення Value відображає вміст комірки.

Тепер запишемо значення 35 в комірку A1:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Value = 35

3. 'Значення комірки A1 дорівнює 35

4. End Sub

Спробуємо тепер записати текст в комірку (коли присвоюємо текстове значення, його потрібно брати в подвійні лапки ""):

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Value = "Тут є якийсь текст"

3. End Sub

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

Варіант 1. Звертаємось по назві листа - Sheets("Sheet2").

1. Sub properties() 'moonexcel.com.ua

2. Sheets("Sheet2").Range("A1").Value = "Тут є якийсь текст"

3. 'Означає: відкрити лист 2, вибрати комірку A1 та в її значення записати текст

4. End Sub

Варіант 2. Звертаємось не по назві листа, а по його порядковому номеру - Sheets(2).

1. Sub properties() 'moonexcel.com.ua

2. Sheets(2).Range("A1").Value = "Тут є якийсь текст"

3. End Sub

Аналогічно, якщо ми хочемо звернутися до комірки в іншій книзі, нам потрібно на початоку коду прописати назву книги:

1. Sub properties() 'moonexcel.com.ua

2. Workbooks("Book2.xlsx").Sheets("Sheet2").Range("A1").Value = "Тут є якийсь текст"

3. End Sub

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

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Value = 35

3. Range("A1") = 35

4. End Sub

2. Видалення значень.

Видалимо значення 35 з комірки A1, яке ми записали раніше:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Clear

3. 'Означає: вибрати комірку A1 та очистити її

4. End Sub

3. Форматування значень.

Якщо ви виберете значення Font, то з'явиться перелік властивостей, які ви зможете застосувати до комірки:

Форматування: зміна розміру тексту

Присвоїмо комірці значення 35 та зменшимо розмір шрифта до 8:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1") = 35

3. Range("A1").Font.Size = 8

4. End Sub

Форматування: зробити текст жирним

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Font.Bold = True

3. End Sub

Забрати виділення жирним:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Font.Bold = False

3. End Sub

Форматування: зробити текст похилим:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Font.Italic = True

3. End Sub

4.

Форматування: підкреслити текст:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Font.Underline = True

3. End Sub

Форматування: встановити тип шрифту:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Font.Name = "Arial"

3. End Sub

Форматування: зафарбувати комірку:

1. Sub properties() 'moonexcel.com.ua

2. Range("A1").Interior.ColorIndex = 6

3. End Sub

2.4 Приклад макроса ……………………………………………... 15

2.4 Приклад макроса

Приклад 2.1. Напишемо процедуру, що виконує такі дії:

1) Заносить в клітину А1 текст «Кількість одиниць», в клітину В1 текст «Вартість одиниці», в клітину С1 текст «Ціна одиниці», в клітину D1 текст «Загальна вартість»;

2) У клітину А2 записує число 1, в клітину А3 число 2 і потім заповнює клітинии від А4 до А16 значеннями від 3 до 15;

3) У клітини від В2 до В16 записує випадкові числа з інтервалу від 0 до100 і округляє їх до цілого;

4) Копіює вміст клітин В2:В16 у буфер, а потім вставляє з буфера в діапазон С2:С16 тільки значення з клітин В2:В16;

5) У клітину D2 записує формулу, що перемножує числа з клітин C2 і А2 і копіює цю формулу в діапазон D3:D16;

6) Проводить межу під діапазоном А16:D16 подвійною тонкою лінією.

7) У клітину D18 поміщає формулу, що сумує усі числа зі стовпчика D.

8) У клітину C19 записує формулу, що обчислює мінімальне значення з діапазону C2:C16.

9) Знаходить у діапазоні С2:С16 клітину із мінімальним значенням і виділяє її кольором.

10) Сортує список за значеннями третього стовпчика.

Sub Пример21()

Range("A1"). Value = "Кількість одиниць"

Range("B1"). Value = "Вартість одиниці"

Range(“C1”),Value= ”Ціна одиниці”

Range("D1"). Value = "Загальна вартість"

Range("A1:C1"). Select

Selection. Columns. AutoFit ‘ автопідбір ширини стовпчиків

Range("A2"). Value = 1

Range("A3"). Value = 2

Range("A2:A3"). AutoFill Destination:=Range("A2:A16"),

Range("B2:B16"). Formula = "=INT(RAND()*100)"

Range(“B2:B16”). Copy

Range("C2"). PasteSpecial xlPasteValues

Range("D2"). Formula = "=A2*C2"

Range("D2"). Copy Destination:=Range("D3:D16")

Range("A16:D16"). Borders(xlEdgeBottom). LineStyle = xlDouble

Range("D18"). Formula = "=SUM(D2:D16)"

Range("C19"). Formula = "=MIN(C2:C16)"

Range("C2:C16"). Find(Range("C19"). Value, , xlValues, xlWhole). Select

ActiveCell. Interior. ColorIndex = 4

Range("A2:D16"). Sort key1:=Range("C1")

End Sub

3 Мова програмування VBA (Visual Basic for Application). ……

• ⇐ Назад
• 12