Използване на функции на работен лист в макрос - Примери за VBA код

Има много начини за използване на функции във VBA. VBA се зарежда с много вградени функции. Можете дори да създадете свои собствени функции (UDF). Можете обаче да използвате много от функциите на Excel във VBA, като използвате Application.WorksheetFunction.

Как да използвате функциите на работен лист във VBA

За достъп до функция на Excel във VBA добавете Application.WorksheetFunction пред функцията, която искате да извикате. В примера по -долу ще извикаме максималната функция на Excel:

12	Затъмнете максималната стойност толкова дългоmaxvalue = Application.WorksheetFunction.Max (Range ("a1"). Value, Range ("a2"). Value)

Синтаксисът на функциите е един и същ, но ще въведете аргументите на функцията точно както бихте направили всяка друга функция VBA.

Обърнете внимание, че синтаксисът на функцията Max се появява, когато въвеждате (подобно на функциите VBA):

Метод на работен лист

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

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

Приложение.Функция на работен лист срещу приложение

Всъщност има два начина за достъп до тези функции:

Application.WorksheetFunction (както се вижда по -горе):

1	maxvalue = Application.WorksheetFunction.Max (Range ("a1"). Value, Range ("a2"). Value)

или можете да пропуснете функцията на работния лист

1	maxvalue = Application.Max (Range ("a1"). Стойност, Range ("a2"). Value)

За съжаление, пропускането на WorksheetFunction ще премахне Intellisense, който показва синтаксиса (вижте изображението по -горе). Той обаче има едно голямо потенциално предимство: Обработка на грешки.

Ако използвате Application и вашата функция генерира грешка, тя ще върне стойността на грешката. Ако използвате метода WorksheetFunction, VBA ще изхвърли грешка по време на изпълнение. Разбира се, можете да се справите с грешката на VBA, но обикновено е по -добре да избегнете грешката на първо място.

Нека разгледаме един пример, за да видим разликата:

Работа с Vlookup Работен лист Функционална грешка

Ще се опитаме да извършим Vlookup, който няма да доведе до съвпадение. Така че функцията Vlookup ще върне грешка.

Първо ще използваме метода WorksheetFunction. Забележете как VBA издава грешка:

След това пропускаме функцията на работния лист. Забележете как

След това ще пропуснем функцията WorksheetFunction. Забележете как не се извежда грешка и вместо това функцията „стойност“ съдържа стойността на грешката от Vlookup.

Списък с функции на работен лист VBA

По -долу ще намерите списък с повечето от често срещаните функции на VBA работен лист.

Функция	Описание
Логически
И	Проверява дали са изпълнени всички условия. ВЯРНО НЕВЯРНО
АКО	Ако условието е изпълнено, направете нещо, ако не, направете нещо друго.
ГРЕШКА	Ако резултатът е грешка, направете нещо друго.
ИЛИ	Проверява дали са изпълнени някакви условия. ВЯРНО НЕВЯРНО
Търсене и справки
ИЗБИРАМ	Избира стойност от списък въз основа на номера на позицията.
HLOOKUP	Потърсете стойност в първия ред и върнете стойност.
ИНДЕКС	Връща стойност въз основа на номерата на колоните и редовете.
ПОГЛЕДНИ НАГОРЕ	Търси стойности хоризонтално или вертикално.
СЪВПАДА	Търси стойност в списък и връща нейната позиция.
ТРАНСПОЗИРАНЕ	Обръща ориентацията на редица клетки.
VLOOKUP	Потърсете стойност в първата колона и върнете стойност.
Време за среща
ДАТА	Връща дата от година, месец и ден.
ДАТИВАЛ	Преобразува дата, съхранена като текст, в валидна дата
ДЕН	Връща деня като число (1-31).
ДНИ 360	Връща дни между 2 дати в 360 -дневна година.
EDATE	Връща дата, на n месеца от началната дата.
EOMONTH	Връща последния ден от месеца, датата след n месеца.
ЧАС	Връща часа като число (0-23).
МИНУТА	Връща минутата като число (0-59).
МЕСЕЦ	Връща месеца като число (1-12).
МРЕЖНИ ДНИ	Брой работни дни между 2 дати.
NETWORKDAYS.INTL	Работни дни между 2 дати, уикенди по поръчка.
СЕГА	Връща текущата дата и час.
ВТОРО	Връща втория като число (0-59)
ВРЕМЕ	Връща часа от час, минута и секунда.
ВРЕМЕННО	Преобразува времето, съхранено като текст, в валидно.
ВЕДНЕЛЕН ДЕН	Връща деня от седмицата като число (1-7).
WEEKNUM	Връща номера на седмицата за една година (1-52).
РАБОТЕН ДЕН	Датата n работни дни от датата.
ГОДИНА	Връща годината.
ГОДИНА	Връща част от годината между 2 дати.
Инженерство
ПРЕВЪРНЕТЕ	Преобразувайте числа от една единица в друга.
Финансови
FV	Изчислява бъдещата стойност.
PV	Изчислява настоящата стойност.
NPER	Изчислява общия брой периоди на плащане.
PMT	Изчислява сумата на плащането.
ОЦЕНКА	Изчислява лихвения процент.
NPV	Изчислява нетната настояща стойност.
IRR	Вътрешната норма на възвръщаемост за набор от периодични CF.
XIRR	Вътрешната норма на възвръщаемост за набор от непериодични CF.
ЦЕНА	Изчислява цената на облигация.
ИНТЕРТ	Лихвеният процент на напълно инвестирана ценна книга.
Информация
ISERR	Тествайте дали стойността на клетката е грешка, игнорира #N/A. ВЯРНО НЕВЯРНО
ГРЕШКА	Тествайте дали стойността на клетката е грешка. ВЯРНО НЕВЯРНО
НЕВИНО	Проверете дали стойността на клетката е четна. ВЯРНО НЕВЯРНО
ИСЛОГИЧЕСКИ	Тествайте дали клетката е логична (TRUE или FALSE). ВЯРНО НЕВЯРНО
ISNA	Проверете дали стойността на клетката е #N/A. ВЯРНО НЕВЯРНО
ISNONTEXT	Тествайте дали клетката не е текст (празни клетки не са текст). ВЯРНО НЕВЯРНО
ISNUMBER	Проверете дали клетката е число. ВЯРНО НЕВЯРНО
ISODD	Проверете дали стойността на клетката е нечетна. ВЯРНО НЕВЯРНО
ISTEXT	Проверете дали клетката е текст. ВЯРНО НЕВЯРНО
ТИП	Връща типа стойност в клетка.
Математика
коремни мускули	Изчислява абсолютната стойност на число.
АГРЕГАТ	Определете и извършете изчисления за база данни или списък.
ТАВАН	Закръглява число нагоре, до най -близкото определено кратно.
COS	Връща косинуса на ъгъл.
СТУПЕНИ	Преобразува радиани в градуси.
DSUM	Записи от базата данни за суми, които отговарят на определени критерии.
ДОРИ	Закръглява до най -близкото четно число.
EXP	Изчислява експоненциалната стойност за дадено число.
ФАКТ	Връща факториала.
ЕТАЖ	Закръглява число надолу до най -близкото определено кратно.
GCD	Връща най -големия общ делител.
INT	Закръглява число до най -близкото цяло число.
LCM	Връща най -малкото общо кратно.
LN	Връща естествения логаритъм на число.
ВХОД	Връща логаритъма на число към определена основа.
LOG10	Връща логаритъма на числото с основа-10.
MROUND	Закръглява число до определено кратно.
ODD	Закръглява до най -близкото нечетно цяло число.
PI	Стойността на PI.
МОЩНОСТ	Изчислява число, повишено до степен.
ПРОДУКТ	Умножава масив от числа.
КОЛИЧЕСТВО	Връща целочисления резултат от делението.
РАДИАНИ	Преобразува ъгъл в радиани.
РАНТБЕГ	Изчислява случайно число между две числа.
КРЪГЪЛ	Закръглява число до определен брой цифри.
ROUNDDOWN	Закръглява число надолу (към нула).
ЗАКРЪГЛЯМ	Закръглява число нагоре (далеч от нула).
ГРЕХ	Връща синуса на ъгъл.
ПОДБОТА	Връща обобщена статистика за поредица от данни.
SUM	Добавя числа заедно.
SUMIF	Суми, които отговарят на критерий.
SUMIFS	Суми, които отговарят на множество критерии.
СУМПРОДУКТ	Умножава масиви от числа и сумира получения масив.
TAN	Връща тангенса на ъгъл.
Статистика
СРЕДНО АРИТМЕТИЧНО	Средни числа.
СРЕДНО	Средни числа, които отговарят на критерий.
СРЕДНИ	Средни числа, които отговарят на множество критерии.
КОРЕЛ	Изчислява корелацията на две серии.
БРОЯ	Преброява клетки, които съдържат число.
COUNTA	Пребройте клетките, които не са празни.
COUNTBLANK	Преброява клетки, които са празни.
COUNTIF	Преброява клетки, които отговарят на критерий.
ПРОФИСИ	Преброява клетки, които отговарят на множество критерии.
ПРОГНОЗА	Предсказване на бъдещи y-стойности от линейната тенденция.
ЧЕСТОТА	Преброява стойности, които попадат в определени диапазони.
РАСТЕЖ	Изчислява Y стойности въз основа на експоненциален растеж.
ПРЕХВЪРЛЯНЕ	Изчислява Y прихващане за най-подходяща линия.
ГОЛЯМ	Връща k -тата най -голяма стойност.
LINEST	Връща статистически данни за линия на тренда.
МАКС	Връща най -голямото число.
МЕДИАНА	Връща медианното число.
МИН	Връща най -малкото число.
РЕЖИМ	Връща най -често срещания номер.
ПЕРЦЕНЦИЛНО	Връща k -тия персентил.
PERCENTILE.INC	Връща k -тия персентил. Където k е включително.
PERCENTILE.EXC	Връща k -тия персентил. Където k е изключителен.
КВАРТИЛЕН	Връща посочената квартилна стойност.
QUARTILE.INC	Връща посочената квартилна стойност. Приобщаващ.
QUARTILE.EXC	Връща посочената квартилна стойност. Изключителен.
РАНГ	Ранг на число в серия.
RANK.AVG	Ранг на число в серия. Средни стойности.
RANK.EQ	Ранг на число в серия. Най -висок ранг.
НАКЛОН	Изчислява наклона от линейна регресия.
МАЛКИ	Връща k -тата най -малка стойност.
СТДЕВ	Изчислява стандартното отклонение.
STDEV.P	Изчислява SD на цялото население.
СТДЕВ С.	Изчислява SD на пробата.
STDEVP	Изчислява SD на цялото население
ТЕНДЕНЦИЯ	Изчислява Y стойности въз основа на линия на тренда.
Текст
ЧИСТ	Премахва всички неразпечатващи се знаци.
ДОЛАР	Преобразува число в текст във валутен формат.
НАМИРАМ	Намира позицията на текста в клетка.
НАЛЯВО	Съкращава текст от няколко знака отляво.
LEN	Преброява броя знаци в текста.
MID	Извлича текст от средата на клетка.
ПРАВИЛНО	Преобразува текст в подходящ регистър.
ЗАМЕНЕТЕ	Заменя текста в зависимост от местоположението му.
REPT	Повтаря текста няколко пъти.
ПРАВО	Съкращава текста на няколко знака отдясно.
ТЪРСЕНЕ	Намира позицията на текста в клетка.
ЗАМЕСТНИК	Намира и заменя текст. Различаващ главни от малки букви.
ТЕКСТ	Конвертира стойност в текст с определен формат на числото.
TRIM	Премахва всички допълнителни пространства от текста.