Създаване на функции по избор в Excel

Функциите по избор, като например макроси, използват програмния език за Visual Basic for Applications (VBA ). Те се различават от макросите по два съществени начина. Първо, те използват процедури за функции вместо подпроцедури. Т.е. те започват с команда Function вместо Sub и завършват с End Function вместо End Sub. Второ, те извършват изчисления, вместо да извършват действия. Някои видове команди, като например команди, които избират и форматират диапазони, са изключени от функциите по избор. В тази статия ще научите как да създавате и използвате функции по избор. За да създавате функции и макроси, работите с редактора на Visual Basic (VBE), който се отваря в нов прозорец, отделен от Excel.

Да предположим, че вашата фирма предлага отстъпка в количество от 10 процента от продажбата на продукт, при условие че поръчката е за повече от 100 бройки. В следващите абзаци ще демонстрираме функция за изчисляване на тази отстъпка.

Примерът по-долу показва формуляр за поръчка, който показва всеки елемент, количество, цена, отстъпка (ако има такива) и получената разширена цена.

За да създадете потребителска функция DISCOUNT в тази работна книга, изпълнете следните стъпки:

1. Натиснете Alt+F11 , за да отворите редактора на Visual Basic (на Mac натиснете FN+ALT+F11) и след това щракнете върху Вмъкване > модул. В дясната страна на редактора на Visual Basic се показва прозорец на нов модул.

2. Копирайте и поставете следния код в новия модул.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Сега сте готови да използвате новата функция DISCOUNT. Затворете редактора на Visual Basic, изберете клетка G7 и въведете следното:

=DISCOUNT(D7;E7)

Excel изчислява 10 процента отстъпка от 200 единици на 47,50 лв. за единица и връща 950,00 лв.

В първия ред на кода на VBA Функция DISCOUNT(количество; цена) посочихте, че функцията DISCOUNT изисква два аргумента, количество и цена. Когато извиквате функцията в клетка на работен лист, трябва да включите тези два аргумента. Във формулата =DISCOUNT(D7;E7) D7 е аргументът количество , а E7 е аргументът цена . Сега можете да копирате формулата DISCOUNT в G8:G13, за да получите резултатите, показани по-долу.

Нека разгледаме как Excel интерпретира тази процедура на функция. Когато натиснете Enter, Excel търси името DISCOUNT в текущата работна книга и открива, че това е потребителска функция във VBA модул. Имената на аргументите, оградени в скоби, количество и цена, са контейнери за стойностите, на които се базира изчислението на отстъпката.

Инструкцията If в следния блок код изследва аргумента количество и определя дали броят продадени артикули е по-голям или равен на 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Ако броят на продадените артикули е по-голям или равен на 100, VBA изпълнява следната декларация, която умножава стойността на количеството по стойността на цената и след това умножава резултата по 0,1:

Discount = quantity * price * 0.1

Резултатът се съхранява като променливата "Отстъпка". Vba команда, която съхранява стойност в променлива, се нарича команда за присвояване , защото изчислява израза от дясната страна на знака за равенство и присвоява резултата на името на променливата отляво. Тъй като променливата Discount има същото име като процедурата на функцията, стойността, съхранена в променливата, се връща във формулата в работния лист, която се нарича функция DISCOUNT.

Ако количество е по-малко от 100, VBA изпълнява следната команда:

Discount = 0

И накрая, следната команда закръглява стойността, зададена на променливата Discount , до две цифри след десетичния знак:

Discount = Application.Round(Discount, 2)

VBA няма функция ROUND, но Excel има. Следователно, за да използвате ROUND в тази команда, казвате на VBA да търси round метода (функцията) в обекта Application (Excel). Можете да направите това, като добавите думата Приложение преди думата Закръгляване. Използвайте този синтаксис всеки път, когато имате нужда от достъп до функция на Excel от модул на VBA.

Потребителската функция трябва да започва с инструкция за функция и да завършва с команда End Function. Освен името на функцията, командата Function обикновено задава един или повече аргументи. Можете обаче да създадете функция без аргументи. Excel включва няколко вградени функции – например RAND и NOW – които не използват аргументи.

След командата Function процедурата за функция включва една или повече VBA команди, които вземат решения и извършват изчисления с помощта на аргументите, подадени на функцията. И накрая, някъде в процедурата за функция трябва да включите команда, която присвоява стойност на променлива със същото име като функцията. Тази стойност се връща във формулата, която извиква функцията.

Броят на ключовите думи на VBA, които можете да използвате във функции по избор, е по-малък от числото, което можете да използвате в макроси. Потребителските функции не могат да правят нищо друго, освен да връщат стойност във формула в работен лист или в израз, използван в друг VBA макрос или функция. Например функциите по избор не могат да преоразмеряват прозорци, да редактират формула в клетка или да променят опциите за шрифт, цвят или шарка за текста в клетка. Ако включите код на "действие" от този вид в процедура за функция, функцията връща #VALUE! грешка.

Едното действие, което процедурата за функция може да извършва (освен извършването на изчисления), показва диалогов прозорец. Можете да използвате команда InputBox в потребителска функция като средство за въвеждане от потребителя, който изпълнява функцията. Можете да използвате команда MsgBox като средство за предаване на информация към потребителя. Можете също да използвате диалогови прозорци по избор или потребителски формуляр, но това е тема извън обхвата на това въведение.

Дори простите макроси и персонализираните функции могат да бъдат трудни за четене. Можете да ги направите по-лесни за разбиране, като въведете пояснителен текст под формата на коментари. Добавяте коментари, като поставите пред обяснителния текст апостроф. Примерът по-долу например показва функцията DISCOUNT с коментари. Добавянето на коментари като тези улеснява вас или другите да поддържате кода на VBA с течение на времето. Ако трябва да направите промяна в кода в бъдеще, ще имате по-лесно време да разберете какво сте направили първоначално.

Апострофът казва на Excel да игнорира всичко надясно на същия ред, така че можете да създадете коментари или в редовете сами, или от дясната страна на редовете, съдържащи VBA код. Можете да започнете сравнително дълъг блок с код с коментар, който обяснява общата му цел, и след това да използвате вградени коментари, за да документирате отделни твърдения.

Друг начин да документирате вашите макроси и функции по избор е да им дадете описателни имена. Например вместо да наименуването на макрос Етикети, можете да го наречете MonthLabels , за да опишете по-конкретно целта, която служи макросът. Използването на описателни имена за макроси и функции по избор е особено полезно, когато сте създали много процедури, особено ако създавате процедури, които имат сходни, но не идентични цели.

Начинът на документиране на вашите макроси и функции по избор е въпрос на лично предпочитание. Важното е да възприемете някакъв метод на документация и да го използвате съгласувано.

За да използвате потребителска функция, работната книга, съдържаща модула, в който сте създали функцията, трябва да бъде отворена. Ако тази работна книга не е отворена, получавате #NAME? при опит за използване на функцията. Ако препращате към функцията в друга работна книга, трябва да поставите пред името на функцията името на работната книга, в която се намира функцията. Ако например създавате функция, наречена DISCOUNT, в работна книга, наречена Personal.xlsb, и извикате тази функция от друга работна книга, трябва да въведете =personal.xlsb!discount(), а не просто =discount().

Можете да си спестите някои натискания на клавиши (и възможни грешки при въвеждане), като изберете вашите персонализирани функции от диалоговия прозорец Вмъкване на функция. Вашите потребителски функции се показват в категорията Дефинирани от потребителя:

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

След като изпълните тези стъпки, вашите персонализирани функции ще бъдат достъпни всеки път, когато изпълнявате Excel. Ако искате да добавите към библиотеката с функции, се върнете към редактора на Visual Basic. Ако погледнете в project Explorer на редактора на Visual Basic под заглавието VBAProject, ще видите модул, кръстен на файла на добавката. Добавката ви ще има разширение .xlam.

Двукратното щракване върху този модул в Project Explorer води до показване на кода на функцията от редактора на Visual Basic. За да добавите нова функция, поставете точката на вмъкване след командата Крайна функция, която прекратява последната функция в прозореца "Код", и започнете да въвеждате. Можете да създадете толкова функции, колкото ви трябват, по този начин и те винаги ще са налични в категорията Дефинирани от потребителя в диалоговия прозорец Вмъкване на функция .

Това съдържание е създадено първоначално от Марк Додж и Крейг Стинсън като част от тяхната книга Microsoft Office Excel 2007 Inside Out. След това е актуализирано да се прилага и за по-нови версии на Excel.