В този урок ще научим за функцията Excel VBA
1) Какво е Visual Basic в Excel?
2) Как да използвам VBA в Excel?
3) Как да създадете дефинирана от потребителя функция?
4) Как да напиша макрос?
Как да напиша VBA код
Excel предоставя на потребителя голяма колекция от готови функции, повече от достатъчна, за да задоволи обикновения потребител. Много други могат да бъдат добавени чрез инсталиране на различните налични добавки. Повечето изчисления могат да бъдат постигнати с предоставеното, но не след дълго се окажете, че желаете да има функция, която е свършила определена работа и не можете да намерите нищо подходящо в списъка. Имате нужда от СДС. UDF (User Defined Function) е просто функция, която сами създавате с VBA. UDF често се наричат „персонализирани функции“. UDF може да остане в кодов модул, прикрепен към работна книга, като в този случай винаги ще бъде достъпен, когато тази работна книга е отворена. Като алтернатива можете да създадете своя собствена добавка, съдържаща една или повече функции, които можете да инсталирате в Excel точно като търговска добавка. UDF могат да бъдат достъпни и чрез кодови модули. Често UDF са създадени от разработчиците, за да работят единствено в рамките на кода на VBA процедура и потребителят никога не знае за тяхното съществуване. Както всяка функция, UDF може да бъде толкова прост или толкова сложен, колкото искате. Нека започнем с един лесен …
Функция за изчисляване на площта на правоъгълник
Да, знам, че можеш да направиш това в главата си! Концепцията е много проста, така че можете да се концентрирате върху техниката. Да предположим, че имате нужда от функция за изчисляване на площта на правоъгълник. Преглеждате колекцията от функции на Excel, но няма една подходяща. Това е изчислението, което трябва да се направи:
ПЛОЩ = ДЪЛЖИНА x ШИРИНА
Отворете нова работна книга и след това отворете редактора на Visual Basic (Инструменти> Макро> Редактор на Visual Basic или ALT+F11).
Ще ви е необходим модул, в който да напишете вашата функция, така че изберете Вмъкване> Модул. В празния тип модул: Функционална област и натиснете ENTERРедакторът на Visual Basic попълва реда вместо вас и добавя ред за крайна функция, сякаш създавате подпрограма. Досега изглежда така …
Функционална област () Крайна функция
Поставете курсора между скобите след „Area“. Ако някога сте се чудили за какво са скобите, предстои да разберете! Ще посочим "аргументите", които нашата функция ще вземе (an аргумент е информация, необходима за извършване на изчислението). Тип Дължина като двойна, ширина като двойна и щракнете върху празния ред отдолу. Обърнете внимание, че докато пишете, се появява поле за превъртане, в което са изброени всички неща, подходящи за това, което пишете.
Тази функция се нарича Членове на автоматичен списък. Ако не се появи или е изключен (включете го на Инструменти> Опции> Редактор) или може би сте допуснали грешка при въвеждане по -рано. Това е много полезна проверка на вашия синтаксис. Намерете необходимия елемент и щракнете двукратно върху него, за да го вмъкнете във вашия код. Можете да го игнорирате и просто пишете, ако искате. Вашият код сега изглежда така …
Функционална област (Дължина като двойна, Ширина като двойна) Крайна функция
Декларирането на типа данни на аргументите не е задължително, но има смисъл. Можеше да пишеш Дължина, ширина и го остави така, но предупреждаването на Excel какъв тип данни да очаквате помага на кода ви да работи по -бързо и улавя грешки при въвеждането. The двойно тип данни се отнася до число (което може да бъде много голямо) и позволява дроби. Сега за самото изчисление. В празния ред първо натиснете РАЗДЕЛ ключ за отстъпване на кода (улеснявайки четенето) и въведете Площ = Дължина * Ширина. Ето завършения код …
Функционална област (Дължина като двойна, Ширина като двойна) Област = Дължина * Крайна функция на ширината
Ще забележите още една от помощните функции на Visual Basic Editor, докато изписвате, Автоматична бърза информация…
Тук няма значение. Неговата цел е да ви помогне да напишете функции във VBA, като ви каже какви аргументи са необходими. Можете да тествате функцията си веднага. Превключете към прозореца на Excel и въведете цифри за дължина и ширина в отделни клетки. В трета клетка въведете функцията си, сякаш е една от вградените. В този пример клетка А1 съдържа дължината (17), а клетката В1 ширината (6,5). В C1 написах = площ (A1, B1) и новата функция изчислява площта (110.5) …
Понякога аргументите на функция могат да бъдат незадължителни. В този пример бихме могли да направим Ширина аргумент по избор. Да предположим, че правоъгълникът е квадрат с равни дължина и ширина. За да спестим на потребителя да въведе два аргумента, бихме могли да му позволим да въведе само дължината и функцията да използва тази стойност два пъти (т.е. умножи Дължина х Дължина). Така че функцията знае кога може да направи това, трябва да включим IF изявление да му помогне да реши. Променете кода, така че да изглежда така …
Функционална област (Дължина като двойна, Незадължителна ширина като вариант) Ако липсва (Ширина) Тогава Площ = Дължина * Дължина Друга площ = Дължина * Ширина Край Ако Крайна функция
Обърнете внимание, че типът данни за Width е променен на Вариант за допускане на нулеви стойности. Функцията вече позволява на потребителя да въведе само един аргумент, напр. = площ (A1). Инструкцията IF във функцията проверява дали аргументът Width е предоставен и съответно изчислява …
Функция за изчисляване на разхода на гориво
Обичам да проверявам разхода на гориво на колата си, така че когато купувам гориво си отбелязвам пробега и колко гориво е необходимо за пълнене на резервоара. Тук във Великобритания горивото се продава в литри. Милометърът на колата (добре, значи това е километраж) записва разстоянието в мили. И тъй като съм твърде стар и глупав, за да се променя, разбирам само MPG (мили на галон). Сега, ако мислите, че всичко това е малко тъжно, какво ще кажете за това. Когато се прибера, отварям Excel и въвеждам данните в работен лист, който изчислява MPG за мен и показва графиките на автомобила. Изчислението е броят мили, които автомобилът е изминал от последното зареждане, разделен на броя галони използвано гориво …
MPG = (MILES THIS FILL - MILES LAST FILL) / ГАЛОНИ ГОРИВО
но тъй като горивото идва в литри и има 4,554 литра в галон …
MPG = (MILES THIS FILL - MILES LAST FILL) / ЛИТРИ ГОРИВО x 4,554
Ето как написах функцията …
Функция MPG (StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / литри * 4.546 Крайна функция
и ето как изглежда на работния лист …
Не всички функции извършват математически изчисления. Ето един, който предоставя информация …
Функция, която дава името на деня
Често ме питат дали има функция за дата, която дава деня от седмицата като текст (например понеделник). Отговорът е не*, но е доста лесно да го създадете. (*Допълнение: Казах ли не? Проверете бележката по -долу, за да видите функцията, която забравих!). Excel има функцията WEEKDAY, която връща деня от седмицата като число от 1 до 7. Можете да изберете кой ден е 1, ако не ви харесва по подразбиране (неделя). В примера по -долу функцията връща "5", което случайно знам, че означава "четвъртък".
Но не искам да виждам номер, искам да видя „четвъртък“. Мога да променя изчислението, като добавя функция VLOOKUP, която се позовава на таблица някъде, съдържаща списък с числа и съответния списък с имена на дни. Или бих могъл да имам всичко самостоятелно с множество вложени IF изрази. Твърде сложно! Отговорът е персонализирана функция …
Функция DayName (InputDate As Date) Dim DayNumber As Integer DayNumber = Седмичен ден (InputDate, vbSunday) Изберете Case DayNumber Дело 1 DayName = "Неделя" Case 2 DayName = "Понеделник" Дело 3 DayName = "Вторник" Дело 4 DayName = "Сряда" Дело 5 DayName = "Четвъртък" Дело 6 DayName = "Петък" Дело 7 DayName = "Събота" Край Изберете Крайна функция
Извиках функцията си „DayName“ и тя отнема един аргумент, който наричам „InputDate“, който (разбира се) трябва да бъде дата. Ето как работи…
- Първият ред на функцията декларира променлива, наречена от мен "DayNumber", която ще бъде цяло число (т.е. цяло число).
- Следващият ред на функцията присвоява стойност на тази променлива, като използва функцията WEEKDAY на Excel. Стойността ще бъде число между 1 и 7. Въпреки че по подразбиране е 1 = неделя, все пак я включих за яснота.
- Накрая а Декларация по случая изследва стойността на променливата и връща съответния текст.
Ето как изглежда на работния лист …
Достъп до вашите персонализирани функции
Ако към работна книга е приложен модул с VBA код, който съдържа персонализирани функции, тези функции могат лесно да бъдат адресирани в същата работна книга, както е показано в горните примери. Използвате името на функцията, сякаш е една от вградените функции на Excel.
Можете също да намерите функциите, изброени в Съветника за функции (понякога наричан инструмент за поставяне на функция). Използвайте съветника, за да вмъкнете функция по нормалния начин (Вмъкване> Функция).
Превъртете надолу списъка с категории функции, за да намерите Дефиниран от потребителя и го изберете, за да видите списък с наличните UDFs …
Можете да видите, че дефинираните от потребителя функции нямат описание, освен безполезното съобщение „Няма налична помощ“, но можете да добавите кратко описание …
Уверете се, че сте в работната книга, която съдържа функциите. Отидете на Инструменти> Макрос> Макроси. Няма да виждате функциите си изброени тук, но Excel знае за тях! В Име на макроса поле в горната част на диалоговия прозорец, въведете името на функцията, след което щракнете върху диалоговия прозорец Настроики бутон. Ако бутонът е сив, или сте написали името на функцията погрешно, или сте в грешна работна книга, или тя не съществува! Това отваря друг диалогов прозорец, в който можете да въведете кратко описание на функцията. Щракнете Добре за да запазите описанието и (ето объркващия бит) щракнете Отказ за да затворите диалоговия прозорец Макро. Не забравяйте да запазите работната книга, съдържаща функцията. Следващият път, когато отидете на съветника за функции, вашият UDF ще има описание …
Подобно на макроси, потребителските функции могат да се използват във всяка друга работна книга, стига работната книга, която ги съдържа, да е отворена. Това обаче не е добра практика. Въвеждането на функцията в друга работна книга не е просто. Трябва да добавите името на работната книга на хоста към името на функцията. Това не е трудно, ако разчитате на съветника за функции, но е неумело да пишете ръчно. Съветникът за функции показва пълните имена на всички UDF в други работни книги …
Ако отворите работната книга, в която сте използвали функцията, в момент, когато работната книга, съдържаща функцията, е затворена, ще видите съобщение за грешка в клетката, в която сте използвали функцията. Excel е забравил за това! Отворете работната книга на хоста на функцията, преизчислете и отново всичко е наред. За щастие има по -добър начин.
Ако искате да напишете Потребителски функции за използване в повече от една работна книга, най -добрият метод е да създадете Excel Добави. Научете как да направите това в урока Изграждане на добавка за Excel.
Допълнение
Наистина трябва да знам по -добре! Никога, никога, никога не казвай! Като ви казах, че няма функция, която да дава името на деня, сега си спомних тази, която може. Вижте този пример …
Функцията TEXT връща стойността на клетка като текст в определен формат на число. Така че в примера, който можех да избера = ТЕКСТ (A1, "ddd") за връщане на "Чет", = ТЕКСТ (A1, "мммм") за връщане на „Септември“ и т.н. Помощта на Excel има още няколко примера за начини за използване на тази функция.
Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook.
Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайт