КомпютриСофтуер

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 for Application език, са скрити. По този начин програмирането в Excel е създаването на макроси с желания код. Благодарение на тази функция, таблицата процесор на Microsoft се саморазвива, адаптиране към изискванията на определен потребител. След като разберете как да създавате модули за писане на макроси, можете да започнете да разглеждате конкретни примери на програмите на VBA Excel. Най-добре е да започнете с най-основните кодове.

Пример 1

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

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

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

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

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

Напред:

  • Отново отидете на реда "Макроси";
  • В списъка изберете "Макро 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 2 + 3x 3 - cos (x). Трябва да създадете макрос, за да получите графиката му. Можете да направите това само с помощта на VBA цикли.

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

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

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

X1 = 1

Х2 = 10

Shag = 0.1

I = 1

Докато x1

Y = х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. Примери за програми, в които е избран по-нататъшен курс на изпълнение на алгоритъма, а не предварително зададени първоначално, най-често използват ако ... След това конструкция (за сложни случаи) Ако ... Тогава ... 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

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

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

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

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

Similar articles

 

 

 

 

Trending Now

 

 

 

 

Newest

Copyright © 2018 bg.birmiss.com. Theme powered by WordPress.