VBA Excel: примери за програми. Макроси в Excel

Малко хора знаят, че първата версия на популярния продукт на Microsoft Excel се появи през 1985 г. Оттогава тя е претърпяла няколко модификации и е търсена от милиони потребители по целия свят. В същото време много от тях работят само с малка част от възможностите на този процесор и дори не познават как биха улеснили работата в програмните умения на Excel.

Какво представлява VBA?

Програмирането в Excel се осъществява чрез програмния език Visual Basic for Application, който първоначално е вграден в най-известния процесор от Microsoft.

По своята същност, експертите приписват сравнителната лекота на развитие. Както показва практиката, потребителите на VBA могат да овладеят дори тези, които нямат професионални умения за програмиране. Особеността на VBA е изпълнението на сценария в средата на офис приложения.

Недостатъкът на програмата са проблемите, свързани със съвместимостта на различните версии. Те се дължат на факта, че кодът на програмата VBA се отнася до функционалността, която се съдържа в новата версия на продукта, но не е в старата версия. Също така, недостатъците включват твърде висока откритост на кода за промяна от неоторизиран човек. Независимо от това, Microsoft Office, както и IBM Lotus Symphony, позволяват на потребителя да използва криптиране на първоначалния код и да зададе парола за преглеждането му.

Обекти, колекции, свойства и методи

Именно с тези концепции трябва да разберете кой ще работи в средата на VBA. На първо място, трябва да се разбере какъв е обектът. В Excel, това е лист, книга, клетка и диапазон. Тези обекти имат специална йерархия, т.е. се подчиняват взаимно.

Основната е приложението, което съответства на самата програма на Excel. След това следвайте работни книги, работни листове и също диапазон. Например, за да получите достъп до клетка А1 на конкретен лист, трябва да посочите път, който включва йерархията.

Що се отнася до понятието "колекция", тогава това е група от обекти от същия клас, които в записа изглеждат като ChartObjects. Неговите индивидуални елементи са също обекти.

Следващото понятие е свойствата. Те са необходима характеристика на всеки обект. Например, за Range, това е стойност или формула.

Методите са команди, които показват какво трябва да се направи. Когато пишете код в VBA, трябва да ги отделите от обекта за период от време. Например, както ще бъде показано по-късно, много често при програмиране в Excel, използвайте командата Клетки (1,1) .Изберете. Това означава, че трябва да изберете клетка с координати (1.1), т.е. А1.

С него Selection.ClearContents често се използва. Извършването му означава изчистване на съдържанието на избраната клетка.

Как да започнете

На първо място, трябва да създадете файл и да го запазите, като зададете име и изберете типа на "Работна книга на Excel с макроси".

След това трябва да отидете на приложението VB, просто използвайте клавишите "Alt" и "F11". Напред:

• в лентата с менюта, разположена в горната част на прозореца, кликнете върху иконата до иконата на Excel;
• изберете командата Mudule;
• освен ако кликнете върху иконата с изображението флопи диск;
• пишете, да речем, скица на кода.

Това изглежда така:

Подпрограмата ()

- Нашият код

End Sub

Имайте предвид, че редът "Нашият код" ще бъде откроен с различен цвят (зелен). Причината е в апострофа в началото на линията, което показва, че следва коментар.

Сега можете да напишете всеки код и да създадете нов инструмент за себе си в VBA Excel (вижте примерите по-долу). Разбира се, тези, които са запознати с основите на Visual Basic, ще бъдат много по-лесни. Но дори и тези, които не ги имат, ако искат, бързо могат да свикнат с това.

Макроси в Excel

За това име програми, написани в езика на Visual Basic за приложения, са скрити. По този начин програмирането в Excel е създаването на макроси с желания код. Благодарение на тази функция процесорът за електронни таблици на Microsoft се развива, като се адаптира към изискванията на конкретен потребител. След като разберете как да създадете модули за писане на макроси, можете да започнете да разглеждате конкретни примери на програмите на VBA Excel. Най-добре е да започнете с най-основните кодове.

Пример 1

Задача: напишете програма, която ще копира стойността на съдържанието на една клетка и след това ще напише на друга.

За да направите това:

• отворете раздела "Преглед";
• отидете на иконата "Макроси";
• Натиснете "Record macro";
• попълнете отворената форма.

За простота полето "Макро име" е оставено с "Macro1", а в полето "Клавишна комбинация" въведете например hh (това означава, че можете да стартирате програмата с командата "Ctrl + h"). Натиснете Enter.

Сега, когато макро записът вече е започнал, копирайте съдържанието на клетката в друга. Върнете се в оригиналната икона. Кликнете върху "Record Macro". Това действие означава края на програмата.

Напред:

• отново отидете на реда "Макроси";
• в списъка изберете "Макро 1";
• кликнете върху "Изпълнение" (същото действие се стартира, като стартирате клавишната комбинация "Ctrl + hh").

В резултат на това има действие, което е било извършено по време на записа на макроса.

Има смисъл да се види как изглежда кода. За да направите това, върнете се към реда "Макроси" и кликнете върху "Редактиране" или "Вход". В резултат на това те са в средата на VBA. Всъщност самият код на макроса е между линиите Sub Macro1 () и End Sub.

Ако копирането е извършено например от клетка А1 до клетка С1, тогава една от редовете на кода ще изглежда като Range ("C1"). В превод той изглежда като "Range (" C1 "). Изберете", с други думи, той превключва на VBA Excel в клетка C1.

Активната част от кода се прекратява с командата ActiveSheet.Paste. Това означава записване на съдържанието на избраната клетка (в този случай A1) в избраната клетка C1.

Пример 2

Цикли на VBA ви помагат да създавате различни макроси в Excel.

Цикли на VBA ви помагат да създадете различни макроси. Да предположим, че има функция y = x + x² + 3x³ - cos (x). Трябва да създадете макрос, за да получите графиката му. Можете да направите това само с помощта на VBA цикли.

За началната и крайната стойност на аргумента на функцията вземете x1 = 0 и x2 = 10. Освен това трябва да въведете постоянна стойност за стъпката за промяна на аргумента и началната стойност на брояча.

Всички примери на макросите на VBA Excel се създават, като се използва същата процедура, както по-горе. В този конкретен случай кодът изглежда така:

Подпрограми ()

x1 = 1

х2 = 10

shag = 0.1

i = 1

Докато x1 < x2 (цикълът ще бъде изпълнен, докато изразът x1 е вярен < x2)

у = х1 + х1 ^ 2 + 3 * х1 ^ 3 - Cos (х1)

Клетки (i, 1) .Value = x1 (стойността x1 се записва в клетката с координати (i, 1))

Клетки (i, 2) .Value = y (стойността на y се записва в клетката с координати (i, 2))

i = i + 1 (работи брояч) -

x1 = x1 + shag (аргументът се променя с размера на стъпката) -

контур

End Sub.

В резултат на това макрос в "Excel" получаваме две колони, първата от които съдържа стойности за x, а втората - за y.

След това те графиката на графиката по начин, който е стандартен за Excel.

Пример 3

За изпълнението на циклите във VBA Excel 2010, както и в други версии, заедно с вече дадена конструкция Do Do, се използва For.

Помислете за програмата, която ще създаде колоната. Във всяка от клетките му ще бъдат написани квадратите на съответния номер на ред. Използването на конструкцията ще ви позволи да го напишете много скоро, без да използвате брояч.

Първо трябва да създадете макрос, както е описано по-горе. След това записваме самия код. Смятаме, че се интересуваме от стойностите за 10 клетки. Кодът изглежда така.

За i = 1 до 10 Следваща

Командата се превежда в "човешки" език, като "Повторете от 1 до 10 на стъпки от една".

Ако задачата е да получите колона с квадратчета, например всички нечетни номера от диапазона от 1 до 11, тогава пишем:

За i = 1 до 10 стъпка 1 Следваща.

Тук стъпката е стъпка. В този случай той е равен на два. По подразбиране отсъствието на тази дума в цикъла означава, че стъпката е единична.

Получените резултати трябва да се съхраняват в клетки с число (i, 1). След това всеки път, когато стартирате цикъла с увеличаване на i с размера на стъпката, номерът на реда автоматично ще се увеличи. По този начин кодът ще бъде оптимизиран.

По принцип кодът ще изглежда така:

Подпрограмата ()

За i = 1 до 10 Стъпка 1 (можете да напишете просто за i = 1 до 10)

Клетки (i, 1) .Value = i ^ 2 (т.е. стойността на квадрата i) се записва в клетка (i, 1)

След това (в известен смисъл тя играе ролята на брояч и означава още един старт на цикъла)

End Sub.

Ако всичко е направено правилно, включително записването и пускането на макроса (виж инструкциите по-горе), тогава когато го наричате, всеки път получавате колона със зададения размер (в този случай състоящ се от 10 клетки).

Пример 4

В ежедневния живот много често има нужда да се вземе решение в зависимост от някакво състояние. Не можете да правите без тях в VBA Excel. Примери за програми, в които е избран по-нататъшен курс на изпълнение на алгоритъма, а не първоначално предварително определени, най-често използват конструкцията If hellip-Then (за сложни случаи) Ако hellip-Тогава hellip-END Ако.

Нека разгледаме конкретен случай. Да предположим, че трябва да създадете макрос за Excel, така че клетката с координати (1,1) да бъде написана:

1, ако аргументът е положителен -

0, ако аргументът на нулево-

-1, ако аргументът е отрицателен.

Създаването на такъв макрос за Excel се стартира по стандартния начин чрез използването на "горещите" клавиши Alt и F11. След това е написан следният код:

Подпрограмата ()

x = клетки (1, 1) .Value (тази команда определя стойността на съдържанието на клетката с координати (1, 1))

Ако x> 0 Тогава клетките (1, 1) .Value = 1

Ако x = 0 Тогава клетките (1, 1) Стойност = 0

Ако x<0 Тогава клетките (1, 1) .Value = -1

End Sub.

Остава да стартирате макроса и да получите правилната стойност за аргумента в Excel.

Функции на VBA

Както може би сте забелязали, програмирането в най-известния процесор на Майкрософт не е толкова трудно. Особено, ако научите как да използвате функциите на VBA. Общо този език за програмиране, създаден специално за писане на приложения в Excel и Word, има около 160 функции. Те могат да бъдат разделени на няколко големи групи. Това са:

• Математически функции. Прилагайки ги на аргумента, получавате стойността на косинуса, естествения логаритъм, цялата част и т.н.
• Финансови функции. Поради наличието и използването на програмиране в Excel можете да получите ефективни инструменти за счетоводство и финансово счетоводство.
• Функции за обработка на масиви. Те включват Array, IsArray-LBound-UBound.
• VBA Excel функции за низ. Това е доста голяма група. Това включва например космическите функции за създаване на низ с брой интервали, равен на цялостен аргумент, или Asc за преобразуване на знаците в ANSI код. Всички те са широко използвани и ви позволяват да работите със струни в Excel, създавайки приложения, които значително улесняват работата с тези таблици.
• Функции за конвертиране на типове данни Например CVar връща стойността на аргумента за израз, като го преобразува в тип данни на вариант.
• Функции на работа с дати. Те значително разширяват стандартните функции на Excel. Така че функцията WeekdayName връща името (пълно или частично) на деня от седмицата с номера му. Още по-полезно е таймерът. Той дава броя на секундите, които са минали от полунощ до конкретния момент от деня.
• Функции за преобразуване на цифров аргумент в различни системи с номера. Например, Окт издава осмото представяне на число.
• Форматиране на функции. Най-важният от тях е "Формат". Той връща стойност от тип Variant с израз, форматиран съгласно инструкциите, които са посочени в описанието на формата.
• и други.

Проучването на свойствата на тези функции и тяхното приложение значително ще разшири обхвата на приложение на Excel.

Пример 5

Нека се опитаме да решим по-сложни проблеми. Например:

Предвид хартиен документ от доклада за действителното ниво на разходите на предприятието. изисква:

• да разработи своя шаблонна част чрез табличен процесор "Excel";
• създайте VBA програма, която ще запише изходните данни, за да я запълни, да направи необходимите изчисления и да я запълни с подходящите шаблонни клетки.

Помислете за едно от решенията.

Създаване на шаблон

Всички действия се извършват на стандартен лист в Excel. Запазени свободни клетки за въвеждане на данни месец, година, името на фирмата-потребител, размера на разходите, тяхното ниво, оборот. Тъй като броят на компаниите (дружествата), за които е изготвен доклад, не е фиксиран, клетките за създаване на стойности въз основа на резултатите и името на специалиста не са запазени преди това. На работния лист се дава ново име. Например, "Օ tchet".

променливи

За да напишете програма за автоматично попълване на шаблона, трябва да изберете символите. Те ще се използват за променливи:

• NN- номер на текущия ред на таблицата;
• TP и TF - планиран и действителен оборот;
• SF и SP - действителния и планиран размер на разходите;
• IP и IF - планирано и действително ниво на разходите.

Обозначете едни и същи букви, но с "prefix" Itog натрупването на сумата за тази колона. Например ItogTP се отнася до колоната на таблица, озаглавена "Планиран оборот".

Решаване на проблеми чрез използване на VBA програмиране

Използвайки въведената нотация, получаваме формули за отклонения. Ако искате да изчислите в%, имате (F - P) / P * 100, а в сумата - (F - P).

Резултатите от тези изчисления могат най-добре да бъдат добавени незабавно към съответните клетки на таблицата в Excel.

За резултатите от факта и прогнозата се получава формулите ItogP = ItogP + P и ItogF = ItogF + F.

За отклонения използвайте = (ItogF - ItogP) / ItogP * 100, ако изчислението е в проценти, а в случай на обща стойност - (ItogF - ItogP).

Резултатите незабавно се записват в съответните клетки, така че няма нужда да ги зададете на променливи.

Преди да стартирате създадената програма, трябва да запазите работната книга, например, наречена "Report1.xls".

Бутонът Създаване на таблица на отчетите трябва да бъде натиснат само 1 път след въвеждането на информацията за заглавката. Трябва да знаете други правила. По-специално, бутонът "Добавяне на линия" трябва да бъде натиснат всеки път, след като въведете стойностите за всяка дейност в таблицата. След като въведете всички данни, трябва да кликнете върху бутона "Завършване" и след това да преминете към прозореца на Excel.

Сега знаете как да решите задачи за Excel, като използвате макроси. Възможността да се прилага vba excel (примери за програми, вижте по-горе) може да е необходима, за да работите в средата на най-популярния текстов редактор "Word". По-специално, чрез записване, както е показано в началото на тази статия, или като пишете код, за да създадете бутоните на менюто, чрез които много от операциите по текста може да бъде направено чрез натискане на клавишите на дълг или чрез иконата "View" и "Макрос" в раздела.